On Error MsgBox Not Wanted

[Andy]

Hi;

When a user types in a required field, and then deletes the characters, and
tries to move to a different record the user gets the internal warning box:
"Index or Primary key cannot be blank".

Created a simple procedure that works:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Response = acDataErrContinue
DoCmd.DoMenuItem acFormBar, acEdit, acUndo
End Sub

Realized that this is helpful for many Forms.

Want to create one Function instead of multiple Private Subs.

Public Function CantBeBlank()
Dim DataErr As Integer
Dim Response As Integer

Response = acDataErrContinue
DoCmd.DoMenuItem acFormBar, acEdit, acUndo

End Function

It called correctly =CantBeBlank()
But the internal warning box appears.

Added: DoCmd.SetWarnings False

Not working.

Any Suggestions?

Andy

 

[Allen Browne]

Teach the user to press Esc key to undo the entry if they want out.

If you use Form_Error, it should be limited ot DataErr 3314. Form_Error
could fire for other reasons also, e.g. related record required (3201),
invalid type (2113), duplicate index (3022), or even a network problem if
the data is on a server (3024). You cannot do that if you set the form's
OnError property to =CantBeBlank, because you can only pass the DataErr
value from inside the Form_Error procedure.

 

[Andy]

Allen;

Thank You for Your reply.

Will follow Your instructions an not use =CantBeBlank.

The thing I'm trying to overcome is the "Cryptic" message: "Index or Primary
key cannot contain a null value." and create a custom msgbox "...Hit the
escape key."

What I tried is:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
If DataErr = 3314 Then
MsgBox "YES"
Else
MsgBox "NO"
End If
End Sub

To verify that 3314 is the error that generates the "Cryptic Message"

What returns is No.

Searched MSDN and it pointed to 8261. Tried that still No.

Help and search all Microst wasn't much help either.

Is it my code that is incorrect? Where do You find the actual error number
that generates "Index or Primary key cannot contain a null value."?

Andy

 

[Allen Browne]

Select the line
If DataErr = 3314 Then
and press F9 to create a break point.

Cause the error. The code will stop there. When it does, open the Immediate
window (Ctrl+G), and enter:
? DataErr
That will tell you the number. Then press F8 or F5 to continue the code. You
can then remove the breakpoint (press F9 in the same line, or clear all
breakpoints through the menu)

 

[Andy]

Allen;

Thank You.

It's error 3058!

Andy

Select the line
If DataErr = 3314 Then
and press F9 to create a break point.

Cause the error. The code will stop there. When it does, open the Immediate
window (Ctrl+G), and enter:
? DataErr
That will tell you the number. Then press F8 or F5 to continue the code. You
can then remove the breakpoint (press F9 in the same line, or clear all
breakpoints through the menu)