I have a form with 2 combo boxes (Ship and Unit). When the user selects their criteria for each combo box I have a search button they press which will query a SQL table with their selections and the results will display in a subform. I'm having issues with the SQL code using BOTH combo box selections. Current code gives no errors but will only recognize what the user selected in the "Unit" combobox and not what they selected in the "ship"
Any help would be greatly appreciated!!!
Code:
Private Sub cmdSearch_Click()
Dim Varitem As Variant
Dim strShip As String
Dim StrUnit As String
Dim strSQL As String
' Gets what Ship been selected
For Each Varitem In Me!Cbo_Ship.ItemsSelected
strShip = strShip & ",'" & Me!Cbo_Ship.ItemData(Varitem) & "'"
Next
'get selections from LeadDept multiselect listbox
For Each Varitem In Me!cbo_Unit.ItemsSelected
StrUnit = StrUnit & ",'" & Me!cbo_Unit.ItemData(Varitem) & "'"
Next
strSQL = "SELECT * FROM dbo_tbUnitPreAssembly_Master WHERE "
strSQL = "Select * From dbo_tbUnitPreAssembly_Master where [Ship] = '" & Me.Cbo_Ship & "' AND "
strSQL = "Select * From dbo_tbUnitPreAssembly_Master where [UnitType] = '" & Me.cbo_Unit & "'"
DoCmd.SetWarnings False
Me.tbUnitPreAssembly_Master_subform.Form.RecordSource = strSQL
End Sub