By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,248 Members | 1,979 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,248 IT Pros & Developers. It's quick & easy.

VBA Code for Search Button on Access 2016 form

P: 2
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.


Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2.    If IsNull(TxtSearch) = False Then
  3.     Me.Recordset.FindFirst '[Full_Name]=' & TxtSearch
  4.     Me!TxtSearch = Null
  5.     If Me.Recordset.NoMatch Then
  6.         MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
  7.             Me!TxtSearch = Null
  8.     End If
  9. End If
  10.  
  11. End Sub

What am I missing?
3 Weeks Ago #1
Share this Question
Share on Google+
5 Replies


zmbd
Expert Mod 5K+
P: 5,397
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
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2. '
  3.   Dim myTxtSearch as String
  4.   myTxtSearch = "[Full_Name]= %1" & me.TxtSearch & "%1"
  5.   myTxtSearch = Replace(myTxtSearch , "%1", Chr(34))
  6. '
  7.    If IsNull(TxtSearch) = False Then
  8.     Me.Recordset.FindFirst myTxtSearch 
  9.     Me.TxtSearch = Null
  10.     If Me.Recordset.NoMatch Then
  11.         MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
  12.             Me.TxtSearch = Null
  13.     End If
  14. End If
  15.  
  16. 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.
3 Weeks Ago #2

NeoPa
Expert Mod 15k+
P: 31,419
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 :
Expand|Select|Wrap|Line Numbers
  1. "([Full_Name] Like 'Robert *')"
What you have is more like :
Expand|Select|Wrap|Line Numbers
  1. "([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 :
Expand|Select|Wrap|Line Numbers
  1. ([FirstName] + ' ') & [Surname] AS [Full_Name]
That assumes there will always be a value for [Surname] but that [FirstName] may hold Nulls.
3 Weeks Ago #3

NeoPa
Expert Mod 15k+
P: 31,419
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.
3 Weeks Ago #4

P: 2
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.
3 Weeks Ago #5

NeoPa
Expert Mod 15k+
P: 31,419
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.
3 Weeks Ago #6

Post your reply

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