Unstable Form Before Update Cancel - GotoControl Error

[FuzMic]

Hi everybody out there!

I think this a very interesting issue to tinker with.

The circumstances of the problem is as follows:
C1. System is xp office sp3 in xp win sp2
C2. A number of forms in msAccess has a Cancel = True in the "Form Before Update" event at the occurence of some validation failures.
C3. When the validation failed, a docmd.gotoControl "ctrlName" follows.
C4. Some Exit field event codes exist but it should be occur before or after "Form Before Update event"
C5. The problem manifest itself particularly if it is a SubForm Before Update event.


The problem
P1. Error of missing name of "ctrlName" or goto action is not available now. (Form Event Error 2046)

But....
B1. Once the error occurred, it will not occur again after debug.
B2. If form is imported into another .db, problem occur again & resolved itself as stated in B1.
B3. Use docmd.CancelEvent seems to be exactly the same as using Cancel = True

Temporary Resolution:
Use "On Error resume next" or forget abt gotoControl will hide the problem.

Any Views to shared ? Is this a bug in Access or I missed out something? Because the problem is intermittent, I did not realized this this bug for a number of years on a many forms until I accidentally found it.

HELP!!!
Thanks & Cheers!

 

[BobNTN]

In Before Update, this is the code I use

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Provide the user with the option to save/undo
'changes made to the record in the form

    If MsgBox("Changes have been made to this record." _
        & vbCrLf & vbCrLf & "Do you want to save these changes?" _
        , vbYesNo, "Changes Made...") = vbNo Then
            Cancel = True
            DoCmd.RunCommand acCmdUndo
End If

End Sub

 

[FuzMic]

Hi Bob
U r right to use .undo if u want to undo in which case there is no point to gotocontrol. I have in fact provided a branch in Msgbox to select undo the whole record.
However what i want is to allow user to keep all the other valid entries on the record, remain on the record & just setfocus on the control or goto the control that is failing in the validation.
Regards.

 

[gemma-the-husky]

after yuo get an error, you need a resume statement, then you can reset the error handler with another on error statement

if you dont resume Access thinks everythink thereafter is still handling the original error

is this your problem?

 

[FuzMic]

Hi there, Thanks for yr prompt

For yr info, i have a on 'error resume next' in the form beforeupdate event. When the application restarts each time, this code comes into action handling the error as evidenced by the fact that the intended gotoControl control did not occur. As u rightly said, once the error is handled the original error is gone; thereafter gotoControl acts on the intended control; not just on this form but on for all other forms in the same application.

The question still remains is: Why in the first place is there an error of failing to gotoControl after an form beforeUpdate event is cancelled by 'Cancel=true' or 'Docmd.cancelEvent'. To tinker this further, i will remove all possible events that are in queue before or after this event to see what happens.

Still thinking why!! Cheers.

 

[Rich]

Me.MyTextboxName Undo

 

[FuzMic]

Hi Rich

Non of the fields, the form is leaving, is failing field validation. There is actually no field validation in the form at all.

A failing field happens at the Form BeforeUpdate event due to a typical code line as follows:

If Isnull(failingFieldName) then
msgbox "error info"
gotocontrol failingFieldName
end if

Hence field undo seems inappropriate.

In fact once the "On Error Resume Next" is removed from the BfUpdate codes, the gotoControl error pops up for sure.

Cheers!

 

[boblarson]

Post the full code

 

[Rich]

This works fine
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Msg, Style, Title, Response, MyString
With CodeContextObject


If (Not IsNull(.txtAmnt) And IsNull(.HowPaid)) Then
Cancel = True
Beep
MsgBox "Please select payment method", vbOKOnly, "Error"
CmbHowPaid.SetFocus
End If
If IsNull(.txtAmnt) And Not IsNull(.HowPaid) Then

Beep
Msg = "You have entered a payment method however you have not enterd a payment date which may be different from the actual invoice date. If this invoice has not been paid yet then select no which will allow you to enter the date at a later time, if you select yes you will be returned to the form to enter a date. "
Style = vbYesNo + vbExclamation + vbDefaultButton1
Title = "Payment method without date"
Response = MsgBox(Msg, Style, Title)
If Response = vbNo Then
End If
Exit Sub
Me.CmbHowPaid.Undo
txtAmnt.SetFocus

End If

End With

End Sub

 

[FuzMic]

Hi Bob

Here's the codes

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Tit As String, Csez As Byte, tNl as string

On Error Resume Next 'with this, there is no err msg but intermittently gotoCtrl

tNl = " can't be Nil"

Select Case True
Case IsNull([CtName]) 'this is kept nil while [AcNum] is entered correctly thus causing code to jump below to gotoCtrl "CtName_t"
Tit = "Name of Customer" & tNl: Csez = 1
Case IsNull([AcNum])
Tit = "CustomerID" & tNl: Csez = 2
Case Left([AcNum], 2) <> "30"
Tit = "CustomerID must start with 30": Csez = 2
Case Else
If F7gDirt1 Or Me.NewRecord Then
[AcName] = [CtName]
[OBalDr] = 0
F7gDirt1 = False
End If
Me.AcNum_c.Requery
Exit Sub
End Select

Cancel = True

If MsgBox("Enter OK to Edit field, Cancel to Undo, _
49, Tit) = vbCancel Then
Me.Undo
Else:
Select Case Csez
Case 1: DoCmd.GoToControl "CtName_t" 'CtName_t.setfocus
Case 2: DoCmd.GoToControl "AcNum_t"
End Select
End If
End Sub

Hope Bob can enlighten me. Thanks in advance