Form's BeforeUpdate and Unlooad Events

  • Thread starter Thread starter Melissa
  • Start date Start date
M

Melissa

Access97

My single form has the standard navigation buttons on the bottom left and a
Close button in the bottom right corner. The form also has a 'New' button. When
it is clicked, the form goes to a new record and a field named MyCode is filled
with ABCxx where xx is sequential. Finally the form has a field named MyField.
Any time after the 'New' button is clicked, I wish to keep the form from moving
to another record if MyField has not been entered and to keep the form from
closing if MyField has not been entered. I have tried using both the form's
BeforeUpdate event and Unload event, opening a MessageBox in each and setting
Cancel = True in each if MyField is null. Using both events works to keep the
form from moving to a new record. However, when the Close button is clicked, I
get both messages, one from each event and the value in MyCode is deleted.
However, the form does not close which is what I want.

How should I set this all up so the form does not move to a new record if
MyField is null and so when I click the close button, I get one message, MyCode
is not deleted and the form does not close if my field is null?

Thanks!

Melissa
 
Melissa, can I suggest that you do not use the Current event of the form to
populate MyCode immediately the user moves to a new record. Instead use its
BeforeInsert event, so the new record is not created until the user starts
to enter something. This will avoid the unnecessary creation of the new
records in the first place. (Better still, use the BeforeUpdate event of the
form so the value is assigned at the last possible moment, to reduce the
chance that 2 users are assigned the same value.)

The form's BeforeUpdate event is the only event you can use to ensure a
field has a value:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.MyField) Then
Cancel = True
MsgBox "MyField is required."
Else
Me.MyCode = "ABCxx"
End If
End Sub

Unfortunately, the way the close action works, the form will still close
(without the new record being saved) with the code above.

If you want to insist that no record can be saved without a value in
MyField, you could open the table in design view, and set the field's
Required property (lower pane) to Yes.
 
Back
Top