Prev: default value for one field based on another
Next: Credit Card Integration
From: Anne on 5 May 2010 11:52
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


From: Amy E. Baggott on 14 May 2010 15:43
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
>
>
 | 
Pages: 1
Prev: default value for one field based on another
Next: Credit Card Integration