• Choose a category
• All
• Classic-Fiction

By Root 1878 0

In any case, the concepts are the same: resize each of the controls based on the current size of the form. The tricky part is finding some "reference" on which you can base your sizing decisions; in this example, we used the offset of the expanding text box from the left edge of the form.

2.8. Make a Simple "Searching" List Box

Problem

You'd like to create a text box/list box combination like the one in Windows Help. As you type in the text box portion of the control, you want the list box to scroll to match whatever's been typed so far. You know you could use a combo box for this, but the combo box keeps closing up. You want something that's permanently open.

Solution

Entering a portion of the value they're looking for and seeing the matches displayed as users type is an excellent way to find specific values in a list. You get the best of both worlds: the functionality of a combo box and the "permanently open" look of a list box.

The key to implementing this functionality is the text box's Change event. Every time the text in the text box changes, the code you'll use will automatically find the matching value in the associated list box. You'll be able to call a function that will handle all the work for you. In addition, because searching through indexed tables is so much faster than walking through dynasets (the results of running a query or a SQL expression), this solution offers two solutions to this problem: one for list boxes that are bound to tables and another for list boxes that are bound to queries or SQL expressions. Figure 2-15 shows frmSearchFind in action.

Figure 2-15. Using Incremental Search on frmSearchFind

The methods you'll find in this solution apply only to bound list boxes.

To test out the functionality, open the database 02-08.MDB and then open either frmSearchFind or frmSearchSeek. As you type in the text box, you'll see the associated list box scroll to match what you've typed. If you backspace to delete some characters, the list box will still match the characters that remain in the text box. When you leave the text box or click on an item in the list box, you'll see the full text of the chosen item in the text box. The functionality is the same no matter which form you use. frmSearchSeek will look up items faster, though, because it's guaranteed to use an index to do its work.

Follow these steps to build a form like frmSearchFind, which will use a query or SQL expression as the row source for the list box:

In your own database, create a new form that contains at least a text box and a list box. For the sake of this example, name the text box txtCompany and the list box lstCompany.

Set properties, as shown in Table 2-6.

Table 2-6. Controls and properties for search project form

Control type

Property

Setting

Text box

Name

txtCompany

OnExit

[Event Procedure]

OnChange

[Event Procedure]

List box

Name

lstCompany

AfterUpdate

[Event Procedure]

RowSource

qryCustomers

ColumnCount

2

ColumnWidths

0

BoundColumn

2

Import the table Customers and the query qryCustomers from 02-08.MDB.

Put the following code in the lstCompany_AfterUpdate event procedure:

Private Sub lstCompany_AfterUpdate( )

acbUpdateSearch Me.txtCompany, Me.lstCompany

End Sub

Put the following code in the txtCompany_Change event procedure:

Private Sub txtCompany_Change( )

Dim varRetval As Variant

varRetval = acbDoSearchDynaset(Me.txtCompany, _

Me.lstCompany, "Company Name")

End Sub

Put the following code in the txtCompany_Exit event procedure:

Private Sub txtCompany_Exit(Cancel As Integer)

acbUpdateSearch Me.txtCompany, Me.lstCompany

End Sub

Import the module basSearch from 02-08.MDB. This module contains the code that does all the work.

Every time you change the value in txtCompany, Access triggers txtCompany's Change event. The code attached to that event calls down into the common function, acbDoSearchDynaset. In general, the syntax for calling acbDoSearchDynaset is:

varRetval = acbDoSearchDynaset(textbox, listbox,