My query lists 108 current members, but a form based on it lists all 400 members. Why?

Local time
Today, 13:35
Joined
Mar 25, 2024
Messages
78
My query lists 108 current members, but when I base a form on it [I used the Wizard, without making any design changes] it lists all 400+ members, including those who have left. Why? Surely the form should only show the records in the query's datasheet view?
 
create new form.
click on the query on Navigation pane, and choose to make new form out of it.
 
It should, so you have obviously done something wrong?
Are you sure it is the query and not the table? Have you called them the same name? :(
 
you can't have same name on Table and Query.
I obviously did not know that, as it is not something I have ever tried. I was just going on controls being the same name as source when the wizard is involved. :(

That makes the issue even stranger. :unsure:
 
create new form.
click on the query on Navigation pane, and choose to make new form out of it.
Same thing happened.
I should have said it is a form with a subform (which shows CategoryTypes).
But I see the problem, the Wizard created the main form on SQL it created, selecting fields from the Contacts table that were in my query* , which is using data from four tables: .......(*without excluding departed members)

1761131724261.png
 
Main form should only include contacts and contact types. Subform includes contact-contact categories and category type.
Since some records have multiple categories you are getting multiple records if you include the junction table in main form.
 
Main form should only include contacts and contact types. Subform includes contact-contact categories and category type.
Since some records have multiple categories you are getting multiple records if you include the junction table in main form.
Agreed.
I'm working on it. I'll post here if I get stuck again.
Thanks all
 
Also with a many to many you may want to also create the other view. You can make the main form contact category and the subform then list all contacts.
 
Main form should only include contacts and contact types.
The only advantage to be gained by including the Contact Types table in the form's query would be to order the rows returned by ContactType. Otherwise it would be omitted, and a combo box bound to the ContactTypeID foreign key column, set up in the usual way to show the referenced ContactType value. The control's NotInList event procedure could then be used to transparentlty insert new rows into ContactTypes.
 
Last edited:
The only advantage to be gained by including the Contact Types table in the form's query would be to order the rows returned by ContactType. Otherwise it would be omitted, and a combo box bound to the ContactTypeID foreign key column, set up in the usual way to show the referenced ColumnType value. The control's NotInList event procedure could then be used to transparentlty insert new rows into ContactTypes.
That is a personal design consideration. I almost never have a main form and subform where you can add/edit new mainform records and subform records. I always have a separate pop up for my main form records. Less confusing IMO and better/easier to enforce data integrity. I would have an add edit button on main form to pop open a new/edit contact form.
 
In this case the many to many is so simple since you are only picking a category without a lot of related fields. And when you create a new contact you are likely to already know all the categories and probably that list of categories is relatively set. Often in a many to many the related records in the other table are not yet entered and you are coming back later to create the association. So in this case I would probably have just the form to add and edit records. However, IMO this is more a checklist. The categories are probably pretty set and known when you add a new contact. In this case I usually do not have a subform because it is cumbersome. You have to create a new record in the subform by pulling down a choice from a combobox in each child record. Then you have to code you combos so that you remove choices that are already selected. In this case I would use a "checklist" design or MVF.
 

Users who are viewing this thread

Back
Top Bottom