Hi all,

How do I get data from 2 tables into 1 ComboBox. Probably simple but I cannot work it out. My theoretical knowledge says create a Query, then populate the ComboBox using the Query as the RecordSource.

(tblContacts.chrSurname & ", " & tblContacts.chrFirstName) AND (tblEmployees.chrSurname & ", " & tblEmployees.chrFirstName)

so... ComboBox LIST =

Marley, Bob Contact
Moore, Gary Employee
Rock, Kid Contact
Osbourne, Ozzy Employee (the bloody people working for us ;o))
Vedder, Eddie Contact

Any help is greatly appreciated.

Clinton.

Hey Clinton,
What are you going to do with the data after selection?

Kevin

dartcoach

Hey Kevin... How's it going, mate?

For the Call DB... Contacts call, but so can employees (to pass info to the rest of the team)... I'm hoping to create report that staff members can run at the beginning of their shift to get up to speed with any current issues)... I see your thinking now... yes, this all gets placed in mtblCalls with lngzContactID = mtblContacts.idsContactID... hmmmmmmm??? What to do? I now know my devil, but he's bigger.

Clinton

Clinton,
So, when you pull the info from both tables, you also need the contact id from the contact table whether they are a contact or an employee right?
How many people will be using this and how often?

Kevin
P.S. If you want to go to straight email let me know.


dartcoach

Kevin,

Yes, that's correct. Up to 400 people using it for 37.5 hours a week. In shift patterns. I'd work on about 300 at peak time. But the Employees calling in will only be up to 12 max (the Support Team). I'm wondering if it wouldn't just be easier to add the Support Team into mtblContacts. I have a Notes MemoBox so can just add a note "This person is an employee"... What do you think? Easy option or Expandability... I have other ideas for future use of the Call DB for communication amongst Advisors, Support Staff, and Customers (via internet)... to report, record and publish Tech Issues...

Cheers for your help again. I'm happy, here or email...

Clinton

Clinton, here is an example where I select first names from two tables to populate the rowsource of a second combobox based on the choice of a first combobox. It uses a UNION query.

Me.cboFirstName.RowSource = "SELECT DISTINCT [strFirstName] FROM tblPSMTeam WHERE [strLastName] = '" & Trim(Me.cboLastName) & "' UNION SELECT DISTINCT [strNameF] as strFirstName FROM tblAQDStaff WHERE [strNameL] = '" & Trim(Me.cboLastName) & "' ORDER BY [strFirstName];"


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

Hi Greg,

Cheers. That works great. many thanks.

Clinton

Hi Greg,

... but as Kevin pointed out earlier, I have an issue passing the data back to the correct table... Cheers though... I am using that UNION QUERY elsewhere... so still great info... Nice one...

Clinton