Return to Same Record, Not Top

[LarryP]

My second visit in one day to the fount of all knowledge ...

Access 2003/Windows XP, have a form/subform where the user can flag selected
items in the subform, then pop open a batch actions form, set parameters, and
run any of several batch actions on the flagged records. All that runs okay,
but when the batch actions form is closed the main form jumps back to the
first record rather than the one from which the batch form was called. What
VBA will prevent that and bring the user back to the record where he was
working?

 

[Steve Sanford]

Visit the wellspring at

http://allenbrowne.com/ser-18.html

Title: Return to the same record next time form is opened



HTH

 

[LarryP]

Thanks, Steve, for your response. I get the general gist of that, but in my
app the main form isn't actually closed while the batch action(s) are run; it
stays open in the background, and appears again once the batch actions form
is closed. Ergo, I don't have the Unload and Load events to work with. (I
could modify things so it is closed, then re-opened, but there's a
"calculating..." delay of 20-30 seconds on open that users would probably
find annoying if they had to deal with it every time they run a batch
action.) Can you suggest some other event on either the batch form or the
main form that would cause the main form to move back to the record it was in
when the batch form was opened? I've experimented with Activate, Current,
GotFocus, LostFocus and Unload for the batch form, and a couple others, and
haven't found the right event yet.

 

[Klatuu]

I don't know where the code will go, but this the technique to do that. The
principle is before you do your batch, you need to capture the primary key
value of the current record so that when the batch is complete, you can use
the FindFirst method to return to the record. Note, it is not absolutely
necessary it be the primary key field, but it does need to be a unqiue values
field or you may not get the record you were previously on:

Dim lngPrimeKey As Long

lngPrimeKey = Me.txtPrimeKeyControl

'Do the batch here

With Me.RecordsetClone
.FindFirst "[PrimeKeyField] = " & lngPrimeKey
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

 

[Steve Sanford]

Why does the main form change records? That suggests somewhere there is a
"Requery" in the code.

What do you mean by "batch form"? What does it do?

 

[LarryP]

Hi, Steve. I've solved this in the meantime using one of the other posts,
but don't like to not answer questions from helpful people. In reverse order:

The user flags various subrecords in the subform for batch action, then
opens the "batch form" where he selects WHICH batch action to perform and
provides necessary values -- dates, names, comments, etc. -- depending on the
chosen action. He then runs the batch transaction, which involves one or
more append and/or update queries. When the batch transaction completes, he
runs another or just closes the batch form; when he closes the form he is
offered the option to clear all the batch flags or leave them in place.

As to why back to first record, ???? The Close event on the batch form
doesn't do a requery, but the OnActivate and OnCurrent events of the main
form do some refreshing, so that's probably where the culprit resides. I
couldn't eliminate that refreshing, but as I said, I've solved it in the
meanwhile with some suggestions relating to bookmarks, so all's well. Thanks
again for your help and interest.

 

[Steve Sanford]

Goad you solved the problem.

And thanks for answering even though it wasn't necessary.