Updating a Junction Table

  • Thread starter Thread starter April K
  • Start date Start date
A

April K

I have a situation where I would like to create a many-to-
many relationship in my database. I will need one table
to house contacts and one table to house addresses. My
junction table will hold the contactID and addressID.
This all makes perfect sense to me...

My question is how do I update my junction table when I
want to add a new record to either my contacts or my
addresses (or both)? Do I have to do the update through
and append query or can it be done automatically while
entering the new data through a form?

Thanks for your help.

~April
 
Hi,


Generally it would be through the "Not in list" event of a combo box, where
you would append the data to the junction table. There is an example in
"Running Microsoft Access 2000", pp843-846 among other places.

You can also relay on autolookup if you have a query that bring the
two(three) tables in such a way that the query is updateable, but in that
case, the order into which you append a new record may become important and
so, the first case is the preferred one.



Hoping it may help,
Vanderghast, Access MVP
 
My question is how do I update my junction table when I
want to add a new record to either my contacts or my
addresses (or both)?
Click to expand...

Depends on how the rest of the UI is working. I sometimes create a simple
dialog (unbound form) that is launched by command button on (say) the
Contacts form, called "Add Address". This form contains a method of finding
an address (listbox, combo, cont-forms subform etc.) and a button saying
ADD -- which launches a SQL insert to put in the record

strSQL = "INSERT INTO LivesAt(ContactID, AddressNumber) " & _
"VALUES( " & Forms!frmContacts!txtContactID & ", " & _
Me!lisSelectAddress.Value & ");"


You can use a second listbox to display the addresses that have already
been added to the current Contact; for A-points you can make the allocated
Addresses disappear from the list the user is selecting from, to prevent
the same one being allocated twice to the same contact.

B Wishes



Tim F
 
Back
Top