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?

-- 
Daryl S


"jassnaround" wrote:

> 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?

On Mon, 29 Mar 2010 09:36:01 -0700, jassnaround
<jassnaround@discussions.microsoft.com> wrote:

>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?

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.
-- 

             John W. Vinson [MVP]

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.

"Daryl S" wrote:

> 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?
> 
> -- 
> Daryl S
> 
> 
> "jassnaround" wrote:
> 
> > 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 don't see where the household members are stored in your table structure.  
Can you provide that?

-- 
Daryl S


"jassnaround" wrote:

> 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.
> 
> "Daryl S" wrote:
> 
> > 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?
> > 
> > -- 
> > Daryl S
> > 
> > 
> > "jassnaround" wrote:
> > 
> > > 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?

Household Members is a field in the Tbl_FedPovertyLevel table along with 
HouseholdMemberYrIncome field.

"Daryl S" wrote:

> Jassnaround -
> 
> I don't see where the household members are stored in your table structure.  
> Can you provide that?
> 
> -- 
> Daryl S
> 
> 
> "jassnaround" wrote:
> 
> > 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.
> > 
> > "Daryl S" wrote:
> > 
> > > 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?
> > > 
> > > -- 
> > > Daryl S
> > > 
> > > 
> > > "jassnaround" wrote:
> > > 
> > > > 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?

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. 

"John W. Vinson" wrote:

> On Mon, 29 Mar 2010 09:36:01 -0700, jassnaround
> <jassnaround@discussions.microsoft.com> wrote:
> 
> >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?
> 
> 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.
> -- 
> 
>              John W. Vinson [MVP]
> .
> 

On Mon, 29 Mar 2010 13:28:01 -0700, jassnaround
<jassnaround@discussions.microsoft.com> wrote:

>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. 

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!
-- 

             John W. Vinson [MVP]

Thanks so much.  Appreciate the help.

"John W. Vinson" wrote:

> On Mon, 29 Mar 2010 13:28:01 -0700, jassnaround
> <jassnaround@discussions.microsoft.com> wrote:
> 
> >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. 
> 
> 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!
> -- 
> 
>              John W. Vinson [MVP]
> .
> 

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.

-- 
Daryl S


"jassnaround" wrote:

> Household Members is a field in the Tbl_FedPovertyLevel table along with 
> HouseholdMemberYrIncome field.
> 
> "Daryl S" wrote:
> 
> > Jassnaround -
> > 
> > I don't see where the household members are stored in your table structure.  
> > Can you provide that?
> > 
> > -- 
> > Daryl S
> > 
> > 
> > "jassnaround" wrote:
> > 
> > > 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.
> > > 
> > > "Daryl S" wrote:
> > > 
> > > > 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?
> > > > 
> > > > -- 
> > > > Daryl S
> > > > 
> > > > 
> > > > "jassnaround" wrote:
> > > > 
> > > > > 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?