GoToRecord stopped working during Form_Load()

So I've finally gotten around to diving into your stuff... but it keeps telling me, "Sorry, there was a problem downloading the file every time I try to download it.

By the same reasoning, I probably need to make another table for subsponsors

No, ONE sponsors table. I'm guessing a sponsor can also be a subsponsor and vice versa. So whether a sponsor is a main or sub is identified where they are assigned. So one table.

I'm not really sure what we'd need a PK for though

EVERY table needs a PK. You need something that uniquely identifies a record. This can be a single field or combination (though I'm not a fan of composite keys) or it can be a surrogate key (like an autonumber), but every table must have one.

I will make it magically know from GrantID relationship the PIIDs of researchers

Its not magic, its called a many to many relationship. A Grant can have multiple PIs and a PI can be associated with multiple Grants. So you use, what's called, a Junction table to model that relationship. That's what tblPIAssigned is.

pull quarters from submission dates

How about a table like this:

tblMth2Qtr

MonthID (PK)

FiscalQtr

This table has 12 records. One for each month with the corresponding fiscal quarter. Then all you need is either a DLookup or Join in a querter to pull the Quarter.

Yes it was me who suggested tblPIAssigned. And you figured out why: to suppory multiple PIs assigned to a Grant.

What I would suggest is a main form/subform. The mainform Bound to the Grants table and the Subform bound to tblPIAssigned and linked on GrantID. The user can then enter the PI data in to subfoirm whihc can have multiple records.

Otherwise your methodology is looking good.

You DO want to make things as easy as possible for your users. This means, among other things, eliminating repetive entry of data. If sponsors can be associated with multiple grants, then you want a Sponsors table and a foreign key in the Grants table for the SponsorID. Or, if a grant can have multiple sponsors, then you want a separate table to list the multiple sponsors (similar to PIs).

Do Not make GrantID a PK in tblAward. You shopuld have an AwardID Autonumber as the PK. However if there can only be ONE award associated with a Grant then the GrantID becomes a foreign key and is set for No Duplicates.

Yeah, Sponsor table makes sense. I was going to try and avoid it because there will be so many sponsors, but in retrospect, the same reasoning applied elsewhere dictates that a sponsor table is necessary. By the same reasoning, I probably need to make another table for subsponsors... I'm starting to understand why there are full-time database people. I can't imagine handling the customer database for Apple. My head would literally explode. Huge mess. All two of my brain cells scattered around the room. Ok so new algorithm tweak:

1. Grant created
2. User enters PIs on a subform (will figure out subforms later; I at least want to get tables and maybe relationships done today)
   1. Subforms is going off of tblPIAssigned. Tomorrow's problem.
   2. PIAssignedID will be PK.... I'm not really sure what we'd need a PK for though. Maybe it's just best practice to always have one.
   3. Each individual record in tblPIAssigned will have GrantID as FK
3. When pulling grant info, somehow I will make it magically know from GrantID relationship the PIIDs of researchers. This relationship magic is tomorrow's problem. If it goes anything like my typical relationships, it will indeed require some form of magic to work.

Actually things are starting to make sense now, I just need to figure out and understand relationships.

Side note, this is what I tried to do to pull quarters from submission dates. It looks like it works but is this a reasonable way to do it? I know there's a function that pulls quarters, but as I understand it the function pulls calendar year quarters and I need fiscal year quarters. I KNOW THERE IS A BETTER WAY TO DO THIS lol it's convoluted and made my brain hurt but I couldn't find one on the internet that would actually work when I typed it in so I did a ridiculous nested iif statement.

Field1 is a calculated field:

month(DateSubmitted)

I didn't bother naming it properly b/c it didn't occur to me that this would actually work for the Quarter calculated field:

IIf([Field1]>=6 And [Field1]<9,1,IIf([field1]>=9 And [Field1]<12,2,IIf([Field1]=12 Or [field1]=1 Or [field1]=2,3,IIf([field1]>=3 And [field1]<6,4,[field1]))))

Field1 is a calculated field:

month(DateSubmitted)

This ridiculous eyesore seems to be working properly, but I feel like you're going to say, "Lol why didn't you <insert simple, common sense solution that didn't occur to me>." I probably should've asked before researching this for 30 minutes and giving up and trying to code it for another 20. Side note - absolutely nothing that I do makes the Between function work within calculated field formulas and I have no idea why. That's half of why that line is so convoluted.

Yes it was me who suggested tblPIAssigned. And you figured out why: to suppory multiple PIs assigned to a Grant.

What I would suggest is a main form/subform. The mainform Bound to the Grants table and the Subform bound to tblPIAssigned and linked on GrantID. The user can then enter the PI data in to subfoirm whihc can have multiple records.

Otherwise your methodology is looking good.

You DO want to make things as easy as possible for your users. This means, among other things, eliminating repetive entry of data. If sponsors can be associated with multiple grants, then you want a Sponsors table and a foreign key in the Grants table for the SponsorID. Or, if a grant can have multiple sponsors, then you want a separate table to list the multiple sponsors (similar to PIs).

Do Not make GrantID a PK in tblAward. You shopuld have an AwardID Autonumber as the PK. However if there can only be ONE award associated with a Grant then the GrantID becomes a foreign key and is set for No Duplicates.