Update Query?

[tyleri]

I have a small design dilemna. I have never done this before in Access so I'm not 100% sure of how it should be handled.

I have a parent/child form/subform relationship. The parent form is software applications. The subform is Contacts. We already have a Contacts table with all the information filled out. What I want to do is select a Contact name from a drop-down list (i can handle that) and automatically have the rest of the fields update depending on what Contact name I select (what i need some help with).

the kicker is that I might use the same Contact for multiple Software Applications. How can I make this relationship so that it:

1) Is always linked to the correct Software Applications
2) Updates after selecting the Contact Name from a list

Thanks

 

[jimmythegeek]

Part 1:

You need to create a Many to Many relationship between the Applications table and the Contacts table because ONE application could have MANY contacts, and ONE contact could have MANY applications. This requires a "Join" or "Link" table between the two:

tblApps
============
AppID(PK) App
-------------------
1 MS Office
2 Adobe Photoshop
3 Macromedia Flash

tblContacts
=================
ContID(PK) Contact
-------------------
10 John Smith
11 Bill Adams
12 Riley Jones

tblAppContacts (Join table)
===================
AppID ContID
---------------
1 10
1 12
2 10
2 11


If you need information on just a contact, than you retrieve that from the contacts table. The same on an application. If you need to know which contacts "Go with" which application, you get that from joining all three tables and using the ID's from the join table.

Part 2

Your child form should have a record source of tblAppContacts, NOT tblContacts. The linking fields will be AppID.

Part 3

One easy way to populate fields based on a combo box selection is by using the combo box columns. Make the row source of you combo box, a select statement that pulls in all the fields from the contacts table you want to see on the form, starting with the primary key.

Make the primary key first, followed by the contact name, and then the other fields.
Change the number of columns (properties window) to the number of columns in your select stmt.
Make the second column the only visible column by making the column widths like this: 0;1;0;0;0;0;0 etc.
Now, on the AfterUpdate of the combo box you simply put code like this (remember that the column properties are 0 based, so they start with column 0 not 1):

Private Sub cboMyCombo_AfterUpdate()
With Me
.txtPhone = .cboContact.Column(2)
.txtFax = .cboContact.Column(3)
etc.
End With
End Sub

Don't forget to bind the combo box to the ContID field in the tblAppContacts table which should be the source of you subform. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?