VBA Code for Search Button on Access 2016 form

[duffyt60]

I have created a unbound text box (txtSearch) and a Button to use with the text box for searching all records on a form.

The field on the form that I am using for the search is labeled Full_Name.

When I try to search for a first name of any user in the Full_Name field nothing happens and I get no error.

Here is my code.

Private Sub cmdSearch_Click()
   If IsNull(TxtSearch) = False Then
    Me.Recordset.FindFirst '[Full_Name]=' & TxtSearch
    Me!TxtSearch = Null
    If Me.Recordset.NoMatch Then
        MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
            Me!TxtSearch = Null
    End If
End If
 
End Sub

What am I missing?

[zmbd]

Good Morning duffyt60 and Welcome!

1) You have not set " TxtSearch " to any value; thus, it is null
2) I'll send you a template in your Bytes.com>Inbox with some suggestions on how to properly setup the VBA-IDE
The minimum is that you should have both

Option Explicit
Option Compare Database

Set properly
The first one will force you to properly declare your variables

3) It's a guess; however, I suspect that [Full_Name] is a text field; thus, you will need to quote the value you are searching for

with the bit of tweaking you now have:

Private Sub cmdSearch_Click()
'
  Dim myTxtSearch as String
  myTxtSearch = "[Full_Name]= %1" & me.TxtSearch & "%1"
  myTxtSearch = Replace(myTxtSearch , "%1", Chr(34))
'
   If IsNull(TxtSearch) = False Then
    Me.Recordset.FindFirst myTxtSearch 
    Me.TxtSearch = Null
    If Me.Recordset.NoMatch Then
        MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
            Me.TxtSearch = Null
    End If
End If
 
End Sub

The replace() makes inserting the double quote much cleaner otherwise you have silly things such as """" - it's a trick I picked up from Neopa
>> BTW: Building the string outside of the function (as I have done in lines 4 and 5) allows you to troubleshoot the string by just inserting a Debug.print yourStringHere following the formation and then [<ctrl><G> to open the immediate pane to see the string. A malformed string is one of the more common causes of unexpected results and errors.

[NeoPa]

It seems like you're missing any wild-card in your search criteria. In simple terms you're searching for the record where the [Full_Name] field is exactly equal to the first name entered. I'm guessing, as most values in the table will consist of both first and surnames, that it never finds a matching record.

Your search criteria needs to end up something like :

"([Full_Name] Like 'Robert *')"

What you have is more like :

"([Full_Name]='Robert')"

That's guessing the format of your data but hopefully you can see the logic.

Another point that may be worth mentioning is that fields in tables should hold elemental data rather than grouping similar data together. Have a [Full_Name] field if you must, but create it in your query/SQL as a formula using other fields that are used to make it up. A common usage is :

([FirstName] + ' ') & [Surname] AS [Full_Name]

That assumes there will always be a value for [Surname] but that [FirstName] may hold Nulls.

[NeoPa]

There may be some misunderstanding about what I've advised so I'll add here a link to an article that discusses such stuff (Quotes (') and Double-Quotes (") - Where and When to use them).

I only noticed after reading Zmbd's post, after posting my last one, that your original use of quotes was further off-base than I'd realised when first I read your post. That's fine. The linked article will help clarify a lot of the potential problems when working with strings in code.

Replace() is very important and very helpful for putting strings together in a clear and obvious way. Personally I don't recommend using Char() for dealing with quotes of any sort but I know many do. As you'll see in the linked article it's rarely necessary to double up on quotes when dealing with VBA (") and SQL (') together, but when it is necessary I believe the code should show as clearly and as readably as possible what is going on. That said it is an option that many use so choose your poison and be ready to interpret that of other developers when you come across them.

PS. Some questions are clearer than others and this one is pretty decent. Especially for a first-timer.

[duffyt60]

I have updated my Access 2016 database form with the information provided by zmbd; however, I continue to get no error and nothing happens when I enter a first name in the search box. I have added a copy of my database for review.
{Admin edit}
Please don't attach databases unless requested by someone trying to help you.
We require (and expect) you to explain your situation clearly in words.

[NeoPa]

Please explain clearly what you've changed and confirm that your project compiles without error. I take what you posted earlier to mean that but we need to be 100% clear.

Zmbd posted a buch of instructions and we need to know exactly which of those you've followed. It may well be all, and that would be great, but it's very rarely so. n explanation of exactly what you've done while following those instructions would make your situation a lot clearer.

Until you can manage that let's see what we do have enough information to progress with.

If your code all compiles ok but nothing happens when you enter a filtering value there are two things to check first :

1. Is the property set so that the code you've written in the event procedure is run when the event triggers?
   One way to force that is to cut & paste all the code from the associated module back into itself. That way the associations are made for you automatically.
2. Which event are you using? If it's the .AfterUpdate() event then that should work once you've made a change and moved off to another control on the form. If it's a Click() event then in needs to be clicked after making the changes (obviously).

Check that whatever event you're using actually triggers the correct code.