Required fields at form level

[Guest]

I have a field that can not be set as required at the table level so I have
code at the form level to decide if the field is required, and then force
entry of the required data.

I have placed this code in Form_BeforeUpdate. It works as intended if I use
the record selector buttons to change records.

However, if I close the form using the close button the message box is
displayed but when I click ok the form closes and does not give me a chance
to enter the required data.

How do I stop the form from closing?

Another thing: Why does Access automatically insert ‘(Cancel As Integer)’
after ‘Private Sub Form_BeforeUpdate’?

Thanks,
Seth

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me!cboTestRprtVRangeLow) = False And IsNull(Me!cboTestRprtFreq) =
True Then
MsgBox "You must enter a frequency rating."
Me!cboTestRprtFreq.SetFocus

ElseIf MsgBox("Changes have been made to this record." _
& vbCrLf & vbCrLf & "Do you want to save these changes?" _
, vbYesNo, "Changes Made...") = vbYes Then
DoCmd.Save

Else
DoCmd.RunCommand acCmdUndo
End If

End Sub

 

[Allen Browne]

Access gives you the Cancel argument so you can cancel the event, i.e. stop
the record being saved. Just set Cancel to True to kill the save.

You can optionally undo the form as well, which is saver than:
DoCmd.RunCommand acCmdUndo
because:
a) it works even if the form does not have focus, and
b) it undoes all changes, not just the one field.
So:
Cancel = True
Me.Undo

You do not need:
DoCmd.Save
That does not save the record anyway (it saves form designs). More
importantly, Form_BeforeUpdate only fires when Access is trying to save the
record, so unless there is a problem or you cancel the event, the save will
happen.

If the user clicks the close button at the right of the form's title bar
when the record cannot be saved, Access (appropriately) gives the user the
choice to go back and fix up the bad entry or lose it. That makes sense.

However, if the form is closed using the Close action in a macro or the
Close method in code, Access *loses* the entry, *without* notifying the user
that the entry failed! From the user's point of view, they have no idea the
data was not saved, or why, and later they may come to believe that Access
is an unreliable program that cannot be trusted to save the data.

To avoid that problem, always explicitly save before using the Close action
on a bound form. In a macro, use the RunCommand with SaveRecord. In code:
If Me.Dirty Then Me.Dirty = False
DoCmd.Close acForm, Me.Name