Page 1 of 1

Creating a Search on an Access Form Using a "Search" Button Rate Topic: ****- 4 Votes

#1 jjsaw5  Icon User is offline

  • I must break you
  • member icon

Reputation: 92
  • View blog
  • Posts: 3,060
  • Joined: 04-January 08

Posted 11 March 2008 - 08:16 AM

IMPORTANT: This search work best when searching records by a single field. Namely an ID number or some other Unique Identifier. This search works best if you have an inventory, products, or something where you need to pull up records based on an ID.

So you need a search on a form thats not a drop down or combo box? Well I'm going to provide you with an alternative to that. This search will be set up like a internet search. But you will only be able to search based on one field.

The first thing that you need to do is find a place on your form where you would like to put your search at. I recommend you put it in the heading of the from, preferably in the top right so you can have your form title on the left and then your search on the right.

Your first step is to create a text box. I'll state the obvious here, but this is where the user is going to enter their search. To the right of that you want to create your search button.

Now I'm assuming that you already know how to preform basic functions in Access, but if not I'll explain how.

Text Box

When you are in Design View you notice that you have a "Toolbox" (this will either be floating or docked) The second icon in the tool box should be this "ab|". You'll want to click on that and then you will notice when you scroll over your form the cursor has changed. This is to let you know you have selected the "Text Box" tool.

Simply click and drag to make your box, and then you can resize it to suit your needs.

Simple enough

Search Button

Also located in your "ToolBox" is a tool for making a button. SHould be the 7th icon and it looks like a little 3d box, and its labeled "Command Button"

Same thing as the text box, you want to select the tool, go to your form, and click and drag to create your button. When the wizard pops up for the button, just hit cancel.

You can edit the text on the button in Design View to say "Search" or whatever you feel is appropriate.

***It would be good to create the text box on the left and the search button on the right of it***

***You will also notice that when you create the box a label appears with it. You can go ahead and delete the label. You won't be needing it***

Now that we have the general setup of the search we can get into the code.

Your going to want to get the name of the text box and command button. To do this you want to right click on the text box and command button and select "Properties" a screen will pop up with different tabs.
Click the "All" tab and the 1st field displayed is the name of the box or button(depending on which you did 1st)

Write those down because they are going to be very important.

If you went in order getting the names you should have the properties of the command button still open. If not open that back up.

Now you are going to want to go to the "Event" tab. (This is on the properties of the command button)

***to make sure you have the properties of the button you should see the name of the button in the drop down box at the top of the properties window***

You will see many events here you want the "On Click" event. Across from the words "On Click............................" there is a white box and if you click in the box a drop down will appear and "..." and the end of the box, click on the "..." it will open a box with three choices, you want "code builder"

This will open Visual Basic and code that looks like this....
Private Sub Command48_Click()

End Sub

Command48 is the name of my command button. Yours will be something different, but for this i will use Command48

We are going to now create your search....remember the names your wrote down(text box and command button names), this is where we are going to use them. You are going to want to create the following statement

My Names:
Text box : Text01
COmmand button : Command48

Private Sub Command48_Click()
   If IsNull(Text01) = False Then
	  Me.Recordset.FindFirst "[Database Feild]=" & Text01
	  Me!Text01 = Null
	  If Me.Recordset.NoMatch Then
		 MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
			Me!Text01 = Null
	  End If
   End If
End Sub

You can just copy and paste this, but remember to change the names or it won't work!

Explanation of the above code

We are saying in this expression to take the information entered in text01 and search for a matching record in Database Field.(this is the field you are search)

If there is no record or no match, we prompt the user with a message stating " No record found Sorry"

Then if a match was found all the information is displayed.

So if you have database with your products in it and each product has a unique number, and you want to see how the info for a specific product, create this search for unique ID number.

There are a couple key points here that you need to understand in order for this search to work.

1. This search works best for searching a table by an ID number
2. All of the fields associated with what you are searching for need to be on the from. Example:

Search Field= Product Number
Associated Fields = Name, Size, Price, Quantity

The associated fields need to be on the form so the information from the search can be displayed.

Any question feel free to PM me

Is This A Good Question/Topic? 4
  • +

Replies To: Creating a Search on an Access Form

#2 mahst34  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 30
  • Joined: 26-May 09

Posted 26 May 2009 - 08:21 AM


All of this works fine for me except one thing, how do i tell the command button where to search for the record? I have a separate table with all of my records in it and i want it to search through that for the certain search criteria that i have entered. does it have something to do with this line:
Me.Recordset.FindFirst "[Database Field]=" & txtMac Me.Recordset.FindFirst "[Database Field]=" & txtMac

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1