Subform- Can't link child field to listbox

[Don Starnes]

I've follwed the advice from several forum posts but still can't get my
subform to work. What am I doing wrong?

My purpose is to select a "group" name from a listbox on a "persons" form
and view fields from the corresponding recrord in a "group_persons" join
table in a subform.

My main form ("persons") has a "PersonsGroups" single select listbox which
has the "index_groups" (primary key) field from the "groups" table as its
bound column.

This form has a "subform_joins_group_person" subform whose
"joins_group_person" form displays a data query of the "joins_group_person"
table (which also has a "index_groups" field; the two tables are related by
their "index_groups" fields). The query is:

SELECT joins_group_person.group_person01, joins_group_person.group_person02,
joins_group_person.group_person03
FROM joins_group_person
WHERE
(((joins_group_person.index_persons)=[Forms]![persons]![index_persons]) AND
((joins_group_person.index_groups)=[Forms]![persons]![PersonsGroups]));

The "index_groups" and "index_persons" fields in the "joins_group_person"
table are not selected in the query because I don't want to display them on
the (sub)form.

I set the subform's Master Link Field to the
"Forms![persons]![PersonsGroups]" listbox and its Child Link Field to the
"Forms![joins_group_person]![index_groups]" field.

In the form_persons module, I put this event handler proceedure:

Private Sub PersonsGroups_AfterUpdate()
'update the subform(s) after changing the selection in the PersonsGroups
listbox.
Forms![persons]![subform_joins_group_person].Requery
End Sub

The subform displayed the data and updated the data, but stopped working
when I closed the project and reopened it. At that point, I had the child /
master fields had been set to the field names without the forms specified; I
added the form specification in an effort to make it work again. Now, in the
Form view, a dialog box asks for value of
"Forms![joins_group_person]![index_groups]".

The Subform Field Linker only shows visible fields on forms, not controls
(listboxes} or "invisible" fields.

How can I use a listbox in a main form to query a subform?

Thanks for your time!

Don

 

[Anne]

The reason you are probably having this problem, is the you are dropping the
primary keys. You must use the primary keys.
The child and master field must be by primary key.
On your form, you must list the primary keys, just the set the visible
property to no.