Filter combobox options with Select Case (1 Viewer)

xBirdman

Registered User.
Local time
Today, 11:49
Joined
Oct 14, 2019
Messages
38
I hate to ask this question, but I'm getting nowhere fast.

I have a straightforward combobox based on a table. I need the values available on the form to be filtered by a second field in the table so that if one condition is met, a tempvar takes on a value, and in all other cases, it takes on a value including a wildcard to cover several available options. So I've currently got (though I've tried several options)

Code:
Private Sub Form_Load() 
    Dim caseCode As String
    Dim fltr As TempVars
 
    caseCode = Me.ReportingAgency.Value
 
    Select Case caseCode
        Case "CCA", "CCP"
        TempVars!fltr = caseCode
        
        Case Else
        TempVars!fltr = "Like 'OIT*'" 
    End Select
 
End Sub
This just sets the TempVars to filter the underlying query. Column 1 has the values to show up in the combobox, and column 2 has either CCA, CCP, or some version of OIT (could have others in the future). For CCA and CCP it works fine, but the 'OIT*' is not returning anything, so I'm obviously just not calling it correctly and everything is getting filtered out. I can't believe this is taking me so long, but... Monday.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:49
Joined
Aug 30, 2003
Messages
36,118
How are you using it? My guess is that Like is getting added to =.
 

xBirdman

Registered User.
Local time
Today, 11:49
Joined
Oct 14, 2019
Messages
38
I'm simply applying the [TempVars]![fltr] as a where condition in the query so you may be right:

WHERE (((table.program)=[TempVars]![fltr]));

How would I create the temp variable so that it doesn't conflate '=' and 'LIKE'?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:49
Joined
Aug 30, 2003
Messages
36,118
It would make the query less efficient but changing = to Like in the query may work. Also dropping Like from the Case Else:

TempVars!fltr = "'OIT*'"
 

xBirdman

Registered User.
Local time
Today, 11:49
Joined
Oct 14, 2019
Messages
38
And works like a charm now. Thank you much. I can now move along with my Monday to do list...
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:49
Joined
Aug 30, 2003
Messages
36,118
Happy to help!
 

Users who are viewing this thread

Top Bottom