Below is a generic function I use on all our forms, held in a global module
Basicly, every control on the form must either be filled in, or locked (not needed) or invisible (normally control fields)
The function returns a string of all the controls not filled in, and true/false.
If true, save the record, if false, display the list of controls not filled in and do not save the record.
-
Public Function Form_Check_NULL(ByVal frm As Form, ByRef strError As String) As Boolean
-
On Error GoTo Form_Check_NULL_Err
-
Dim ctl As Control
-
Dim Form_Valid As Boolean
-
Form_Valid = True
-
strError = ""
-
For Each ctl In frm.Controls
-
Select Case ctl.ControlType
-
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
-
If IsNull(ctl.Value) And ctl.Enabled = True And ctl.Visible = True And ctl.Locked = False Then
-
strError = strError & ctl.Name & "/"
-
Form_Valid = False
-
End If
-
End Select
-
Next ctl
-
Form_Check_NULL = Form_Valid
-
Form_Check_NULL_Exit:
-
Exit Function
-
Form_Check_NULL_Err:
-
MsgBox Err.Description & " in Form_Check_NULL"
-
Resume Form_Check_NULL_Exit
-
End Function
-
it is used on the form via a command button 'cmdRecordSave' as follows
-
If Check_Null = False Then
-
Form_Valid = False
-
showerror strError
-
Else
-
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
-
MsgBox strTranslate("TR000000130", "Record Saved")
-
End If
-
-