Required fields in forms and their sub forms. (Suppress warnings?)

[JGalletta]

I have a form containing two sub forms. These forms contain boxes that are bound to tables which contain required fields (as defined in the table design). On entering/exiting form/subform focuses, a warning is displayed if the required fields in each form/subform are empty/null.

A few ways I'm envisioning to suppress these warnings:

1.) On form open event, setwarnings false. (Will this suppress that type of warning?) Subsequently, setwarnings true on closing. - What I don't like is that other warnings may not appear as well (is this true, too?).

2.) Change required to no for all fields. On form close event, check for nulls in form table and sub tables with matching primary keys relevant to the form, and display msgbox "Warning..." if nulls are found and exit sub.

3.) Your idea here.

Questions:
Case 1
Will this work? Will setwarnings false cover warnings on both form and sub forms. Is there a way to suppress only the "Required Fields" warning for these forms?

Case 2
If this is the option, what is the procedure for creating a recordset of all fields on a form (and sub form) and looping to check for nulls/blanks?

Case 3
What do you guys think?

 

[jdraw]

What are the implications if you go to the Table(s) involved and switch the Required from Yes to No?

 

[JGalletta]

I can do that, as long as the form does not allow the user to close or exit without completing the records (what happens in the event of a crash?). Many calculations are based on these records, and this db is based on a legal requirement for chemical application record keeping in which all data is necessary.

 

[Simon_MT]

You could try instead creating a Flag say Status and once all the fields are complete it is set to true. any incomplete transactions aare ignored until all the fields are complete. This could be long winded.

Simon

 

[JGalletta]

I understand the general concept (flag, boolean, true/false conditional statement etc.).. just need to know the means of getting the true/false value for whether all fields contain a value. Would it be something like this?:

Sub CloseButtonOnClick()
Dim Ctrl As Control
With Forms!frmApplicationRecord
[INDENT]For each Ctrl in .Controls
[INDENT]If Forms!frmApplicationRecord(Ctrl) is null Then
[INDENT]Goto MessageBoxStart[/INDENT]
End If[/INDENT]
Next[/INDENT]
End With
Goto DoButtonAction
MessageBoxStart:
MsgBox "This form contains empty cells, please complete the form prior to continuing.",,"Warning!"
Exit Sub
DoButtonAction:
Forms!frmApplicationRecord.SetFocus
DoCmd.Close
End Sub

 

[jdraw]

I haven't tried your code, but Is NULL is typically an SQL command.
For vba you may need the IsNull() function
The effect

this line in your code

If Forms!frmApplicationRecord(Ctrl) is null Then

becomes

If IsNull(Forms!frmApplicationRecord(Ctrl)) Then

 

[JGalletta]

Thanks! Is Forms!frmApplicationRecord(Ctrl) correct syntax? I'm not at the computer at the moment..

 

[JGalletta]

Here is the final tested and working code (Access 2007-2010 tested)

Private Sub apprecdone_Click()
Dim Ctrl As String
With Forms!frmApplicationRecord
    If IsNull(Forms!frmApplicationRecord.Application_Number) = True Then
        GoTo DoButtonAction
    End If
    For Each Control In .Controls
        Ctrl = Control.Name
        If IsNull(Forms!frmApplicationRecord(Ctrl)) = True Then
            GoTo MessageBoxStart
        End If
    Next
End With
With Forms!frmApplicationRecord!frmFieldApps
    For Each Control In .Controls
        Ctrl = Control.Name
        If IsNull(Forms!frmApplicationRecord!frmFieldApps(Ctrl)) = True Then
            GoTo MessageBoxStart
        End If
    Next
End With
With Forms!frmApplicationRecord!frmTankMixesSubform
    For Each Control In .Controls
        Ctrl = Control.Name
        If IsNull(Forms!frmApplicationRecord!frmTankMixesSubform(Ctrl)) = True Then
            GoTo MessageBoxStart
        End If
    Next
End With
GoTo DoButtonAction
MessageBoxStart:
    MsgBox "This form contains empty cells, please complete the form prior to continuing.", , "Warning!"
Exit Sub
DoButtonAction:
    Forms!frmApplicationRecord.SetFocus
DoCmd.close

End Sub

As you might notice, the first If statement seems to be against the logic I was pursuing, but if the Auto Numbered field is null then the record is not dirty (the record doesn't even exist at this point), and the form can be closed without storing partial records. I probably could have also combined parts of the with loops into one, but I'm satisfied with the way this works, so I'm not going to "fix it if it ain't broke."