Combobox Row Source based on choices from two comboxes

[Rich J]

I have a form with several comboxes. The first combobox (cboContractor) has
a list of companies. After the company is selected the next combobox
(cboWorker1) shows only the people who work for that company. After the
person is selected in the second cbo, their craft is shown in one more
(cboCraft) for reference only. That works great.
I want the next combobox (cboWorker2) to further filter the list of people
to only the same craft as the person in cboWorker1.
This is the row source code for cboWorker1 which works -
SELECT DISTINCT qryContrWkrs.WkrID,
qryContrWkrs.qWkrName,qryContrWkrs.tCraft,qryContrWkrs.ContrID FROM
[qryContrWkrs] WHERE qryContrWkrs.ContrID = cboContractor

This is one of the row source codes I've tried so far for cboWorker2 -
SELECT DISTINCT qryContrWkrs.WkrID,
qryContrWkrs.qWkrName,qryContrWkrs.tCraft,qryContrWkrs.ContrID FROM
[qryContrWkrs] WHERE qryContrWkrs.ContrID = cboContractor AND
qryContrWkrs.tCraft = cboWkr.Column(2)

I think part of the problem is that tCraft is not the primary field. I
think my concept can work but having trouble with the exact coding.
Any help would be appreciated.

 

[Ofer]

Mybe that is the problem, but what is the location of the craft field in the
cboWkr, the column numbers starts with 0, so if the craft is the second field
in the source of the combo then it should be qryContrWkrs.tCraft =
cboWkr.Column(1)
=============================================
If it's not the case try to write the all path
qryContrWkrs.tCraft = Forms![FormName].cboWkr.Column(1)
=============================================
Did you perform a requery on the cboWorker2 after you selected a worker?
If not then on the after update event of cboWorker1 write the code
me.cboWorker2.requery
=============================================

 

[Rich J]

Thanks for your reply. I tried several different things based on what you
suggested and the cboWorker2 always came up empty.
I created an unbound Textbox and in VB code in cboWorker1_AfterUpdate I put
the requeries and Text75 = Me.cboCraft.Column(2) , then I changed the code
after the AND to
AND qryContrWkrs.tCraft = Text75
That actually works now but I think it is going to give other problems with
the unbound textbox in the continuous subform.
But if I put ...AND qryContrWkrs.tCraft = Me.cboCraft.Column(2) or leave out
the Me. directly in the combobox it does not work.

 

[Ofer]

Can you post please
1. the name of the form
2. the name of the subform, if there is one
3. the Sql of cboWorker2

Thanks for your reply. I tried several different things based on what you
suggested and the cboWorker2 always came up empty.
I created an unbound Textbox and in VB code in cboWorker1_AfterUpdate I put
the requeries and Text75 = Me.cboCraft.Column(2) , then I changed the code
after the AND to
AND qryContrWkrs.tCraft = Text75
That actually works now but I think it is going to give other problems with
the unbound textbox in the continuous subform.
But if I put ...AND qryContrWkrs.tCraft = Me.cboCraft.Column(2) or leave out
the Me. directly in the combobox it does not work.

I have a form with several comboxes. The first combobox (cboContractor) has
a list of companies. After the company is selected the next combobox
(cboWorker1) shows only the people who work for that company. After the
person is selected in the second cbo, their craft is shown in one more
(cboCraft) for reference only. That works great.
I want the next combobox (cboWorker2) to further filter the list of people
to only the same craft as the person in cboWorker1.
This is the row source code for cboWorker1 which works -
SELECT DISTINCT qryContrWkrs.WkrID,
qryContrWkrs.qWkrName,qryContrWkrs.tCraft,qryContrWkrs.ContrID FROM
[qryContrWkrs] WHERE qryContrWkrs.ContrID = cboContractor

This is one of the row source codes I've tried so far for cboWorker2 -
SELECT DISTINCT qryContrWkrs.WkrID,
qryContrWkrs.qWkrName,qryContrWkrs.tCraft,qryContrWkrs.ContrID FROM
[qryContrWkrs] WHERE qryContrWkrs.ContrID = cboContractor AND
qryContrWkrs.tCraft = cboWkr.Column(2)

I think part of the problem is that tCraft is not the primary field. I
think my concept can work but having trouble with the exact coding.
Any help would be appreciated.

 

[Rich J]

I defined the rowsource for CboWkr2 in the cbowkr1_afterupdate and everything
works great. Thanks for your help, it led me to this solution. For some
reason putting the code directly in the rowsource of the combobox did not
pass the value to it. I added CraftID to the string also but I think I can
take it out.

Private Sub cboWkr1_AfterUpdate()

TEMP = Chr(34) & Me.cboCraft.Column(2) & Chr(34) ' chr(34) = "
sqlstr = "SELECT DISTINCT QryContrWkrs.WkrID,
QryContrWkrs.qWkrName,qryContrWkrs.CraftID,qryContrWkrs.tCraft,qryContrWkrs.ContrID
FROM [QryContrWkrs] WHERE qryContrWkrs.ContrID = cboContr AND
qryContrWkrs.tCraft = " & TEMP

cboWkr2.RowSource = sqlstr

Can you post please
1. the name of the form
2. the name of the subform, if there is one
3. the Sql of cboWorker2

Thanks for your reply. I tried several different things based on what you
suggested and the cboWorker2 always came up empty.
I created an unbound Textbox and in VB code in cboWorker1_AfterUpdate I put
the requeries and Text75 = Me.cboCraft.Column(2) , then I changed the code
after the AND to
AND qryContrWkrs.tCraft = Text75
That actually works now but I think it is going to give other problems with
the unbound textbox in the continuous subform.
But if I put ...AND qryContrWkrs.tCraft = Me.cboCraft.Column(2) or leave out
the Me. directly in the combobox it does not work.

I have a form with several comboxes. The first combobox (cboContractor) has
a list of companies. After the company is selected the next combobox
(cboWorker1) shows only the people who work for that company. After the
person is selected in the second cbo, their craft is shown in one more
(cboCraft) for reference only. That works great.
I want the next combobox (cboWorker2) to further filter the list of people
to only the same craft as the person in cboWorker1.
This is the row source code for cboWorker1 which works -
SELECT DISTINCT qryContrWkrs.WkrID,
qryContrWkrs.qWkrName,qryContrWkrs.tCraft,qryContrWkrs.ContrID FROM
[qryContrWkrs] WHERE qryContrWkrs.ContrID = cboContractor

This is one of the row source codes I've tried so far for cboWorker2 -
SELECT DISTINCT qryContrWkrs.WkrID,
qryContrWkrs.qWkrName,qryContrWkrs.tCraft,qryContrWkrs.ContrID FROM
[qryContrWkrs] WHERE qryContrWkrs.ContrID = cboContractor AND
qryContrWkrs.tCraft = cboWkr.Column(2)

I think part of the problem is that tCraft is not the primary field. I
think my concept can work but having trouble with the exact coding.
Any help would be appreciated.