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.
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.
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
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