Find Function of Edit Menu is Slow in Shared Database

[CathyLynnHughes]

I have a shared database with 25 users. It is not a split database. Users all access the same database using a shortcut. It runs fine most of the time.

The only problem is when someone uses the Find feature on the Edit Menu. Depending on the number of users accessing the database at one time, the system can take 2 to 5 minutes to find a record with a specified phone number in a form. The Find feature started getting slow when the number of records reached 50,000.

I have a command button which searches for the same phone number in the table of 50,000 records. This search, which is based on a query, only takes a few seconds.

How can I speed up the search for a record on the form?

 

[SeeThru]

Couple of things:

1) Make the phone field indexed(duplicates allowed). It speeds up searching.

2) Rather than doing a find, resent the forms recordsource, or reset the Froms filter property

3) Do the find in a recordsetclone, and then use the index to jump to the correct record.


It should be easy enough to find samples of all of these.

 

[CathyLynnHughes]

Dear SeeThru,

Using recordsetclone worked well for finding a record number (AutoNumber). This is the code I used:

'Declare variable for value of Record Number
'that user wants to find.
Dim strResponse As String


'Assign value of Input Box to Record Number variable.
strResponse = InputBox("Enter Data.", "Find:")


'Make Record Number visible and set focus.
Me.Record_Number.Visible = True
Me.Record_Number.SetFocus

'Find first record with the appropriate Record Number.
Dim rst As Recordset
Dim strSearch As String

Set rst = Me.RecordsetClone
strSearch = strResponse
rst.FindFirst "[Record Number] = " & strSearch
If rst.NoMatch Then
MsgBox "Record not found"
Else
Me.Bookmark = rst.Bookmark
End If
rst.Close

Unfortunately, when I tried to use the same code to find an phone number [text field formatted as (999) 999-9999], I got an error message:

Run-time error ‘3077’:
Syntax error (missing operator) in expression.

When I clicked Debug, the system highlighted this line:

rst.FindFirst "[ATN] = " & strSearch

How can I correct this problem?

 

[PHV]

If ATN is defined as text:
[tt]rst.FindFirst "[ATN]='" & strSearch & "'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[CathyLynnHughes]

Dear PHV,

The code that you suggested to find the ATN is wonderful. It worked. Thank you.