ACCESS: Finding Records

FIND DATA

THE FIND TOOL   You can click in any field on a form and use the Find tool (the binoculars icon) on the Home tab. Enter the text or data you want to find, and select whether you want to match the whole field, any part of the field, or the start of the field. You can also search all the records or limit the search to go up or down from the current record. You can also choose to Match Case (or not) and Search Fields as Formatted. If you’re searching for text and aren’t sure what case it is, enter the text in the Find What field in lower case.  

You can also use the Find and Replace dialog to replace something once you find it. But use caution with this. If you replace the wrong data, it can be very difficult to recover. You may want to make a copy of a table or the entire database before performing a Find and Replace.  

COMBO BOX CONTROL

Another method to help users find data is to add a combo box to the form. You can choose which fields to include in the dropdown, letting the user select the record he or she wants to see. For example, let’s say you want users to be able to locate a particular product from the Product Data Entry form for the Bob’s Bicycle database. Open the form in Design view. From the Design tab, select the Combo Box control and place it at the top of the form, ideally to the right of the key field (i.e., Product Number). When you place the combo box on the form, the Combo Box Wizard (see Figure 1) will appear to help you select the fields to use in the dropdown. The wizard defaults to hiding the key field, so you should also include the description field when possible. In this case, include Product Number and Product Name. Add a label for the combo box. Test the combo box to make sure it brings up the appropriate records. Be sure to assign a name to the control so you can reference it later. I named it ProductCombo.  

Figure 1. Combo Box Wizard    

LIMIT OUTPUTS

USE A COMBO BOX

You can also use a combo box to select a field that limits what appears in a report. For example, the Materials List report we created made each product appear on a separate page. Now we can use the combo box we just added to create a variation of the Materials List report that allows us to print an individual Materials List for any specific product.  

Open the Materials List report in Design view. Save it as “Individual Materials List Report.” On the Property Sheet, change the Caption property to “Individual Materials List Report,” and then click the ellipses (…) in the Record Source property to open the query that the report is based on (see Figure 2). Save the query as Individual Materials List Data. Add the Product Number field to the query. Enter the criteria [Forms]![Product Data Entry]![ProductCombo]. Save and close the query, then save and close the report.  

Figure 2. Individual Materials List Report Property Sheet  

Finally, add a button to the Product Data Entry form to open the Individual Materials List. In Design view, click the Button tool in the Controls Group on the Design tab. Place the button on the form, which opens the Command Button Wizard. For the first step, the Category is Report Operations, and the Action is Preview Report. In the next step, select Individual Materials List Report, then click Next. For the text on the button, type “Preview Individual Product Materials List.” Click Finish. Save the form.  

SET ANNUAL DEFAULTS

We used a text box on the Reports Menu form to enter a budget year that would be used on all of the reports. You can also use two text boxes for entering the beginning and ending dates of a period as you go thru various budget cycles. Whenever you need to input something on the form and use it in a query, you can use this technique.  

These options will give you a wide variety of ways to find things on the forms and use form input to limit your reports. You will use these techniques again and again in your Access projects.  

Download this month's database here: SF_MAR_2015.    

SF Best Practices