Own Navigation-Buttons... strange behaviour

[Stefan Oedenkoven]

Hi NG,

after a click on my own 'New-Button' I would like to create a new record,
save an ID and make it the currently displayed record on the (single)form.
erm, sounds simply - but i don't get it ;-(

When i start with no records and add one after another it works fine. But
for example I've 10 records and delete the 2nd, then the next new record
will be on position 2 and another new one will be on position 3 and so
on....

Access help writes:
In a dynaset-type Recordset object, records are inserted at the end of the
Recordset, regardless of any sorting or ordering rules that were in effect
when the Recordset was opened.


Erm, but not in my case...
please let me know if you have a solution

thanks for your help,
Stefan





my Code:
--------------------
Private Sub NewButton_Click()
RunCommand acCmdSaveRecord
Dim RS As Recordset
Set RS = Me.RecordsetClone
RS.addNew
RS![fRMAID] = Me.Parent.Parent![RMAID] ' directly save the new
record, because more than one user can add records
RS.Update

Me.Requery
DoCmd.GoToRecord , , acLast

RS.Close
End Sub

 

[Allen Browne]

The Requery is reloading all records.
Naturally enough, Access applies any sorting you asked for when they reload.

Drop replacing:
Me.Requery
DoCmd.GoToRecord , , acLast
RS.Close
with:
Me.Bookmark = RS.LastModified
Set RS = Nothing

Setting the form's bookmark makes it the active record.
The RecordsetClone is not closed, but you do need to destroy your RS object
(by setting to Nothing).


If you just wanted to move to a new record without creating a new record,
try:
If Me.Dirty Then 'Save any edits.
RunCommand acCmdSaveRecord
End If
RunCommand acCmdRecordsGotoNew

 

[Stefan Oedenkoven]

The Requery is reloading all records.
Naturally enough, Access applies any sorting you asked for when they reload.

Drop replacing:
Me.Requery
DoCmd.GoToRecord , , acLast
RS.Close
with:
Me.Bookmark = RS.LastModified
Set RS = Nothing

Setting the form's bookmark makes it the active record.
The RecordsetClone is not closed, but you do need to destroy your RS object
(by setting to Nothing).


If you just wanted to move to a new record without creating a new record,
try:
If Me.Dirty Then 'Save any edits.
RunCommand acCmdSaveRecord
End If
RunCommand acCmdRecordsGotoNew

thank you!
It's working fine now ;-)
regards,
Stefan