Cancel Button not working

[alexandervj]

I have a cancel button on an add new record form and its not working for some reason. When I press cancel it prompts if I really want to cancel and when I press yes it cancels the record creation BUT it adds a number to the recordID autonumber as if one has been created. Is there anyway to stop this? Here is my code

Option Compare Database

Private Sub Cancel_Click()
On Error GoTo Err_Cancel_Click
If Me.Dirty = True Then
If MsgBox("Are you sure you wish to Cancel? Any changes you made will be lost. Please click Yes to cancel or No to return to the form", vbYesNo) = vbYes Then
'If yes then undo unsaved changes:
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
'Then close form:


DoCmd.Close
End If
End If
If Me.Dirty = False Then
'If no changes close form:
DoCmd.Close
End If
Exit_Cancel_Click:
Exit Sub
Err_Cancel_Click:
Resume Exit_Cancel_Click
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
'If the form data has changed a message is shown asking if
'the changes should be saved. If the answer is no then
'the changes are undone

On Error GoTo BeforeUpdate_Error

If Me.Dirty Then
'if record has been changed the dirty property
' is set to true Display message to save the record
If MsgBox("Are you sure you want to save your changes?", _
vbYesNo + vbQuestion, "Save Changes") = vbNo Then
Me.Undo
End If
End If
BeforeUpdate_Exit:
Exit Sub
BeforeUpdate_Error:
MsgBox Err.Description
Resume BeforeUpdate_Exit
End Sub
End Sub

[June7]

Bing: Access vba cancel add record

Does this help http://www.access-programmers.co.uk/...d.php?t=215497

[alexandervj]

I changed beforeUpdate to beforeInsert but still doing the same thing

[June7]

Did you do the global variable as described in the link?

[alexandervj]

Yes, heres my code

Option Compare Database
Public CancelPressed As Boolean
Private Sub Cancel_Click()
CancelPressed = True
On Error GoTo Err_Cancel_Click
If Me.Dirty = True Then
If MsgBox("Are you sure you wish to Cancel?", vbYesNo) = vbYes Then
'If yes then undo unsaved changes:
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
'Then close form:
DoCmd.Close
End If
End If
If Me.Dirty = False Then
'If no changes close form:
DoCmd.Close
End If
Exit_Cancel_Click:
Exit Sub
Err_Cancel_Click:
Resume Exit_Cancel_Click
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
'If the form data has changed a message is shown asking if
'the changes should be saved. If the answer is no then
'the changes are undone

On Error GoTo BeforeInsert_Error

If Me.Dirty Then
'if record has been changed the dirty property
' is set to true Display message to save the record
If MsgBox("Are you sure you want to save your changes?", _
vbYesNo + vbQuestion, "Save Changes") = vbNo Then
CancelPressed = True
End If
End If
BeforeInsert_Exit:
Exit Sub
BeforeInsert_Error:
MsgBox Err.Description
Resume BeforeInsert_Exit
End Sub

[June7]

Try simplifying code. Add message and error handler later if you want. But why annoy user with another question?

Private Sub Form_BeforeInsert(Cancel As Integer)
Cancel = CancelPressed
End Sub

However, in the Click event, shouldn't set CancelPressed = True until user confirms they want to cancel.

[alexandervj]

I simplified it to
Option Compare Database
Public CancelPressed As Boolean
Private Sub Cancel_Click()
CancelPressed = True
DoCmd.Close
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
If CancelPressed = True Then
Cancel = True
End If
End Sub


but still not working :/ idk what Im doin wrong

[June7]

Unfortunately, I've never actually used that code.

However, that latest looks good and I don't understand failure.

Have you step debugged? Refer to link at bottom of my post for debugging guidelines.

If you want to provide db, follow instructions at bottom of my post.

[alexandervj]

I'll try step debugging. Thanks for your help June

[alexandervj]

I think maybe it has to do with I'm using a default value for an ID passed through from a previous form. That value shows up automatically before anything is inserted. Once you start entering data into another field all fields that are populated based on that LotID_FK are populated. I wonder if theres a way I can make that a default value after the user starts inputting data into another field

[June7]

Could populate that field in the BeforeUpdate or BeforeInsert event.

What do you mean by 'all fields that are populated based on that LotID_FK are populated'? Is data being duplicated between tables?

[alexandervj]

This is how the form looks when it first opens. The fields in light grey with no borders auto populate based on the LotID_FK which is the field that has a default value assigned to it from the last form. All that greyed out, non border Lot data that gets populated actually comes from a different table

[alexandervj]

heres another picture. This is what happens when you start inputting data into one of the fields, it auto populates from another table based on that LotID_FK

[June7]

Okay, you populate textboxes but is that data saved to table this form is bound to? I am suggesting it should not.

[alexandervj]

there is data from two tables on this form - there is a run table and a lot table. The lot table holds information that is used in multiple runs (LotID, LotSKU, Year Manufactured, Work Week Manufactured, TypeWL, Epi/Lot, Descriptor). The runs table basically is the number of production runs in that specific lot and needs to hold notes about the run and the run date. After I do this I will also have a products table. Each run will have many products.

From this perspective form 1 is the Lot Details Form (which I'll attach here right after I post this), which has an add run button which adds a run to this specific lot and you can enter data about the run (images attached in my previous posts). I basically just want to assign that LotID which is in the lots table to the LotID_FK which is in the runs table so it knows that that run is assigned to that lot