GoToRecord

[Guest]

I have a hidden form which I want to requery, then take the focus back to the
same record that had focus before the requery.

I presume I use the GoToRecord action but I need to find the record number
beforehand so that I can use this in the Offset argument. How can I do this?

Thanks in advance.

 

[Dirk Goldgar]

I have a hidden form which I want to requery, then take the focus
back to the same record that had focus before the requery.

I presume I use the GoToRecord action but I need to find the record
number beforehand so that I can use this in the Offset argument.
How can I do this?

There's a logical problem with what you're trying to do, apart from the
technical question. There's no guarantee that record number X on the
form *after* you requery the form will be the same record as record
number X *before* the requery. Requerying a form reruns the form's
recordsource query to pick up records that may have been added to the
tables since the form was loaded, and to drop records that no long meet
the query's criteria. If there's no possibility of any such changes,
what's the point of requerying the form in the first place?

If you really want to find the same record after requerying, what you
need to do is save the primary key of that record before you requery,
and then find the record with that key again, after you requery. You
wouldn't use GoToRecord for that. I could write code to do that for you
(and many examples have been posted before), but first I'd like to
understand better exactly what you're trying to do and why.

 

[Guest]

Thanks for your reply.

I do not necessarily want to go to a record *X*, rather I want to go to a
record where MyControl is the same value os before the requery.

The reason for this is that another form, which will be requeried after the
first, will display records depending on the record that has the focus in the
first form.

 

[Dirk Goldgar]

Thanks for your reply.

I do not necessarily want to go to a record *X*, rather I want to go
to a record where MyControl is the same value os before the requery.

The reason for this is that another form, which will be requeried
after the first, will display records depending on the record that
has the focus in the first form.

Okay, that's perfectly reasonable. The easiest way to do this is with
VBA code, not a macro, because macros don't let you define local
variables. I would do it like this:

'----- start of example code (Access 2000 or later) -----

' Declare a variable to hold the current record's key.
Dim vMyControlValue As Variant

' Save the current record's key.
vMyControlValue = Me.MyControl

' Requery the form.
Me.Requery

' Find the record whose key we saved.
Me.Recordset.FindFirst "MyControlField=" & vMyControlValue

'----- end of example code -----

In the above, "MyControl" is the name of the bound control containing
the record key, and "MyControlField" is the name of the field to which
that control is bound. These names may or may not be the same.

If the original record is no longer in the form's recordset, the above
code will leave the requeried form positioned at the first record.