Problem scrolling to the next sorted record after I created a new one

[slyvia]

I am trying to fix a scrolling problem within a current Form. Currently, when I create a new record, it stays at the end until the form is requeried. I want to be able to create a new record, resort it, and then go to the next record after the record I created.

What I tried to do is bookmark the id of the new record in the after update sub, requery the form and carry that id into my next_click or prev_click routine. It finds that record I created and then moves to the next one. The problem is that when I haven't created a new record, it retains the id of the last record I created and thus, locks the form if I hit next or prev.

Does anyone have any ideas to overcome this problem?

 

[JGillespie]

Hi Slyvia,

try setting up a variable as a flag to check when a new record has been entered. Have this variable set to false on form_open and changed to true on after_insert.

Check the state of this variable in your next_click and prev_click routines if it is false then perform standard next record and previous record operations if true then your bookmarking and sorting routine.

Remember to set the flag back to false again ready for the addition of another record.

Let me know if it helps. Jamie Gillespie
j-gillespie@s-cheshire.ac.uk

 

[Aivars]

Hi, Slyvia!

Try these codes:
Private Sub Form_AfterInsert()
On Error GoTo errX
Dim MyId
Dim rst As Recordset

MyId = Me.txtID
Me.Requery
Set rst = Me.RecordsetClone
rst.FindFirst "ID=" & MyId
if not rst.nomatch then
Me.Bookmark = rst.Bookmark
end if
rst.close
set rst=nothing
DoCmd.GoToRecord , , acNext

ExitSub:
Exit Sub

errX:
MsgBox Err.Number & vbLf & Err.Description
Resume ExitSub

End Sub

Aivars

 

[slyvia]

How do I reset that flag after I am done with it? That was one of the problems all along.