Refresh a form AND go to a specific record?

[Amit]

Hi,

I have a form with a subform on it. There is a "refresh"
button on the main form, that requeries the form. When
I "refresh" the form from record 3, it does that, but then
goes to the first record. I'd like it to refresh and then
stay at (or move to) the same record from which the button
was clicked.

I'd like this to happen for the record in both the form
and the subform. A record in the main form can have
multiple records in the subform.

I've implemented this by having a global variable, and
then assign Me.CurrentRecord to it before
refreshing/clicking, and then go that record
using "DoCmd.GoToRecord". All this in the code for button -
OnClick property.

I've tried this, but with limited success. I'm able to go
to the same record for the main form, but not sure how to
reference the subform record through the main form.

Here's the code I'm using in the OnClick event in the main
form:
=====================================================
saverecnum = lngrecordnum 'to save the current record
'MsgBox "Before requery"
Me.Requery
'MsgBox "After requery"
DoCmd.GoToRecord , , acGoTo, saverecnum
Me.InvoiceAmount = Me.txtRunningTotal
======================================================

I'm assigning current record to "lngrecordnum" in
onCurrent event of the form.
How will I access through the main form, the current
record value of the record on the subform??
Additionally, will it be a better programming practice to
assign "saverecnum" to 0 or null in the OnCurrent event of
the form?

Thanks for any help.

-Amit

 

[Amit]

Thanks for reading, but I figured it out after playing
around with it. Here is the pertinent code snippet that
worked for me, just in case you are having the same
problem:

=================================================
Private Sub cmdRefresh_Click()
On Error GoTo Err_cmdRefresh_Click
saverecnum = Me.CurrentRecord
saverecnumsfrm = Me!sfrmInvoiceItem.Form.CurrentRecord
Me.Requery
DoCmd.GoToRecord , , acGoTo, saverecnum
Me!sfrmInvoiceItem.SetFocus
DoCmd.GoToRecord , , acGoTo, saverecnumsfrm
Me.InvoiceAmount = Me.txtRunningTotal
....
....
=================================================

Cheers,

-Amit