GoToRecord irritation

[DaveMere]

Hi all.

I've written some code in the form_current procedure that is designed to query the user when they try to create a new record, then proceed accordingly. Basically;

If msgbox ("Confirm") = vbYes then

(Run query to fetch next primary key and create new record)
(This bit works fine.)

Else

DoCmd.GoToRecord acActiveDataObject, , acLast
(This bit is where the problem is)

End if

This DoCmd statement does not seem to go to the last record in the available set - it just leaves the primary key field blank. I've tried variations on the statement; such as acGoTo (variable) and even acFirst, none of which take me to a different record.

What am I doing wrong?

Thanks

DaveMere

 

[DaveMere]

Solution

Out of interest, thought I'd publish my solution to this.

The problem was that the Msgbox querying whether the user wanted to proceed with creating a new record was contained within the form_current event - but by the time the code reached this event the record was already created.
Instead, I placed the user query within the Form_BeforeInsert subroutine like so;

Private Sub Form_BeforeInsert(Cancel As Integer)

If MsgBox("Create New?" = vbYes Then

Else

DoCmd.CancelEvent
DoCmd.GoToRecord acActiveDataObject, , acLast

End If

End Sub

The CancelEvent method cancels the insert of a new record , then the next line takes the form to the last record in the dataset.

Dave