Tables:

tblStaff - List of all staff for my company.  Fields include strLastName 
and strFirstName.

tblContacts - List of contacts for a project given its Project ID 
(strProjectID).  Fields include strLastName and strFirstName.  Contacts 
can be outsiders as well as staff.

Form:

frmContacts - a form to enter contacts for tblContacts.

Situation:

frmContacts has a combobox called cboLastName.  I would like the rowsource 
for this combobox to be a list of last names of all contacts from 
tblContacts where the strProjectID = current project ID combined with all 
the last names from tblStaff.  If a last name happens to appear on both, I 
want that name appearing only once in the dropdown list.

frmContacts will then have cboFirstName whose rowsource will change 
because of cboLastName's "After Update" event.  I would like that to be 
filtered so that the rowsource is a list of first names from contacts 
where strProjectID = current project ID And strLastName = cboLastName 
combined with a list of staff's first names from tblStaff where 
strLastName = cboLastName.  If a first name happens to appear on both, I 
want that name appearing only once in the dropdown list.

I'm not sure of the SQL for merging two tables, weeding out duplicates, 
and applying them as a rowsource like this.  Can anyone help?  Thanks.

Greg

--0-1125779930-1025284282=:23079
Content-Type: text/plain; charset=us-ascii


the rowsource for cboLastName will be
"SELECT DISTINCT strLastName FROM stlStaff " _ 
& "ORDER BY strLastName "                                        
& "UNION SELECT DISTINCT strLastName FROM tblContacts " _
& "WHERE strProjectID = " & Me!ProjectID 
In the after update event of cboLastName you can put...
cboFirstName.RowSource =  "SELECT DISTINCT strFirstName FROM stlStaff " _ 
& "WHERE strLastName = '" & Me!cboLastName & "'"
& "ORDER BY strFirstName "                                        
& "UNION SELECT DISTINCT strFirstName FROM tblContacts " _
& "WHERE strProjectID = " & Me!ProjectID _
& " AND strLastName = '" & Me!cboLastName & "'"
 
Let me know if this works...
 
 
 Gregory Serrano wrote: Tables:

tblStaff - List of all staff for my company. Fields include strLastName 
and strFirstName.

tblContacts - List of contacts for a project given its Project ID 
(strProjectID). Fields include strLastName and strFirstName. Contacts 
can be outsiders as well as staff.

Form:

frmContacts - a form to enter contacts for tblContacts.

Situation:

frmContacts has a combobox called cboLastName. I would like the rowsource 
for this combobox to be a list of last names of all contacts from 
tblContacts where the strProjectID = current project ID combined with all 
the last names from tblStaff. If a last name happens to appear on both, I 
want that name appearing only once in the dropdown list.

frmContacts will then have cboFirstName whose rowsource will change 
because of cboLastName's "After Update" event. I would like that to be 
filtered so that the rowsource is a list of first names from contacts 
where strProjectID = current project ID And strLastName = cboLastName 
combined with a list of staff's first names from tblStaff where 
strLastName = cboLastName. If a first name happens to appear on both, I 
want that name appearing only once in the dropdown list.

I'm not sure of the SQL for merging two tables, weeding out duplicates, 
and applying them as a rowsource like this. Can anyone help? Thanks.

Greg

Lonnie Johnson 
ProDev, Builders of MS Access Databases
Check me out ==> http://www.galaxymall.com/software/PRODEV 




---------------------------------
Do You Yahoo!?
Sign-up for Video Highlights of 2002 FIFA World Cup

As Lonnie has said you can do this with a union query, but what happens when
you have a staff member and a contact with the same last name?  Better to
have a table with staff and contacts in it with a autonumber field to
provide uniqueness and add a field (Yes/No type) as a flag for staff.  Then
you can make the combo box have Last Name, First Name and the staff flag.
This way people would have the ability to pick the correct person when there
is more than one with the same last name.

|-----Original Message-----
|From: Gregory Serrano [mailto:SerranoG@m...]
|Sent: Friday, June 28, 2002 6:05 PM
|To: Access
|Subject: [access] Merging Data from Two Tables into Rowsource for One
|Combobox
|
|
|Tables:
|
|tblStaff - List of all staff for my company.  Fields include strLastName
|and strFirstName.
|
|tblContacts - List of contacts for a project given its Project ID
|(strProjectID).  Fields include strLastName and strFirstName.  Contacts
|can be outsiders as well as staff.
|
|Form:
|
|frmContacts - a form to enter contacts for tblContacts.
|
|Situation:
|
|frmContacts has a combobox called cboLastName.  I would like the rowsource
|for this combobox to be a list of last names of all contacts from
|tblContacts where the strProjectID = current project ID combined with all
|the last names from tblStaff.  If a last name happens to appear on both, I
|want that name appearing only once in the dropdown list.
|
|frmContacts will then have cboFirstName whose rowsource will change
|because of cboLastName's "After Update" event.  I would like that to be
|filtered so that the rowsource is a list of first names from contacts
|where strProjectID = current project ID And strLastName = cboLastName
|combined with a list of staff's first names from tblStaff where
|strLastName = cboLastName.  If a first name happens to appear on both, I
|want that name appearing only once in the dropdown list.
|
|I'm not sure of the SQL for merging two tables, weeding out duplicates,
|and applying them as a rowsource like this.  Can anyone help?  Thanks.
|
|Greg