How do you cancel a combo box update?

  • Thread starter Thread starter David Anderson
  • Start date Start date
D

David Anderson

I want to put some code in the BeforeUpdate event of a combo box control on
a form in Access 2000. In certain predefined conditions, this code should
cancel the combo box change and ensure that the combo box remains at its
previous setting. I had assumed that setting the Cancel property to True
would do this but it doesn't seem to work.

I feel sure that a very simple solution is staring me in the face but it's
late at night and the brain is starting to shut down. Any ideas?

David
 
David said:
I want to put some code in the BeforeUpdate event of a combo box control on
a form in Access 2000. In certain predefined conditions, this code should
cancel the combo box change and ensure that the combo box remains at its
previous setting. I had assumed that setting the Cancel property to True
would do this but it doesn't seem to work.
Click to expand...


In addition to the Cancel = True add the line:

Me.comboboxname.Undo
 
Thanks, Marshall. That has fixed most of my problem. All the other parts of
my form that react to a change in value of this combo box now remain at
their current settings, so the update does appear to have been cancelled.
However, the combo box itself still displays the new setting. How do I make
the combo box revert to the value selected before the cancelled attempt to
change it?

Note that this is an unbound combo box (on an unbound form). The previous
value of the combo box was set by code.

BTW, I never make a post here without first doing some homework of my own. I
always look up the VBA online help and Google for possible solutions, but in
this case I have been surprised at how little information was readily
available. My combo boxes are always created using the wizard in Access
2000, which automatically creates a subroutine whose first line always
includes the Cancel property, e.g. Private Sub
CurrencyCombo_BeforeUpdate(Cancel As Integer). I would therefore have
expected the online help to tell me how to use the Cancel property, but the
list of Combo Box Control Properties does not include Cancel, nor does it
include Undo. Checking for info in VBA help by searching on the word
'Cancel' only ever brings up irrelevant info about command buttons. A search
on the word 'Undo' returns no results at all!

Where is all this stuff documented?

David
 
Sorry, I missed the part about this being an unbound combo
box on an unbound form. Undo has little or no effect on
unbound objects because there is no original data to
restore. For unbound objects, you are in total control and
it is your responsibility to implement whatever behavior you
want to happen.

I think in this case you want to use some code to save the
"original" value somewhere so it is available later in case
you want to reset/undo a user action. You might be able to
use the combo box's Enter event to save the value and then
retrieve it in the BeforeUpdate event.

If you have a bunch of other controls on the form you also
want to "undo", then I think you might be better off saving
values in the code that sets the controls. (A save area in
the module's declaration area or maybe even a special
purpose table would serve this purpose.) Then you can use
code wherever you want to reset control values.

Cancel is not a property, it is just an argument to the
BeforeUpdate event procedure. Help for BeforeUpdate Event
has a one line explanation that setting it to True cancels
the event (i.e. the update to the control does not proceed).
Most things are explained, usually very briefly, in Help,
but can often difficult to find. I don't think this
difficulty is due to sloppy work by the Help authors, but
more a matter of there being such a huge amount of
intricate, interconnected and subtle information to work
with.

Sometimes a feature's documentation really isn't hard to
find and is reasonably complete. But, as with any complex
subject, gaining an understanding the implications and
interactions is a matter of tutorials, experience and even
the cleverness of the reader.
 
The Access 2000 VBA online help section entitled 'BeforeUpdate, AfterUpdate
Events' on my PC does not contain anything about using the Cancel argument.
Not even a one-liner. Just in case my old eyes were deceiving me, I copied
this entire help section to a Word document and searched for the word
'cancel'. There are a couple of references to cancelling an update but no
guidance on how to do it. I checked this on two of my PCs. Are you perhaps
looking at the online help in a different version of Access?

However, despite this apparent omission in the online help, I did guess
correctly that setting Cancel to True was the way to do it. The only problem
was that it doesn't work as I would expect. To my simple mind, when you say
that setting Cancel to True "cancels the event (i.e. the update to the
control does not proceed)", I would expect that the ComboBox would then
revert to the selection previously displayed (no matter whether that
previous setting had been set as a control default or by being bound to a
table or by some code I wrote). I am clearly confused....

I'm currently trying to design a very simple test form where I can make a
combo box behave as I would wish but it's not totally straightforward. I can
certainly save the combo box value just prior to a change but I can't reset
it to that value via the AfterUpdate event of the combo box (Access
generates an error). I will continue experimenting.

David
 
David said:
The Access 2000 VBA online help section entitled 'BeforeUpdate, AfterUpdate
Events' on my PC does not contain anything about using the Cancel argument.
Not even a one-liner. Just in case my old eyes were deceiving me, I copied
this entire help section to a Word document and searched for the word
'cancel'. There are a couple of references to cancelling an update but no
guidance on how to do it. I checked this on two of my PCs. Are you perhaps
looking at the online help in a different version of Access?

However, despite this apparent omission in the online help, I did guess
correctly that setting Cancel to True was the way to do it. The only problem
was that it doesn't work as I would expect. To my simple mind, when you say
that setting Cancel to True "cancels the event (i.e. the update to the
control does not proceed)", I would expect that the ComboBox would then
revert to the selection previously displayed (no matter whether that
previous setting had been set as a control default or by being bound to a
table or by some code I wrote). I am clearly confused....

I'm currently trying to design a very simple test form where I can make a
combo box behave as I would wish but it's not totally straightforward. I can
certainly save the combo box value just prior to a change but I can't reset
it to that value via the AfterUpdate event of the combo box (Access
generates an error). I will continue experimenting.
Click to expand...


I never used A2K because of all the reported problems. I
think most of them were fixed in subsequent updates, but
A2000 Help was always a problem. I really liked Help in A97
and while A2003 Help is not that good, it is way better than
A2000 and a nice improvement over A2002 Help.

Upon further reflection, the BeforeUpdate event is near
useless for unbound controls. I think you can do all you
want using the AfterUpdate event.

A possibly more convenient place where you could save each
control's "original" value is in the control's Tag property.
 
Marshall,
You are quite correct - the BeforeUpdate event has indeed proved to be
useless in my situation. Neither setting Cancel to True or using the combo
box's Undo property appears to serve any purpose at all. As you said, the
AfterUpdate event is where I needed to put some straightforward code to
reset the control back to its previous value. All is now well.

Thanks very much for your assistance in cracking this problem. Oddly enough,
it's often the simplest looking tasks in Access that create the greatest
difficulty. I've just bought a copy of Access 2003 on eBay and will be
interested to see how much the help system has improved.

David
 
Back
Top