Subform causes Main Form to show the same record multiple times

  • Thread starter Thread starter jassnaround
  • Start date Start date
J

jassnaround

I have a form named Patients Info with a subform on it for Household Members.
The Patients Info form is created from the table: Patients and the Household
Members subform is created from the table:FedPovertyLevel. The table
Patients is related to the table FedPovertyLevel in a one to many
relationship.
The problem I have is that when a second, third, etc Household Member is
added, the button for the record selector on the main form now has to be
clicked forward by the number of household members. For Example, if only one
name is entered as a Household member, there is only one instance of the
Patient record shown when the record selector button is clicked. However, if
2 names are entered as Household Members, then the record selector button
will show the same patient 2 times.
What can I do to make it so that all Household members show up on one click
of the Patients record selector button?
 
Jassnaround -

I would suspect your relationships are not quite right. Can you post your
table structures (table names with any key and foreign key field names) and
how the two tables are related?
 
I have a form named Patients Info with a subform on it for Household Members.
The Patients Info form is created from the table: Patients and the Household
Members subform is created from the table:FedPovertyLevel. The table
Patients is related to the table FedPovertyLevel in a one to many
relationship.
The problem I have is that when a second, third, etc Household Member is
added, the button for the record selector on the main form now has to be
clicked forward by the number of household members. For Example, if only one
name is entered as a Household member, there is only one instance of the
Patient record shown when the record selector button is clicked. However, if
2 names are entered as Household Members, then the record selector button
will show the same patient 2 times.
What can I do to make it so that all Household members show up on one click
of the Patients record selector button?
Click to expand...

It sounds like you based the mainform, not on the Patients table, but on a
Query joining the Patients table to the FedPovertyLevel table (if that's in
fact the table containing household members).

Please open the form in design view and view its Properties. The first
property on the Data tab is the "Record Source". What's in that property? If
it's a query, click on the ... icon, open it in SQL view and post the SQL
here, if it's not obvious what to change.
 
Thanks. The relationships were my thought but I couldn't figure out what to
do. Here it is:
Tbl_Patients, PK = Patients_ID
Tbl_FedPovertyLevel, PK = FedPovertyLevel_ID, FK = FPLPatients_ID

Tbl_Patients is on the one side of the one-to-many relationship with
Tbl_FedPovertyLevel being on the many side (My thinking--one patient can have
several Household Members)

The tables are linked between Patients_ID and FPLPatients_ID.
 
Jassnaround -

I don't see where the household members are stored in your table structure.
Can you provide that?
 
Household Members is a field in the Tbl_FedPovertyLevel table along with
HouseholdMemberYrIncome field.
 
It says: SELECT Tbl_Patients.*, Tbl_FedPovertyLevel.TotalAnnualIncome,
Tbl_FedPovertyLevel.TotalHouseholdMembers,
Tbl_FedPovertyLevel.[PovertyLevelAmt], Tbl_FedPovertyLevel.[Qualifies?],
Tbl_FedPovertyLevel.FedPovLvlNotes FROM Tbl_Patients INNER JOIN
Tbl_FedPovertyLevel ON
Tbl_Patients.Patients_ID=Tbl_FedPovertyLevel.Patients_ID;

The FedPovertyLevel fields are the other fields from the Tbl_FedPovertyLevel
table and the query was created by Access automatically.
 
It says: SELECT Tbl_Patients.*, Tbl_FedPovertyLevel.TotalAnnualIncome,
Tbl_FedPovertyLevel.TotalHouseholdMembers,
Tbl_FedPovertyLevel.[PovertyLevelAmt], Tbl_FedPovertyLevel.[Qualifies?],
Tbl_FedPovertyLevel.FedPovLvlNotes FROM Tbl_Patients INNER JOIN
Tbl_FedPovertyLevel ON
Tbl_Patients.Patients_ID=Tbl_FedPovertyLevel.Patients_ID;

The FedPovertyLevel fields are the other fields from the Tbl_FedPovertyLevel
table and the query was created by Access automatically.
Click to expand...

Access isn't always very clever! In this case it goofed.

If you have the Tbl_Patients information on the mainform, and the fields from
Tbl_FedPovertyLevel on the subform, then the recordsource for the mainform
should be JUST Tbl_Patients, not a query; or if a query, it should not have
Tbl_FedPovertyLevel joined to it. You're using "belt and suspenders" - putting
tblFedPovertyLevel on the mainform and also on the subform. Don't!
 
Thanks so much. Appreciate the help.

John W. Vinson said:
It says: SELECT Tbl_Patients.*, Tbl_FedPovertyLevel.TotalAnnualIncome,
Tbl_FedPovertyLevel.TotalHouseholdMembers,
Tbl_FedPovertyLevel.[PovertyLevelAmt], Tbl_FedPovertyLevel.[Qualifies?],
Tbl_FedPovertyLevel.FedPovLvlNotes FROM Tbl_Patients INNER JOIN
Tbl_FedPovertyLevel ON
Tbl_Patients.Patients_ID=Tbl_FedPovertyLevel.Patients_ID;

The FedPovertyLevel fields are the other fields from the Tbl_FedPovertyLevel
table and the query was created by Access automatically.
Click to expand...

Access isn't always very clever! In this case it goofed.

If you have the Tbl_Patients information on the mainform, and the fields from
Tbl_FedPovertyLevel on the subform, then the recordsource for the mainform
should be JUST Tbl_Patients, not a query; or if a query, it should not have
Tbl_FedPovertyLevel joined to it. You're using "belt and suspenders" - putting
tblFedPovertyLevel on the mainform and also on the subform. Don't!
Click to expand...
 
Jassnaround -

First, check your data. Make sure there is only one record in the
tbl_Patients for the patient, and that all the household members in the
tbl_FedPovertyLevel table have the same FPLPatients_ID. If this is right,
then check the recordsource for the Patients form - it could be the
tbl_Patients or a query based on tbl_Patients, but it should NOT contain any
data from the tbl_FedPovertyLevel table.

If you need more help, post the record sources for both the main form and
the subform.
 
Back
Top