Slow performance of .FindRecord

[CPGDeveloper]

Hello~

I administer a db application --MS Access Front End/Azure SQL Back End. The form in question is based on an MS Access Query. I'm having this bizarre issue where I'm using something like this

Dim selid as long
selid = Me.txtSearchID.Value
Me.txtID.SetFocus
DoCmd.FindRecord selid

While the above scenario works to navigate to a specific record, it is painfully slow at times -- upwards of 60 seconds. I have the same speed issue if I'm using a recordset clone as well.

I do notice however, that if I use 'GoToRecord', it is much faster...almost instantaneous:
DoCmd.GoToRecord acDataForm, "MyForm", acGoTo, 7

The problem, of course, is '7' -- that's made up -- I'm trying to figure out how I would determine the proper row number -- something logically along the lines of:

'SELECT rownumber FROM myquery WHERE myid = x'

 

[Micron]

Is the field being searched indexed?
Are you loading hundreds of thousands of records into your form (a huge table) instead of filtering the form's records via query instead?
acGoTo, 7 means you're not searching so that makes perfect sense.

 

[CPGDeveloper]

Micron Thanks for your reply -- yes, silly me, I was searching on a non-indexed field. Changing that solves the issue. Thank You!

 

[Micron]

You're welcome. Note that having too many indexes can slow things down as well. You should only index fields that you'll search on or are required to ensure unique values or combinations of values.