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, suppose you have a form that allows a user to find and edit customers. At the top of that form, you could provide a drop-down menu, perhaps named Quick Find or something, as in Figure 10-17. When the user chooses a name from the drop-down menu, the form instantly displays that customer's record. (I 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.

QuickFind control (unbound)

Figure 10-17:

QuickFind control offers fast customer name lookup.

il MOM 2003 AiJdrsss Bonk

Edit Customers 1&HSH|

Email Phone

(618)555-4949

First Name |Tori Company |Arbor Classics

Abaiaster, Igor ABC Productions Angstrom, Margaret Bebopj Stacey Biasing Carlos Citrus, Michael Costeiio, Karen testing® 123, com

[email protected]

[email protected]

[email protected]

[email protected]

[email protected]

[email protected], com

[email protected]

Address 1 |345 Pacific Coast Hwy

Address 2 |Suite 3232

City (Escondido

Tax Exempt ID

QuickFind properties -

[email protected]

QuickFind properties -

2T Combo Box: QuickFind

m

Format Data Event Other AH

A

Control Source

Format

Decimal Places

Auto

Input Mask

Row Source Type

Table/Query

Row Source

CustomerLookupQry

Column Count

3

Column Heads

No

Column Widths

1.4"jl.6"j0"

Bound Column

List Rows

8

List Width

3.1"

Status Bar Text

Limit To List

Yes

Auto Expand

Ϯ_

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 earlier in this chapter as the drop-down menu for a combo box named Quick Find. Here are the basic steps for creating such a control:

1. In the Access database window, 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 Control Wizards button in the Toolbox is pushed in. 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 I Want the Combo Box to Look Up the Values in a Table or Query and then click Next.

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

In my example, I click Queries and then click CustomerLookupQry.

5. On the third wizard page, click the button with the right-facing chevrons (>) to add all the fields from your query to the drop-down menu; 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.

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

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 my example, CustID is unique to each customer, so I specify the CustID field.

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

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

I labeled my Combo Box QuickFind.

That takes care of creating the initial unbound combo box. To get it to act as a Quick Find, you need to write some VBA code. First, I suggest you go into the All tab of the Properties sheet and change the Name property something more meaningful — for example, QuickFind instead of ComboOl or whatever Access named the control. Then click the Event tab in the Properties sheet and click the After Update event. You'll be 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 "[fldName] = " & 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 fldName is the name of the field being searched in the form's underlying table or query. In my example, the QuickFind control will contain an integer value that matches the CustID value of the customer you're searching for. (Both values are 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 will run whenever a user chooses a customer from the QuickFind control's dropdown menu:

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 MySetSet. The Set statement makes MyRecSet into a recordset that's an exact clone of the table/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.

The next statement in the procedure uses the FindFirst method to locate CustID value in the recordset that matches whatever value is currently 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 will contain 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/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.

Avoid retyping common entries

Here's another situation where a dynamic combo box can be very helpful in data entry. Suppose 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 customer's data.

As an alternative to typing the same city name repeatedly, you could make the City field on the form a self-referential combo box that automatically lists every unique city name that's 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 menu. In the future, when it comes time to type Los Angeles into another record, you can just choose that name from the dropdown menu rather than retyping it again.

To get started, you'll need a drop-down menu of unique city names. You can use a query to design the initial drop-down menu. 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 Properties sheet to Yes is what provides the unique city names.

Figure 10-18:

Unique CitiesQry lists unique city names from the City field.

Figure 10-18:

Unique CitiesQry lists unique city names from the City field.

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 Customer's table, you could create a unique value's combo box by following these steps:

1. In the Toolbox, make sure the Control Wizards button is pushed in, 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 I Want the Combo Box to Look Up the Values in a Table or Query and then click Next.

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

5. On the third wizard page, click the button with the right-facing chevrons (>) 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 Store That Value in This Field and the name of the field to which the combo box is attached; then click Next.

Most likely, the correct options will already be 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 my example) and then click Finish.

That's it. When you switch to Form view, the City drop-down menu should display the name of each unique city that's currently in the Customers table. It might seem like you're done, but there's just one small problem. As you add new records to the Customers table, the drop-down menu in the City field will not be able to keep up at first because the City field's drop-down list won't automatically requery with each new record.

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

1. In forms Design, double-click the gray area behind the Design grid to get to the Form properties in the Properties sheet.

2. In the Properties sheet, click the Event tab and choose the After Insert event.

3. Click the Build button next to the After Insert event.

You'll be taken to a procedure named Form_AfterInsert(), which will run every time the current form adds a new record to its underlying table.

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

In my example, that would be

Me![City].Requery

5. Choose FileOClose and Return to Microsoft Access.

6. Close and save your form.

In the future, whenever you're adding records to 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 menu. If you do type a new city name, that name will be added to the drop-down menu of existing field names automatically, thanks to the little, one-line VBA procedure.

0 0

Post a comment