You can do it without any code at all. Set the LinkMasterFields property of
the subform control to the name of the text box in which you enter the
household ID and its LinkChildFields property to the name of the household ID
field in the subform's underlying table. As soon as the ID is entered in the
text box (and the enter or tab key pressed, or focus moved to another control
with the mouse) the subform should show the records for that household. A
separate button is not required.
You could make it more user friendly by using a combo box to select the
household rather than a text box. As well as your table of members you
should really have a Households table, with one row per household. If the
Household ID is something intelligible like a house hold name then you simply
list the household names in a single column combo box whose RowSource
property would be like this:
SELECT [HouseholdID] FROM [Households] ORDER BY [HouseholdID];
If you don't have a Households table then you can do it by listing the
distinct values from the table of members, e.g.
SELECT DISTINCT [HouseholdID] FROM [Members] ORDER BY [HouseholdID];
though I'd urge you to create Households table if you don't have one,
relating it to the members table on the Household ID columns and enforcing
referential integrity.
If the Household ID is an arbitrary numerical value and you have a separate
household name or similar column then you'd set the combo box up like this so
that you see the intelligible values from the second column, but the value of
the combo box is the hidden household ID:
RowSource: SELECT [HouseholdID], [HouseholdName] FROM [Households] ORDER
BY [HouseholdName];
BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.
In either case you'd set the LinkMasterFields property of the subform
control to the name of the combo box.
If you don't link the subform to the main parent form in the above way you
can do it by means of your button. Firstly set the subform's RecordSource to
a query which references the text or combo box on the parent form, e.g.
SELECT *
FROM [Members]
WHERE [HouseholdID] = Forms!frmSearchMembers!txtHouseHoldID
ORDER BY LastName, FirstName;
Then in the button's Click event procedure requery the subform control:
Me.sfrMembers.Requery
where sfrMembers is the name of the subform control on the parent form, i.e.
the control which houses the subform, not the name of its underlying form
object; unless both have the same name of course.
Ken Sheridan
Stafford, England
