Trying To Join 2 Combos

[aamer]

I have 2 combo boxes on a form
cboWkLoc This selects workers city where employed This combo gets data from company table
CboEmp This Selects Employee

I am trying to join these both combos so when I select City (e.g Reno ) its shows only employees working in Reno - This combo gets data from Purchases table

I have tried to work on the following code but could not figure it out.






Private Sub cboWkLoc_AfterUpdate()
Dim strSource As String
strSource = "SELECT City " & _
"FROM Purchase " & _
"WHERE Employee = '" & Me.cboWkLoc & "' ORDER BY City"
Me.cboEmp.RowSource = strSource
Me.cboEmp = vbNullString
End Sub

[pbaldy]

Looks like you have your fields mixed up. You're comparing employee to city.

[aamer]

are there any examples available so I can see how its done

[pbaldy]

Sure:

http://www.baldyweb.com/CascadingCombo.htm

but I think your method is fine, it's the logic of what fields you have where in your SQL that looks off.

[aamer]

I was using this example, but both combo boxes are using the same table
whereas I want to use 2 different tables
combo: cboWkLoc uses table: COMPANY (City is Selected) Field: EmployeeWorkLoc
Combo: cboemp uses table: PURCHASES (Employee is Selected) Field: EmployeeName

I could not figure how to define that in code

[pbaldy]

My point is that where you say the second combo is to select an employee, you have "SELECT City" in the SQL. You say the user selects a city in the first combo, but in the SQL you refer to employee: "WHERE Employee = ..." I would expect those to be reversed.

[aamer]

Have done it
Sample attached

Used the following code

Private Sub CboLoc_AfterUpdate()
Dim strSource As String
strSource = "SELECT ComapnyName " & _
"FROM Company " & _
"WHERE EmployeeWorkLoc = '" & Me.CboLoc & "' ORDER BY ComapnyName"
Me.CboEmpl.RowSource = strSource
Me.CboEmpl = vbNullString
End Sub



pbaldy Thank you for pointing me in the right direction.

[pbaldy]

No problem.