We help IT Professionals succeed at work.

Access Entering data on form for query that joins two tables

Murray Brown
Murray Brown asked
on
Hi

I have an Access query which is the combination of two tables shown in the image. There are multiple entries for the tblPeople for each entry in tblHouseholds. I want to set up a single form with all fields where the user can make these entries. I have a query that combines both tables.If I let Access create a form from this query automatically I get the form shown in the second image - the problem with this form is that it looks like I can enter one person per household when I want to enter multiple

1
2
Comment
Watch Question

President / Owner CARDA Consultants Inc.
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
A form should only be tied to a single table.  So you should have a Household form and then use a People Subform within it.  Then you'll be able to add ass many people to each household.
Dale FyeOwner, Dev-Soln LLC
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2010
Commented:
Agree, with Daniel.  If you need more than one of something that relates to something on your main form, then you need a subform.

This could apply to phone numbers or email addresses on frmPeople, members on frmHousehold, or  OrderDetails on frmOrders.

When you create this subform, you create a link between the main form and the subform by clicking on the subform control, opening the Data tab on the Properties dialog, and setting he appropriate values in the linkMaster and linkChild properties.

HTH
Dale
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Ok. Thank you very much for the advice

Explore More ContentExplore courses, solutions, and other research materials related to this topic.