Limiting choices in a junction table

[cmcollins]

This is sort of a complicated question, but one which probably has a very easy answer...

I'm making a database of contacts; each contact is associated with multiple counties in the US, and each US county has more than one contact associated with it.

I've made a junction table with two columns: ContactID and CountyID. I've used the lookup wizard to set up my table and allow me to select organizations and counties from a drop-down list.

But I'm wondering if there's a way to choose a state and then limit the choices in both fields (ContactID and CountyID) to the state that I've chosen in order to avoid endlessly scrolling through all the organizations and counties in the country.

The states in which the organizations and the counties are found are already stored in tables (Contact State in the Contacts table and County State in the Counties Table)... is there was a way to make that information show up in my junction table and use that to limit my options?

Thanks,
Christen

 

[LittleSmudge]

Yes, In the AfterUpdate event of the combo box where you choose the state ( refered to as cboPickState in the code below. ) you put:-

cboPickCounty.RowSource = "SELECT CountyId, CountyName " _
       & "FROM tblCounty " _
       & "WHERE StateRef = " & cboPickState
cboPickCounty.Requery


'Do a similar thing with cboPickContact
cboPickContact.RowSource = "SELECT ContactId, ContactFName, ContactLName " _
       & "FROM tblContact " _
       & "WHERE StateRef = " & cboPickState
cboPickContact.Requery

( Rename the objects to the names you've actually used. )

Then - if you want to make sure that a contact cannot be allocated to the same county more than once - Make the CountyRef and ContactRef fields in the linking table into a Joint Primary Key.





'ope-that-'elps.

G LS
accessaceNOJUNK@valleyalley.co.uk
Remove the NOJUNK to use.