Need Help with VBA Code for Access Form Filtering

[nimeshaanethmini]

Hey guys......

I’m working on a project in Microsoft Access and have hit a bit of a roadblock with some VBA code. I’m trying to filter records on a form based on user input, but I’m having trouble getting it to work as expected.

Here’s what I’m aiming to do:

I have a form with several fields, and I want users to be able to enter criteria into these fields to filter the records displayed in a continuous subform. The filtering should be dynamic, meaning that as users type in each field, the records in the subform should automatically update to match the criteria.

So far, I’ve set up the form and subform, and I’ve written some basic VBA code to handle the filtering. However, I’m encountering issues with the filter not applying correctly, and sometimes it even results in an error.

Here is the VBA code:

Private Sub txtFilter_AfterUpdate()
    Dim strFilter As String
    
    strFilter = "1=1" ' Start with a base condition that’s always true
    
    If Not IsNull(Me.txtField1) Then
        strFilter = strFilter & " AND Field1 LIKE '*" & Me.txtField1 & "*'"
    End If
    
    If Not IsNull(Me.txtField2) Then
        strFilter = strFilter & " AND Field2 LIKE '*" & Me.txtField2 & "*'"
    End If
    
    Me.subForm.Form.Filter = strFilter
    Me.subForm.Form.FilterOn = True
End Sub

A few things I’m struggling with:

1. Handling Null Values: The current setup should handle null values, but sometimes I get an error when the form loads if a field is left blank. I’m not sure if this is causing issues with the filtering.
2. Special Characters: I need to make sure the filter works correctly even if the user enters special characters. The current implementation uses LIKE with wildcards, but I’m concerned about how special characters might affect this.
3. Performance: The form is becoming slower with more records. Are there any performance considerations or best practices I should be aware of for optimizing this filtering process?
4. Error Handling: I’d like to add some error handling to provide user-friendly messages if something goes wrong. What’s the best way to incorporate error handling into this VBA code?

Thanks!

 

[arnelgp]

you need filter based on txtFilter and not on txtField1 and txtField2?

 

[Gasman]

Add a Debug.Print strFilter and see what you have, not what you think you have?

you need filter based on txtFilter and not on txtField1 and txtField2?

You have me confused there @arnelgp, as long as txtFilter is updated, that should work, should it not?
I would use a button myself.

 

[moke123]

The filtering should be dynamic, meaning that as users type in each field, the records in the subform should automatically update to match the criteria.

What you are referring to is commonly known as "Filter as you type" or FAYT. If you search this site or google you should find something.

It will look something like ( the below has glitches but should give you the general idea.)

Private blnSpace As Boolean

Private Sub Text3_Change()

    Dim strFilter As String

    If blnSpace = False Then
   
        strFilter = "FirstName Like ""*" & Me.Text3.Text & "*"" or LastName like ""*" & Me.Text3.Text & "*"""
        Me.Filter = strFilter
        Me.Requery
        Me.Refresh
        Me.Text3.SetFocus
        Me.Text3.SelStart = Len(Me.Text3.Text)
       
    End If
   
    Me.FilterOn = True
   
   
End Sub
 
Private Sub Text3_KeyPress(KeyAscii As Integer)

    If KeyAscii = 32 Then
        blnSpace = True
    Else
        blnSpace = False
    End If
   
End Sub

 

[Pat Hartman]

The form is becoming slower with more records. Are there any performance considerations or best practices I should be aware of for optimizing this filtering process?

And this is surprising, why? Every time you type a character you are asking Access to run the query again. This is a cute effect but not recommended for larger recordsets. And it is far, far worse as a technique for SQL Server recordsets because each time you run the query you must reselect ALL the rows of a table/query.

If you must use the filter concept, don't requery the form until the user finishes the data entry.

If you wanted to do this and were willing to code it yourself, you could use an in memory recordset and never go back to the table to refresh the data. If your tables are not volatile, that would probably work but you would miss any record that was added or changed after you read your copy into memory.

 

[Uncle Gizmo]

I created the following search form as per the YouTube video:-

It can be installed, set up and working in about four minutes!

Please send me a private message within Access World Forums and I will send you a free copy.