Linking tables on forms

[John H W]

I am attempting to set up a form which has the data in three tables. I am new to Access, although I had years of setting up DOS databases

Table one is unique and has a one to many to the second table using an ID #

Table two has a one to many to the third table using both the ID # and a date

Table three has a many to one link to the table 2 based on the ID # and date on second table

The form is set up using customer info in the top third. Only one "customer" having same ID #. Need to add new customer

Middle third has information on the customer's actions based on the (many) dates. Need to be able to add new date(s) in this area

Bottom area has info on an event (can be more than one), linked to a specific date in second table. Need to be able to add new entry

Can you point me to any information (code) which will help me in this endeavor

Thanks
John H

 

[Marshall Barton]

I am attempting to set up a form which has the data in three tables. I am new to Access, although I had years of setting up DOS databases.

Table one is unique and has a one to many to the second table using an ID #.

Table two has a one to many to the third table using both the ID # and a date.

Table three has a many to one link to the table 2 based on the ID # and date on second table.

The form is set up using customer info in the top third. Only one "customer" having same ID #. Need to add new customer.

Middle third has information on the customer's actions based on the (many) dates. Need to be able to add new date(s) in this area.

Bottom area has info on an event (can be more than one), linked to a specific date in second table. Need to be able to add new entry.

The typical way to set up a form that deals with a one to
many relationship is to set the main form's recordsource to
the one side table/query amd use a subform for the many
side. The Link Master/Child properties of the subform
control allows you to specify the ID# field to limit the
subform1's records to only those related to the main form's
current record. The subform may be either in Single or
Continuous view.

To deal with the third table's data, I suggest that you add
a second subform to the main form. In this case, you'll
need a hidden text box on the main form that refers to the
first subform's date field =subform1.Form.thedatefield.
Then subform2's Link Master property can be the ID# and the
hidden text box. With this setup, subform2 will only
display records that match the current record in subform1.