Help w/ Multiple Filters on Combo Boxes

[dmuroff]

Hi,

I have multiple combo boxes at the top of a form which are used to filter records in the details section of the form.

I have a procedure called FilterCommand which is called everytime one of the combo boxes is updated (afterupdate event) Here is the procedure:

Private Sub FilterCommand()
Dim strFilter As String
strFilter = "1 = 1"
    If Len(cboInternalCode) > 0 Then
        strFilter = strFilter & " AND InternalCode='" & cboInternalCode & "'"
    End If
    If Len(cboBallastType) > 0 Then
        strFilter = strFilter & " AND ballasttype = " & cboBallastType
    End If
    If Len(cboInput) > 0 Then
        strFilter = strFilter & " AND InputWatts = " & cboInput
    End If
    If Len(cboType) > 0 Then
        strFilter = strFilter & " AND Type =" & cboType
    End If
    If Len(cboLampType) > 0 Then
        strFilter = strFilter & " AND lamptype = '" & cboLampType & "'"
    End If
    If Len(cboBase) > 0 Then
         strFilter = strFilter & " AND base = '" & cboBase & "'"
    End If
    If Len(cboWatts) > 0 Then
        strFilter = strFilter & " AND watts = '" & cboWatts & "'"
    End If
    If Len(cboVolts) > 0 Then
        strFilter = strFilter & " AND volts = '" & cboVolts & "'"
    End If
Me.Filter = strFilter
Me.FilterOn = True
End Sub

Now the problem is that lets say a user selects a value from cboWatts which returns 5 records. Now the user selects a value from cboVolts (making the filter cbovolts and cboWatts) If there are no records which meet this filter the cboVolts combobox displays nothing (It appears to display nothing but in fact in the text in the box is just invisible).

Is there some way to improve how I am filtering these records? or modify my code so the value of the box will not be invisible if the filter does not return any records?
Any suggestions would be greatly appreciated. Thanks!

 

[dmuroff]

Hi, I have tried putting each individual Filter in each afterupdate event but it returns no records

 

[dmuroff]

ANYONE??!?!?!?!

 

[PHV]

Hopefully, none of the above combo are bound ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dmuroff]

PHV...ALL the combo boxes are unbound.

Can you help me in any way? Thanks!

 

[TheAceMan1]

How are ya dmuroff . . . . .

[blue]If there are no records which meet this filter the cboVolts combobox displays nothing ([purple]It appears to display nothing but in fact in the text in the box is just invisible[/purple]).[/blue]

[purple]This is not normal behavior![/purple], and from what I gather you have no code [blue]setting the Fore Color to the BackGround Color.[/blue] I've simulated what you've posted and can't duplicate the problem.

Does this happen on the last selected combobox each time no results are returned, or, is it specific to [blue]cboVolts[/blue]?

You could try and set the [blue]Fore Color[/blue] back to visible with [purple]Me!cboVolts.ForeColor=0 [/purple], black in this case. If its just the one combo, try deleting and reconstituting the combo from scratch.

[blue]Is there some way to improve how I am filtering these records?[/blue]

Taking into account a few ambiguities, the code can be shortened. Note that [blue]cboBallastType[/blue], [blue]cboInput[/blue], and [blue]cboType[/blue] are treated as text with [purple]Len(???)[/purple], but are apended as numeric ([blue]no delimiting single quotes[/blue])! I'm believing you just forgot single quotes.

So believing all the combo's are text, and if you change the name of [blue]cboInput to cboInputWatts[/blue] you can replace with the following code:

[blue]Private Sub FilterCommand()
   Dim Build As String, n As Integer
   Dim basName As String, cbName As String
   
   For n = 1 To 8
      basName = Choose(n, "InternalCode", "ballasttype", "InputWatts", _
                          "Type", "lamptype", "base", "watts", "volts")
      cbName = "cbo" & basName
      
      If Len(Me(cbName) & "") > 0 Then
         Build = Build & "(" & basName & "='" & Me(cbName) & "') AND "
      End If
   Next
   
   Me.Filter = Left(Build, Len(Build) - 5) [green]'remove last " AND "[/green]
   If Not Me.FilterOn Then Me.FilterOn = True
   
End Sub[/blue]

[blue]Hi, I have tried putting each individual Filter in each afterupdate event but it returns no records[/blue]

I'm not sure exactly what ya did here, as [blue]your saying individually you get no return records[/blue], yet with your routine you've indicated that you do. Either way, [blue]individual[/blue] is academic [blue]as your intent is a compound filter statement.[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

dmuroff . . . .

[purple]Corrected the code for the case where all combobox text is deleted/removed:[/purple]

[blue]Private Sub FilterCommand()
   Dim Build As String, n As Integer
   Dim basName As String, cbName As String
   
   For n = 1 To 8
      basName = Choose(n, "InternalCode", "ballasttype", "InputWatts", _
                          "Type", "lamptype", "base", "watts", "volts")
      cbName = "cbo" & basName
      
      If Len(Me(cbName) & "") > 0 Then
         Build = Build & "(" & basName & "='" & Me(cbName) & "') AND "
      End If
   Next
   
   If Len(Build & "") = 0 Then
      Me.FilterOn = False
      Me.Filter = ""
   Else
      Me.Filter = Left(Build, Len(Build) - 5) [green]'remove last " AND "[/green]
      If Not Me.FilterOn Then Me.FilterOn = True
   End If
   
End Sub[/blue]

See Ya! . . . . . .

 

[dmuroff]

Hey Ace,

Unfortunately the samething occurs.
Here's what happens: I select a value from cboWatts and it returns the records. Then I select a value from cboBase where I know it will return no records. cboBase goes blank (not invisible) and then it ALWAYS sets focus to cboType. but then when I select another value from cboBase which again returns no records it will display that value in cboBase.
I have set all fields to text fields but the same darn thing happens. I've tried it on a multiple computers and the same thing occurs. I'm running Access 2003.

I have also tried removing all the tab stops from the form but when it the filter returns 'false' it always loses focus on the selected combobox

 

[TheAceMan1]

dmuroff . . . . .

In that case [blue]I have to consider this method unreliable[/blue] and move to another. What were gonna do is use [purple]Dynamic SQL[/purple] instead (won't have any problems here).

What I need you to do is make a query that returns all your current fields, including any sorting. When your done, [purple]post the SQL[/purple]. If your not sure where to get it, goto [purple]SQL View[/purple] while in query design view.

I await . . . . .

See Ya! . . . . . .

 

[dmuroff]

Hi Ace,

I am familiar with SQL but I have tried using it to sort or filter but haven't found a great way to do it yet.

Here is the query that returns all the current fields in the products table

SELECT Products.InternalCode, Products.ItemNumber, Products.Description, Products.NetPrice, Products.QuebecJobPrice, Products.OntarioJobPrice, Products.BCJobPrice, Products.MidwestJobPrice, Products.MaritimesJobPrice, Products.Type, Products.NoLamps, Products.LampType, Products.Base, Products.Volts, Products.StartTemp, Products.InputWattsANSI, Products.BallasFactor, Products.THD, Products.BallastType, Products.Circuit, Products.InputVolts, Products.InputWatts, Products.MinStartTemp, Products.ANSICode, Products.Watts, Products.ColorTemp, Products.CRI, Products.AvgLife, Products.InitialLumens, Products.MOL, Products.CaseQty, Products.Dimensions, Products.Description2, Products.Color, Products.BeamO, Products.Finish, Products.MeanLumens, Products.LCL, Products.Filament, Products.AmpsWatts, Products.MSCP, Products.Amps, Products.Material
FROM Products
ORDER BY Products.InternalCode;

I know that there are are heck of alot but it was the only probable way of designing this database.

Here's the SQL for only those fields are I am required to filter on.

SELECT Products.InternalCode, Products.Type, Products.LampType, Products.Base, Products.Volts, Products.BallastType, Products.InputWatts, Products.Watts
FROM Products
ORDER BY Products.InternalCode;

Hope this is what you were asking for. Thanks!

 

[TheAceMan1]

dmuroff . . . .

Great! I'll get back to ya about 7:00 pm EST . . . . .

See Ya! . . . . . .

 

[TooGoon]

Would you consider modifying the contents of each combo box when it is opened, so that it only displays possible options based upon already filtered criteria?

I don't have any fancy code for this but attach it to the combo boxes' On Got Focus event and set the combo boxes' recordsource to an sql string which is filtered by the other combo box selections.
Maybe insert a number as a reference in the tag property of each cbo, increment through the controls list looking for the cbo's, get their Control Source and value, check for empty combo boxes and whatnot, and append to the existing sql string.

 

[TheAceMan1]

OK dmuroff . . . . .

In the [blue]Dynamic SQL[/blue], where just [blue]properly cancatenating in the criteria[/blue] and updating the [blue]RecordSource[/blue] of the form:

[blue]Private Sub FilterCommand()
   Dim SQL As String, n As Integer
   Dim basName As String, cbName As String
   
   [green]'The Base SQL[/green]
   SQL = "SELECT InternalCode, ItemNumber, Description, " & _
                 "NetPrice, QuebecJobPrice, OntarioJobPrice, " & _
                 "BCJobPrice, MidwestJobPrice, MaritimesJobPrice, " & _
                 "Type, NoLamps, LampType, Base, Volts, StartTemp, " & _
                 "InputWattsANSI, BallasFactor, THD, BallastType, " & _
                 "Circuit, InputVolts, InputWatts, MinStartTemp, " & _
                 "ANSICode, Watts, ColorTemp, CRI, AvgLife, " & _
                 "InitialLumens, MOL, CaseQty, Dimensions, " & _
                 "Description2, Color, BeamO, Finish, MeanLumens, " & _
                 "LCL, Filament, AmpsWatts, MSCP, Amps, Material " & _
         "FROM Products "

   [green]'Concatenate Criteria[/green]
   For n = 1 To 8
      basName = Choose(n, "InternalCode", "ballasttype", "InputWatts", _
                          "Type", "lamptype", "base", "watts", "volts")
      [green]'Pack combobox name[/green]
      cbName = "cbo" & basName
      
      [green]'Skip concatenation if combobox has no selection[/green]
      If Len(Me(cbName) & "") > 0 Then
         [green]'Since criteria exist, check if Where clause has been added.
         'If not, make it so![/green]
         If Right(SQL, 6) <> "Where " Then SQL = SQL & "Where "
         [green]'Concatenate criteria here . . .[/green]
         SQL = SQL & "(" & basName & "='" & Me(cbName) & "') AND "
      End If
   Next
   
   [green]'Check if criteria was added. If yes, remove last "AND " leaving
   'leader space for concatenation of Order By. If no, concatenation of
   'Order By produces Base SQL and all records show! AKA No Criteria![/green]
   If Right(SQL, 4) = "AND " Then SQL = Left(SQL, Len(SQL) - 4)
   
   [green]'Concatenate Order By & complete SQL[/green]
   SQL = SQL & "ORDER BY InternalCode;"
   
   [green]'Finally, update the forms recordSource & view results[/green]
   Me.RecordSource = SQL
   
End Sub[/blue]

[purple]Thats it . . . . give it a whirl & let me know . . . .[/purple]

See Ya! . . . . . .

 

[dmuroff]

Hey there Ace!

Thank you very much for your help. Just one thing. If I select values from 2 comboboxes I recieve R/T error 3085:undefined function 'where' in expression.

This happens always no matter if it returns records or not.

Thanks!

 

[dmuroff]

Ace,

It appears to be writing 'Where' twice to the expression:

SELECT InternalCode, ItemNumber, Description, NetPrice, QuebecJobPrice, OntarioJobPrice, BCJobPrice, MidwestJobPrice, MaritimesJobPrice, Type, NoLamps, LampType, Base, Volts, StartTemp, InputWattsANSI, BallasFactor, THD, BallastType, Circuit, InputVolts, InputWatts, MinStartTemp, ANSICode, Watts, ColorTemp, CRI, AvgLife, InitialLumens, MOL, CaseQty, Dimensions, Description2, Color, BeamO, Finish, MeanLumens, LCL, Filament, AmpsWatts, MSCP, Amps, Material FROM Products Where (watts='105') AND Where (volts='120') ORDER BY InternalCode;

I'll try my best to figure out what the problem is and I will post back.Thanks!

 

[PHV]

Replace this:
If Right(SQL, 6) <> "Where "
By this:
If Left(SQL, 6) <> "Where "

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dmuroff]

solved it!

Just modified your code a bit:

If Count <> 1 Then
            If Right(SQL, 6) <> "Where " Then
                SQL = SQL & "Where "
                Count = 1
            End If
          End If
         'Concatenate criteria here . . .
         SQL = SQL & "(" & basName & "='" & Me(cbName) & "') AND "
      End If

But you would not believe this: The same damn thing happens. The damn combobox still display nothing if no records are found. I guess I'm just gonna have to live with it. Really doesn't make any sense to me. would it make any difference if the records were displayed in a subform instead of the main form? I don't think this would make a difference but I'm sure you know better than I do.

Nevertheless this was a good lesson in Dynamic SQL and it sure beats the damn built-in Microsoft filtering!

Thanks for all the help Ace. Really appreciate it!

 

[dmuroff]

TooGoon:

Thanks for the advice. It might actually get to that point where users will only be able to select valid records. I'll test that out today and post back. Thanks!

 

[TheAceMan1]

dmuroff . . . .

Can't use [purple]If Right(SQL, 6) <> "Where "[/purple] because each time criteria is added the [blue]expression evaluates to True.[/blue] So we just need to flag [blue]Where[/blue] . . . .

So . . . . add the new variable in purple:

[blue]Dim SQL As String, n As Integer, [purple][b]flgWhere As Boolean[/b][/purple][/blue]

Then:

[blue][purple][b]Replace:[/b][/purple]
         'If not, make it so!
         If Right(SQL, 6) <> "Where " Then SQL = SQL & "Where "
[purple][b]With:[/b][/purple]
         'If not, make it so!
         If Not flgWhere then
            SQL = SQL & "Where "
            flgWhere = True
         End If[/blue]

See Ya! . . . . . .

 

[dmuroff]

Hi Fellas,

Just one last one. If I were to take TooGoon's advice and when one combo box is selected then it would change the rowsource of the other combo box to display only those values where a record exists, How would I go about this?

Is it just a matter of modifying AceMan's code?

Thanks