After Update, Goes to Last Record

  • Thread starter Thread starter Gerard
  • Start date Start date
G

Gerard

Hey all,
I am running SQL 2k with Access 2k. I am using SQL
Server with Access as the front end for the forms. I have
code in Form_AfterUpdate that forces a Recordset.Requery
when a record is added so that the record just added
appears in the recordset with it's joins, and thus appears
on the form correctly. This works fine, but after
Form_AfterUpdate executes something else is fired and
Access goes to the last record in the recordset(it assumes
that's where the record just added is). Well after
the .Requery, the record no longer appears at the end of
the recordset, it is now in it's OrderBy location, so
Access is going to the wrong record. I can't find where
or how this .MoveLast is being executed, nor can I
suppress it. It goes to the last record as soon as the
End Sub executes in Form_AfterUpdate. Below is the Code
for the Form_AfterUpdate. Any help is greatly appreciated
as always.

Thanks,
Gerard
--------------------------------------------------------
Private Sub Form_AfterUpdate()
Dim lngHomeID As Long

lngHomeID = Me.Homeid

Form.Recordset.MoveLast
Form.Recordset.MoveFirst

Form.Recordset.FindFirst ("[HomeID] = " & lngHomeID)
Form.Refresh

End Sub
 
Siamak,
Thanks for the post, that solved the problem! Here is
a box of recycled bits to thank you.

Thanks,
Gerard
-----Original Message-----
Hi,
I've almost faced the same situation.
I had a refresh button on my form, when I was on a record
Click to expand...
(that wasn't the last record) and I hit the refersh button
access would automatically take me to the last Here is how
I got around the problem:
Clone the recordset and bookmark it to the record I was
Click to expand...
looking at (in your case the bookmark would be the ID of
the inserted record), then do the refresh and go to the
last record to make sure all the records are loaded (I've
had problems with the Max records property). After the
refresh was completed, I would bookmark the RecordSet of
the form to the cloned Recordset.
here is the code, hope it works for you:

Private Sub ComIssueRefresh()
Call ShowMsg("Refreshing...")

Dim rs As Object
Set rs = Me.Recordset.Clone
rs.Find "[ComID]=" & Me!ComID


'Refresh the form
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
DoCmd.GoToRecord , , acLast

Me.Bookmark = rs.Bookmark
Me.BxMsg.Visible = False
Me.LbMsg.Visible = False

Call HideMsg
End Sub

Gerard said:
Hey all,
I am running SQL 2k with Access 2k. I am using SQL
Server with Access as the front end for the forms. I have
code in Form_AfterUpdate that forces a Recordset.Requery
when a record is added so that the record just added
appears in the recordset with it's joins, and thus appears
on the form correctly. This works fine, but after
Form_AfterUpdate executes something else is fired and
Access goes to the last record in the recordset(it assumes
that's where the record just added is). Well after
the .Requery, the record no longer appears at the end of
the recordset, it is now in it's OrderBy location, so
Access is going to the wrong record. I can't find where
or how this .MoveLast is being executed, nor can I
suppress it. It goes to the last record as soon as the
End Sub executes in Form_AfterUpdate. Below is the Code
for the Form_AfterUpdate. Any help is greatly appreciated
as always.

Thanks,
Gerard
--------------------------------------------------------
Private Sub Form_AfterUpdate()
Dim lngHomeID As Long

lngHomeID = Me.Homeid

Form.Recordset.MoveLast
Form.Recordset.MoveFirst

Form.Recordset.FindFirst ("[HomeID] = " & lngHomeID)
Form.Refresh

End Sub
Click to expand...
.
Click to expand...
 
Back
Top