By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,073 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

How do I create a filter in a form

P: 2

I'm a complete newbie to Access but work have asked me to create a form on my database which will allow all users to search in a box in a split form, and relevant records appear in the lower screen. I'm working in Access 2007.

I have a text box at the moment for users to type their number into (document number, etc) with a macro button to search. The code for the button is:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command26_Click()
  2.     If IsNull(Text24) = False Then
  3.         Me.Recordset.FindFirst "[t0000_DRAWING_LIST]=" & Text24
  4.         Me!Text24 = Null
  5.         If Me.Recordset.NoMatch Then
  6.             MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
  7.             Me!Text24 = Null
  8.         End If
  9.     End If
  10. End Sub
My table is t0000_DRAWING_LIST and the command button is Text24.

At the moment when I click the button it shows up with "No records found" and I'm not sure why.

Lots of people are going to be using this for searching so I want it to be as user friendly as possible.

Can anyone help me with this please? I apologise if I have left anything out.

Thanks in advance,

Nov 4 '13 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 15k+
P: 31,186

Your code is not filtering the data, but is simply trying to move to the first matching record.

It won't do that as the specification is not correctly formed. You need to specify which field the data you're searching for matches and not the table name itself.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command26_Click()
  2.     With Me
  3.         If IsNull(.Text24) Then
  4.             .FilterOn = False
  5.             Exit Sub
  6.         End If
  7.         .Filter = Replace("[FieldName]=%N", "%N", Nz(.Text24, ""))
  8.         .FilterOn = True
  9.     End With
  10. End Sub
You need to change where it says [FieldName] to match your own. The message is probably superfluous as when they see there are no records it will be fairly clear nothing was found.
Nov 4 '13 #2

P: 2
I've entered what you've shown above and it comes up with a parameter box which asks me to re-enter each part of the number.

The type of number i'm searching is actually a series of numbers and letters with dashes, eg XXX-YYY-ZZZ. The parameter box then shows "enter parameter value XXX" then "enter parameter value YYY" etc. Final after I've clicked through the boxes, an error message comes up saying that my parameters are too complex to be evaluated.

Any suggestions?
Nov 4 '13 #3

Expert Mod 15k+
P: 31,186
Any suggestions?
The filter code will need to change of course, as the number isn't a number, but a string. Replace line #7 with :
Expand|Select|Wrap|Line Numbers
  1.         .Filter = Replace("[FieldName]='%N'", "%N", Nz(.Text24, ""))
That should sort it if I'm not mistaken. For more on this see Quotes (') and Double-Quotes (") - Where and When to use them
Nov 5 '13 #4

Post your reply

Sign in to post your reply or Sign up for a free account.