Auto-Populating Subforms/Keeping data visible while entering

[johnwayneoftheseas]

Hello! I've been working on creating a new database and really want to create a simple, tab-through data entry page for entering applicants for each of our programs. I'm having some trouble, however. I currently have it set up in the following way:

frmIndividuals comprises the main form. tblIndividuals is the related table, and it generates a new IndividualID for each new individual entered by the user.

Above the area where the user enters an individual's information, there is a subform (joined on IndividualID) asking for information about the program this Individual wishes to apply to. The data entry works fine, but when the user clicks into the main form to enter the Individual's information the data entered in the subform just above disappears. Could it be creating multiple IndividualIDs? If so, what is the best way to remedy this?

From there one moves on to subforms that are all joined on ApplicationID (the first subform, which doesn't seem to be auto generating the AppID field as it should be) as well as IndividualID. Is there a way I can populate all of the subforms to have the same ApplicationID, say, OnDirty?

Thank you so much!

 

[Scooterbug]

Lets backtrack for a moment. Sounds like you need to revisit your table structures. From what I can gather from reading your post, you want to record the following information:

Individuals
Programs

What this means is that you should have at least 3 tables.

tblIndividual
IndividualID (Primary Key)
FirstName
LastName
Other relavent Info

tblPrograms
ProgramID (Primary Key)
ProgramName
Other Data related to the Specific Program

tblIndividualInProgram
IndProgID (Primary Key)
IndividualID (Foreign Key)
ProgramID (Foreign Key)

What you should be doing, Once you enter the Individual's data, is using a Combo Box to select the program that the individual is participating in and storing the ProgramID and the IndividualID into the last table mentioned above. This way, you are not repeating Program data nor Individual data if they are in more than one program.

EDIT:
I did up a quick little example for you. Let me know if you have any questions.

 

[missinglinq]

Above the area where the user enters an individual's information, there is a subform (joined on IndividualID) asking for information about the program this Individual wishes to apply to. The data entry works fine, but when the user clicks into the main form to enter the Individual's information the data entered in the subform just above disappears.

You appear to have the horse before the cart! In a main form/subform scenario, which normally is used to display a one (main form record) to many (subform records) relationship, the main form record is filled first, then the subform records.

If you're filling in the subform record first, the IndividualID doesn't exist at that point, so when you move to the mainform, and a new ID is generated, there is no subform record with that ID, hence no record.

 

[johnwayneoftheseas]

Scooterbug - Thanks! I do have my tables set up in more or less that way. (I have a lot of tables and for brevity I was just mentioning a few.)

MissSingLinq - Rats. I thought that might be the case. So it IS generating multiple IndividualIDs, in all likelihood? And if I just re-order things, taht should fix my problem, correct?

I'm still stuck when it comes to the ApplicationIDs, though.