What Event Fires When User Cancels a Record Update on Form?

[whdyck]

I'm using MS Access 2003.

I have a cascading dropdown on a columnar form that works well except when the user cancels a record update by pressing the Escape key. Not sure where to code the cure.

DropDown #1: cmdDestinationCode

After changing the DropDown value, DropDown #2 is refreshed as follows:

Private Sub cmbDestinationCode_AfterUpdate()
On Error GoTo ErrorHandler
 
    ' Refresh the child dropdown for Terminal based on the Destination selected
    Me.cmbTerminalCode.Requery
 
    ' Enable Terminal dropdown iff the Destination is selected
    If Not IsNull(Me.cmbDestinationCode.Value) Then
        Me.cmbTerminalCode.Enabled = True
    Else
        Me.cmbTerminalCode.Enabled = False
    End If
 
    ' If there's only one Terminal available to select, select it
    If Me.cmbTerminalCode.ListCount = 1 Then
        Me.cmbTerminalCode = Me.cmbTerminalCode.ItemData(0)
    Else
        Me.cmbTerminalCode = vbNullString
    End If
 
. . . 
 
End Sub

DropDown #2: cmbTerminalCode (can have multiple Terminals per Destination)
The list is filtered based on the Destination selected. It has this Row Source:

SELECT qryDestinationAndTerminal.TerminalCode,
qryDestinationAndTerminal.TerminalName 
FROM qryDestinationAndTerminal 
WHERE qryDestinationAndTerminal.DestinationCode=Forms!frmCwbOrder!cmbDestinationCode 
ORDER BY [TerminalCode];

When the user changes Dropdown #1, Dropdown #2 refreshes to values relevant to the new value in Dropdown #1. However, if the user then presses the Escape key to revert the record back to original state, the list in DropDown #2 does not refresh.

I tried to put the refresh code in the Form Undo event, but that didn't do it.

Any idea where I should put this code so that it fires when the user cancels the record update? It needs to refresh *after* the record has reverted.

Thanks.

Wayne

 

[AccessMSSQL]

Use the KeyPreview property of the form. Set this to true and then put your event trapping in the On KeyPress event. You will have to trap the ESC key press.

 

[whdyck]

Use the KeyPreview property of the form. Set this to true and then put your event trapping in the On KeyPress event. You will have to trap the ESC key press.

But it seems that the user can no longer cancel the record update because I'm always trapping the Esc.

Or am I missing something?

 

[boblarson]

Put the REQUERY (not refresh) in the form's On Current event or on the combo's click event.

 

[whdyck]

Put the REQUERY (not refresh) in the form's On Current event or on the combo's click event.

On Current does not seem to fire when I click Esc. The combo box Click event fires only after I click one of the *entries* in the combo box list (which is still the unrefreshed old list) rather than when I click the combo box to display the list. But I'd want the list requeried before it is displayed.

Wayne

 

[boblarson]

On Current does not seem to fire when I click Esc. The combo box Click event fires only after I click one of the *entries* in the combo box list (which is still the unrefreshed old list) rather than when I click the combo box to display the list. But I'd want the list requeried before it is displayed.

Wayne

The click event for me has always occurred when clicking the down arrow, but maybe doesn't work that way in later versions.

The Undo event would have been my first choice. I just tried it out and the Undo event fires for me when hitting ESC or Ctl+Z.

 

[AccessMSSQL]

After you trap the esc key try calling cmdDestinationcode_AfterUpdate. This should evaluate to a null value and reverse the first action

 

[whdyck]

The click event for me has always occurred when clicking the down arrow, but maybe doesn't work that way in later versions.

The Undo event would have been my first choice. I just tried it out and the Undo event fires for me when hitting ESC or Ctl+Z.

You're right that Undo does indeed fire on ESC; however, if I look at the form after it hits the breakpoint, it still has not reverted the values. So it appears that it's a timing problem: The Undo event fires before Access restores the values, so the requery happens but it's still against the old (unrestored) values. So then when the restore happens DropDown #2 still has the wrong list.

Wayne

 

[boblarson]

You're right that Undo does indeed fire on ESC; however, if I look at the form after it hits the breakpoint, it still has not reverted the values. So it appears that it's a timing problem: The Undo event fires before Access restores the values, so the requery happens but it's still against the old (unrestored) values. So then when the restore happens DropDown #2 still has the wrong list.

Wayne

So, can't you requery the first one and then requery the second in that order in the undo event?

 

[whdyck]

So, can't you requery the first one and then requery the second in that order in the undo event?

No, because neither of the dropdowns have reverted to old values yet when those requeries happen. I need to somehow be able to run the requeries *after* the values revert. I can do that after the fact by manually by clicking on the parent and child combos, but I had hoped I could do that in code.

Wayne

 

[boblarson]

Create a global variable for each value you rely on and update them in the form's on current event.

Then base the combo rowsource off a function that returns that value or at least any that you may be dependent on the saved value.

I'm running out of ideas and it is hard to come up with them without seeing it in action.

 

[doug9]

No, because neither of the dropdowns have reverted to old values yet when those requeries happen. I need to somehow be able to run the requeries *after* the values revert. I can do that after the fact by manually by clicking on the parent and child combos, but I had hoped I could do that in code.

Wayne

I have a similar problem: I want to set up the form controls depending on data values (enable or disable controls). The way I handle this normally is in the _current event code. But _current doesn't get run after the _undo. As you said, the dirty values are still set when _undo is run.
Did you ever find out how to get control when the form is refreshed from the old values?