return to same record on refresh

[belovedcej]

I have a main form (frmCases) with two subforms. I have a stored procedure running on the After UPdate event of on of the controls on sbfrmMeetingsPerCase. The SP creates a new record in the table represented by sbfrmStatus.

I then want to refresh the data so sbfrmStatus reflects the record just added, return to the same record on frmCases, and set the focus back on a particular control in sbfrmMeetings.

I checked other threads for this and used the code suggested. The problem is that after I refersh it does not take me to the same record. It flips through the records and puts me somewhere else.

Here's the relevant code:

Dim myVal As Integer
Dim stDocName As String

myVal = Me.Case_ID
stDocName = "frmCases"

Form_frmCases.Refresh

DoCmd.GoToRecord acDataForm, stDocName, acGoTo, myVal

Form_frmCases.sbfrmMeetingsPerCase.SetFocus
Me.txtMeetingDate.SetFocus


By the way - if it matters, the forms are bound because I'm still to much of a beginner at SQL Server and VBA to do it any other way - we plan to change that for a future version.

Thanks for any help you can give!

 

[belovedcej]

I figured out an alternative that works. I replaced all the above mention code with just one line.

Form_frmCases.sbfrmStatusPerCase.Requery

works like a charm!

 

[danvlas]

That will position the subform on its first record.
If you want the subform to go back to the initial record after refresh/requery:


Dim varBkm as Variant

varBkm = Me.{Subform Name}.Recordset.Bookmark
Me.{Subform Name}.Refresh
Me.{Subform Name}.Recordset.Bookmark = varBkm

HTH





Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html