Undo on error

[Pyro]

Hey,

So i have come across a problem that i have found in some error trapping that i am doing this afternoon. When trying to enter data into a subform before the parent record is generated, Access throws you an error - no biggie, stock standard basic stuff. Replacing that error with something more coherent to my readers is easy, again no problem. The problem lies in that the value that was selected from that combo box or entered into that field is now still sitting in that control staring at me. If i try to select elsewhere i get that same error thrown back at me. I know i can hit the Escape key and undo it, i can even tell my users to do that. What i would like to know is has anyone come up with a way to undo that entry, or simulate the Escape key scenario.

Undo does not work because it seems that the error is trapped before the control is updated. I tried to skip around that by first moving the focus somewhere else and then running the undo command, but that seemed to lock every combo box in the entire db, or as i like to think of it, break Access.

I have managed to achieve this in small subforms by forcing a save on the one or two controls within it and trapping the error in the afterupdate event, but there is no way that i am going to try this on a larger form with many more controls...

To me it just seems more intuitive to drop an error that says "Sorry, you can't update X until you update Y. Please update Y and then try again", and then undo the entry. As opposed to saying "Sorry, you can't update X until you update Y. Please click OK, then press Escape then update Y and then try again."

Any help will be much appreciated.

 

[Khalid_Afridi]

you question is too complecate to understand. alway try to post in simple and clear way so others can help you instead of reading all the long story

 

[Pyro]

you question is too complecate to understand. alway try to post in simple and clear way so others can help you instead of reading all the long story

OK.

Private Sub Form_Error(DataErr As Integer, Response As Integer)
 
    Select Case DataErr
        Case 3162
            MsgBox "You have not yet selected a buyer for this order. Select a buyer before proceeding.", vbExclamation, "Error"
[COLOR=red]           Me.Undo[/COLOR]
 
        Response = acDataErrContinue
 
    Case Else
        Response = acDataErrDisplay
    End Select
 
End Sub

Private Sub Form_Error(DataErr As Integer, Response As Integer)
 
    Select Case DataErr
        Case 3162
            MsgBox "You have not yet selected a buyer for this order. Select a buyer before proceeding.", vbExclamation, "Error"
[COLOR=royalblue]           Me.SomeOtherControl.Setfocus[/COLOR]
[COLOR=red]           Me.Undo[/COLOR]
 
        Response = acDataErrContinue
 
    Case Else
        Response = acDataErrDisplay
    End Select
 
End Sub

If i run the second block of code, the blue line causes all combo boxes in the database to lock up.

I am trying to achieve the red line. Any ideas?

 

[Khalid_Afridi]

in this case:
try to use the cancel event procedure instead of using me.undo

  Select Case DataErr
        Case 3162
            MsgBox "You have not yet selected a buyer for this order. Select a buyer before proceeding.", vbExclamation, "Error"
[COLOR=red]          DoCmd.CancelEvent[/COLOR]
[COLOR=#ff0000]'OR Exit sub[/COLOR]

 

[Pyro]

in this case:
try to use the cancel event procedure instead of using me.undo

  Select Case DataErr
        Case 3162
            MsgBox "You have not yet selected a buyer for this order. Select a buyer before proceeding.", vbExclamation, "Error"
[COLOR=red]         DoCmd.CancelEvent[/COLOR]
[COLOR=#ff0000]'OR Exit sub[/COLOR]

Sorry Khalid. Both of those ideas failed...

 

[Khalid_Afridi]

i am wonder that you are still using the Me.SomeOtherControl.Setfocus before you use the Exit Sub.

By the way why you use the:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
it could be some other event for example After_update event.

If IsNull (someFiled) then
msgbox ("Error Msg")
Exit Sub
End if

 

[smig]

you can change the required property of the fields causing you problems to "no".
you can also give these fields a default value.

 

[Pyro]

You are right in that i can set the required property to no, however in most of these instances, referential integrity needs to be enforced. And i would hate for my users to enter values into several fields and then getting a before update error that tells them that their record will not be saved...

One potential solution that i found was to set the timer interval in the error trapping code to a small figure, and then run my undo code in the on timer event. However, with no experience in using the timer event, i wonder if this mechanism has other problems that i have not considered.

Since the error appears to trap before a value is entered into any field, i guess i was hoping for a way to prevent that value from being entered in a similar way to the Cancel event available on other form/control events..

 

[SOS]

How about just disabling the subform until there is a record in the main form?

 

[Pyro]

How about just disabling the subform until there is a record in the main form?

SOS, that is certainly something i have considered. It does allow for a visually intuitive way of saying "no you can't enter a record here yet." Another idea would be to create an error in the on enter event of the subform.

I do have some fall back ideas. I just wanted to see what other people would come up with.

 

[SOS]

Yeah, I normally just use the Main Form's On Current event to test whether it is a new record or not. If new then disable the subform and if not let it be enabled. Then you can enable the subform as soon as there is enough entered to make it a valid record.

 

[c_smithwick]

One simple solution would be to populate the combo box for "X" in the after_update event of the combo box for "Y". That way, until a user makes a "Y" choice, there is no "X" choice to be made, hence no error. Make the "X" combo box unbound and in the after_update event for the "X" combo box, write the value to your record. You could use a hidden bound text box, bound to the field for "X" and set its value to the chosen value in the "X" combo box in the after_update event. Rather than trying to undo stuff after an error is thrown, why not prevent the error in the first place - better programming.