GoToRecord stopped working during Form_Load()

Have some free time so starting the do-over now using these tables. Should all FKs be integers and all PKs be autonumbered?

No, you misunderstood me. Ken and I are not saying to not use the Record number to filter records, but you select that record number from a combobox.

If you create a combobox with the following properties you can use it to select the RecrodID:

RowSource: SELECT SIROID, Professor, DueDate FROM table ORDER BY Professor, DueDate;

Bound column: 1

Column Count: 3

Column Widths: 0";2";1"

This will display the professor and due date but when selected, will return the SIROID.

Another plce you misundertood Is with the Expr1000, this means the feild appears twoce in the recordSOURCE, not thge recordSET. The Recordsource ius where the form piulls the set of records from. If you can post the Recordsource as I asked we can point out the error.

Finally you don't have fields on a form, you have controls that may or may not be bound to a field in a table. Each control has a Name property that is how you refenrece the control in code. And no, that code does not go in the Form Load event. It would go in the After update event of the combobox.

This got lost in the sauce somehow. Is recordsource not table1 then? I'm confused.

The problem I'm seeing is that my fields aren't necessarily unique. For example, grant entity 1 and grant entity 2 may name their grants the same thing. I think it would be rare, but would the workaround here be to make some trivial difference and put it into the second one?

As Scott has pointed out, duplicating grant names is not a problem.  Each row in a Grants table is identified by a GrantID or similar primary key column of a numeric data type, usually an autonumber for convenience, and is referenced in other tables by a GrantID foreign key column, again of a numeric data type, but not an autonumber this time.

Remember the contacts form in my DatabaseBasics.zip demo to which I referred you earlier, in which the name of each contact was concatenated with other data?  In the Contacts table there are two Jennifer Blacks.  In the combo box in the form header they are differentiated as:

Black, Jennifer - Staffordshire, United Kingdom

Black, Jennifer - Texas, United States

The BoundColumn of the combo box, and hence its value, is in a hidden ContactID column in which the two values are 1 and 5 respectively.  So when the value in a foreign key column in another table is 1 we are dealing with English Jennifer, and when it's 5 we are dealing with American Jennifer.

In the same form you'll see that there are two cities named Paris.  These are differentiated in the same way, so that from the CityID numeric foreign key column we know whether we are dealing with Paris, Île-de-France, France or Paris, Texas, USA.  In the form the Region and Country controls show the correct values by basing the form on a query which joins the Contacts, Cities, Regions and Countries tables.  When a city is selected in the city combo box, the region and country in which the selected city is located show automatically in the other two controls, which are read-only.

OK, First the reason why the PI is blank is because its unbound. That is clear in your image.

A Bound control is one who's controlsource is set to a field in the Form's Recordsource. This means that the PI will NOT be saved in the record. Generally Unbound controls on a Bound form are used for search criteria and temporary values.

Fields do not have to be unique from record to record. If different grantees want to use the same name, that's fine. But you don't want to keep repeating a PI's name. You want to select the PI from a list and store the PI_ID instead of the name. One major reason for this is what happens if user a enters John Jones as the PI and user 2 enters Johnny Jones. If you wanted to see all grants for the same PI you couldn't because of the differences. But, by using a table of PIs and select the PI from a combobox and stored the ID, its easy to group or filter records for a specific PI.

"What I'm understanding (which is probably wrong lol): Basically I'm pulling the same data but it's coming from different tables. The utility of that is that it minimizes input error and inconsistencies via superior organization. Also, as time goes by, the tables could get increasingly complex. I can get away w/ what I'm doing right now b/c I'm assuming max 100-150 records and a limited # of variables, but as the university grows a manually manageable 100-150 records could evolve into 1500 and my lack adequate organization from the start would make it into a logistical nightmare. By properly organizing it like you're saying now, in 5 years the table will still work as intended, whereas my hodge-podge "screw it - it works" version works b/c my dbase is very small and manageable, but will create an avoidable nightmare in the future as we grow."

In the immortal words of Prof Henry Higgins; By Geoger I think she's (he's) got it!

That's actually one of the best synopsis of this issue I've seen.