MS Access subform to query based form populating wrong

Question

I'm working on an Access database that has a main table that keeps track of basic user information and several sub-tables that have a one-to-many relationship to the main. Now all users are in one of two groups, which they can move between, and half of the tables only apply to one group. Which group a user is in is determined by a Boolean.

How I intended to set up the the various forms and sub-forms was to have the main form based on the queries:

SELECT *
FROM Foo
WHERE InGroup1 = TRUE

and

SELECT *
FROM Foo
WHERE InGroup1 = FALSE

and then add the appropriate sub-forms.

However, what keeps happening is that if there isn't a record in the sub-table associated with the currently selected ID of the main table, it just displays the first record in the table.

If I make a main form not based on a query it works fine, but then the two groups are mixed together.

Is there a way around this?

Fionnuala Fionnuala · Accepted Answer · 2012-08-14T18:39:15

I cannot reproduce your problem. This is an example of a form and subform where the subform data does not contain a record for the main form. The main form is based on a query:

 SELECT * FROM groups WHERE  ingroup1 = true

And is built in MS Access 2010.

sample form/subform

MS Access subform to query based form populating wrong

1 Answers