Help using Validation and beforeUpdate? a better way?

[Carlos Silva]

Hello, I'm tryng to avoid
NULLs values when entering data in a form

the table fields are

IDPayItem ' this is a long integer field
NoOrder
ItemCode
ItemDescription
ItemUnit
Price
PayList


on the form i have a code that
creates automacally a nomber for IDPayitem and
this numbers is aasigned once NoOrder got focus


the fields that I use for the check of Null values are

tboItemCode
tboItemDescription
tboPrice
tboPayList

then I use this Procedure for BeforeUpdate

Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(tboItemCode.Value) Then
MsgBox "CHECK EMPTY FIELD ITEM CODE"
Cancel = True
End If

If IsNull(tboItemDescription.Value) Then
MsgBox "CHECK EMPTY FIELD ITEM DESCRIPTION"
Cancel = True
End If
If IsNull(tboPrice.Value) Then
MsgBox "CHECK EMPTY FIELD PRICE"
Cancel = True
End If

If IsNull(tboPayList.Value) Then
MsgBox "CHECK EMPTY FIELD TYPE PAYLIST"
Cancel = True
End If

End Sub

what happens is that when the user hit the close button
after all the Msgbox appears
this message appears..

-----------------------------------------------------------

You can't save this record at this time.

"database" may have encountered an error while trying
To save a record.

If you close this object now, the data changes you made will
be lost.
Do you want to close the database object anyway?


Yes No
----------------------------------------------------------------

Is there another better way of avoiding saving null values
when user closes the forms? is there a way to trigger some action
when te user hits the 'ESC' Key??

thank you very much

Carlos silva

 

[Wayne Gillespie]

Carlos, you may also consider opening your table in design view and setting
the Required property to Yes for the fields that must not be Null.

Wayne, your reply explains exactly what's going on, and does prevent the
user closing the form, but I think the result is confusing for the user.
After the MsgBox from Form_BeforeUpdate, Access gives the "Can't save: close
anyway?" message. If they answer Yes, they lose their edits, but the form
doesn't close (cancelled by Form_Unload). That seems like a lose/lose
outcome. They now have lost edits and a form that won't close.

We used an approach similar to what you suggest back in the Access 2 days,
but gave up on it when MS introduced the "Can't save: close anyway?" dialog.

I made the assumption that cancelling the BeforeUpdate would also stop the
message, obviously thinking about it now the record would have to be saved
before the form unloads, hence throwing the message. The Unload event is too
late to prevent this.

Thanks for the pickup Allen.

As an aside, this is one of the reasons that I remove the control box and close
button from all my forms, and use a command button as the only exit method. It
is much easier to handle a range of issues such as this.

Wayne Gillespie
Gosford NSW Australia