List box filter

E

elfranzen

Registered User.
Local time
Yesterday, 16:30
Joined
Jul 26, 2007
Messages
93
I have attched my database. Now this is stripped down I just want get the code working and will then move over to my main database. I am trying to filter a list box buy picking from combo boxes and then the last filter will be from typing in from a text box. Take a look and see if you can help me out.

Thanks Erik
 

Attachments

I've basically solved the search-as-you type problem for the text box. Getting the combos included shouldn't be too bad.
What I'd do to get all three working together is put the listbox rowsource assignment in a separate subroutine, and call that routine whenever one of the search controls changes. In this example I do the list box rowsource assignment in the change event handler for the text box, but you'll need each of the search controls to perform this same task. Right? A change to any of the controls needs to modify the rowsource of the list.
Get it? Lemme know.
 

Attachments

lagbolt said:
I've basically solved the search-as-you type problem for the text box. Getting the combos included shouldn't be too bad.
What I'd do to get all three working together is put the listbox rowsource assignment in a separate subroutine, and call that routine whenever one of the search controls changes. In this example I do the list box rowsource assignment in the change event handler for the text box, but you'll need each of the search controls to perform this same task. Right? A change to any of the controls needs to modify the rowsource of the list.
Get it? Lemme know.
Click to expand...

Wow, I think I can get the other dropdowns to work this is such a big help.

Thank you so much
 
Private Sub Form_Open(Cancel As Integer)
'run the change event of the textbox to make sure the list rowsource is correct
tbThemeSearch_Change
End Sub
OK what is this for?

Private Sub tbThemeSearch_Change()
On Error GoTo handler
Dim where As String

'construct a where clause as required
If Nz(Me.tbThemeSearch.Text, "") <> "" Then
where = "WHERE Description LIKE '*" & Me.tbThemeSearch.Text & "*' "
End If
OK this says if Text field is not blank then make a where clause (example: like *what ever you type*) and put it in the string where
final:
don't get what the Final: is for
'modify the row source of the list box
Me.list.RowSource = _
"SELECT ID, Description, Par, MaxCoins, PayLines " & _
This is telling the listbox what to do
"FROM MachineTypeQuery " & _
select the fileds to use (don't get the &_ at the end)
where & _
I am guessing this is the "Where" sting and telling to query to use this to filter the query(again still don't get the &_ as the end)
"ORDER BY Description;"
And then order by description

Exit Sub

handler:
If Err = 2185 Then
what is error code 2185? and there is nothing the in the then statement
'You can't reference a property or method for a control unless the control has the focus.
Else
MsgBox Err & " " & Err.Description
End If
Resume final

End Sub
Again Thanks for all the help
 
in the rowsource what if I wanted two "where" to filter by how would I type that on the line I tried different ways but keep erroring out on me

examples

"where, where1" &_
where, where1 &_

can't seem to get it to work

any help would be very helpful Thanks
 
"tbThemeSearch_Change" is called from the Form_Open() event handler to ensure that the row source of the text box is set up properly when the form opens. Comment it out to see what happens if you comment it out.

"final:" is where the error handler begins re-execution of code if there is an error in the previous block. In all cases, regardless of errors, the block following this label must run. See the line "Resume final" in the error handler.

"&" is the plus sign for strings. The string before and the string after the "&" are what's called concatenated--stuck together.

"_" is a line continuation character. The line it's on and the line after are the same line of code.

"& _" used in conjunction join the string on the current line with the string on the next line. This is commonly done to make long strings more readable.

Error code 2185 is 'You can't reference a property or method for a control unless the control has the focus.' and if this error occurs it should be ignored, thus there is no code in the block. It's reasonable to expect this error to occur. Errors that are not expected will be handled by the ELSE block.
- Note that during the Change event of a textbox the .Value property of the control doesn't change despite the fact that the user has indeed entered text into the control. In this case, to create a new SQL statement for each character a user enters you must reference the .Text property of the control, which occurs a couple of times in the first IF Block. This, however, causes error 2185 if said control does not have the focus.

If you wanted additional restrictions included in the where clause, and you will if you want to include the user's selection in the combos, you use "AND".
Code: 
Me.list.RowSource = _
  "SELECT ID, Description, Par, MaxCoins, PayLines " & _
  "FROM MachineTypeQuery " & _
  "WHERE Description LIKE '*" & me.somestring & "*' & _
    "AND Par >= " & me.someparvalue & " " & _
  "ORDER BY Description;"

Good questions. Hope this helps. Also, if your brain feels like melted cheese you're on the right track.
:)
 
Thanks for all the Help Here is the Code I came up with for all three input fields. I haven't put any documentation in yet. I am sure there is a better way of doing this but this works. I will also attach a sample data base with the working code if anybody would like to check it out.

Code: 
Option Compare Database
Option Explicit

Private Sub Denom_Change()

   'construct a where clause as required

If Nz(Me.Denom.Text, "") <> "" Then
    If IsNull(Manufacturer) Then
        
            If IsNull(tbThemeSearch) Then
                Whereall = "WHERE DenomFix LIKE '" & Me.Denom.Text & "*' "
                GoTo RunListQuery
            
                If tbThemeSearch.Value = "" Then
                    Whereall = "WHERE DenomFix LIKE '" & Me.Denom.Text & "*' "
                    GoTo RunListQuery
                End If
                Else
                    Where1 = "WHERE DenomFix LIKE '" & Me.Denom.Text & "*' "
                    tbThemeSearch.SetFocus
                    Where2 = "And Description LIKE '*" & Me.tbThemeSearch.Text & "*' "
                    Whereall = Where1 & Where2
                    GoTo RunListQuery
                End If
       
       
    
    Else
        If tbThemeSearch.Value = "" Then
            Where1 = "WHERE DenomFix LIKE '" & Me.Denom.Text & "*' "
            Where2 = "And MFRcode LIKE '*" & Me.Manufacturer.Column(0) & "*' "
            Me.tbThemeSearch.SetFocus
            Where3 = "And Description LIKE '*" & Me.tbThemeSearch.Text & "*' "
            Whereall = Where1 & Where2 & Where3
            GoTo RunListQuery
          Else
            If IsNull(tbThemeSearch) Then
               Where1 = "WHERE DenomFix LIKE '" & Me.Denom.Text & "*' "
                Where2 = "And MFRcode LIKE '*" & Me.Manufacturer.Column(0) & "*' "
                Whereall = Where1 & Where2
                GoTo RunListQuery
                
                Else
                
            
                Where1 = "WHERE DenomFix LIKE '" & Me.Denom.Text & "*' "
                Where2 = "And MFRcode LIKE '*" & Me.Manufacturer.Column(0) & "*' "
                Me.tbThemeSearch.SetFocus
                Where3 = "And Description LIKE '*" & Me.tbThemeSearch.Text & "*' "
                Whereall = Where1 & Where2 & Where3
                GoTo RunListQuery
            End If
    
        End If
    End If
        
Else
    If IsNull(Manufacturer) Then
        If Manufacturer.Value = "" Then
            If IsNull(tbThemeSearch) Then
            Exit Sub
            End If
            Else
            tbThemeSearch.SetFocus
            Whereall = "Where Description LIKE '*" & Me.tbThemeSearch.Text & "*' "
            GoTo RunListQuery
        End If
        
    Else
        If IsNull(tbThemeSearch) Then
            If tbThemeSearch.Value = "" Then
            Exit Sub
            Else
                Whereall = "Where MFRcode LIKE '*" & Me.Manufacturer.Column(0) & "*' "
                GoTo RunListQuery
            End If
        Else
        Where1 = "Where MFRcode LIKE '*" & Me.Manufacturer.Column(0) & "*' "
        tbThemeSearch.SetFocus
        Where2 = "And Description LIKE '*" & Me.tbThemeSearch.Text & "*' "
        Whereall = Where1 & Where2
        GoTo RunListQuery
        End If
    End If
End If
Exit Sub
   
RunListQuery:
   
   Me.list.RowSource = _
      "SELECT ID, Description, Par, MaxCoins, PayLines " & _
      "FROM MachineTypeQuery " & _
      Whereall & _
      "ORDER BY Description;"
   
   Denom.SetFocus
   Exit Sub
   
End Sub

Private Sub Form_Open(Cancel As Integer)
   'run the change event of the textbox to make sure the list rowsource is correct
   tbThemeSearch_Change
  
   
End Sub

Private Sub Manufacturer_Change()

If Nz(Me.Manufacturer.Text, "") <> "" Then
    If IsNull(Denom) Then
        
            If IsNull(tbThemeSearch) Then
                Whereall = "WHERE MFRcode LIKE '" & Me.Manufacturer.Column(0) & "*' "
                GoTo RunListQuery
            
                If tbThemeSearch.Value = "" Then
                    Whereall = "WHERE MFRcode LIKE '" & Me.Manufacturer.Column(0) & "*' "
                    GoTo RunListQuery
                End If
                Else
                    Where1 = "WHERE MFRcode LIKE '" & Me.Manufacturer.Column(0) & "*' "
                    tbThemeSearch.SetFocus
                    Where2 = "And Description LIKE '*" & Me.tbThemeSearch.Text & "*' "
                    Whereall = Where1 & Where2
                    GoTo RunListQuery
                End If
       
       
    
    Else
        If tbThemeSearch.Value = "" Then
            Where1 = "WHERE Manufacturer LIKE '" & Me.Manufacturer.Column(0) & "*' "
            Where2 = "And denom LIKE '*" & Me.Denom.Text & "*' "
            Me.tbThemeSearch.SetFocus
            Where3 = "And Description LIKE '*" & Me.tbThemeSearch.Text & "*' "
            Whereall = Where1 & Where2 & Where3
            GoTo RunListQuery
          Else
            If IsNull(tbThemeSearch) Then
               Where1 = "WHERE MFRcode LIKE '" & Me.Manufacturer.Column(0) & "*' "
                Denom.SetFocus
                Where2 = "And Denomfix LIKE '*" & Me.Denom.Text & "*' "
                Whereall = Where1 & Where2
                GoTo RunListQuery
                
                Else
                
            
                Denom.SetFocus
                Where1 = "WHERE DenomFix LIKE '" & Me.Denom.Text & "*' "
                Manufacturer.SetFocus
                Where2 = "And MFRcode LIKE '*" & Me.Manufacturer.Column(0) & "*' "
                Me.tbThemeSearch.SetFocus
                Where3 = "And Description LIKE '*" & Me.tbThemeSearch.Text & "*' "
                Whereall = Where1 & Where2 & Where3
                GoTo RunListQuery
            End If
    
        End If
    End If
        
Else
    If IsNull(Denom) Then
        tbThemeSearch.SetFocus
            Whereall = "Where Description LIKE '*" & Me.tbThemeSearch.Text & "*' "
        GoTo RunListQuery
        
        If Denom.Value = "" Then
            If IsNull(tbThemeSearch) Then
            Exit Sub
            End If
            Else
            tbThemeSearch.SetFocus
            Whereall = "Where Description LIKE '*" & Me.tbThemeSearch.Text & "*' "
            GoTo RunListQuery
        End If
        
    Else
        If tbThemeSearch.Value = "" Then
            If IsNull(tbThemeSearch) Then
            Exit Sub
            Else
                Whereall = "Where MFRcode LIKE '*" & Me.Manufacturer.Column(0) & "*' "
                GoTo RunListQuery
            End If
        Else
        Denom.SetFocus
        Where1 = "Where denomfix LIKE '*" & Denom.Text & "*' "
        tbThemeSearch.SetFocus
        Where2 = "And Description LIKE '*" & Me.tbThemeSearch.Text & "*' "
        Whereall = Where1 & Where2
        GoTo RunListQuery
        End If
    End If
End If
Exit Sub
   
RunListQuery:
   
   Me.list.RowSource = _
      "SELECT ID, Description, Par, MaxCoins, PayLines " & _
      "FROM MachineTypeQuery " & _
      Whereall & _
      "ORDER BY Description;"
   
  Manufacturer.SetFocus
   Exit Sub
   
End Sub

Private Sub tbThemeSearch_Change()

tbThemeSearch.SetFocus
If Nz(Me.tbThemeSearch.Text, "") <> "" Then
    If IsNull(Manufacturer) Then
        
            If IsNull(Denom) Then
                Whereall = "WHERE description LIKE '*" & Me.tbThemeSearch.Text & "*' "
                GoTo RunListQuery
            
                If Denom.Value = "" Then
                    Whereall = "WHERE description LIKE '*" & Me.tbThemeSearch.Text & "*' "
                    GoTo RunListQuery
                End If
                Else
                    Where1 = "WHERE description LIKE '*" & Me.tbThemeSearch.Text & "*' "
                    Denom.SetFocus
                    Where2 = "And denomfix LIKE '" & Me.Denom.Text & "*' "
                    Whereall = Where1 & Where2
                    GoTo RunListQuery
                End If
       
       
    
    Else
        If Denom.Value = "" Then
            Where1 = "WHERE DenomFix LIKE '" & Me.Denom.Text & "*' "
            Me.Manufacturer.SetFocus
            Where2 = "And MFRcode LIKE '*" & Me.Manufacturer.Column(0) & "*' "
            Me.tbThemeSearch.SetFocus
            Where3 = "And Description LIKE '*" & Me.tbThemeSearch.Text & "*' "
            Whereall = Where1 & Where2 & Where3
            GoTo RunListQuery
          Else
            If IsNull(Denom) Then
               Where1 = "WHERE description LIKE '*" & Me.tbThemeSearch.Text & "*' "
                Where2 = "And MFRcode LIKE '*" & Me.Manufacturer.Column(0) & "*' "
                Whereall = Where1 & Where2
                GoTo RunListQuery
                
                Else
                
                Denom.SetFocus
                Where1 = "WHERE DenomFix LIKE '" & Me.Denom.Text & "*' "
                Denom.SetFocus
                Where2 = "And MFRcode LIKE '*" & Me.Manufacturer.Column(0) & "*' "
                Me.tbThemeSearch.SetFocus
                Where3 = "And Description LIKE '*" & Me.tbThemeSearch.Text & "*' "
                Whereall = Where1 & Where2 & Where3
                GoTo RunListQuery
            End If
    
        End If
    End If
        
Else
    If IsNull(Manufacturer) Then
        If Manufacturer.Value = "" Then
            If IsNull(Denom) Then
            Exit Sub
            End If
            Else
            Denom.SetFocus
            Whereall = "Where denomfix LIKE '*" & Me.Denom.Text & "*' "
            GoTo RunListQuery
        End If
        
    Else
        If IsNull(Denom) Then
            If Denom.Value = "" Then
            Exit Sub
            Else
                Whereall = "Where MFRcode LIKE '*" & Me.Manufacturer.Column(0) & "*' "
                GoTo RunListQuery
            End If
        Else
        Manufacturer.SetFocus
        Where1 = "Where MFRcode LIKE '*" & Me.Manufacturer.Column(0) & "*' "
        Denom.SetFocus
        Where2 = "And denomfix LIKE '*" & Me.Denom.Text & "*' "
        Whereall = Where1 & Where2
        GoTo RunListQuery
        End If
    End If
End If
Exit Sub
   
RunListQuery:
   
   Me.list.RowSource = _
      "SELECT ID, Description, Par, MaxCoins, PayLines " & _
      "FROM MachineTypeQuery " & _
      Whereall & _
      "ORDER BY Description;"
   
 tbThemeSearch.SetFocus
 
If IsNull(tbThemeSearch) Then
Exit Sub
Else
 With Me.tbThemeSearch
.SetFocus
.SelStart = Len(Me.tbThemeSearch)
End With
End If
 
   
   Exit Sub
   
End Sub

Thanks for all the help
 

Attachments

And here's how I'd solve it. See how all the work is done, regardless of which control generated the change, by the ApplyListBoxFilter() routine?
Whenever you see code repeated, you'll want to consider a subroutine.
And I've thrown in a resize handler you might like.
 

Attachments

Wow, that’s not even funny. Well now I know and knowing is half the battle. Well I knew there was a simpler way but I thought I would give it a try. There is enough if then statements to make your head hurt in mine. I have the logic down I just need to know all the little short cuts like this one.:)
 
ApplyListBoxFilter Nz(Me.Denom, ""), Nz(Me.Manufacturer, ""), Nz(Me.tbThemeSearch, "")

Could you help me out with this what is this code doing?
 
Because the ApplyListBoxFilter() routine's parameters are string data types, an error will occur if we attempt to pass in a Null value, and the Nz() function always returns a string.
Code: 
Nz(<expression>, <return value if expression is null>)
Check it out in Access help for more details.
 
You must log in or register to reply here.

Similar threads

G
Open Filter Dialog box
Replies
12
Views
585
LarryE
LarryE
M
Open Unbound form and filter subform
Replies
7
Views
276
Gasman
Gasman
H
Problem with Search Boxes
2
Replies
24
Views
935
Pat Hartman
P
HavingDatabaseRelations
Solved Combo Box will only work if Access is not minimized
Replies
10
Views
233
Mike Krailo
Mike Krailo
I
Make Table from filtered subform
Replies
4
Views
347
mike60smart
mike60smart

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom