Results 1 to 6 of 6
  1. #1
    dataqueen is offline Novice
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    3

    Custom error message for error 3314 (can't save record because required field is blank) issue

    Hi all. I'm using a variation of a bit of VBA code that I found through some Google sleuthing, but I can't seem to figure out why it only works some of the time.



    Basically, I want a custom error message to appear instead of the somewhat cryptic one my end users would get if they left a required field blank on a form, and tried to close it (where Access alerts them to the fact that the record cannot be saved). The code works perfectly when I try using it with an EXISTING record: that is, if they press NO (don't close without saving), they return to the form, and if they press YES (continue close without saving), the form closes without saving.

    BUT, if this is a new record, it doesn't work. If they try to close a form for a new record with a required field blank, the custom error message comes up, but if they press NO (which should return to the form), it closes without saving. If they press YES (should close without saving), it says "Run-time error 2501: the close action was canceled" and then it still closes without saving.

    Here is my code. Could someone please point me in the right direction on what needs changing? THANK YOU
    Code:
    Private Sub Form_Error(DataErr As Integer, Response As Integer)
    
    'If an error occurs because of missing data in a required field
    'display our own custom error message
    
    
        Const conErrRequiredData = 3314
    
    
        If DataErr = conErrRequiredData Then
            If MsgBox("Access can't save the data you've entered because at least 1 required field was not filled in. Required fields appear in bold. Are you sure you want to close without saving?", vbYesNo + vbDefaultButton2, "Cannot Save") = vbYes Then
            Response = acDataErrContinue
            DoCmd.Close acForm, "Resources", acSaveNo
            Else
            Response = acDataErrContinue
        End If
                Else
            'Display a standard error message
            Response = acDataDisplay
        End If
    End Sub

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Are you using a bound or unbound form. A bound form is a form that is tied directly to a data source (query/table), an unbound form is one that only touches your data when specifically commanded to.

    If it's a bound form, how are you indicating the required fields, simply by making the field required in your table design?

  3. #3
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by rpeare View Post
    ...simply by making the field required in your table design?
    That's what it looks like to me. Maybe some additional code is needed to trap on Me.NewRecord = True

  4. #4
    dataqueen is offline Novice
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    3
    Quote Originally Posted by rpeare View Post
    Are you using a bound or unbound form. A bound form is a form that is tied directly to a data source (query/table), an unbound form is one that only touches your data when specifically commanded to.

    If it's a bound form, how are you indicating the required fields, simply by making the field required in your table design?
    Hi, thanks for the reply! It is a bound form, and yes the fields are required in the table.

  5. #5
    dataqueen is offline Novice
    Windows 8 Access 2013
    Join Date
    Apr 2015
    Posts
    3
    Quote Originally Posted by ItsMe View Post
    That's what it looks like to me. Maybe some additional code is needed to trap on Me.NewRecord = True
    Hi, I did try to add in some code with that as an IF statement, but it didn't seem to make a difference, I still had problems with new records.

  6. #6
    Perceptus's Avatar
    Perceptus is offline Expert
    Windows 7 64bit Access 2007
    Join Date
    Nov 2012
    Location
    Knoxville, Tennessee
    Posts
    659
    Have you tried to validate the fields in the BeforeUpdate Event?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 15
    Last Post: 11-01-2013, 03:24 PM
  2. custom error message
    By msasan1367 in forum Access
    Replies: 1
    Last Post: 04-27-2013, 09:14 AM
  3. Replies: 1
    Last Post: 12-07-2012, 02:14 AM
  4. Replies: 14
    Last Post: 06-06-2012, 12:50 PM
  5. Custom validation error message
    By snorkyller in forum Access
    Replies: 2
    Last Post: 03-21-2011, 03:40 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums