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

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.
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


"Anne" wrote:

> 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
>
>