Form validation code causing access errors

[pzaccess]

I have a form with several required textboxes/comboboxes. I have the following validation code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
For Each ctl In Me
If ctl.Tag = "*" Then
If IsNull(ctl) Or ctl = "" Then
MsgBox "You must complete the required fields (marked by *) before you can continue.", vbCritical, "Required Field"
ctl.SetFocus
Cancel = True
Exit Sub
End If
End If
Next
Set ctl = Nothing
End Sub

When I click the Close button on the form, the code works fine and the custom error message pops us. However, when I click OK, another access error message pops up: "You can't save this record at this time.... Do you want to close the database object anyway?" When I click NO, an Action Failed message pops up with error number 2950 requiring me to "Stop all Macros".

What can I do to prevent these two access messages from coming up!

Thanks!

 

[PHV]

I click the Close button on the form
What is the code of the Click event procedure of this button ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pzaccess]

It is just an embedded macro with a "Close" action and the following arguments: ",,Yes". Created though a button wizard.

 

[RoyVidar]

The cancel you do in the BeforeUpdate event of the form, only cancels that event - the saving of the record, which is called from your "Close" macro, then it continues to try to close the form...

So, what you need, is for the "Close" thingie not to continue, if the form is dirty... Now this can probably be done umpteen ways, here's a completely untested version that might, if you're lucky, do the trick - but requires you to dump that macro stuff and use VBA...

[tt]private sub MyClose()

if me.dirty then me.dirty = false
docmd.close acform, me.name

myexit:
exit sub
myerr:
if err.number<>2101 then
msgbox err.description
end if
resume myexit

end sub[/tt]

this will only be used when the user is using your button, thouh, if you have other means of closing the form available, you'll still get the same message.

What it does - or should do - is to see whether there are unsaved changes to the form, then try to force a save.

If the save is preventet, due to for instance failing violation, then the Me.Dirty = False gives error 2101, which is then trapped (and the close is skipped).

Roy-Vidar

 

[pzaccess]

This helped a bit since now I'm getting just one pop-up box instead of 2. But I'm getting a different one now than what I had before.

I get the following:

"Run-time error '2101': The setting you entered isn't valid for this property"

When I click "Debug", it highlights "Me.Dirty=False" statement.

Any other way I can accomplish it? THANKS!

 

[RoyVidar]

Sorry, that's what one gets when not testing - forgot one line, just below the Private Sub statement and before the If statement, enter:

[tt]on error goto myerr[/tt]

Roy-Vidar

 

[pzaccess]

Thanks so much! It works now.