Very slow find.

[dmg2206]

I've created a standard "find" button on my form. It works, but very slowly, too slowly to be useful. It works fine on the ID# (primary key), but not on the lastname field, even though it's indexed. The underlying code is as follows:

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70


Are there any alternatives to this code that would be more efficient? Users are waiting over a minute to pull up a record, which they say is unacceptable.

 

[rewdee]

Going through controls will be slower. Best bet is to create a another dialog form and write a query.

Rewdee

 

[KyleS]

dmg,
Your users are right (first time for everything :) a minute is way too long, so let's see if we can't fix that...

Try this on your button


Dim strFld As String, strItem As String

strFld = "[" & Screen.PreviousControl.ControlSource & "]"
strItem = Screen.PreviousControl

strFld = strFld & " = '" & strItem & "'"

Me.RecordsetClone.FindFirst strFld
Me.Bookmark = Me.RecordsetClone.Bookmark Kyle

 

[dmg2206]

Unfortunately, that code isn't any faster.

Perhaps the problem isn't with the code itself, but with the link. The form accesses an SQL database on a Linux server through an ODBC connection. Searches on the ID field (the primary key) seem to work instantaneously. So do filters. *shrugs*

 

[KyleS]

dmg,
If you write a query to return records based on an indexed (but not Primary) key, does it take a while or is it quick? Kyle

 

[scriverb]

Try using a ComboBox to perform your Name selection but have the ID# be the bound column of the combobox's underlying query. Then perform your record selection using the combobox bound value. Loading the ComboBox in the onload procedure of the form and refreshing only when new records are added will be the only delays.

Create a query with two columns. ID# and Name sorted Ascending by Name Combobox Properties: RowSource -qryIDName column count 2, bound column 1, column widths 0;2.5". In the after update event procedure of the ComboBox put the following code.

me![ID#].setfocus
docmd.findfirst "[ID#] = " & me![ComboBoxName]

Now you are actually performing your selection on the ID# which is your Primary key and it should be fast. Now the downside is the size of the database and the number of records being pulled in to populate the ComboBox. Whenever that becomes a problem because of size what I do is have the User enter into an unbound textBox the Name or the any portion of the name and and then requery the ComboBox query using this textbox value in a LIKE [name field]* criteria statement. Like me![TextBoxEntry] & "*"

When the ComboBox is requeried only a small number of records are returned to be picked from and then the above record find process is used.

Give it a try and let me know if it works for you.

Bob Scriver