VBA Filter

[ntallman]

Good afternoon everyone,



Access is really frustrating me today. It was working the way I wanted it... then magically it stopped and is giving me errors. I'm trying to setup an advanced search form. In the form header I have unbound text fields for the user to input criteria. After they input the criteria, the idea is to apply a filter to the data below based on what they identified. It worked the first time I tried it, now I get a compiling error "label not defined". Here's the code:

Private Sub SearchButton_Click()
On Error GoTo Err_SearchButton_Click

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "CONVERT_LNAME LIKE '" & Me![Text16] & "*" & "'" And "CONVERT_FNAME LIKE '" & Me![Text18] & "*" & "'" And "RABBI_LNAME LIKE '" & Me![Text20] & "*" & "'" And "RABBI_FNAME LIKE '" & Me![Text22] & "*" & "'" And "PLACE LIKE '" & Me![Text24] & "*" & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
DoCmd.ApplyFilter , "CONVERT_LNAME LIKE '" & Me![Text16] & "*" & "'" And "CONVERT_FNAME LIKE '" & Me![Text18] & "*" & "'" And "RABBI_LNAME LIKE '" & Me![Text20] & "*" & "'" And "RABBI_FNAME LIKE '" & Me![Text22] & "*" & "'" And "PLACE LIKE '" & Me![Text24] & "*" & "'"

End Sub

Debugger highlights the first line. SearchButton is indeed the name of the object. I'm at a lost and am going crazy on this one. Especially since Access teased my by functioning the first time I tired it. I did delete the original button, but coped the code over and renamed the object.

Any suggestions?

Thanks!
Nathan

[pbaldy]

I doubt it was magic. You're telling it to go somewhere that doesn't exist with that line. It's looking for an error handler that starts with:

Err_SearchButton_Click:

[ntallman]

Thanks pbaldy. Okay, maybe it wasn't magic, but I don't know what it was.

I don't think the error handling is the real problem though. When I delete the line Err_SearchButton_Click, I think the true error message appears "Run-time error '13': Type mismatch", which has something to do with the recordset I think, but I'm not sure. The debugger takes me to the line starting with "rs.FindFirst"

[pbaldy]

The error handling was a real problem, just not the last problem.

You have what I consider to be a lot of unnecessary concatenation in your string, and it masks another problem. Your "And" is hung out between strings. In other words, the quotes in red close off the previous string, leaving the And outside:

...
& "'" And "CONVERT...

That also means the next set of quotes is not necessary.

[ntallman]

I'm not sure how to fix the concatenation or "And" problem. I tried deleting the closing quotes, extra &, and deleting & "'" all together, but couldn't get anything to work.

Thanks again,
Nathan

[pbaldy]

You want literal strings inside the quotes, form references outside:

rs.FindFirst "CONVERT_LNAME LIKE '" & Me![Text16] & "*' And CONVERT_FNAME LIKE '"...

[ntallman]

Quotation marks in VBA confuse me. I made the adjustments suggested and my search is functioning! Thank you so much pbaldy, again!

Nathan

[pbaldy]

No problem Nathan. For me, it helps to visualize what I want the finished string to look like. Then it becomes fairly intuitive what I have to do in VBA to achieve that look.