PreDetermined search perimeters on Find Box

[Yarbz]

Hi guys, I'm a relative newby to programing and here's my situation.
I'm trying to idiot proof my '97 MS Access file, which is a multi user database I created at work, and I need to program in a predetermined search perimeters on a command button.

In my main data entry form, I've put a Find button that pulls up the Find box just like the one on the tool bar but because of the amount of entries placed in it daily and number of fields it searches, the search is getting slow.

Is there a way to program the Find box to automatically select the Client Number field and search it only, Just as if I clicked on the client number and clicked on find, and clicked on "search only current field", But all in one click?

Thanks in Advance
Yarbz

 

[Steve101]

If you're after performance, then instead of using the inbult Find dialogue, which you bring up via a button on your form, why not use an unbound combo box on your form, which allows you to select the client from the box, and then syncs the form based on your selection.

To do this:

(a) Add the combo box to the form; give it a label of 'Find Customer:'

(b) Add the RowSourceType as Table/Query, and specify the Rowsource as 'SELECT ClientNumber, ClientName FROM tblClient ORDER BY ClientName' - modify this SQL as appropriate to your table; but make the first field the Id, and subsequent fields descriptors; make sure that the list orders alphabetically.

(c) Add the column count and column width properties to the combo; make the width of the initial column 0, so that the first column to show is the ClientName field. This should appear in alphabetical order.

(d) Make sure the LimitToList property is set to Yes, and the AutoExpand property is set to Yes. Autoexpand allows you to type one or more characters, and then restricts the list to only those items which match the initial characters.

(e) Add the following code behind the AfterUpdate event of the combo:

Dim F as form: Set F = me
F.recordsetclone.findfirst "ClientNumber = " & F!cmbClientNumber
F.bookmark = F.Recordsetclone.bookmark

You will need to adapt the above code according to your own field names and combo name. I'm also assuming that ClientNumber is numeric, otherwise you'll need to use quotes; ie. the line will change as follows:

F.recordsetclone.findfirst "ClientNumber = '" & F!cmbClientNumber & "'"

(f) With the above in place, when you select a customer from the list, the underlying form should synchronise with the selection.

This approach is food for thought. I hope you find it helpful. Make sure that the ClientNumber field is indexed. You may also have to deal with the situation where new clients are added, as the data behind the combo box then has to be requeried; get this going first; then that can be addressed,



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[Yarbz]

Thanks,
But unfotunately my boss doesn't like too many new things. Hence the reason I wanted to use something she is familar with - the Find Box - Cause we add new clients on a daily basis, and have to use find to make sure no one else has already entered them in, then enter the new person, which starts a chain reaction of info that is entered.
Find is used a lot.

Thanks again though
Yarbz