Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

Access Cookbook, 2nd Edition [Electronic resources] - نسخه متنی

Ken Getz; Paul Litwin; Andy Baron

| نمايش فراداده ، افزودن یک نقد و بررسی
افزودن به کتابخانه شخصی
ارسال به دوستان
جستجو در متن کتاب
بیشتر
تنظیمات قلم

فونت

اندازه قلم

+ - پیش فرض

حالت نمایش

روز نیمروز شب
جستجو در لغت نامه
بیشتر
لیست موضوعات
توضیحات
افزودن یادداشت جدید










Recipe 2.8 Make a Simple "Searching" List Box



2.8.1 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.


2.8.2 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:

  1. 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.

  2. 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

  1. Import the table Customers and the query qryCustomers from

    02-08.MDB .

  2. Put the following code in the lstCompany_AfterUpdate event procedure:

    Private Sub lstCompany_AfterUpdate( )
    acbUpdateSearch Me.txtCompany, Me.lstCompany
    End Sub
  3. 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
  4. Put the following code in the txtCompany_Exit event
    procedure:

    Private Sub txtCompany_Exit(Cancel As Integer)
    acbUpdateSearch Me.txtCompany, Me.lstCompany
    End Sub
  5. 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, "Field to search")

where textbox is a reference to the text
box in which you're typing,
listbox is the list box in which
you're searching, and
"Field
to
search" is the field in
the list box's underlying record source through
which you're going to search.

The function

acbDoSearchDynaset creates a
dynaset-type Recordset object, searches through it for the current
value of the text box, then sets the value of the list box to match
the value the code found in the underlying record source. Its source
code is:

Public Function acbDoSearchDynaset(ctlText As Control, _
ctlList As Control, strBoundField As String) As Variant
' Search through a bound list box, given text to find from
' a text box. Move the list box to the appropriate row.
' The list box can have either a table or a dynaset (a query
' or a SQL statement) as its row source.
' In:
' ctlText: A reference to the text box you're typing into
' ctlList: A reference to the list box you're looking up in
' strBoundField: The name of the field in the underlying
' table in which you're looking for values
' Out:
' Return value: Either 0 (no error) or an error variant
' containing the error number
Dim rst As DAO.Recordset
Dim varRetval As Variant
Dim db As DAO.Database
On Error GoTo HandleErr
Set db = CurrentDb( )
Set rst = db.OpenRecordset(ctlList.RowSource, dbOpenDynaset)
' Use the .Text property, because you haven't left the control
' yet. Its value (or its .Value property) isn't set until you
' leave the control.
rst.FindFirst "[" & strBoundField & "] >= " & acbcQuote & _
ctlText.Text & acbcQuote
If Not rst.NoMatch Then
ctlList = rst(strBoundField)
End If
varRetval = acbcErrNoError
ExitHere:
acbDoSearchDynaset = varRetval
On Error Resume Next
rst.Close
Set rst = Nothing
Exit Function
HandleErr:
varRetval = CVErr(Err)
Resume ExitHere
End Function

The example in this solution is also set
up so that if you leave the text box, it pulls in the currently
selected item from the list box. That means that you can use Tab to
leave the text box, and the code will place the value that matches as
much as you've typed so far in the text box.


2.8.3 Discussion


Notice that the list
box's ColumnCount property is 2 and the ColumnWidths
property is 0 in this example. This occurs because the query used,
qryCustomers, contains two columns, with the first column hidden in
the list box. Because you're searching for the
second column, that must be the bound column.

This example, as shown so far, uses a
query as the data source for the list box. This method can really
slow things down for large data sets, since it's not
guaranteed that it will be able to use an index. If possible you
should base your list box directly on a table instead, especially if
your data set is much larger than a few hundred rows. In that case,
you can use the Seek method, which is generally much faster than the
FindFirst method used in this example. On the other hand, because it
works with only a single table as its data source,
it's a lot more limiting.

To use the Seek method,
you'll need to change a few properties. To test it
out, make a copy of frmSearchFind and call the new form
frmSearchSeek. Change the RowSource property of your list box to be
Customers, rather than qryCustomers. In addition, change the function
that txtCompany calls from its Change event procedure to the
following:

Private Sub txtCompany_Change ( )
Dim varRetval As Variant
varRetval = acbDoSearchTable(Me.txtCompany, _
Me.lstCompany, "Company Name", "Company Name")
End Sub

In this case, you'll be calling the

acbDoSearchTable function, which searches
through an indexed table instead of through an unindexed dynaset. In
general, you'll call

acbDoSearchTable with the following syntax:

intRetval = acbDoSearchTable(textBox, listBox, "BoundField", "IndexName")

where textbox is a reference to the text
box in which you're typing,
listbox is the list box in which
you're searching,
"BoundField"
is the field in the list box's underlying record
source through which you're going to search, and
"IndexName"
is the name of the index you're going to use.
(Usually it'll just be
"PrimaryKey," but in this example
use "Company Name". This table is
indexed both on the Customer ID field (the primary key) and the
Company Name field; you're using the Company Name
index.)

The code for

acbDoSearchTable is almost identical to that for

acbDoSearchDynaset , except that the table search
uses the Seek method to search through an indexed recordset instead
of the FindFirst method. Because it can use the index, it should be
able to find matches much more rapidly than

acbDoSearchDynaset .


Because

acbDoSearchTable requires that the list
box's record source be a table, it will trap for
that error and return a nonzero value as an error variant if you try
to use it with some other data source. In addition, the function will
not work correctly if you mismatch the bound field and the index.
That is, the bound field must be the only field in the selected
index).

The code for

acbDoSearchDynaset ,

acbDoSearchTable , and

acbUpdateSearch is in the module basSearch. If
you want to use this functionality in other applications, import that
module into your application and follow the steps outlined earlier to
set the properties for your text and list boxes. In addition, if you
import the sample code into a database created in Access 2000 or
later, make sure you use the Tools References menu item
from within VBA to add a reference to the Microsoft DAO type library.
By default, Access applications created in those versions
don't include a reference to DAO, and the sample
code in this demonstration requires this reference in order to do its
work.


/ 232