Access 2007 search box within form

Hi there,

I'm a bit of a novice in terms of access 2007 and know nothing about VBA. I was hoping someone could help me out with this small query - probably you may even think it not worthy of a post, but it i'm at a lost and need help.

I have created a table called Candidates. It's fields include firstname, lastname, address, telnumber, etc - that kind if stuff. I have also created a split form from this table. This form is meant so that the users can update the data onto the database. So far so good.

However, i want to create (in the header section of my Candidate form) a search function of the records, so that even when i am viewing a record (of say Candidate 1) and i want to go to another (say candidate 99), i just type the lastname of the Candidate in the search box and that particular form comes up. I'm not looking for a search report or a search subform which then gives me something else - i would just like the record to pop up so i can view amend it.

I have tried setting up a query to search for last names, but am getting nowhere. i see other examples and the end result is a sub-form with the search results - not really what i want.

Can someone please tell me how it's done? sorry if it sounds a little trivial to some of you, but it your reply would be very helpful.

thanks

I don't have 2007, but the following works for 2003 and earlier so it should be at least similar for 2007. I think the main difference is that in 2007 the properties box is one of the side windows instead of a pop up window.

To view the header for a form:

1. open the form in Design View
2. At the top of the form (just under the ruler bar), on the left, you will either see 'Form Header' or 'Detail'.
3. If you only see 'Detail' right-click on the 'Detail' bar. (This will bring up some options like: "Build Event'; 'tab order'; etc.)
4. Click on the 'Form Header/Footer' option near the bottom of the list.

Now your form will have three sections: Form Header; Detail; Form Footer.

In the header section add a text box and two buttons.

When you add the buttons and the wizard comes up just choose cancel since you will be adding your own code.

You will need to know the name of the text box you create so click on the text box. If the 'Properties box' is displayed, check what the name is. It will be the word text and a number like Text5 or Text33.

If the Properties box is not displayed, right-click on the text box and then choose 'Properties' at the bottom of the list.

Once you have the name of the text box it is time to work on the buttons.

You want to change what is displayed on the buttons. One of the buttons will be Filter and one will be Undo Filter.

Click on the button that you want to the be the 'Filter' button.
In the Properties box change the Caption to the word Filter.

Now you are going to add the code to the button.

Right-click on your 'Filter' button and select 'Build Event'.
Choose 'Code Builder' and click OK

This will bring up a whole different screen and you'll see something like:

Private Sub Command12_Click()

End Sub

You want to add the following lines before the 'End Sub'

Me.Filter = "<lastNamefield> = " & Me.<textboxName>
Me.FilterOn = TRUE
Me.Requery

so it will look like this:

Private Sub Command12_Click()

Me.Filter = "<lastNamefield> = " & Me.<textboxName>
Me.FilterOn = TRUE
Me.Requery

End Sub

You will need to replace <name> with the appropriate name from your table/form.

Now go back to your form.

For the other button:

Click on the button and change the Caption to read 'Undo Filter'

Right-click on the Undo Filter button and choose Build Event
Choose Code Builder

This time you are only going to add two lines of code:

Me.FilterOn = False
Me.Requery

Save the form.

When you open the form you should have a textbox at the top and two buttons (Filter, Undo Filter)

You should be able to enter your incident number into the text box and then click the Filter button to see that record.

Hope this make sense!

Many thanks mate. i'll give this a go. Good luck with the job search!

Hey dykebert,

I stumbled across this thread and am trying to do a similar thing.

I'm creating a vaccination records database

So I've created a split form and as I enter the data it automatically stores it in the table below the form. Also before creating the split form I created a table with a bunch of fields entered and used that table to create the form.

So now say a child comes in on a return visit, I want to put a search box on the split form so that I simply search the child's name or by his/her unique vaccination card number and the form autopopulates with all the data previously entered (possibly bio data and vaccines already administered).

I tried what you described. This is what I code looks like:

Private Sub Command86_Click()

Me.Filter = "<ID>=" & Me.<ID>
Me.FilterOn = True
Me.Requery


End Sub

Private Sub Command87_Click()

Me.FilterOn = False
Me.Requery

End Sub



I'm hoping to search by the child's ID.
But as soon as I type the above code in it gives me this error

Compile error:
Expected: identifier or bracketed expression



I'm so confused! Please help. Thanks so much!

could i please possibly email you my database?

I am trying to create a form or report, which ever is easier, to search a particular table or group of tables. I want to be able to search any particular field from the table and it bring up that row from the table. My fields on my table are: Document/Form, Section Title, Origination Date, Revision Number, Revision Date, Reason for Revision, and Procedure Link.

I word prefer it to look like a form in which you could enter information but perform a search instead on whatever information the person knows.

I have messed with coding some but can't figure it out.

Any help and suggestions would be of great help!

Look at this tutorial

http://www.datapigtechnologies.com/flashfiles/buildcustomfilter.html

Alan