What gives with Cancel Update? 1

[Quehay]

I've got a routine to check for values in some fields (I'm not using Field-Required because this is a context distinct from the usual use of the table). If they aren't filled I want to cancel the Update and give user another chance to fill the boxes if "Retry" is chosen. When I step through this with "Retry" selected I get the "Can't save record at this time--Still want to close object" box. WHY???

[tt]
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Add
Dim ctrl As Control
Dim strPrompt As String

strPrompt = "You haven't filled in all the fields" & vbCrLf & "The Site won't be added without all fields "
strPrompt = strPrompt & vbCrLf & "Press Retry to fill in fields or Cancel to close without saving"

For Each ctrl In Me.Controls

If ctrl.Tag = "Required" Then

If TypeOf ctrl Is TextBox And IsNull(ctrl.Value) Then

If MsgBox(strPrompt, vbExclamation + vbRetryCancel, "INFO NEEDED&quot = vbRetry Then

Cancel = True
Exit For

Else

Cancel = True
GoTo CloseWithoutAdd

End If



End If

End If


Next



Exit_Err_Add:
Exit Sub

CloseWithoutAdd:
DoCmd.Close acForm, "frm_AddSites_pop", acSaveNo
Exit Sub

Err_Add:
MsgBox Err.Description
Resume Exit_Err_Add[/tt]

 

[RickSpr]

Are you sure you've got the symptom right? It looks like it would work fine if Retry was chosen, but not if Cancel was. The problem is that, if Cancel is chosen, you set Cancel to True, but then you try to close the form before you exit. Access won't see your Cancel being true unless you exit the procedure, so setting it has no effect in this case.

You're getting the message because Access can't save the record while your BeforeUpdate procedure is still active. But it has to save or discard the record when you close the form. It already asked the program by calling the event procedure, but the program didn't answer, so now it's asking the user. The program's answer (setting Cancel to True) doesn't take effect until your event procedure returns to Access.

This is a sticky wicket. I guess I'd set a timer event to go off, and do the close in the Timer event procedure. The BeforeUpdate event has to exit normally. Rick Sprague

 

[Quehay]

Thanks for your response Rick. You're right about the "Cancel" sequence. The problem is that when I select "Retry" and step through it gives the "Can't Save" box and then promptly closes the form. I can live with the message box but don't understand why it immediately closes the form. It's frustrating because this sort of thing is so easy with VB and Access forms, which indeed do so much with so little programming effort, make it hard to close them in code and do what should be done first (like the Query Unload that VB forms offer). I am using "X out" and I was thinking that cancel would stop the entire process--that's probably the real bug. The x-out process is ocurring in a level that isn't exposed in the programming interface, and I'm thinking it's analogous to Query Unload.

The solution is probably to do validation prior to the Update>Close sequence.

 

[RickSpr]

What about using the Timer technique? That would get you very close to what you want, wouldn't it? Rick Sprague

 

[Quehay]

Forgive my slow uptake--not sure what the scheme is with timer. I guess I could cancel form close, if me.dirty, and show message box unless a boolean had been set to true (this latter indicating that a deliberate, button click save had occurred) and do validation in the button click and call the Update within the button click routine (does this make sense?)

 

[RickSpr]

Actually, it's much easier than that. And I was going to explain it, but when I went to test it I realized what's really wrong here. You actually can close the form in your BeforeUpdate procedure, but you have to undo the record changes first!

Just replace your "Cancel = True" with "Me.Undo". This backs out all the record changes, so there's nothing left to save, and Access won't ask when you DoCmd.Close.

Me.Undo is the same as selecting Edit>Undo. It actually has 2 functions: If the currently selected control has unsaved changes, it backs out the changes to that control only. Otherwise, it backs out all the changes to the current record. (It even backs out the previous record if nothing has been changed in the current record.) In this case, however, any changes to the current control will already have been done before the form's BeforeUpdate event fires, so you don't have to worry about it. In other situations, though, you might have to Undo twice--once to back out the current control, and then again to back out the record.

Anyway, I tested it and it worked. Sorry for all the bad advice earlier. Rick Sprague

 

[Quehay]

Thanks for your diligence and insight Rick!! I remember reading about the undo danger in another site and thinking it couldn't still be true in 2000 (previous record deletion)--it's definitely a quirk to watch out for. The neatly bound Access forms come at a price. . .

 

[RickSpr]

Actually, I thinking calling it the Undo "Danger" is a lack of understanding. As an Access user, I like being able to back out a saved record.

As an access programmer, it's not really a problem. If I want to make sure I don't back out a previously saved record, I just check Me.Dirty first. If False, there haven't been any changes to the current record, so no Undo is necessary. Rick Sprague