GoToRecord stopped working during Form_Load()

In an earlier reponse, I suggested reading up on Normalization. These are set of rules for the design of relational databases. These rules have existed, largley unchanged, since the early 1970s. So they have stood the test of time. It is those rules that drive my recommendationbs for table structure.

FK means Foreign Key (PK = Primary Key). A Foreign key is a field in a related table that identifies the associated record in the other table. You have a situation where then can be multiple PIs assigned to a Grant. Your strucutre only allows you to record the primary. But with my suggestion you have a child table lists the PIs, the GrantID is then a FK which tells you whihc Grant the PI is assigned to.

Generally you use subforms to have a single form work with multiple tables. But the only time you need to work with multiple tables is if your need to display data of related records in a single form. In some cases you might be able to use a query that joins multiple tables as the Recordsource of a form. But since, when you involved, multiple tables its usually in a 1:many relation, you want to use subforms to display the many side.

I understand Research Type is a Boolean. It shouldn't be. Right now there are only 2 choices. But what happens, in the future, if you add a third type, you have to redo your table structure. By planning for the future you make your life easier.

Thanks soooo much for your help. It seems to be working now (I just need to add the fields I left out)... but if someone sneezes it might stop working so I want to make it better. I didn't click that the reply solved the problem b/c (I guess?) it might close the thread, but I'm confident that I'm functional at this point.

I understand in the absolute sense what you're saying to do about tables, but I don't understand why you're doing it. Why is it better to do separate tables? What do the FKs mean - are those somehow used to reference records? How do I tell the forms to look at multiple tables at once? I feel like I went into the wardrobe for a coat and wound up in Narnia lol; this was not as simple as anticipated. I'm probably going to go live w/ the functional version tomorrow, but I want to upgrade into a "this is what it should've been in the first place" version next week (I'm off half of this week).

ResearchType is a boolean; it's either Research or CSA (which can loosely be defined as "not research" - I forget what it stands for).

FK = from my googling, Foreign Key, which apparently has to do with how you link tables?

PK I guess is primary key which I was originally using SIROID for? I'm confused as to how these interact and what I use these keys for.

I think that you're saying:

GrantID --> Primary Key --> Autogenerated number

FK --> has something to do with making one table know how to interact with another one.

I feel like I entered the Matrix and no one gave me my pill😅. Thanks again for all of your help!!! This community is awesome

Thanks again - you guys are so helpful! Using the combo button worked like a charm; I'm confident that I can get everything functional from here. At this point my questions are aimed at finishing it "well" instead of finishing it "functionally" since it seems to be functioning as desired... but I want it to be done well b/c I won't be here forever. My fear is someone coming behind me and gently touching my house of cards and having no idea why it all fell down lol, so I'm going to try to incorporate what you guys are saying.

..............except when it loads, I'm going to tell it to make a new record when it opens. My plan was to do that with DoCmd.GoToRecord , , acNewRec in form load.

There's no need to do that.  Just set the form's DataEntry property to True (Yes).  This property is confusingly named.  Setting it to True causes the form to open at an empty new record, and it does not show any existing records from the table.  However, you don't need two forms.  For editing records just set the form's AllowAdditions property to False (No). For adding a new record open the same form with the AcFormAdd constant as the DataMode argument:

 

Yeah that actually explains a lot.... Idk why they named it DataEntry instead of like LoadNewRecord T/F. The reason I opted for two forms is b/c when a grant submission starts, we really only have like 5 pieces of data - the grant name, the PI name, the max grant amount, the max grant years... maybe 1-2 other things depending, but it's a very small subset of the normal. I suppose I could use a popup to ask them if they were trying to update or make a new entry, then enable/hide/disable/etc. appropriately?

DoCmd.OpenForm "NameOfFormGoersHere", DataMode:=AcFormAdd

 

More fundamental is the issue which Scott has raised of the decomposition of your single table into a set of normalized tables.  You might find it helpful to take a look at a couple of the demo files in my OneDrive folder at:

This might sound flippant but it's not intended that way; I'm asking because I generally don't understand. What is the purpose of having multiple tables? I only have ~30 pieces of info, and in a given year I'll likely have ~100-150 records then start over with a table for FY2024. It seems like extra work for me to have to figure out how to go between tables and I (literally) don't understand what the utility is. That's not intended to be disrespectful at all I'm so appreciative of you guys, I just don't get what it helps. Is it b/c it's more efficient as dbs get big or something?

 

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

 

Firstly DatabaseBasics.zip, to which I referred your earlier, includes an outline of the various relationship types used in designing a relational database, and how these are reflected in the user interface by forms/subforms.  The second is Relationships.zip, which takes a more in-depth look at how relationships are built up across the database to achieve the final model.  It uses a simple medical prescriptions database as its example.

 

Another file which you might find helpful is Normalization.zip.  This explains as simply as p[possible, but no more so, the various Normal Forms which govern the structure of a relational database.  Concentrate on getting a good understanding of the first three Normal Forms to start with. 

B/c I'm at work, I haven't had time to deep-dive into your one drive but believe me I'm going to. This took way longer than expected. Thankfully the amount of time I mentally allotted and the amount of time I told my manager it would take weren't the same lol. I need this functional and populated by lunch time tomorrow if possible. The fiscal year has started and ideally we would already be using this db, but after that I'm going to figure out how this should work so that I can do rebuild it better.

..............except when it loads, I'm going to tell it to make a new record when it opens. My plan was to do that with DoCmd.GoToRecord , , acNewRec in form load.

There's no need to do that.  Just set the form's DataEntry property to True (Yes).  This property is confusingly named.  Setting it to True causes the form to open at an empty new record, and it does not show any existing records from the table.  However, you don't need two forms.  For editing records just set the form's AllowAdditions property to False (No). For adding a new record open the same form with the AcFormAdd constant as the DataMode argument:

DoCmd.OpenForm "NameOfFormGoersHere", DataMode:=AcFormAdd

More fundamental is the issue which Scott has raised of the decomposition of your single table into a set of normalized tables.  You might find it helpful to take a look at a couple of the demo files in my OneDrive folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Firstly DatabaseBasics.zip, to which I referred your earlier, includes an outline of the various relationship types used in designing a relational database, and how these are reflected in the user interface by forms/subforms.  The second is Relationships.zip, which takes a more in-depth look at how relationships are built up across the database to achieve the final model.  It uses a simple medical prescriptions database as its example.

Another file which you might find helpful is Normalization.zip.  This explains as simply as p[possible, but no more so, the various Normal Forms which govern the structure of a relational database.  Concentrate on getting a good understanding of the first three Normal Forms to start with.