vba code for search boxes

[chielviper]

Hello all!

I have stranded with a problem. I tried to search some data in a form but my vba code doesn't work. I have managed it with 2 search boxes but i need now to search with 5 boxes. I will show you my code and my form so you guys/girls can see what i am trying to do. Please i need your help =)

greetings Chiel (beginner in vba code)

*when you click on the button 'zoek' then it has to search for 1 of the 5 boxes.*

VBA code what doesn't work.

Private Sub Knopzoekbetaling_Click()
    If IsNull(tb_voorletters) And IsNull(tb_achternaam) _
        And IsNull(tb_persoonnummerbetaling) And IsNull(tb_reserveringnr) Then
        MsgBox "vul voorletters, achternaam, persoonnummer of reserveringnummer in", vbInformation, "zoeken"
    Else
        If Not IsNull(tb_voorletters) Then
            Me.Filter = "voorletters like '" & tb_voorletters.Value & "*'"
        Else
            If Not IsNull(tb_tussenvoegsels) Then
                Me.Filter = "tussenvoegsels like '" & tb_tussenvoegsels.Value & "*'"
            Else
                If Not IsNull(tb_voorletters) Then
                    Me.Filter = "achternaam like '" & tb_achternaam.Value & "*'"
                Else
                    If Not IsNull(tb_persoonnummerbetaling) Then
                        Me.Filter = "persoonnummer = " & Me.persoonnummer.Value
                    Else
                        If Not IsNull(tb_reserveringnr) Then
                            Me.Filter = "reserveringnummer = " & Me.reserveringnummer.Value
                        Else
                        End If
                        Me.FilterOn = True
                    End If
                End If
            End If
        End If
    End If
End Sub

[zmbd]

VBA code what doesn't work.

What about your code doesn't work?
Have you tried placeing a stop between lines 1 and 2 and then debug step thru to determine if your logic is happening as expected?

> We don't mind helping; however, we do ask that you do the basic troubleshooting first.
> Before Posting (VBA or SQL) Code

1) learning to step your code will help you follow your logic. I've done this for you in the posted code. Please use the [CODE/] button to format script and tables.

2) As it appears now, your filter doesn't get turned on unless the form at design has it turned on at default or unless you have a value for every on of your controls. So move Me.FilterOn = True from line 22 to just before the end sub.

3) Build your string first, then set it in the form's filter property. That way you can do a debug.print on the string to check if it's being built correctly.

[chielviper]

Hello, thank you for helping me!

At first i wanted to say i am sorry for not using the [code/].

I have done step 2 and i also understand why that has to be done.
But i do not understand step 3. I dont know how to build a string. (I haven't learned that yet at school). Can you please show me a simple example so i can use that to understand it.

Also the thing what doesnt work on my vba code is the section below line 9. Then i can search on voorletters but when i add other code it says this error: 'compile error: method or data member not found'.

Again i appreciate your help!

Greetings Chiel

(i have already changed in line 12 the tb_voorletters to tb_achternaam, i saw this fault but fixed it. Still i get the error)

[ADezii]

If I am reading your Code cirrectly, you may want to restructure and simplify it, as in:

Dim strFilter As String
 
If IsNull(tb_voorletters) And IsNull(tb_achternaam) _
        And IsNull(tb_persoonnummerbetaling) And IsNull(tb_reserveringnr) Then
        MsgBox "vul voorletters, achternaam, persoonnummer of reserveringnummer in", _
        vbInformation, "zoeken"
ElseIf Not IsNull(tb_voorletters) Then
  strFilter = "voorletters like '" & tb_voorletters.Value & "*'"
ElseIf Not IsNull(tb_tussenvoegsels) Then
  strFilter = "tussenvoegsels like '" & tb_tussenvoegsels.Value & "*'"
ElseIf Not IsNull(tb_voorletters) Then
  strFilter = "achternaam like '" & tb_achternaam.Value & "*'"
ElseIf Not IsNull(tb_persoonnummerbetaling) Then
  strFilter = "persoonnummer = " & Me.persoonnummer.Value
ElseIf Not IsNull(tb_reserveringnr) Then
  strFilter = "reserveringnummer = " & Me.reserveringnummer.Value
Else
  'escape hatch
End If
 
If strFilter = "" Then Exit Sub
 
With Me
  .Filter = strFilter
  .FilterOn = True
End If

[chielviper]

Thank you, but i still get the same error: compile error: method or data member not found. =/

[zmbd]

line 9: "tb_tussenvoegsels"
The error is telling you that this is not in the recordset or is not a control on the form, double check the spelling.

[chielviper]

Ok i found a spelling error. Thank you for saying that.
I also found that the with had to close with a end with. Also the last end if had to go.

But now it works!

I will thanks you both guys, I really appreciate the time you took for my problem!

Greetings, Chiel