Uneducated Question

[Steve]

Thanks to this group I found that in order to make my db structure correct I
needed to join two tables in a many to many relationship by using a third
junction table.

I have done this:
tblPerson
tblLease
tblLease_Person

Now I have found that queries and data entry forms are difficult to build.
What's the secret? How would I make a simple data entry form that lets me
enter a Lease and the people at the same time? Can I do this? I have
tried several things and it appears that I will need to enter people and
leases with separate forms and then create a third form that ties them
together. Is this the only way? Dumb question probably....but this is the
getting started group right?

TIA
Steve

 

[Andrew Smith]

You use a form with a subform to do this.

You want to create a lease so your main form is based on tblLease.

Your subform should be a continuous form based on tblLease_Person, and
should display only the PersonID field using a combo-box control. Set up the
combo box so that the person's name is displayed, but the ID number is
stored - you can use the combo box wizard for this.

The main form and subform should be linked using the LeaseID field (or
whatever you have named the primary key in tblLease). If you have related
your tables in the relationships window then Access should set this up
correctly for you.

Once you have created your lease record you will be able to add people from
tblPeople using the subform. You will, however, probably need a means for
adding new people from this form. You could use a separate form for this, or
you could use some VBA code on the "Not In List" event of the combo box. Get
the forms working correctly, and worry about this bit later!