error when saving

[sue gray]

I have a form that I check for these 2 fields to be complete beforeupdate. I
also want to give the user the chance to verify they want to save the record.
If the user hits save and the counselorsignature is blank it tells them its
required and then I get a MS Access error box that pops up (You can't save
this record at this time!
Registrations 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)

How do I get rid of the MS error. THanks for the help.


Private Sub Form_BeforeUpdate(Cancel As Integer)

If IsNull(Me!CounselorSignature) Then
MsgBox "You must sign Contact Note", vbOKOnly
Cancel = True
Me!CounselorSignature.SetFocus
Else
If IsNull(Me!counselorsigndate) Then
MsgBox "You must date Contact Note", vbOKOnly
Cancel = True
Me!counselorsigndate.SetFocus
Else
If MsgBox("Do You Want To Save", vbYesNo) = vbNo Then
Cancel = True
End If
End If
End If
End Sub

 

[Brian]

I use a separate complete-checking procedure along with a Boolean flag
variable.

In Form's declarations section, enter this: Private CompleteFlag as Boolean

Private Sub Form_BeforeUpdate(Cancel as Integer)
CompleteCheck
If Not CompleteFlag Then
Cancel = True
Exit Sub
End If
End Sub

Private Sub CompleteCheck()
CompleteFlag = False
If Not Form.NewRecord Or Me.Dirty Then
If IsNull(ItemID) Then
MsgBox "Please select the item.", vbExclamation, ""
ItemID.SetFocus
ItemID.Dropdown
Exit Sub
End If
If IsNull(Quantity) Then
MsgBox "Please enter the quantity.", vbExclamation, ""
Quantity.SetFocus
Exit Sub
End If
End If
CompleteFlag = True
End Sub

CompleteFlag starts out False and only becomes True once it passes all
completion tests - and it will not give you any of the typical conflicts when
trying to interrupt updates.

I also disable the Close (X) button on the form itself and make another
regular close button on all my forms with this code:

Private Sub ButtonClose_Click()
CompleteCheck
If Not CompleteFlag Then Exit Sub
If Me.Dirty Then Me.Dirty = False 'save record
DoCmd.Close
End Sub

This does not allow the code in my BeforeUpdate info above to try to stop
the close of the form - it has to pass the CompleteCheck before it even gets
to the Close command.

 

[Linq Adams via AccessMonster.com]

The short answer is to go back into your table and change the field's
Required Propeprty from Yes to No. This will eliminate the Access error
message. You don't need it to be required as you're doing your own validation
routine in the Form_BeforeUpdate event.