GoToRecord

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Angus - 18 Nov 2006 16:11 GMT

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.

Reply to this Message

Dirk Goldgar - 18 Nov 2006 22:26 GMT

> 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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Angus - 18 Nov 2006 23:57 GMT

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.

> > 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.
[quoted text clipped - 18 lines]
> (and many examples have been posted before), but first I'd like to
> understand better exactly what you're trying to do and why.

Reply to this Message

Dirk Goldgar - 19 Nov 2006 00:53 GMT

> Thanks for your reply.
>
[quoted text clipped - 4 lines]
> 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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message