Could use some VB help with dealing with combo box data from a Union All query (1 Viewer)

spOOk123

New member
Local time
Today, 10:12
Joined
Oct 3, 2020
Messages
11
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:
 Please Use Code Tags
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
Please Use Code Tags

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:
 Please Use Code Tags
    If Not IsNull(Me.cboBathTypes) Then ' Baths
        CustomerType = CustomerType & " And ([BathTypes1] = '" & Me.cboBathTypes & "')"
    Else
        CustomerType = CustomerType & " And ([BathTypes2] = '" & Me.cboBathTypes & "')"
    End If
Please Use Code Tags
My experience at coding is very limited and any help in the right direction is much apprecaited
 
Last edited by a moderator:

theDBguy

I’m here to help
Staff member
Local time
Today, 08:12
Joined
Oct 29, 2018
Messages
21,358
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
Hi. Unfortunately, I fail to understand the problem. Are you saying you couldn't make these comboboxes cascade like the others?
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:12
Joined
Feb 28, 2001
Messages
26,999
My first question is why you have three different "Bath Type" data sources. If you can do a UNION of them then they are enough alike that they should probably be the same table in the first place, hence no UNION or UNION ALL required. You might wish to discuss why you think they should be three different tables. We might be able to simplify your life here.
 

spOOk123

New member
Local time
Today, 10:12
Joined
Oct 3, 2020
Messages
11
Thank you both for responding

Combo boxes are working fine it is in the VB filtering I am having the issue

Three different bath types due that one customer may have three different baths / type (main = bath (master = shower)) etc and they are in the same table. If I were not use a union how would I bring [tbl_Customer].[BathTypes1], [tbl_Customer].[BathTypes2], [tbl_Customer].[BathTypes3] all into one combo box (cboBathTypes)? Though I am not directly using the [tbl_Customer] but rather a query [qry_Customer].

I assumed that ( Task = "SELECT * FROM qry_Customer where " & strCriteria & " Order by CustomerName asc") would have shown that all fields are in a singular table / query - I apologize for not clarifying that

I hope that help clear things up
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:12
Joined
Oct 29, 2018
Messages
21,358
Thank you both for responding

Combo boxes are working fine it is in the VB filtering I am having the issue

Three different bath types due that one customer may have three different baths / type (main = bath (master = shower)) etc and they are in the same table. If I were not use a union how would I bring [tbl_Customer].[BathTypes1], [tbl_Customer].[BathTypes2], [tbl_Customer].[BathTypes3] all into one combo box (cboBathTypes)? Though I am not directly using the [tbl_Customer] but rather a query [qry_Customer].

I assumed that ( Task = "SELECT * FROM qry_Customer where " & strCriteria & " Order by CustomerName asc") would have shown that all fields are in a singular table / query - I apologize for not clarifying that

I hope that help clear things up
Hi. I had to reread your original post to try and get a better understanding. Is the problem you can't tell which bath the user selected from the combo, so you can adjust your criteria to use BathTypes1 or 2 or 3?

Remember, we can't see what you're looking at, so what makes sense to you, may be a mystery to us.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:12
Joined
Feb 28, 2001
Messages
26,999
how would I bring [tbl_Customer].[BathTypes1], [tbl_Customer].[BathTypes2], [tbl_Customer].[BathTypes3] all into one combo box

In the combo box rowsource, you would select from the available list with a WHERE clause that includes the customer id as one criterion and allow multiple rows to be selected (which is the default anyway.) But you would NOT have a criterion of the bath type because that would filter away things you apparently want to see.
 

spOOk123

New member
Local time
Today, 10:12
Joined
Oct 3, 2020
Messages
11
The combo boxes work perfectly and has already combined the data - that is not the problem. The problem is that the code does not know if the value selected from the combo box is coming from [BathType1],[BathType2],[BathType3]. If the data from the combo box happens to be from field [BathType1] then the working code is
If Not IsNull(Me.cboBathTypes) Then ' Baths
CustomerType = CustomerType & " And ([BathTypes1] = '" & Me.cboBathTypes & "')"
End If

If the data from the combo box happens to be from field [BathType2] then the working code is
If Not IsNull(Me.cboBathTypes) Then ' Baths
CustomerType = CustomerType & " And ([BathTypes2] = '" & Me.cboBathTypes & "')"
End If
and so on
I need to adjust the code to be like
If Not IsNull(Me.cboBathTypes) Then ' Baths
CustomerType = CustomerType & " And ([BathTypes1] or [BathTypes2] or [BathTypes3] = '" & Me.cboBathTypes & "')"
End If

I just dont know how to tell the code that if its not in one data field then look in the next and so on
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:12
Joined
May 7, 2009
Messages
19,169
Code:
Function SearchCriteria()
    Dim Task As String
    Dim strCriteria As String
    ' for combobox bathTypes1
    ' replace the name of the combo if not correct
    If Not IsNull(Me.cboBathTypes1) Then ' Baths
        strCriteria = strCriteria & "([BathTypes1] = '" & Me.cboBathTypes1 & "') OR "
    End If
    ' for combobox bathTypes2
    ' replace the name of the combo if not correct
    If Not IsNull(Me.cboBathTypes2) Then ' Baths
        strCriteria = strCriteria & "([BathTypes2 = '" & Me.cboBathTypes2 & "') OR "
    End If
    ' for combobox bathTypes3
    ' replace the name of the combo if not correct
    If Not IsNull(Me.cboBathTypes3) Then ' Baths
        strCriteria = strCriteria & "([BathTypes3] = '" & Me.cboBathTypes3 & "') OR "
    End If
  
    If Len(strCriteria) > 0 Then
        strCriteria = Left(strCriteria, Len(strCriteria) - 4)
    Else
        strCriteria = "(1=1)"
    End If
    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
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:12
Joined
Feb 28, 2001
Messages
26,999
The problem is that the code does not know if the value selected from the combo box is coming from [BathType1],[BathType2],[BathType3]
This is a more serious issue. If you have fields called BathType1 and BathType2 and BathType3, your tables are not properly normalized and this is why you are now struggling with overly complex decisions. You seriously need to understand database normalization in order to get out of the hole that has been dug for you.

You can search this forum for articles on Normalization or you can search the web for articles on Database Normalization. If you go the "general web search" route, limit your first few articles to .EDU sites because the .COM sites often have something to sell you. After you are more comfortable with the concepts, the .COM sites are OK, they just might have more distractions.
 

spOOk123

New member
Local time
Today, 10:12
Joined
Oct 3, 2020
Messages
11
Thank you for responding. This is of course example situation. The object hear was to change code filter data from a singular combo box not create 3 individual combo boxes but thanks anyway.

As for normalizing the tables, I agree but believe but even so I would still end up at the same moot issue - having to merge all relative data into a single combo box and then again (where I am at not), figure out how to make code to filter it. I will keep pounding away at it but again thanks for all the input.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:12
Joined
Oct 29, 2018
Messages
21,358
The problem is that the code does not know if the value selected from the combo box is coming from [BathType1],[BathType2],[BathType3].
Hi. Sorry for the delay. Okay, so that's exactly what I said in post #5 of what I thought the problem was. In that case, I can think of at least 2 solutions for you. However, let me ask you first. Is it possible to have duplicate options among the different bath types?
 

spOOk123

New member
Local time
Today, 10:12
Joined
Oct 3, 2020
Messages
11
Hello and thanks for responding and yes there will be duplicates. Example - cboBathTypes (fixed data) would Tub, Shower, Tub & Shower. Customer - Joe Smith main bath [BathType1], has a tub, master bath [BathType2] has a shower. Search form has cboBathTypes whereas I am searching all customers that have a shower (example) - which means it needs to search all customers [BathType1], [BathType2], [BathType3]. Accomplished either SQL in combo box or Union query. The code filtering the results is my problem. I can poorly rig it to work by modified replication strCriteria - modified strCriteria2 and original code [BathType1] - modified original code [BathType2] when using the following if statement

If findRecordCount(Task) = 0 Then 'When selecting a item that is not in [BathType1]
' bla bla bla
End

But there must be a better way - lol
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:12
Joined
Oct 29, 2018
Messages
21,358
Hello and thanks for responding and yes there will be duplicates. Example - cboBathTypes (fixed data) would Tub, Shower, Tub & Shower. Customer - Joe Smith main bath [BathType1], has a tub, master bath [BathType2] has a shower. Search form has cboBathTypes whereas I am searching all customers that have a shower (example) - which means it needs to search all customers [BathType1], [BathType2], [BathType3]. Accomplished either SQL in combo box or Union query. The code filtering the results is my problem. I can poorly rig it to work by modified replication strCriteria - modified strCriteria2 and original code [BathType1] - modified original code [BathType2] when using the following if statement

If findRecordCount(Task) = 0 Then 'When selecting a item that is not in [BathType1]
' bla bla bla
End

But there must be a better way - lol
Okay, I'm still not sure if I understand it completely. This is similar to what were already suggested. So, what happens if you try it this way?
Rich (BB code):
    If Not IsNull(Me.cboBathTypes) Then ' Baths
        CustomerType = CustomerType & " And ([BathTypes1] = '" & Me.cboBathTypes _
            & "' OR [BathTypes2]='" & Me.cboBathTypes _
            & "' OR [BathTypes3]='" & Me.cboBathTypes & "')"
    End If
Hope that helps...
 

spOOk123

New member
Local time
Today, 10:12
Joined
Oct 3, 2020
Messages
11
Thanks again for responding

That appears to be what I am looking for - I am out of the office at current and will give it a test tomorrow. My fingers are crossed
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:12
Joined
Oct 29, 2018
Messages
21,358
Thanks again for responding

That appears to be what I am looking for - I am out of the office at current and will give it a test tomorrow. My fingers are crossed
I'm keeping my fingers crossed as well. Good luck and please let us know how it goes.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:12
Joined
May 7, 2009
Messages
19,169
if you need to be Specific as to Which field the cboBathTypes refers when a selection is made,
add another column to the Rowsource of the Union Query:

select BathTypes1, 1 As Type from yourTable
UNION
select BathTypes2, 2 As Type from yourTable
UNION
select BathTypes3, 3 As Type from yourTable

Change the Rowsource of your combo and the Column Count to 2.

Now change your code to:
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 & "'"
        CASE IS = "3"
            CustomerType = CustomerType & " And ([BathTypes3] = '" & Me.cboBathTypes & "'"
        END SELECT
    End If
 
Last edited:

spOOk123

New member
Local time
Today, 10:12
Joined
Oct 3, 2020
Messages
11
Does not work, no error, nothing - in fact the combo box still displays drop down values properly but no impact on results, nothing. As if code were rem'd out
Dang
 

Users who are viewing this thread

Top Bottom