Synchronizing 1

[ramondrumon]

I'm having a hard time getting an answer to this because I don't really know how to explain. I need a combo box that will have two tables with one field from each in the same column ( i can do a union query ok), the fields are (subContractors & primeContractors). I need this combo synchronized with the next combo that will give me the employees of which ever contractor I choose. Problem!! I can't figure out how to send the employees to the next combo box, because I have two tables in the query. I can't combine the two tables, its just not practicle.

 

[Schof]

OK here's my take on your question...

You want to list all contractors in a combobox, the subContractors will come from one table and the primeContractors will come from another table. Then when the user selects the contractor you don't know whether (s)he is a subContractor or a primeContractor. Close? If so, create your combo boxes rowsource using a query like this...

select subContractorname, "subcontractor" from ...
union
select primeContractorName, "primecontractor" from ...

Obviously you will get two columns so you will only want to display the first one. When the user selects a contractor you can then use the value in the second column "to send the employees to the next combo box".

Hope this helps...

 

[ramondrumon]

You understand my question fine, but if I have Prime contractors in one column and Sub contractors in a second column and choose one column, I would be choosing a Prime and a Sub both, they both need to be in one column.

 

[Schof]

Actually each row would be either be a Prime contractor or a Sub contractor. The second column in each row is to keep track of the contractor's type.

For example:

John Smith, PrimeContractor
Jill Fone, PrimeConstractor
Bill Jones, SubContractor
...

When the user selects the contractor from the combo box you would use the contractor type to set the next combo boxes recordsource.

For example:

if contractor.column(1) = "PrimeContractor" then
nextComboBox.rowSource = "SELECT ..."
else ' contractor.column(1) = "SubContractor"
nextComboBox.rowSource = "SELECT ..."
endif

 

[ramondrumon]

Okay, I'm getting closer, my only problem now is that one contractor may have up to 50 employees to choose from. I think this is where it gets ugly!

 

[Schof]

I assume you have a table with the contractor's employees so when setting the employee combo box (named nextComboBox above) just include the selected contractor to the filter.

For example, assuming the prime contractor's employees are stored in the PRIME_CONTRACTOR_EMPLOYEES table and you want to display the employee names in the employee combo box...

employeeComboBox.rowSource = "SELECT EMPLOYEE_NAME FROM PRIME_CONTRACTOR_EMPLOYEES WHERE CONTRACTOR = '" & CONTRACTOR.COLUMN(0) & "' ORDER BY EMPLOYEE_NAME"

And don't forget to requery the employee combo box...

 

[ramondrumon]

COOOOOOOOOL. I've been try to get this solved for weeks and you have me on the right track now. Thanks!!!!