Detect if user is changing new or old record??

[Wegets7]

I'm using Access xp and working on a .mdb database. I have a form that is bound to a junction table between student and books. It records the copy number, student number and data in/out.

I need to advise the user when they have changed an old records copy number or student number with a Yes/No message box and by selecting no the user should be able to reset the changes.

Both copy number and student number selected using combo boxes.

Rich

 

[ghudson]

Use the forms BeforeUpdate event. Cancel the event if the user selects no and then use the Undo command to reset the values.

 

[Wegets7]

Thanks g

This strategy doesn't seem to distinguish between the user creating a new record and editing an old record. I didn't make that clear enough I guess. I would like to notify the user only if they are changing a old record.

I also get an error. Undo command unavailable now.

Here is the code I am using.

Private Sub FK_CopyKey_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_FK_CopyKey_BeforeUpdate

    Const strMessage1 As String = "Are you sure you want to change this record?"
    Const strTitle As String = "Changing Records?"
    Dim bytResponse As Byte
    Const intStyle As Integer = vbInformation + vbYesNo + vbDefaultButton2
    Dim Count As Integer
        
        bytResponse = MsgBox(strMessage1, intStyle, strTitle)
 
    If bytResponse = vbNo Then
        Cancel = True
        DoCmd.RunCommand acCmdUndo ' Generates error 
    End If
    Exit Sub

Err_FK_CopyKey_BeforeUpdate:
    If Err.Number <> 3021 Then
           MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    End If

End Sub

Rich

 

[ghudson]

You can throw an IF in there and test if the record is not a new record.

If Not Me.NewRecord Then

 

[Wegets7]

Thanks g
That works great, but I still get the alarm "undo is not available now"

Any suggestions
Rich

 

[ColinEssex]

Have you tried

Me.Undo

Col

 

[Wegets7]

Now I have and it works great.
Thanks much. I knew it had to be something simple.