Required Field

[Kyle Friesen via AccessMonster.com]

Hello,
I have a form that I want to make a few fields required. If I set the required property to Yes in the table the form is linked to, an error message comes up when one tries to save the form. However, this does not identify which fields are required for input. Is there a way to communicate which field the user needs to update. For example Msgbox "You must complete the Customer field".

Thanks,
Kyle

 

[Guest]

Insert before_update events on each of the fields you want to validate. Use
the ISNULL function to check if the field is empty and if it is, use the
MSGBOX function to explain the problem to the end user.

eg:
if isnull([txtYOURFIELDNAME]) then
msgbox "You cannot leave this field empty"
cancel = true
end if

Hope that helps

 

[Kyle Friesen via AccessMonster.com]

Thank you and excuse my ignorance. I am a novice.

where/how do I insert before_update events on each of the required fields? I inserted the code in the save form_click module and the message box comes up upon click but not sure it is validating the null values, I think I just inserted the message box.

Thanks, I really appreciate it.

 

[Guest]

OK,
double click the text box you want to add the validation to. This will bring
up the property box for this text box. Click on the Event tab, then click in
the Before Update field. Three dots will appear at the right. Click on them.
Now, paste the following code in where the cursor is flashing:

If IsNull(Me![YOURFIELD]) Then
MsgBox ("YOURFIELD must not be left empty.")
Cancel = True
Exit Sub
End If


You'll have to change YOURFIELD to the name of the text box once you've
pasted it in to your module.
Hope this helps.
Dave

 

[Dirk Goldgar]

Hello,
I have a form that I want to make a few fields required. If I set the
required property to Yes in the table the form is linked to, an error
message comes up when one tries to save the form. However, this does
not identify which fields are required for input. Is there a way to
communicate which field the user needs to update. For example Msgbox
"You must complete the Customer field".

I'd recommend a somewhat different approach from that suggested by Dave.
Rather than writing a BeforeUpdate event procedure for each control --
which won't work anyway unless the user actually modifies the control --
it would be a lot simpler to write a single BeforeUpdate event for the
form itself. In that procedure, check each required field to see if it
has been filled in, and accumulate a list of those that haven't been.
Then if that list is not empty, you can cancel the update and display an
error message that includes the list of fields to be fixed.

Here's a function you can paste into a standard module and call from the
form's BeforeUpdate event. It checks every text box, combo box, list
box, and check box on the form, but only if the control's Tag property
is set to "Required". That lets you designate, by tagging, those fields
that are required. The function is passed a reference to the form to be
checked and returns True if required fields are missing, as well as
displaying a message and setting the focus to the first control that is
required but empty.

'----- start of function code -----
Function fncRequiredFieldsMissing(frm As Form) As Boolean

Dim ctl As Access.Control
Dim strErrCtlName As String
Dim strErrorMessage As String
Dim lngErrCtlTabIndex As Long
Dim blnNoValue As Boolean

lngErrCtlTabIndex = 99999999 'more than max #controls

For Each ctl In frm.Controls
With ctl
Select Case .ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
If .Tag = "Required" Then
blnNoValue = False
If IsNull(.Value) Then
blnNoValue = True
Else
If .ControlType = acTextBox Then
If Len(.Value) = 0 Then
blnNoValue = True
End If
End If
End If
If blnNoValue Then
strErrorMessage = strErrorMessage & vbCr & _
" " & .Name
If .TabIndex < lngErrCtlTabIndex Then
strErrCtlName = .Name
lngErrCtlTabIndex = .TabIndex
End If
End If
End If
Case Else
' Ignore this control
End Select
End With
Next ctl

If Len(strErrorMessage) > 0 Then
MsgBox "The following fields are required:" & vbCr & _
strErrorMessage, _
vbInformation, "Required Fields Are Missing"
frm.Controls(strErrCtlName).SetFocus
fncRequiredFieldsMissing = True
Else
fncRequiredFieldsMissing = False
End If

End Function
'----- end of function code -----

To use the function, create a BeforeUpdate event for your form like
this:

'----- start of event procedure code -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

Cancel = fncRequiredFieldsMissing(Me)

End Sub
'----- end of event procedure code -----

 

[Guest]

A much better solution than mine Dirk.
I assumed that there would at least be a default value if the field was set
to not allow nulls at the table level.

 

[Kyle Friesen via AccessMonster.com]

Thank You Dirk and Dave!!