Could use some VB help with dealing with combo box data from a Union All query

[spOOk123]

I have a search form with multiple cascading combo boxes. Most combo boxes record sources are linked to a singular table field whereas I am using this code to filter results

Function SearchCriteria()
Dim CustomerType As String
Dim Task As String
Dim strCriteria As String

' Example
If Not IsNull(Me.cboBathTypes) Then ' Baths
CustomerType = CustomerType & " And ([BathTypes1] = '" & Me.cboBathTypes & "')"
End If

strCriteria = CustomerType
Task = "SELECT * FROM qry_Customer where " & strCriteria & " Order by CustomerName asc"
Me.frm_ExtProducts_Subform1.Form.RecordSource = Task
Me.frm_ExtProducts_Subform1.Form.Requery
End Function

My problem is that I have other combo boxes who's record sources are linked to a Union All query - whereas depending on the selection the data may be coming from [BathTypes1] or [BathTypes2] or [BathTypes3] and I am unable to figure out a working code I have tried so many variations including the following - (does not work)

If Not IsNull(Me.cboBathTypes) Then ' Baths
CustomerType = CustomerType & " And ([BathTypes1] = '" & Me.cboBathTypes & "')"
Else
CustomerType = CustomerType & " And ([BathTypes2] = '" & Me.cboBathTypes & "')"
End If

My experience at coding is very limited and any help in the right direction is much apprecaited

[OBP]

You haven't actually said what happens, ie wrong data, no data or an error.
The first thing I would do is replace "Me.cboBathTypes" with an actual value that you know is there to test if the code is working.
I would also test what Me.cboBathTypes produes using
msgbox Me.cboBathTypes

[spOOk123]

You haven't actually said what happens, ie wrong data, no data or an error.
The first thing I would do is replace "Me.cboBathTypes" with an actual value that you know is there to test if the code is working.
I would also test what Me.cboBathTypes produes using
msgbox Me.cboBathTypes

[spOOk123]

Sorry - No data / Nothing found if data from cboBathTypes selects data that resides [BathTypes2] - Works perfectly if selected data from sboBathTypes resides in [BathTypes1]. Did the msgbox = Me.cboBathTypes and displayed proper data regardless if data is from [BathTypes1] or [BathTypes2]

But currently it will not work correctly because I dont know how to code it - so its only responding to data from [BathTypes1]. Here is another non-working try I had hoped would work but did not
If Not IsNull(Me.cboBathTypes) Then
SELECT CASE Me.cboBathTypes.Column(1) & vbNullString
CASE IS = "1"
CustomerType = CustomerType & " And ([BathTypes1] = '" & Me.cboBathTypes & "'"
CASE IS = "2"
CustomerType = CustomerType & " And ([BathTypes2] = '" & Me.cboBathTypes & "'"
End if

[OBP]

OK, rather than try and resolve your problem by guesswork on what you are trying to do I will post a copy of the Search 2000 database by Allen Browne to see if the code he uses will give you what you need, it could be something as simple as a missing appostrophy etc.
Or possibly using a function rather than plain VBA.
Other than that I would need a zipped dummy copy of the database (less any personal data) to look at the form and code.

[spOOk123]

OK, rather than try and resolve your problem by guesswork on what you are trying to do I will post a copy of the Search 2000 database by Allen Browne to see if the code he uses will give you what you need, it could be something as simple as a missing appostrophy etc.
Or possibly using a function rather than plain VBA.
Other than that I would need a zipped dummy copy of the database (less any personal data) to look at the form and code.

Thanks for responding

That database pails in comparison to mine and has no commonality to my issue as it is not using a Union or UnionAll Querry to merge data from multiple data fields & [BathTypes2] into combined results in a singular combo box (cboBathTypes) and then (where my problem is) attempting to sort it out via VBA

Remember that cboBathTypes is retrieving merged data from qry_UnionAll

If my selection from cboBathTypes happens to be originating from [BathTypes1] then this code works:
If Not IsNull(Me.cboBathTypes) Then ' Baths
CustomerType = CustomerType & " And ([[B]BathTypes1] = '" & Me.cboBathTypes & "')"
End If
But If my selection from cboBathTypes happens to be originating from then this code works:
If Not IsNull(Me.cboBathTypes) Then ' Baths
CustomerType = CustomerType & " And ([[B]BathTypes2] = '" & Me.cboBathTypes & "')"
End If
I need working code that would work regardless where cboBathTypes data originated [BathTypes1] or [BathTypes2]

I will try to strip down my dbase this weakend to upload if I do not already have resolved

Thx again