Subform Tab Control Problem

[Joshann]

This is probably simple, but I can't figure it out.

I have a table called Household that has a one-to-many relationship with a table called Individuals. The Individuals table has fields called FirstName and LastName. I have a main form based on Household. I want to have subforms in a Tab control based on the Individuals table. So when you click Page 1, it shows the data for the first person in the household, and when you click Page 2, it shows the data for the second person in the household. This would work by basing the subform on Page 1 on a query that finds the Min of FirstName, and basing the subform on Page 2 on a query that finds that Max of FirstName. However, the data in the subforms would not be updateable, because queries based on aggregate functions are not updateable.

Does anyone have any ideas as to how I can accomplish what I'd like to do?

 

[Joshann]

Thanks. O.K. Let's forget about the tabs for a minute. They're really not the issue here. If you know that there are always two and only two individuals per household, how would you create an updateable subform that shows only the first individual in the household and another updateable subform that shows only the second individual in the household?

 

[Mod]

Thanks. O.K. Let's forget about the tabs for a minute. They're really not the issue here. If you know that there are always two and only two individuals per household, how would you create an updateable subform that shows only the first individual in the household and another updateable subform that shows only the second individual in the household?

What you need is a field in tblIndividuals that shows if the record is person #1 or person #2. Then, when the household form is updated, change the recordsource for each of the subforms, like so:

Form_Subform1.RecordSource = "SELECT * FROM tblIndividuals WHERE tblIndividuals.HouseholdID = tblHousehold.ID AND tblIndividuals.Gender = "M"
Form_Subform1.Requery
Form_Subform2.RecordSource = "SELECT * FROM tblIndividuals WHERE tblIndividuals.HouseholdID = tblHousehold.ID AND tblIndividuals.Gender = "F"
Form_Subform2.Requery

My syntax might be off a bit, but this is the method I would use. Just consider the above to be pseudocode

 

[Joshann]

Don't go down this path. If you have more than one of something, you have many and your application should be structured to support it.

I understand and appreciate what you're saying, but in this instance, there will NEVER be more than two individuals.

What you need is a field in tblIndividuals that shows if the record is person #1 or person #2. Then, when the household form is updated, change the recordsource for each of the subforms, like so:

Form_Subform1.RecordSource = "SELECT * FROM tblIndividuals WHERE tblIndividuals.HouseholdID = tblHousehold.ID AND tblIndividuals.Gender = "M"
Form_Subform1.Requery
Form_Subform2.RecordSource = "SELECT * FROM tblIndividuals WHERE tblIndividuals.HouseholdID = tblHousehold.ID AND tblIndividuals.Gender = "F"
Form_Subform2.Requery

My syntax might be off a bit, but this is the method I would use. Just consider the above to be pseudocode

This would work except that there is not always one female and one male. There can be two females or two males. Do you have any other suggestions?