Validation Rule question

  • Thread starter Thread starter Lori
  • Start date Start date
L

Lori

I am using Access 97. I have a form which uses a single
table as it's source. I have a field, Policy_Nbr which is
required (I have this set within the table properties).

I have tried to set a validation rule to require this
field if a user leaves it null. I have tried to set the
validation rule both at the table level and at the
Policy_nbr control properties on the form.

I have a button called Add Record which has an event
procedure upon clicking on it to take the user to a new,
blank record after completing data entry on the form.

I keep getting an error message stating "You can't go to
the specified record." "You may be at the end of a record
set."

I get this as I am testing adding a record without the
entering data in my required field of policy-nbr.

I don't know why I am getting this as I am trying to add a
new record. In addition, my validation text doesn't
appear to be working. What is the correct data to enter
for validation rule to require a field not be null?

Thanks so much...Lori
 
A simple approach is to set the Required propery of the field to Yes.
Access will not save the record without a value in the field.

Alternatively, you can set the Validation Rule of the field in table design
to:
Is Not Null

Either way, you will receive an error in your form when you try to save the
record if the field is Null. Use the Error event of the form to trap that
error. Alternatively, test if the field is null in Form_BeforeUpdate, since
that event fires before Access tries to save the record:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.MyField) Then
Cancel =True
MsgBox "Hey! Fill in MyField first."
End If
End Sub

The error message you describe makes sense if you consider that Access has
to save the current record before it can move to another (and the current
record cannot be saved). There are several strange messages and some bugs
associated with trying to move record or close the form when the record has
not been saved, so it is worth the effort to explicitly force a save before
you do anything else that requires the record to be saved, e.g. moving
record, finding another, requerying, filtering, changing Order By,
reassiging the RecordSource, closing the form, or opening another into the
same data. To force a save and specify which form you are talking about (not
just hoping it has the focus), use:
If Me.Dirty Then
Me.Dirty = False
End If
 
Back
Top