Override natural error message

[Guest]

Hi,

I'm attempting to override the following error message upon pressing a 'Save Record' command button on a form (to insert a record). The form contains an underlying required field and this error occurs when that field is missing a data value:

"The field <venue> cannot contain a null value because the required property for thiis field is set to true. Enter a value in this field."

I'm using the following code in the On Error event of the Form:

If DataErr = 3314 Then
MsgBox "You must supply a venue."
Response = acDataErrContinue
End If

Upon researching past messages, I believe I am using the correct error code. It is interesting that this code does override the natural error message when I click the forward navigation button and produces my custom error message as desired. However, it does not do this when the 'Save Record' command button (residing on the form) is pressed.

Can anyone suggest how to immediately invoke my custom error message when the 'Save Record' button is pressed after the circumstances described above?

Thanks,
Jody

 

[Ken Snell]

You likely will need to do your own validation for Null in the form's
BeforeUpdate event, and not wait for an error to occur from the field's
validation rule.


Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.Venue) = True Then
MsgBox "You must supply a venue."
Cancel = True
End If
End Sub
--

Ken Snell
<MS ACCESS MVP>

Hi,

I'm attempting to override the following error message upon pressing a

'Save Record' command button on a form (to insert a record). The form
contains an underlying required field and this error occurs when that field
is missing a data value:

"The field <venue> cannot contain a null value because the required

property for thiis field is set to true. Enter a value in this field."

I'm using the following code in the On Error event of the Form:

If DataErr = 3314 Then
MsgBox "You must supply a venue."
Response = acDataErrContinue
End If

Upon researching past messages, I believe I am using the correct error

code. It is interesting that this code does override the natural error
message when I click the forward navigation button and produces my custom
error message as desired. However, it does not do this when the 'Save
Record' command button (residing on the form) is pressed.

Can anyone suggest how to immediately invoke my custom error message when

the 'Save Record' button is pressed after the circumstances described above?

 

[Guest]

Hi Ken,

Excellent suggestion - works fine. The only challenge remaining is that I get the following message box after I press OK to my "you must supply a venue" message box:

"The DoMenuItem action was cancelled"

.....which forces the user to acknowledge a second message box. Any way to have the code automatically acknowledge this second message?

Thanks,
Jody

 

[Ken Snell]

Are you running code somewhere that uses DoMenuItem?

--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

Excellent suggestion - works fine. The only challenge remaining is that I

get the following message box after I press OK to my "you must supply a
venue" message box:

"The DoMenuItem action was cancelled"

....which forces the user to acknowledge a second message box. Any way to

have the code automatically acknowledge this second message?

 

[Guest]

Yes. In order to lead to the Form_BeforeUpdate code in question, I press a 'Save Record' command button. This button has DoMenuItem code as follows:

Private Sub Command15_Click()
On Error GoTo Err_Command15_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command15_Click:
Exit Sub

Err_Command15_Click:
MsgBox Err.Description
Resume Exit_Command15_Click

End Sub

Any suggestions?

 

[Ken Snell]

I don't know what the error number is for that error, but you can get it if
you change your code to the following:

Private Sub Command15_Click()
On Error GoTo Err_Command15_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command15_Click:
Exit Sub

Err_Command15_Click:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Command15_Click

End Sub


The message box will show the error number. Then, you can trap for that
error in your sub's code and ignore it (replace 0000 with the actual error
number that you get from the above code modification that you'll use just
the one time).



Private Sub Command15_Click()
On Error GoTo Err_Command15_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command15_Click:
Exit Sub

Err_Command15_Click:
If Err.Number <> 0000 Then MsgBox Err.Number & _
" - " & Err.Description
Resume Exit_Command15_Click

End Sub
--

Ken Snell
<MS ACCESS MVP>

Yes. In order to lead to the Form_BeforeUpdate code in question, I press

a 'Save Record' command button. This button has DoMenuItem code as follows:

 

[Guest]

Worked perfectly!! Thanks Ken.

I don't know what the error number is for that error, but you can get it if
you change your code to the following:

Private Sub Command15_Click()
On Error GoTo Err_Command15_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command15_Click:
Exit Sub

Err_Command15_Click:
MsgBox Err.Number & " - " & Err.Description
Resume Exit_Command15_Click

End Sub


The message box will show the error number. Then, you can trap for that
error in your sub's code and ignore it (replace 0000 with the actual error
number that you get from the above code modification that you'll use just
the one time).



Private Sub Command15_Click()
On Error GoTo Err_Command15_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Command15_Click:
Exit Sub

Err_Command15_Click:
If Err.Number <> 0000 Then MsgBox Err.Number & _
" - " & Err.Description
Resume Exit_Command15_Click

End Sub
--

Ken Snell
<MS ACCESS MVP>


a 'Save Record' command button. This button has DoMenuItem code as follows: