Search Button - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Search Button

Daniel Golden

16 months ago

Ok, so I gave up on trying to get the TempVars code to work correctly in my search form and instead have watched the video from Richard on creating a search button on the form. I have the code for the click event of the button set as:

Private Sub SearchBtn_Click()

    Dim S As String
    
    S = InputBox("Case Number?", "Search")
    If S = "" Then Exit Sub
    
    Me.Filter = "Case Number" Like "" * " & S & " * """"
    Me.FilterOn = True

End Sub

In the form, the case number is a number/text format (24-011R for example).

When I click the button, the input box appears and asks me for "Case Number?" I enter in "24-011R" and press enter, It throws en error saying: "Run-time error '13': Type mismatch.

I first thought this might be due to the variable being left as a string, so I changed it to long. This did nothing. I know it is going to be something simple which gets the result I am looking for, but it's late in my day and I can't seem to think of it.

	Kevin Robertson @Reply 16 months ago
You have extra double quotes. `Me.Filter = "Case Number Like """ & S & """"`

	Adam Schwanz @Reply 16 months ago
As a tip, if you see Access make the quotations like this with a space in it after you type it in, " * """" you've accidentally added too many. Or maybe not enough, Access doesn't know what it's looking at either way.

	Alex Hedley @Reply 16 months ago
Some more reading :p Easy Search Form Easy Search Form 2 Search Form 2.0 Wildcard Search & Like Access Search Seminar Access Search Template

	Thomas Gonder @Reply 16 months ago
I'm confused by the OP. TempVars and command buttons are entirely different things in ACCESS. How does one work for the other in your new solution?

	Daniel Golden OP @Reply 16 months ago
@Adam I will have to look tomorrow at my code.... my eyes are far too crossed for tonight to attempt it @Alex I have read all of that excluding the seminar and template as I am limited on funds @Thomas In the new solution, I am not using a TempVar to get anything done. I followed Richard's TechHelp video on Search Button

Thomas Gonder @Reply

16 months ago

@ Daniel In my opinion, TempVars are good for storing values that you don't want cleared if programs crash. I use them to save about 30 login arguments. Otherwise, the different module and global variables, and passed arguments are appropriate. In a few cases, some values can be stored dynamically in a table, but I've found negligible need for that. One valid case for example, if doing a month-end close, you may want to save the month that was just closed for additional processing and reports.

I bought the Access Search Template, and it saved me a lot of time, as well as giving me a great starting point. However, it doesn't sound like you're doing what we typically call a "search form".

The kind of form you're doing is what I call a "trigger form". Here, I avoid the use of an input box, once past some basic concept testing. Use a form to get the data you need. Richard has lots of examples of this. To get really tricky, so that one form works for all kinds of reports and processing (getting specific codes, date ranges, value limits, etc.), you want to create a master form that gets fed some code, and then you build the form dynamically with VBA to ask the parameters that you need for the next step of processing. You can see something close to this in the menu form that I created, it creates dynamic menus for users that trigger all the other types of processing.

https://www.youtube.com/@adbs_ads
then look for video 1122. Just observe how the one form can morph based on actions. The rest is menu specific for users.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View