Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Access Training and help » Searching fields in forms

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

Searching fields in forms

Resolved · High Priority · Version 2003

Reply Tue 27 May 2008, 17:00Delegate Chantil said...

Chantil has attended:
Access Intermediate course
Excel Intermediate course

Searching fields in forms

I have a form in an access database which often needss searching to see if records have been previously entered. I have been using the Find function but I would like it to filter the cases so that the relevant ones can be displayed.

I know that you can set up a command button to search a particular field and filter accordingly but I don't know how to do it.

I would be grateful if someone could tell me how to go about this. Thanks

For upcoming training course dates see: Pricing & availability

Reply Tue 1 Jul 2008, 22:04Delegate Mark said...

RE: Searching fields in forms

Hi Chantil,
As you question seems in waiting for a while I thought this might help.
I am not exactly sure whether you want to find records that are present for particular fields or do a specific search. I will guess the latter. To do this open the form you wish to search on. Open it in design mode, select the combo box button in menu bar and click and drag to create the box into a blank space on your form, preferably quite near the top or bottom of the form to allow for a good list length. You will see 3 options. Select Find a Record on my Form Based on the Value I Select in my Combo Box. Then press Next and chose the field by which you wish to search, and then go on to Finish. You can then go to form view to check the result. You should get a drop down showing 8 values in your chosen field and scroll bar if you have more than 8 in the list. I often prefer to see more than 8 list items at a time. To alter this go back to design view and double click on the combo box to call up properties (or you could right click and select propeties). Go to the Format Tab and you will see List Rows about one third the way down which is usually on 8 and you can change this --try 30 or 40. If you are searching on a field that has duplicates you either need to create a twin column combo or add Group By to the undelying query which I will explain.

This search combo box allows you to either select from the list, which takes you straight to the record you want, or you can type a value in. If you type a value in you need to click into another cell to trigger it. It is better to add a save record button next to the combo but label it GO or similar so as not to confuse the user. You can add further search boxes to the same form that search by different fields.

You can limit the search behind the list by returning to design mode and properties in the combo box, go to Data Tab, click into the Row Source cell anywhere preferably at the end to make the dotted box at the end appear. Click on that and you will see the query that feeds your list. Here you can add criteria to your selected field. To remove blanks ie see records entered only add the Not Null criteria. To stop duplicates in the list add Group By. You can add other fields to the query to create more complex criteria conditions.

I hope this helps.
Mark

Reply Wed 2 Jul 2008, 13:50Delegate Chantil said...

RE: Searching fields in forms

Thanks Mark, that's great!

I'll give it a go!

Access articles

· Making The Transition To Access - Why You Might Need It
· So what's new in Access 2007?
· How Do Cross Tabs And Pivot Tables Differ In Microsoft Access?
· How To Use The New Navigation Form In Access 2010
· Practice Microsoft Access Database Decorum To Reap The Rewards

Please browse our web site to find out more about
access vba courses and other Microsoft training courses.

Access tip:

Create a custom Close button on a Form

By default, Forms that you create in Access have a full set of control buttons, that is, a title bar with Minimize, Maximize and Close buttons in the top right-hand corner and a Control Menu in the top left-hand corner.

Sometimes Access needs to follow certain procedures or events as users close a Form (such as automatically opening another Form perhaps). So it is often a good habit to create a custom Close button on a Form for everyone to use, rather than the standard closing cross.

To create a custom Close button:

1) In Form Design View, draw a Command Button onto the Form.

2) Use the Command Button Wizard to select Form Operations under Categories, and Close Form under Actions.

3) Choose Finish.

Access then creates the button and code to close the form for you. You can add any extra code that needs to run as the Form closes to the same Close procedure.

However, the creation of a custom Close button is pointless if the closing X in the top right-hand corner of the Form remains active. To make the closing X inactive, open the Form Properties box then on the Format tab set the Close Button setting to No. This will grey out the closing X leaving it visible, but inactive.

View all Access hints and tips