Cant go to specified record.

[Richard B. Lewis]

Hi,

We have a form with the following construct:

------------------------------------------------
tmpRec = me.CurrentRecord

.... we do calculations here and update the underlying datasource (SQL)
.... when we come back to the form, we want to sit on the same record we left

Me.Refresh
DoCmd.GoToRecord,,acGoTo, tmpRec
-------------------------------------------------

The problem is that on large datasets, the Refresh isn't finishing before
the DoCmd.GoToRecord statement is executed and we get the message that we
can't go to the specified record.

Is there a way to ensure that Access finishes the Refresh before continuing?

Thanks in advance.
(e-mail address removed)

 

[Allen Browne]

Hi Richard

There are actually several things that could go wrong.
If you alter the datasource, will it still be the n-th record in the form?
Will the refresh fail silently? (e.g. if the record is dirty)

If the record source is likely to be changed, it may be better to save the
primary key value in a variable, and then FindFirst again after the changes.
If the record source is not being changed, saving the form's Bookmark into a
variable would be the fastest way to get back there again.

If you wish to continue with your existing approach, a DoEvents might allow
the Refresh to run to completion, but it is one of those things that you
can't be sure of, and it also introduces the possiblity of interference from
other processes, or even re-entering this one before it completes.