textbox filter records in continuous form (1 Viewer)

E

eugzl

Member
Local time
Today, 16:09
Joined
Oct 26, 2021
Messages
125
Hi All.
I created continuous form and trying create filter textbox. Online I found code exactly that I expecting to create.
Code: 
Private Sub txtNameFilter_KeyUp(KeyCode As Integer, Shift As Integer)
On Error GoTo errHandler
Dim filterText As String

'Apply or update filter based on user input.
If Len(txtNameFilter.Text) > 0 Then
   filterText = txtNameFilter.Text
   Me.Form.Filter = "[Contacts]![first_name] LIKE '*" & filterText & "*' OR [Contacts]![last_name] LIKE '*" & filterText & "*'"
   Me.FilterOn = True
  'Retain filter text in search box after refresh.
   txtNameFilter.Text = filterText
   txtNameFilter.SelStart = Len(txtNameFilter.Text)
Else
   ' Remove filter.
   Me.Filter = ""
   Me.FilterOn = False
   txtNameFilter.SetFocus
End If

Exit Sub

errHandler:
MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."
End Sub
But unfortunately it doesn't work. If it possible to fix the bug. Can someone show how it to do? Or may exist other similar idea. I will appreciate for help.
Thanks
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 21:09
Joined
Feb 19, 2013
Messages
16,608
what does 'it doesn't work' mean? nothing happens? wrong result? you get an error? in which case what is the error? and what line of code is highlighted?

best guess is

Me.Form.Filter

should be

Me.Filter
 
E

eugzl

Member
Local time
Today, 16:09
Joined
Oct 26, 2021
Messages
125
Hi CJ_London. Thanks for reply.
It is my first Access project. I also saw online like Me.Filter but code that posted from this site:
https://www.comeausoftware.com/2019/01/create-dynamic-search-filter-microsoft-access/
I modified the code for my data:
Code: 
Private Sub txtSearch_KeyUp(KeyCode As Integer, Shift As Integer)
    On Error GoTo errHandler

    Dim filterText As String

    'Apply or update filter based on user input.
    If Len(txtSearch.Text) > 0 Then
       filterText = txtSearch.Text
       Me.Form.Filter = "[Form2]![Date] LIKE '*" & filterText & "*' OR [Form2]![EmployeeName] LIKE '*" & filterText & "*' " _
                        & "OR [Form2]![TicketNo] LIKE '*" & filterText & "*' OR [Form2]![Device] LIKE '*" & filterText & "*' " _
                        & "OR [Form2]![Model] LIKE '*" & filterText & "*'"

       Me.FilterOn = True
      'Retain filter text in search box after refresh.
       txtSearch.Text = filterText
       txtSearch.SelStart = Len(txtSearch.Text)
    Else
       ' Remove filter.
       Me.Filter = ""
       Me.FilterOn = False
       txtSearch.SetFocus
    End If

    Exit Sub

errHandler:
    MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."
End Sub
When I entered just first character in txtSearch TextBox as a result I'm getting message:
Enter parameter value ?
Form2!Date
when I click OK
Enter parameter value ?
Form2!EmployeeName
when I click OK
and so on
After all the result is a blank form.

When I click Cancel I'm getting message:
3709 The search key was not found in any record.

I will appreciate you show how to fix it.
Thanks
 
Last edited:
C

CJ_London

Super Moderator
Staff member
Local time
Today, 21:09
Joined
Feb 19, 2013
Messages
16,608
so what is form2? you are querying the underlying table so you need to use the names of the fields in that table. Note that Date is a reserved word (it is a function that returns todays date) and should not be used as a field or control name - rename to something more meaningful such as 'startDate', whatever date means.

Also dates are numbers, not as you see them formatted. For example today is 44583 and now is 44583.9615972222. The value after the decimal point is the time expressed as number of seconds so far today divided by 86400 - the number of seconds in a day

So don't expect it to return anything with a character in the search string - 'Jan' will not return January and '21' will not return any date with the 21st. But it will return 4, 5, 8 or 3 because they are in 44583.
 
E

eugzl

Member
Local time
Today, 16:09
Joined
Oct 26, 2021
Messages
125
I modified the code:
Code: 
Private Sub txtSearch_Change()

    On Error GoTo errHandler

    Dim filterText As String

    'Apply or update filter based on user input.
    If Len(txtSearch.Text) > 0 Then
       filterText = [Forms]![Form2]![txtSearch].[Text]
        If InStr(Format(filterText, "\#mm\/dd\/yyyy\#"), "#") > 0 Then
             Me.Form.Filter = "[Forms]![Form2]![RequestDate] LIKE '*" & filterText & "*' OR [Forms]![Form2]![EmployeeName] LIKE '*" & filterText & "*' " _
                              & "OR [Forms]![Form2]![TicketNo] LIKE '*" & filterText & "*' OR [Forms]![Form2]![Device] LIKE '*" & filterText & "*' " _
                              & "OR [Forms]![Form2]![Model] LIKE '*" & filterText & "*' OR [Forms]![Form2]![Location] LIKE '*" & filterText & "*'"
        Else
            Me.Form.Filter = "[Forms]![Form2]![EmployeeName] LIKE '*" & filterText & "*' " _
                             & "OR [Forms]![Form2]![TicketNo] LIKE '*" & filterText & "*' OR [Forms]![Form2]![Device] LIKE '*" & filterText & "*' " _
                             & "OR [Forms]![Form2]![Model] LIKE '*" & filterText & "*' OR [Forms]![Form2]![Location] LIKE '*" & filterText & "*'"
        End If

       Me.FilterOn = True
      'Retain filter text in search box after refresh.
       txtSearch.Text = filterText
       txtSearch.SelStart = Len(txtSearch.Text)
    Else
       ' Remove filter.
       Me.Filter = ""
       Me.FilterOn = False
       txtSearch.SetFocus
    End If

    Exit Sub

errHandler:
    MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."
End Sub
Now when I'm entering some characters I don't get any messages. But form didn't filter also.
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 21:09
Joined
Feb 19, 2013
Messages
16,608
I'm getting confused. in your first post your code was

Me.Form.Filter = "[Contacts]![first_name] LIKE
Click to expand...

then it changed to
Me.Form.Filter = "[Form2]![Date] LIKE
Click to expand...

and then it changed to

Me.Form.Filter = "[Forms]![Form2]![RequestDate] LIKE
Click to expand...

Your first post looks correct in principle but 'it doesn't work', your subsequent posts imply you are thrashing around

Implication is what you posted in your first post is not what you are actually using. If what you are actually using is in the second post then [Form2]![Date] is wrong - it should be the name of the field in your forms recordsource, not the name of the control and you do not reference the form.

If the field is called Date you just need [Date], not Form2!date and not forms!form2!date. You might need to include the name of the table if your recordsource contains two fields called 'Date' from two different tables

you've also added another line

filterText = [Forms]![Form2]![txtSearch].[Text]

if this code in is form2 then all you need us
filterText = [txtSearch].[Text]

Or is your txtSearch control now on a different form to form2?

Take the date reference out for now, it is not right and just just confusing things, get the principle right for text fields and go from there
 
E

eugzl

Member
Local time
Today, 16:09
Joined
Oct 26, 2021
Messages
125
CJ_London said:
I'm getting confused. in your first post your code was



then it changed to


and then it changed to



Your first post looks correct in principle but 'it doesn't work', your subsequent posts imply you are thrashing around

Implication is what you posted in your first post is not what you are actually using. If what you are actually using is in the second post then [Form2]![Date] is wrong - it should be the name of the field in your forms recordsource, not the name of the control and you do not reference the form.

If the field is called Date you just need [Date], not Form2!date and not forms!form2!date. You might need to include the name of the table if your recordsource contains two fields called 'Date' from two different tables

you've also added another line

filterText = [Forms]![Form2]![txtSearch].[Text]

if this code in is form2 then all you need us
filterText = [txtSearch].[Text]

Or is your txtSearch control now on a different form to form2?

Take the date reference out for now, it is not right and just just confusing things, get the principle right for text fields and go from there
Click to expand...
OK I changed like you suggested
Code: 
Private Sub txtSearch_Change()

    On Error GoTo errHandler

    Dim filterText As String

    'Apply or update filter based on user input.
    If Len(txtSearch.Text) > 0 Then
       filterText =txtSearch.Text
        If InStr(Format(filterText, "\#mm\/dd\/yyyy\#"), "#") > 0 Then
             Me.Form.Filter = "[Forms]![Form2]![RequestDate] LIKE '*" & filterText & "*' OR [Forms]![Form2]![EmployeeName] LIKE '*" & filterText & "*' " _
                              & "OR [Forms]![Form2]![TicketNo] LIKE '*" & filterText & "*' OR [Forms]![Form2]![Device] LIKE '*" & filterText & "*' " _
                              & "OR [Forms]![Form2]![Model] LIKE '*" & filterText & "*' OR [Forms]![Form2]![Location] LIKE '*" & filterText & "*'"
        Else
            Me.Form.Filter = "[Forms]![Form2]![EmployeeName] LIKE '*" & filterText & "*' " _
                             & "OR [Forms]![Form2]![TicketNo] LIKE '*" & filterText & "*' OR [Forms]![Form2]![Device] LIKE '*" & filterText & "*' " _
                             & "OR [Forms]![Form2]![Model] LIKE '*" & filterText & "*' OR [Forms]![Form2]![Location] LIKE '*" & filterText & "*'"
        End If

       Me.FilterOn = True
      'Retain filter text in search box after refresh.
       txtSearch.Text = filterText
       txtSearch.SelStart = Len(txtSearch.Text)
    Else
       ' Remove filter.
       Me.Filter = ""
       Me.FilterOn = False
       txtSearch.SetFocus
    End If

    Exit Sub

errHandler:
    MsgBox Err.Number & " - " & Err.Description, vbOKOnly, "Error ..."
End Sub

The continuous form - Form2.
The RecordSource fields - RequestDate, EmployeeName, TicketNo, Device, Model, Location
The search textbox - txtSearch
When I specify criteria in the query, for instance, for EmployeeName and setup criteria Like "*we*" and run query I got record where EmployeeName field has such characters.
When I enter to txtSearch field value= we and then check in the Property Sheet under Data tag Filter value it show me like this:
[Forms]![Form2]![EmployeeName] LIKE '*we*' OR [Forms]![Form2]![TicketNo] LIKE '*we*' OR [Forms]![Form2]![Device] LIKE '*we*' OR [Forms]![Form2]![Model] LIKE '*we*' OR [Forms]![Form2]![Location] LIKE '*we*'
But code execution return blank form.
 
Last edited:
C

CJ_London

Super Moderator
Staff member
Local time
Today, 21:09
Joined
Feb 19, 2013
Messages
16,608
So you have changed it yet again

I said put it back to this

Me.Form.Filter = "[Contacts]![first_name] LIKE '*" & ......

you have responded with

[Forms]![Form2]![EmployeeName] LIKE '*we*' OR.......

As previously advised remove '[Forms]![Form2]!'
 
Last edited:
E

eugzl

Member
Local time
Today, 16:09
Joined
Oct 26, 2021
Messages
125
CJ_London said:
So you have changed it yet again

I said put it back to this

Me.Form.Filter = "[Contacts]![first_name] LIKE '*" & ......

you have responded with

[Forms]![Form2]![EmployeeName] LIKE '*we*' OR.......

As previously advised remove '[Forms]![Form2]!'
Click to expand...
Hi CJ_London. Thanks for continue help.
I substitute RowSource query and also made changes like you suggested. Now a filter is working. Thanks.
 
You must log in or register to reply here.

Similar threads

E
how to optimize search process
Replies
14
Views
1,041
Pat Hartman
P
S
Solved Sub procedure to filter different textboxes in subform
Replies
5
Views
273
silentwolf
S
N
Solved How to filter a single form in MS Access with VBA
Replies
12
Views
471
nector
N
C
Filter report with two criteria using combo boxes and button on form.
Replies
4
Views
271
CraigDouglas
C
D
Dynamic Filter on Unbound Forms
Replies
15
Views
1,641
pbaldy
pbaldy

Users who are viewing this thread

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