Access force user to fill in first three columns of subform

Murray Brown
Murray Brown used Ask the Experts™
I have an Access subform where I don't want to allow the user to enter the next record unless they have filled in the first three columns
How do I control this?

1
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
There are a few ways to do this, and sometimes the methods can work together.
To ensure that we ALWAYS have a valid record, start by looking at the table level, and setting each of the fields "Required=True"
This ensures that whether they use the form, a query, or its code manipulating the table, that it will fail if we are doing an "illegal" operation.

That said, for the user this can give an ugly or incomprehensible error message when they try to save or move to the next record.

So to make a good UI we should add some VBA code to the FORMS beforeupdate event, it could look like this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim msg As String
    Dim response As Integer
    
    If Me.cbo_place_id & ""="" Then
        msg = msg & " - Place has not been selected" & vbNewLine & vbNewLine
    End If
    
    If Me.txt_site & ""="" Then
        msg = msg & " - Site name has not been filled in " & vbNewLine & vbNewLine
    End If
    
    If msg <> "" Then
        Cancel = True
        response = MsgBox("Cannot save record:"  & vbNewLine & _
                msg & _
                "Press OK to return and fill in information." & vbNewLine & "Press Cancel to undo", vbOKCancel + vbInformation)
                    
        If response = vbOK Then
        
            Exit Sub
        
        ElseIf response = vbCancel Then
        
            Me.Undo
        
        End If
                
    End If
    

End Sub

Open in new window

If you have set these 3 columns as non-null (or non-empty) columns, as one of the constraint of the table, user is also not able to leave them empty.
John TsioumprisSoftware & Systems Engineer
A way to implement the "required" is to have the controls that require some extra action to carry that as a Tag (Properties -->Other-->Tag)
So i would mark the Controls that need that "require" with a Tag e.g. "required" and either in the Before_Update as Anders suggested or in the click event of a button
On Error Resume Next
Dim ctl as Control
For each ctl in Me.Controls
If ctl.Tag = "required" Then
if Len(ctl) =0 then
MsgBox "A required values is missing :" & ctl.Name
End if
End if
Next

Open in new window

Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
Thanks all for the help

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial