Validate Required

[BMaerhofer]

Private Sub SAVEForm_Click()
On Error GoTo Err_CloseForm_Click

If IsNull(Vehicle) Then
MsgBox "Must Select Vehicle for Entry before saving", vbOKOnly,
"Invalid Input"
End If

If IsNull(Date) Then
MsgBox "Must Enter Date for Entry before saving", vbOKOnly, "Invalid
Input"
End If

If IsNull(Mileage) Then
MsgBox "Must Enter Mileage for Entry before saving", vbOKOnly,
"Invalid Input"
End If

DoCmd.Close


Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub


The problem that I am having with this is that it will pop up the message if
the fields are not filled in that are required, but once you click "OK" for
the dialog boxes that pop up it will close the form instead of letting you
fill in the blanks then retry with saving. I need that fixed and can't
figure it out. (If false, return to form to fix data)

Also I am having problem with checkbox required fields.

I have 3 fields that have checkboxes that at least one is required, all
three can be checked as well.

Oil / Air / Other - At least one of these is required. I can't figure out
how to validate that one of these is checked before saving. I would like to
validate it like the coding above, but can't figure out how to write that.

Thanks,
Brian

 

[Jeanette Cunningham]

Brian,
The code you have written just pops up a message, it doesn't do anything
else.
Marshall Barton, MVP suggested the following code for a similar problem.
The code below goes in the Before Update event for the form.

Note that controls and the form have Before Update events. Make sure you use
the before update event for the form for this code.

The code for the Save button on your form tells the form to save the data
with Me.Dirty = False
When the form goes to save the record, it always goes through the before
update event before it saves the data.
Your code here will pick up any controls that are empty.

Set the Tag property of each bound control that you want to check to
something like CHECK. Then you could use a loop like this air code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
Dim strEmpty As String
Dim strMsg As String
For Each ctl In Me.Controls
If ctl.Tag = "CHECK" Then
If IsNull(ctl) Then
strEmpty = strEmpty & vbCrLf & Ctl.Name
End If
End If
Next ctl
If strEmpty <> "" Then
Cancel = True
strMsg = "These fields are missing a value" _
& Mid(strEmpty, 3) & vbCrLf & vbCrLf _
& "enter a value or press Esc to cancel."
End If

End Sub

For the save button the code is like this:
Private Sub SAVEForm_Click()
If Me.Dirty = True Then
Me.Dirty = False
End If
End Sub

The line *Me.Dirty = False* is the code that triggers access to go through
the routine that saves the data.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[Cheese_whiz]

Hi Brian,

Replace this line:
DoCmd.Close

with this:
If Me.Dirty = True Then
Me.Dirty = False
End If

That DoCmd.Close is what closes the form. We are replacing it with
something that will save the record. But you need one more 'fix' for it to
work right.

Just above the 'End If' lines in all your code above that new section, add
the new line:
Exit Sub

So all together:
_________________________
Private Sub SAVEForm_Click()
On Error GoTo Err_CloseForm_Click

If IsNull(Vehicle) Then
MsgBox "Must Select Vehicle for Entry before saving", vbOKOnly,
"Invalid Input"
Exit Sub
End If

If IsNull(Date) Then
MsgBox "Must Enter Date for Entry before saving", vbOKOnly, "Invalid
Input"
Exit Sub
End If

If IsNull(Mileage) Then
MsgBox "Must Enter Mileage for Entry before saving", vbOKOnly,
"Invalid Input"
Exit Sub
End If

If Me.Dirty = True Then
Me.Dirty = False
End If

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub
___________________________

That will check each control you targeted. If a control has a null value,
the user gets the popup window and the sub ends (read: the record is not
saved). If there are no null values, the routine will run through to the
part that we added that will save the record.

HTH,
CW