Y/N/Cancel message box

[DavidCantor]

I am trying to code a y/n/cancel message box, the yn works but the cancel gives me an error

Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim strMsg As String
   Dim iResponse As Integer
 
   ' Specify the message to display.
   strMsg = "Do you wish to save the changes?" & Chr(10)
   strMsg = strMsg & "Click Yes to Save or No to Discard changes or Cancel to go back."
 
   ' Display the message box.
   iResponse = MsgBox(strMsg, vbQuestion + vbYesNoCancel, "Save Record?")
    
   ' Check the user's response.
   If iResponse = vbNo Then
    
      ' Undo the change.
      DoCmd.RunCommand acCmdUndo
 
      ' Cancel the update.
      Cancel = True
      
   ElseIf iResponse = vbCancel Then
   
   'Cancel Close, continue editing
   DoCmd.CancelEvent
    
   End If
   
End Sub

the error I am getting is

Run Time error '2001'
You cancelled the previous operation

and when I debug it takes me to this line:

 Me.Dirty = False

can anyone help me fix this?

thanks!

 

[bob fitz]

Try replacing:
DoCmd.CancelEvent
with:
Cancel = True

I can't see the line:
Me.Dirty = False
in the code supplied.

 

[DavidCantor]

Y N Cancel message box help

Does anyone know how I can get a generic YNCAncel message box?

I have seen a generic Y N message box on the microsoft website that works in any form, but I need a Y N Cancel

thanks

 

[DavidCantor]

this is what the error is referencing
Private Sub BtnGoToSymbol2_Click()
' Make sure that there is a Product_ID
If IsNull(Me.Product_ID) Then
MsgBox "Please create a new record in this form first!", vbExclamation

Exit Sub
End If
'Save current record if necessary
If Me.Dirty Then
Me.Dirty = False
End If

' Open the other form
DoCmd.OpenForm FormName:="FrmSymbols", DataMode:=acFormAdd
' Set the default value for Product_ID
Forms!FrmSymbols!Product_ID.Value = Me.Product_ID



'Move to new record
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec

End Sub

 

[pr2-eugin]

Re: Y N Cancel message box help

Try,

Call MsgBox("This is the Message", vbYesNoCancel)

 

[DavidCantor]

Re: Y N Cancel message box help

i get an error on this line :


Private Sub BtnGoToSymbol2_Click()
' Make sure that there is a Product_ID
If IsNull(Me.Product_ID) Then
MsgBox "Please create a new record in this form first!", vbExclamation

Exit Sub
End If
'Save current record if necessary
If Me.Dirty Then
Me.Dirty = False
End If

' Open the other form
DoCmd.OpenForm FormName:="FrmSymbols", DataMode:=acFormAdd
' Set the default value for Product_ID
Forms!FrmSymbols!Product_ID.Value = Me.Product_ID



'Move to new record
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec

End Sub

 

[boblarson]

Just add an error handler to your BtnGoToSymbol2_Click event and have it ignore that error.

Private Sub BtnGoToSymbol2_Click()
[B][COLOR=red]On Error GoTo Err_Handler[/COLOR][/B]

' Make sure that there is a Product_ID
If IsNull(Me.Product_ID) Then
MsgBox "Please create a new record in this form first!", vbExclamation

Exit Sub
End If
'Save current record if necessary
If Me.Dirty Then
[COLOR=black]Me.Dirty = False[/COLOR]
End If

' Open the other form
DoCmd.OpenForm FormName:="FrmSymbols", DataMode:=acFormAdd
' Set the default value for Product_ID
Forms!FrmSymbols!Product_ID.Value = Me.Product_ID



'Move to new record
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
 
[B][COLOR=red]Exit_BtnGoToSymbol2:[/COLOR][/B]
[B][COLOR=red]    Exit Sub[/COLOR][/B]
[B][COLOR=red][/COLOR][/B] 
[B][COLOR=red]Err_Handler:[/COLOR][/B]
[B][COLOR=red]   If Err.Number <> 2001 Then[/COLOR][/B]
[B][COLOR=red]      MsgBox Err.Descripton, vbExclamation, "Error #: " & Err.Number[/COLOR][/B]
[B][COLOR=red]      Resume Exit_BtnGoToSymbol2[/COLOR][/B]
[B][COLOR=red]      Resume     [/COLOR][/B]
[B][COLOR=red]   End If[/COLOR][/B]

End Sub

 

[DavidCantor]

I get error

Run Time Error '438' - Object doesnt support this property or method


Debug takes me to thie line:

MsgBox Err.Descripton, vbExclamation, "Error #: " & Err.Number

 

[boblarson]

Post the whole thing that you now have, including that part.

 

[DavidCantor]

Private Sub BtnStartAgain_Click()
' Make sure that there is a Product_ID
If IsNull(Me.Product_ID) Then
MsgBox "Please create a new record in this form first!", vbExclamation

Exit Sub
End If
'Save current record if necessary
If Me.Dirty Then
Me.Dirty = False
End If
' Close and save current form
' DoCmd.Close
' Open the other form
DoCmd.OpenForm FormName:="FrmProductIcon1", DataMode:=acFormNew
' Set the default value for Product_ID

'Move to new record
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
End Sub
Private Sub BtnStartAgain1_Click()
On Error GoTo Err_Handler
' Make sure that there is a Product_ID
If IsNull(Me.Product_ID) Then
MsgBox "Please create a new record in this form first!", vbExclamation

Exit Sub
End If
'Save current record if necessary
If Me.Dirty Then
Me.Dirty = False
End If
' Close and save current form
'DoCmd.Close
' Open the other form
DoCmd.OpenForm FormName:="FrmProductIcon1", DataMode:=acFormNew
' Set the default value for Product_ID



'Move to new record
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec

Exit_BtnGoToSymbol2:
Exit Sub

Err_Handler:
If Err.Number <> 2001 Then
MsgBox Err.Descripton, vbExclamation, "Error #: " & Err.Number
Resume Exit_BtnGoToSymbol2
Resume
End If

End Sub

 

[boblarson]

Do you have any objects (controls, fields, forms, reports, functions, macros) with either

Description

or

Err

as the name?

 

[DavidCantor]

none at all!!!

 

[boblarson]

Okay, so it shouldn't logically be doing that error, so try doing a DECOMPILE. First make a copy of the database before doing it just in case something goes further wrong.

See here for how to Decompile:
http://www.granite.ab.ca/access/decompile.htm

 

[DavidCantor]

I did a decompile now i get an error on this line:
error '438'
MsgBox Err.Descripton, vbExclamation, "Error #: " & Err.Number

 

[boblarson]

That makes no sense. What version of Access are you using? Is is possible to upload a copy of the database with fake data?

 

[DavidCantor]

I am running 2010, I am unable to post db because the data is sensitive

 

[boblarson]

because the data is sensitive

So, not even if you replaced the data with fake data, or with no data at all? (if you can do it with no data, you can take a copy of it and then use my Database Reset Tool to clear all data from it quickly and easily).

It is found here:
http://downloads.btabdevelopment.com/Tools/DatabaseReset.zip