Getting rid of unwanted message boxes

[hilbertm]

I am using the following code to prompt the use when a new record is added.

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_error
Dim intResponse As Integer
Dim strMsg As String
strMsg = "You have made changes that will add a new record. Do you want to save changes?"
intResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Confirm Adding record")
Select Case intResponse
Case vbNo
Cancel = True
End Select
Form_BeforeUpdate_exit:
Exit Sub
Form_BeforeUpdate_error:
MsgBox Err & vbCr & Err.Description, vbExclamation, "Form_BeforeUpdate"
Resume Form_BeforeUpdate_exit
End Sub

If the user accidentally clicks the cmd button to add a new record, an “add a record” form opens and when the user closes the form (without updating any data because the form was accidentally opened) , a yes no msgbox appears. When no is selected, the user is faced with the following message on “You can’t save this record at this time.
Database may have encountered an error while trying to save a record. If you close this object now, the data changes you make will be lost. Do you want to close the database object anyway?” I am not sure why the message is generated, but a assume it is because the new record is blank since no data has been entered.

This actually works if yes is clicked, but my users will get confused by this message and probably will not know what to do. My question is why is this message generated when the user does not want to save changes, and can I just make a yes no msgbox that says “are you sure you want to close the form and abandon any changes”.

Also,
If a user adds or changes data on the enter new record form, then clicks no when prompted to save changes, the following message is received:
“The DoMenuItem action was canceled.
You used a method of the DoCmd object to carry out an action in Visual Basic, but then clicked the Cancel in a dialog box. For example you used the Close method to close a changed from, then clicked Cancel in the dialog box that asks if you want to save the changes you made on the form.”
Is there any way for this msgbox not to show up If the user cancels their changes? Or maybe a less ominous msg that says “You canceled the changes”.

 

[SteveA]

When the user says they don't want to save the updates enter code like Me.Undo. This will reverse all the changes made and Access won't attempt to save the record when the form closes. This should also resolve the other errors. If not, let me know

SteveA

 

[Rich]

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_error
If IsNull(Me.txtPtasNumber) Then
Exit Sub
End If
Dim intResponse As Integer
Dim strMSG As String
strMSG = "You have made changes that will add a new PTAS. Do you want to save changes?"
intResponse = MsgBox(strMSG, vbQuestion + vbYesNo, "Confirm Adding PTAS")
Select Case intResponse
Case vbNo
Me.Undo
End Select
Form_BeforeUpdate_exit:
Exit Sub
Form_BeforeUpdate_error:
MsgBox Err & vbCr & Err.Description, vbExclamation, "Form_BeforeUpdate"
Resume Form_BeforeUpdate_exit
End Sub
You might also like to add code that checks other data entry without having added a valid PTAS number.

 

[hilbertm]

Thanks guys. I will have to try the suggestions next week as I have forgot my database at work.

 

[hilbertm]

I used the following code to enter a new record.
Private Sub cmdEnterPtas_Click()
On Error GoTo Err_cmdEnterPTAS_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.chkPtasActive = -1
DoCmd.Close acForm, "frmPtasNew"
Exit_cmdEnterPTAS_Click:
Exit Sub
Err_cmdEnterPTAS_Click:
MsgBox Err.Description
Resume Exit_cmdEnterPTAS_Click
End Sub

Then I use this when updating:

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_error
If IsNull(Me.PtasNumber) Then
Exit Sub
End If
Dim intResponse As Integer
Dim strMSG As String
strMSG = "You have made changes that will add a new PTAS. Do you want to save changes?"
intResponse = MsgBox(strMSG, vbQuestion + vbYesNo, "Confirm Adding PTAS")
Select Case intResponse
Case vbNo
Me.Undo
End Select
Form_BeforeUpdate_exit:
Exit Sub
Form_BeforeUpdate_error:
MsgBox Err & vbCr & Err.Description, vbExclamation, "Form_BeforeUpdate"
Resume Form_BeforeUpdate_exit
End Sub

It seems to work when I enter a new PTAS number except the "yes/no are you sure you want to update the record" msgbox appears twice. If I click yes twice, the record gets updated and the PTAS active yes no box gets updated.

If I click yes first, then no second, the new ptas record gets updated but the ptas active yes no box is not updated (not checked).

If I click no then a record with a blank ptas number and the ptas active box checked is generated.

Is there something simple I am missing?

Thanks
Mike

 

[Rich]

Change this On Error GoTo Err_cmdEnterPTAS_Click
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.chkPtasActive = -1
to On Error GoTo Err_cmdEnterPTAS_Click
Me.chkPtasActive = -1
DoCmd.RunCommand acCmdSaveRecord

 

[hilbertm]

Rich,
Thanks that works.
Another problem. I’m using the following code to edit the current record on a new form:

Private Sub cmdPtasEdit_Click()
On Error GoTo Err_cmdPtasEdit_Click
DoCmd.OpenForm "frmPtasEdit"
Forms!frmPtasEdit!PtasID = Forms![frmMaintForm]![PtasID]
Exit_cmdPtasEdit_Click:
Exit Sub
Err_cmdPtasEdit_Click:
MsgBox Err.Description
Resume Exit_cmdPtasEdit_Click

End Sub

I’m trying to join PtasID to PtasID but this code does not work. I think the problem is that I am trying to link an item on a subform to the new form to edit the current record.
FrmPtasEdit = current from to edit the current ptas
FrmMaintForm is the form that the subform “PTAS view” is on.