Concatenating strings for recordsource

[jcardullo]

I have cascading combo boxes that filter a list of employees based on the team number selected in the first box and by what type of equipment they operate. Is it possible to have the list contain the filtered results on top of the list then list the remaining employees sans the ones selected by the filter?

I am relatively new to VB and here is how I am cascading the boxes.


Dim strSQl As String

Combo50 = Null

strSQl = "SELECT DISTINCT [T_EMPLOYEES].[LAST_NAME] FROM [T_EMPLOYEES] "
strSQl = strSQl & " WHERE [T_EMPLOYEES.TEAM] = '" & Combo26 & "' And "
strSQl = strSQl & " [T_EMPLOYEES].[EQUIP] = '" & TypeEqip & "'"
strSQl = strSQl & " ORDER BY [T_EMPLOYEES].[LAST_NAME];"

Combo50.RowSource = strSQl

I tried to create a second string that chose all employees not equal to "TypeEquip" and then concatenate the two strings. But I don't even know if this is possible or if I am completely on the wrong path. My syntax may even be way off.

Thanks for any help.

 

[Travis]

See Union Queries


But it would be faster to sort the Employee list by Equip. You could add a calculated field that [EQUIP]='" & TypeEquip & "'. This would return a negative 1 (-1) for Matches and Zero (0) for non matches. Sorting by this field first would place the Employees that match the equipment First.


Just a hint. It is better to work out the Queries using the QBE grid and then transfer them to code.

 

[jcardullo]

I remain cofused. Here is some more detail on my problem.

I have an employee table that includes a team field and an equip. field. On my form the [cboTeam] cascades the line source for [cboOperator] based on the team selected. The form is for Loader operators so I also have the code filtering down to [employee].[equip] = loader. But sometimes employees with other classifications might operate a loader. So, I would like the combo box to list loader operaters for that team followed by the rest of the employees on that particular team. I have been trying to do this by manipulating my code for cascading combos but not having any luck. Is there a solution? Or am I asking the impossible? Or am I an idiot and going about this all wrong?

Thanks for any help. I looked at Union Queries but I don't think they will work in my situation.