How do I create a filter in a form

[HellsBells]

Hi

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:

Private Sub Command26_Click()
    If IsNull(Text24) = False Then
        Me.Recordset.FindFirst "[t0000_DRAWING_LIST]=" & Text24
        Me!Text24 = Null
        If Me.Recordset.NoMatch Then
            MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
            Me!Text24 = Null
        End If
    End If
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,

Helen.

[NeoPa]

Helen,

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.

Private Sub Command26_Click()
    With Me
        If IsNull(.Text24) Then
            .FilterOn = False
            Exit Sub
        End If
        .Filter = Replace("[FieldName]=%N", "%N", Nz(.Text24, ""))
        .FilterOn = True
    End With
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.

[HellsBells]

Hi
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?

[NeoPa]

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 :

        .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