Entering data in forms with lookup tables

[gretsch]

Hi all,

I have created lookup tables for all comms information for clients and contacts in my db. I am now having issues understanding how to enter data in a form using this structure. I'm thinking I want a combobox showing types of comm, and then a control next to it where I can enter the info.

The structure is as follows:

tblComms
Comm_ID
Client_ID (fk)
Method_ID (fk)
Contact_ID (fk)
CommInfo
CommNotes

tblCommMethods
Method_ID
MethodName (Landline, Cellphone, email, ...)

So for instance, to enter contact info for a Client, what do I put in the frmClient_Details form? My relationship view is attached (with many recently added lookup tables hidden, spot all the ID fields). I'm not confident that I've got my left and right sides correct for the links.



I've managed to get "normal" lookup tables working in forms, but the intermediate table (tblComms) is confusing me. Help?

Thanks,
Andy

[ConneXionLost]

Hi Andy,

A couple points:

- You have set up joins between tblComms, tblClients, and tblContacts. These joins form a closed loop in your relationship diagram. Closed loops in relationship diagrams should be avoided. (In fact, looking for closed loops is an effective way of troubleshooting a database!)

- Ideally, a lookup table would only have a single field. Using Primary Keys in a lookup table tends to be overkill. If you intend tblCommMethods to be a lookup table, then it is a good example of this. Try eliminating the [Method_ID] field, and replacing it with [MethodName] in tblComms.

- - -

It's unclear what your intention is with tblComms, and this stems from not knowing the relationship between Contacts and Clients. If this should be a many-to-many, then all you need to do is delete the join between tblContacts and tblClients, and allow tblComms to act as a child table. If it isn't many-to-many, then perhaps you could clarify what it is.

Cheers,