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

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

Code:

---

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)

Code:

---

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 appreciated

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

Quote:

---

Originally Posted by 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

---

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

Code:

---

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

---

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.

Quote:

---

Originally Posted by 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.

---

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 [BathTypes1] & [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

Code:

---

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 ([BathTypes1] = '" & Me.cboBathTypes & "')"
End If

---

But If my selection from cboBathTypes happens to be originating from [BathTypes2] then this code works:

Code:

---

If Not IsNull(Me.cboBathTypes) Then ' Baths
    CustomerType = CustomerType & " And ([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