Anyone that has ever created a form with a SubForm has come across the error 3314:

which occurs when one tries to enter data in the subform before the parent record has been dirtied (data entered and assigned a PK which is used to link the subform to the parent form).

Today, I thought I’d share the strategy I like to employ to avoid the issue altogether!

There are a several possible ways to handle this, the following quickly came to mind:

1. Do nothing and leave the default error
2. Trap the Error in the SubForm
3. Disable the SubForm until the parent is initialized
4. Hide the SubForm until the parent is initialized
5. Prevent your users from entering the SubForm
6. Set the Allow Additions property to block new entries

No. 1 just doesn’t make any sense. The message is typically referring to the form’s PK that users won’t necessarily even be aware of. It is just confusing and not user-friendly!

No. 3 works, but because Access doesn’t grey out disabled subforms the way it does with all other controls, visually the subform look as if it is still accessible and leads to user frustrations. So once again, not ideal, not a great user experience.

So that leaves us with 2 viable potential solutions worth digging further into!

Trap the Error in the SubForm

So one potential solution can be to trap the error in the subform itself.

Using the Form’s On Error Event

Now, I thought we could simply utilize the form’s On Error event to trap the error and display a nice friendly/comprehensive message to the end-user and rollback the entry by doing something along the lines of:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

    Response = acDataErrContinue    ' Suppress default message
    If DataErr = 3314 Then '3314 -> You must enter a value in the '|' field.
        Call MsgBox("You must first fill-in the Contact Name before trying to provide Telephone Number information." _
                    , vbCritical Or vbOKOnly, "Missing Contact Information")
                    
        ' Won't UNDO!!!  WAPITA!!!
        ' ************************
        'If Me.Dirty Then -> doesn't work, detects dirty but won't delete
        '    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
        '    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
        'End If
        'ActiveControl.Undo '-> doesn't work
        'Me.Undo '-> doesn't work
        'DoCmd.RunCommand acCmdUndo '-> doesn't work
    End If
End Sub

The problem, as you can see by my commented lines of code, is that undo simply does not work via this event! So, even though we can intercept Access’ message and provide a better one, it still leaves the form in an improper state and you’d have to teach your users how to rollback their entry (Ctrl+Z x3 times)… not an acceptable path!

Using the Form’s Before Insert Event

I played around with various events and eventually settled on using the Before Insert event to avoid the invalid entry altogether! So, we could do something like:

Private Sub Form_BeforeInsert(Cancel As Integer)
    'If Me.Parent.NewRecord Then 'This line or the following one are both acceptable approaches
    If IsNull(Me.ContactId) Then
        Call MsgBox("You must first fill-in the Contact Name before trying to provide Telephone Number information." _
                    , vbCritical Or vbOKOnly, "Missing Contact Information")
        Cancel = True
        'Me.Parent.FirstName.SetFocus  'Perhaps set the focus back on the parent form for data entry
    End If
End Sub

Because the Before Insert offers use the Cancel argument, we can catch the issue prior to it even being submitted and thus ensure and then we provide a clear message explaining the issue to our user(s). So this is a proper option for handling the subform error 3314 issue.

Note: In the example provided above, Me.ContactId is the Foreign Key and the subform’s Link Child field.
 

Hide the SubForm until the parent is initialized

The better solution, in my experience, is to simply hide the subform altogether until which time the parent form is dirtied (data input and a PK assigned). This entirely avoid the issue of users trying to input data in the subform …

So to do this, we need to utilize a couple of the main (parent) form events: Before Insert, On Current & On Undo. As such, we would implement something like:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.YourSubformName.Visible = True
End Sub

Private Sub Form_Current()
    Me.YourSubformName.Visible = Not IsNull(Me.PKFieldName)
End Sub

Private Sub Form_Undo(Cancel As Integer)
    Me.YourSubformName.Visible = Not IsNull(Me.PKFieldName.OldValue)
End Sub

so a concreate example might look like:

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.ContactTelephone.Visible = True
End Sub

Private Sub Form_Current()
    Me.ContactTelephone.Visible = Not IsNull(Me.ContactId)
End Sub

Private Sub Form_Undo(Cancel As Integer)
    Me.ContactTelephone.Visible = Not IsNull(Me.ContactId.OldValue)
End Sub

where ContactTelephone is the subform’s name and ContactId is the Form’s Primary Key and the Subform’s Link Master field.

The works beautifully and I’d recommend it to you.
 

Hide the SubForm until the parent is initialized – Form SubClassing

Now, it can get tedious to have to add 3 events every time we insert a subform into a form, so I thought I’d dabble with using form subclassing a little.

I thought this would be a little fun to play around with and useful to demonstrate the technique so others can learn. I’ve done form control subclassing on a several occasions, but not really form subclassing. As you’ll see below, it really isn’t very hard, once you know how!

So we can create a class module like:

Option Compare Database
Option Explicit

Private WithEvents oForm      As Access.Form
Private colSubFormCtrls       As VBA.Collection
Const EventProcedure = "[Event Procedure]"


Public Property Get p_Form() As Access.Form
    Set p_Form = oForm
End Property

Public Property Set p_Form(ByRef oMyForm As Access.Form)
    Set oForm = oMyForm
    Call Class_init
End Property

Private Sub Class_init()
    Dim ctrl                  As Access.Control

    Set colSubFormCtrls = New Collection
    For Each ctrl In oForm.Controls
        Select Case ctrl.ControlType
            Case acSubform
                colSubFormCtrls.Add ctrl
        End Select
    Next

    oForm.BeforeInsert = EventProcedure
    oForm.OnCurrent = EventProcedure
    oForm.OnUndo = EventProcedure
End Sub

Private Sub Class_Terminate()
    Set colSubFormCtrls = Nothing
    Set oForm = Nothing
End Sub

'*************************************************
Private Sub oForm_BeforeInsert(Cancel As Integer)
    Dim oSubForm              As Access.SubForm

    For Each oSubForm In colSubFormCtrls
        oForm.Controls(oSubForm.Name).Visible = True
    Next
End Sub

Private Sub oForm_Current()
    Dim oSubForm              As Access.SubForm

    For Each oSubForm In colSubFormCtrls
        oForm.Controls(oSubForm.Name).Visible = _
        Not IsNull(oForm.Controls(oSubForm.LinkMasterFields))
    Next
End Sub

Private Sub oForm_Undo(Cancel As Integer)
    Dim oSubForm              As Access.SubForm

    For Each oSubForm In colSubFormCtrls
        oForm.Controls(oSubForm.Name).Visible = _
        Not IsNull(oForm.Controls(oSubForm.LinkMasterFields).OldValue)
    Next
End Sub

and once created we can now simply add the following to any main form to automatically insert the necessary code to add the hide/unhide ability to subforms is contains.

Option Compare Database
Option Explicit

Private listener              As New Cls_Form

Private Sub Form_Close()
    Set listener = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
    'listener.Initialize Me.Form
    Set listener.p_Form = Me
End Sub

Now I know at first you might think there’s a lot of code, but the beauty here is once you have the basic framework in place is that if I add 1,4 or even 40 subforms, the code remains the same. The Class module automatically takes care of everything!

Furthermore, you can add any other events that you wish to automatically add. Add even more automation to those already listed. With this approach, you can make one adjustment in a single location, the class module, and have it automatically update all the instance! (instead of having to go an edit every main form 1 by 1)

Prevent Your Users From Entering The SubForm

Another potential solution is to employ the control’s On Enter event to prevent users from entering the control until the parent form is populated. We can do this by doing something like:

Private Sub ContactTelephone_OnEnter_Enter()
    If IsNull(Me.ContactId) Then
        Call MsgBox("You must first fill-in the Contact Name before trying to provide Telephone Number information." _
                    , vbCritical Or vbOKOnly, "Missing Contact Information")
        Me.FirstName.SetFocus
    End If
End Sub

You gain access to the On Enter event by selecting the subform control within the parent form. This is an event of the parent form, not the subform!

Special thanks to George Hepworth for sharing this approach with me in a comment to my YouTube video. This is another effective approach to solving this issue and ensuring a good user experience.
 

Set the Allow Additions property to block new entries

Another approach, brought to my attention by Kent Gorrell, is to use the Allow Additions property to block new entries until the parent form has been populated.

You can acheive this by doing something like:

Private Sub FirstName_AfterUpdate()
    'Use the After Update of a required field to unlock the subform after data is
    'input otherwise it remains inaccessible to the user!
    If Not IsNull(Me.FirstName) And Me.Dirty Then Me.Dirty = False
End Sub

Private Sub Form_AfterUpdate()
    Me.ContactTelephone.Form.AllowAdditions = True
End Sub

Private Sub Form_Current()
    Me.ContactTelephone.Form.AllowAdditions = Not Me.NewRecord
    'Me.ContactTelephone_KentGorrell.Form.AllowAdditions = Not IsNull(Me.ContactId)
End Sub

Final Thoughts

So there you have it, a couple ways you can improve the overall user experience of forms containing subforms and avoid Access’ confusing default error 3314 message. (and a little basic introduction to form subclassing)
 

Problem With Disabling SubForm Controls

As I mentioned earlier in this article, when you disable most controls they get greyed out making it visually apparent that they are disabled. For whatever reason, this does not occur with SubForm controls?! So even if you disable a subform, from a visual standpoint they appear to remain valid/accessible which causes issues and user frustration.

Hence, I recently create a Feedback item regarding this in the hope the Dev Team can address this and help add a visual component to disabling subform controls. If you can, please upvote it to try and get their attention on the matter. This should be an easy thing for them to address. You can access the Feedback item at:

Grey out SubForm controls when disabled

Please standardize the behavior of disabled controls by greying out subforms when they are disabled. Currently they remain unchanged misleading users to continue to think they are available for use.

Page History