Giving users a quick f ind

You can use a combo box as a tool for allowing a user to quickly find a specific record in a table. For example, you might have a form that allows a user to find and edit customers. At the top of that form, you can provide a drop-down list, perhaps named Quick Find, as in Figure 10-17. When the user chooses a name from the drop-down list, the form instantly displays that customer's record. (We also point out some of the properties for the QuickFind control in that figure.) You can assign those properties when you use the Control Wizards to create the initial combo box.

Figure 10-17:

A QuickFind control offers fast customer name lookup.

Figure 10-17:

A QuickFind control offers fast customer name lookup.

Look at an example of creating a QuickFind control. Like with any combo or list box, you can begin by creating a query that defines the columns to be displayed in the list. For this example, you can use the CustomerLookupQry shown a little earlier in this chapter as the drop-down list for a combo box named QuickFind. Here are the basic steps for creating such a control:

1. In the Access Navigation pane, right-click the name of the form to which you want to add a Quick Find capability, and then choose Design View.

2. If you want to use the Combo Box Wizard to create the initial control, make sure that the Use Control Wizards button in the Controls group is highlighted. Then click the Combo Box tool and click where you want to place the control on your form.

The Combo Box Wizard opens.

3. On the first wizard page, select the I Want the Combo Box to Look Up the Values in a Table or Query option button and then click Next.

4. On the second wizard page, choose the Queries option button and then select the query that contains the values to be displayed in the drop-down list. Then click Next.

In our example, we clicked Queries and then CustomerLookupQry.

5. On the third wizard page, click the >> button to add all the fields from your query to the drop-down list; then click Next.

6. (Optional) You can choose a sort order on the fourth wizard page. If your query already has all the records in order, you can just ignore that page and click Next.

7. On the fifth wizard page, size your columns; then click Next.

As shown in Figure 10-15, earlier in this chapter, you can hide any column by narrowing it to the point where it's invisible.

8. The sixth wizard page asks which value from the query the combo box should store. Click whichever field name would provide the most accurate search; then click Next.

In our example, CustID is unique to each customer, so we specify the

CustID field.

9. On the seventh wizard page, select the Remember the Value for Later Use option button and then click Next.

Because you aren't storing the value in a field, this step creates an unbound combo box.

10. On the last wizard page, type a label for the control and then click Finish.

We gave our combo box the label Quick Find.

That takes care of creating the initial unbound combo box. To get it to act as a Quick Find procedure, you need to write some VBA code. First, we suggest that you go to the All tab of the property sheet and change the Name property to something more meaningful — for example, QuickFind rather than Combo01 or whatever Access named the control. Then click the Event tab in the property sheet and click the After Update event property. You're taken to the VBA Editor with the cursor in a procedure named control_AfterUpdate() where control is the name of your unbound ComboBox control.

The basic skeleton structure needed for a Quick Find procedure looks like this:

Private Sub controlName_AfterUpdate()

'Clone the form's table/query into

a recordset.

Dim MyRecSet As Object

Set MyRecSet = Me.Recordset.Clone

'Find first matching record in the

recordset.

MyRecSet.FindFirst "[fieldName] =

" & Me![controlName]

'Set the form's record to found record.

Me.Bookmark = MyRecSet.Bookmark

End Sub

where controlName is the name of the unbound combo box, and fieldName is the name of the field being searched in the form's underlying table or query. In our example, the QuickFind control contains a long integer value that matches the CustID value of the customer you're searching for. (Both values are long integers.) The code for the QuickFind control, which searched the CustID control in the Customers table, looks like this:

Private Sub QuickFind_AfterUpdate()

'Clone the form's table/query into a recordset.

Dim MyRecSet As Object

Set MyRecSet = Me.Recordset.Clone

'Find first matching record in the recordset. MyRecSet.FindFirst "[CustID] = " & Me![QuickFind]

'Set the form's record to found record. Me.Bookmark = MyRecSet.Bookmark End Sub

Like all procedures, this one is a series of steps. Starting at the first line, the name of the procedure defines when it runs. In this case, the procedure runs after a user chooses a customer from the QuickFind control's drop-down list:

Private Sub QuickFind_AfterUpdate()

The following lines provide for a speedy search without any activity on the screen by using an invisible recordset to do the search behind the scenes. The Dim statement declares a general object named MyRecSet. The Set statement makes MyRecSet into a recordset that's an exact clone of the table or query underlying the current form:

'Clone the form's table/query into a recordset.

Dim MyRecSet As Object

Set MyRecSet = Me.Recordset.Clone

With a simple clone recordset like this, you can use the FindFirst method to quickly locate a specific value in a single field. You can't do any sort of fancy SQL WHERE clause — only a simple fieldname = value type expression is allowed, which is all you need when searching in the primary key field.

The next statement in the procedure uses the FindFirst method to locate CustID value in the recordset that matches whatever value is stored in the QuickFind control:

MyRecSet.FindFirst "[CustID] = " & Me![QuickFind]

It takes less than an eyeblink's time for the preceding statement to search the CustID field in the recordset. After the record is found, the recordset's Bookmark property contains a value that indicates that record's position in the recordset. To get the form to show the record that was found in the recordset, the next statement sets the form's underlying table or query Bookmark property equal to the Bookmark property of the recordset:

Me.Bookmark = MyRecSet.Bookmark

The job is done after the form is displaying the requested record, so the End Sub statement marks the end of the procedure:

End Sub

After the procedure is written, you can close the VBA Editor, as usual, save the form, and try out the new control in Form view. The lookup should work when you open the form and choose a customer from the QuickFind combo box.

Avoiding retyping common entries

Here's another situation where a dynamic combo box can be very helpful in data entry. Suppose that you have a table like Customers that includes a City field, and as it turns out, most of your customers are from a few nearby cities. Thus, you find yourself typing the same city name over and over again as you enter customers' data.

As an alternative to typing the same city name repeatedly, you can make the City field on the form a self-referential combo box that automatically lists every unique city name that has ever been typed into the form. For example, the first time you type Los Angeles as the city entry, that name gets added to the City field's drop-down list. In the future, when it's time to type Los Angeles into another record, you can just choose that name from the dropdown list rather than retype it again.

To get started, you need a drop-down list of unique city names. You can use a query to design the initial drop-down list. For example, Figure 10-18 shows a query named UniqueCitiesQry that lists, in alphabetical order, every unique city name in a field named City. Setting the Unique Values property in the query's property sheet to Yes provides the unique city names.

Figure 10-18:

Unique Cities Qry lists unique city names from the City field.

Customers

Addressl

Address2

City

State Prow

n

ZIPCode

Countiy

Fav

Field: City Ta:ile: ( u i-rir" Mh; Sbrt: Ascending;

6

Property Sheet

Selection type: Query Properties

General |

Description riâfai lit V/iûiji;

Datasheet

ueraurt view Output All Fields

No

LI

Top Values

All

Unique Values

Yes

a

Unique Records

No

Source Database

(current]

Source Connect Str

Record Locks

No Locks

Records et Type

Dynaset

ODBC Timeout

60

Filter

Order By

Max Records

el

In the query, switch to Datasheet view to make sure the query shows each city name only once, and then close and save the query. You can then use the query as the Row Source for any combo box or list box that's bound to the City field. For example, on any form that will display the City field from the Customers table, you can create a unique value's combo box by following these steps:

1. In the Controls group, make sure the Use Control Wizards button is highlighted, and then click the Combo Box tool.

2. Drag the City field from the Field List onto your form.

The Combo Box Wizard opens.

3. On the first wizard page, select the I Want the Combo Box to Look Up the Values in a Table or Query option button and then click Next.

4. On the second wizard page, choose the Queries option button and then choose the query that shows the unique values (UniqueCitiesQry in our example). Then click Next.

5. On the third wizard page, click the >> button to add the field to the Selected Fields column; then click Next.

6. On the fourth wizard page, you can just click Next rather than a sort order (because the query has already defined a sort order).

7. On the fifth wizard page, adjust your column width (if necessary), and then click Next.

8. On the sixth wizard page, select the Store That Value in This Field option button and the name of the field to which the combo box is attached; then click Next.

Most likely, the correct options are already selected for you because you already dragged the bound field's name to the form in Step 2.

9. Type in a label for the control (City in our example) and then click Finish.

That's it. When you switch to Form view, the City drop-down list should display the name of each unique city that's in the Customers table. It might seem like you're done, but there's just one small problem: As you add and edit records in the Customers table, the drop-down list in the City field cannot keep up at first because the City field's drop-down list doesn't automatically requery with each change.

The problem is easily solved with a single line of code that requeries the City control every time a record is updated in the Customers table. To requery a control with each update, follow these steps:

1. In the form's Design view, double-click the gray area behind the Design grid to get to the Form properties in the property sheet.

2. In the property sheet, click the Event tab and choose the After Update event property.

3. Click the Build button next to the After Update event property and choose Code Builder.

You're taken to a procedure named Form_AfterUpdate(), which runs every time the current form updates a record in its underlying table.

4. Within the procedure, type MefieldName.Requery where fieldName is the name of the control that contains the self-referential combo box.

In our example, it's

Me![City].Requery

5. Choose FileOClose and Return to Microsoft Office Access.

6. Close and save your form.

In the future, whenever you add or change records in the Customers table through the form, you can either type a new city name or choose an existing city name from the City drop-down list. If you type a new city name, that name is added to the drop-down list of existing field names automatically, thanks to the little, one-line VBA procedure.

0 0

Post a comment