Move cursor to current record after requery another subform

[calvinle]

Hi,

I have 2 subform, once the data is changed in subform2, it will requery the subform 1 to the current date, but my subform 2 keep bringing back to the first record.

Here is my code to change the date in the subform 1:

 Private Sub first_name_AfterUpdate()
  If Me.first_name <> Nz(Me.first_name.OldValue) Then
    sId= Nz(Me.id)
  
    sSql = "UPDATE tbl_name " & _
           "SET [eff_date] = Date() " & _
           "WHERE [id] = " & sId & ""
  
    CurrentDb.Execute sSql, dbFailOnError
  
     Form_frm_main.sf_subform1.Requery
  
  End If
End Sub

How can I make my cursor to go to the next field in the record of that subform 2?

Thanks

 

[Cronk]

Save the bookmark of the second form's recordset before the requery and reset it after the requery ie

dim varBkMark as variant
varBkmark = Form_frm_main.sf_subform1.bookmark
Form_frm_main.sf_subform1.Requery
Form_frm_main.sf_subform1.bookmark = varBkmark

 

[calvinle]

It doesnt work.
There is a main form with subform1 and subform2.
The after update of a field in subform2 will update the field in subform1 and requery the subform1. The cursor in the subform2 will move back to the first record which is not what I want.

 

[Cronk]

Um, could you try adding the same code to reposition the second subform.

 

[calvinle]

I figure it out by recalc the subform after the requery then reposition the cursor.