Combo Box - Selecting data from two tables

[dhanus]

Hi Folks

Is there a way to have a Combo Box select data from two different tables depending on the status of a check box?

I have two tables - Company Drivers and Contract Drivers. If the check box is clicked, I would like the Combo Box to select data from Contract Drivers and vice versa.

At the moment, I use two Combo Box, one overlapping the other and turning the visible status on or off depending on the status of the check box.

 

[JeremyNYC]

Better would be to put all people in one table. Add a field that tells you what type of person it is.

Then, write a function that sets the rowsource of the combo box.

function SetRowSource
dim strWhere as string
if me!chkWhatever = true then
strWhere = "Contract"
else
strWhere = "Company"
end if
me!cmbDriver.rowsource = "SELECT PersonID, PersonName FROM tblPerson WHERE PersonType = '" & strWhere & "';"
end function

Then put a call to that function ("call setrowsource&quot in the Current event of the form and the after update event of the check box.

You will, of course, have to rename everything so it matches your database. And you may only be retrieving the person's name (though it's best to use an autonumber field as the primary key for this kind of table, and use that as the first, 0" width, column in your combo box.

Try that and let us know if you're still having problems.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[dhanus]

THANK-YOU, Jeremy.

I'm afraid, I can't join the two tables as they are rather different in structure.

Can I use your function to select different tables then?

I will have a go at it.

 

[dhanus]

Dear Jeremy

I used your method, changing it to use 2 different SELECT
statements and it works !

Thank-you sooo much for teaching me a new trick !