List box in main form AND in subform

[Guest]

I am trying to create a form to easily enter and update family information.
The main form comes from the family table (last name). The main form has a
working list box.

The subform brings the family members from the familyMembers table. I want
another list box in the subform to easily view all family members and update
them when needed.

When I select a family from the main form, the subform shows me family
members for just that one family, but the list box in the subform shows me
all family members from the table. It doesn't narow down to the family I'm
working on.

Do I to run the entre form off a query and not directly from the tables?
Help please. I'm not very good at VBA and rather do it with controls and
queries, if possible.

 

[Rick Brandt]

I am trying to create a form to easily enter and update family
information. The main form comes from the family table (last name).
The main form has a working list box.

The subform brings the family members from the familyMembers table.
I want another list box in the subform to easily view all family
members and update them when needed.

When I select a family from the main form, the subform shows me family
members for just that one family, but the list box in the subform
shows me all family members from the table. It doesn't narow down to
the family I'm working on.

Do I to run the entre form off a query and not directly from the
tables? Help please. I'm not very good at VBA and rather do it with
controls and queries, if possible.

The MasterLink and ChildLink property settings of the subform control are what
cause it to stay automatically synchronized with the parent form. ListBoxes
have their own RowSources that are not affected by the MasterLink and ChildLink
properties.

What you need to do is modify the RowSource query of the subform's ListBox. The
modification involves adding a criteria for the Family field that references the
appropriate control on the parent form. Then it will stay synchronized just
like the subform does. The reference would look like...

Forms!NameOfParentForm!NameOfFamilyControl

 

[John Vinson]

Do I to run the entre form off a query and not directly from the tables?
Help please. I'm not very good at VBA and rather do it with controls and
queries, if possible.

Base the Subform listbox on a Query referencing the FamilyID from the
mainform, with a criterion like

=[Forms]![mainformname]![controlname]

You *might* need one single line of VBA to requery the listbox after
updating the mainform's listbox, but since it's on a subform I'm
guessing you won't need even that.

John W. Vinson[MVP]

 

[Guest]

I will try this. I made a boo boo while trying other things on my own and
I've messed up the parent list box. I have to fix that first. I'll let you
know how it goes.

Do I to run the entre form off a query and not directly from the tables?
Help please. I'm not very good at VBA and rather do it with controls and
queries, if possible.

Base the Subform listbox on a Query referencing the FamilyID from the
mainform, with a criterion like

=[Forms]![mainformname]![controlname]

You *might* need one single line of VBA to requery the listbox after
updating the mainform's listbox, but since it's on a subform I'm
guessing you won't need even that.

John W. Vinson[MVP]

 

[Guest]

OK guys. Itried this and it's weird. The main form works all by itself as
it did before. The subform works all by itself it I open it directly. but if
I open the main form andthen switch to the subform, the list box in the
subform is emtpy.

I forgot to mention that the subform is inside a tab control in the main
form. I don't think this has anything to do with it, but just in case. Can
you think of somthing else? I'll keep trying other things and see if anyone
else has other ideas.

Do I to run the entre form off a query and not directly from the tables?
Help please. I'm not very good at VBA and rather do it with controls and
queries, if possible.

Base the Subform listbox on a Query referencing the FamilyID from the
mainform, with a criterion like

=[Forms]![mainformname]![controlname]

You *might* need one single line of VBA to requery the listbox after
updating the mainform's listbox, but since it's on a subform I'm
guessing you won't need even that.

John W. Vinson[MVP]

 

[John Vinson]

OK guys. Itried this and it's weird. The main form works all by itself as
it did before. The subform works all by itself it I open it directly. but if
I open the main form andthen switch to the subform, the list box in the
subform is emtpy.

I forgot to mention that the subform is inside a tab control in the main
form. I don't think this has anything to do with it, but just in case. Can
you think of somthing else? I'll keep trying other things and see if anyone
else has other ideas.

I'm still not sure I understand your setup. What are the Name and
Recordsource properties of the mainform and subform? What is the
Rowsource property of the listbox? Is it on the subform? And what
specific code do you have to correlate the two listboxes?

John W. Vinson[MVP]

 

[Guest]

Thanks for replying.

The main form is called frmFamilies. The Data Source is the table
tblFamilies. The main form has a listbox name=List75. When I choose a name
from List 75, a query called qryFamilyMembers runs to get a recordset for
jsut that one family. There is a tab control in the main form.

On page two of the tab control, a subform called frmFamilyMemberSubControl
shows me the recordset extracted by the query. The data source of the
subform is the query mentioned above. the subform has a lsitbox called
List92, not working.

The LinkChildFields for the subform is FamilyID (from the subform).
The LinkMasterFields for the subform is List75 (from the main form).

The main form works fine by itself. The subform works fine if I open it by
itself. but if I open the main form and go to page two of the tab control,
the list box in the subform is empty.

So I tried to set the Rowsource of List92 from the main form OnCurrent Event
but it doens't work. I also tried the suggestions in this thread but they
dont work either.