Combo box synchronization

[Anne]

Hello,
I have a database where I want to have an Events form that will track all
contacts and the Organization they work for. Several contacts can belong to
one Organization, so I have a MainContact and MainOrganization table. The
MainContact table includes first name, and last name as separate fields, as
well as an org_id field that is linked to the id field in MainOrganization.

I want the user to be able to select an organization and have all of the
names of contacts associated with that organization appear. I also want the
user to be able to select more than one contact for each organization.

Here is what I have set up so far in the Form Detail, and does not work:

Row Source for cboOrganization:
SELECT MainOrganization.id, MainOrganization.OrgName
FROM MainOrganization
ORDER BY MainOrganization.OrgName;

Row Source for cboMainContact:
SELECT MainContact.per_first_name, MainContact.per_last_name,
MainContact.org_id
FROM MainContact
ORDER BY MainContact.per_last_name;


After Update for cboOrganization: (this should look familiar; it's posted as
the cure in several places! That's not to say I used it incorrectly, though)
Private Sub cboOrganization_AfterUpdate()
' Update the row source of the cboContacts combo box
' when the user makes a selection in the cboOrganization
' combo box.
Me.cboContacts.RowSource = "SELECT per_first_name, per_last_name FROM" & _
" MainContact WHERE org_id = " & _
Me.cboOrganization & _
" ORDER BY per_last_name"

Me.cboOrganization = Me.cboOrganization.ItemData(0)
End Sub

At first, I could select an Organization and the correct contacts would
appear, but the Organization name would not stay in the combobox; regardless
of what organization was selected, the first one in the list would appear.

Secondly, I could not figure out how to select more than one contact.

Third, with the same information as above, I now get the following error:
“Syntax error (missing operator) in query expression ‘[org_id]=ACMC Granite
Falls Medical Clinic’

Fourth, absolutely nothing would happen if I left Row Source empty, or tried
to set it to only one field from each table.

Help!

Thanks.
Anne

 

[Amy E. Baggott]

If you want to select more than one contact, you'd be better off to use a
list box for your second combo. You can set the source to always use the
WHERE clause ("WHERE org_id = ' & me.cboOrganization). In the AfterUpdate
event of cboOrganization, put the command me.lstContacts.requery

This should work.