469,071 Members | 1,904 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,071 developers. It's quick & easy.

vba code for search boxes

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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Knopzoekbetaling_Click()
  2.     If IsNull(tb_voorletters) And IsNull(tb_achternaam) _
  3.         And IsNull(tb_persoonnummerbetaling) And IsNull(tb_reserveringnr) Then
  4.         MsgBox "vul voorletters, achternaam, persoonnummer of reserveringnummer in", vbInformation, "zoeken"
  5.     Else
  6.         If Not IsNull(tb_voorletters) Then
  7.             Me.Filter = "voorletters like '" & tb_voorletters.Value & "*'"
  8.         Else
  9.             If Not IsNull(tb_tussenvoegsels) Then
  10.                 Me.Filter = "tussenvoegsels like '" & tb_tussenvoegsels.Value & "*'"
  11.             Else
  12.                 If Not IsNull(tb_voorletters) Then
  13.                     Me.Filter = "achternaam like '" & tb_achternaam.Value & "*'"
  14.                 Else
  15.                     If Not IsNull(tb_persoonnummerbetaling) Then
  16.                         Me.Filter = "persoonnummer = " & Me.persoonnummer.Value
  17.                     Else
  18.                         If Not IsNull(tb_reserveringnr) Then
  19.                             Me.Filter = "reserveringnummer = " & Me.reserveringnummer.Value
  20.                         Else
  21.                         End If
  22.                         Me.FilterOn = True
  23.                     End If
  24.                 End If
  25.             End If
  26.         End If
  27.     End If
  28. End Sub

Attached Images
File Type: jpg searchboxes.jpg (7.1 KB, 160 views)
Dec 23 '13 #1
6 1194
5,400 Expert Mod 4TB
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.
Dec 23 '13 #2
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)
Dec 23 '13 #3
8,800 Expert 8TB
If I am reading your Code cirrectly, you may want to restructure and simplify it, as in:
Expand|Select|Wrap|Line Numbers
  1. Dim strFilter As String
  3. If IsNull(tb_voorletters) And IsNull(tb_achternaam) _
  4.         And IsNull(tb_persoonnummerbetaling) And IsNull(tb_reserveringnr) Then
  5.         MsgBox "vul voorletters, achternaam, persoonnummer of reserveringnummer in", _
  6.         vbInformation, "zoeken"
  7. ElseIf Not IsNull(tb_voorletters) Then
  8.   strFilter = "voorletters like '" & tb_voorletters.Value & "*'"
  9. ElseIf Not IsNull(tb_tussenvoegsels) Then
  10.   strFilter = "tussenvoegsels like '" & tb_tussenvoegsels.Value & "*'"
  11. ElseIf Not IsNull(tb_voorletters) Then
  12.   strFilter = "achternaam like '" & tb_achternaam.Value & "*'"
  13. ElseIf Not IsNull(tb_persoonnummerbetaling) Then
  14.   strFilter = "persoonnummer = " & Me.persoonnummer.Value
  15. ElseIf Not IsNull(tb_reserveringnr) Then
  16.   strFilter = "reserveringnummer = " & Me.reserveringnummer.Value
  17. Else
  18.   'escape hatch
  19. End If
  21. If strFilter = "" Then Exit Sub
  23. With Me
  24.   .Filter = strFilter
  25.   .FilterOn = True
  26. End If
Dec 23 '13 #4
Thank you, but i still get the same error: compile error: method or data member not found. =/
Dec 23 '13 #5
5,400 Expert Mod 4TB
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.
Dec 23 '13 #6
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
Dec 23 '13 #7

Post your reply

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

Similar topics

13 posts views Thread by sembiance | last post: by
1 post views Thread by jamesmoore | last post: by
1 post views Thread by CARIGAR | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.