How to call an event procedure in MS Access

Under the Before_Update event of My_Combobox on My_Form, I'm trying to call the On Undo event of My_Combobox.  How do I do it?

This is the code under the Undo event I'm trying to execute:

Private Sub My_Combobox_Undo(Cancel As Integer)
 Dim intResponse As Integer
 Dim strPrompt As String
 
 strPrompt = "Cancel the change?"
 
 intResponse = MsgBox(strPrompt, vbYesNo)
 
 If intResponse = vbYes Then
 Cancel = True
 Else
 Cancel = False
 End If

End Sub
dbfromnewjerseyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

IrogSintaCommented:
Normally you would do something like this:
My_Combobox_Undo 0 or Call My_Combobox_Undo(0)

But where in your Before_Update event are you calling this from.  The Before_Update event has it's own Cancel argument and I don't follow why you'd be calling this other event.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well, I can't see that makes any sense to do this.  Why are you wanting to do this?
0
dbfromnewjerseyAuthor Commented:
I've tried using the Me.MyCombobox.Undo command under the Before_Update event of the combobox and it does not work to undo a combobox selection.

For example, let's say I have a combobox with two possible values, say Yes and No, if it's currently set to Yes and I then select No,  I have not been able to get the combobox to reset to Yes using the Me.MyCombobox.Undo, so I'm trying a different approach.

I'm talking about the combobox value itself, not about undoing any record changes in tables or anything like that.

I want the valued displayed in the combobox to go back to what it was before I made the change.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Can you post what you have in the Combo BeforeUpdate event now ...
0
dbfromnewjerseyAuthor Commented:
I've attached a database to show illustrate what I'm trying to accomplish.

There is a combobox on the form whose default value is YES.

When you change that value to NO, because of the code I have under the Before_Update event of the combobox, I'm expecting the value to change back to YES.

That's what I'm trying to accomplish. To have the combobox revert to its original value before the change.
TEST.accdb
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well ... why have a combo box if you are not allowing to select No?
Are you saying the User cannot select No ?
Sorry I'm confused ...
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Your combo is not bound to any field, thus Undo does nothing.
If you set the Control Source to some field in some table, then:

Private Sub Combo2_BeforeUpdate(Cancel As Integer)
    Me.Combo2.Undo
    Cancel = True
End Sub

Will prevent selecting No ... although I don't see why you would do this ....
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbfromnewjerseyAuthor Commented:
OK. I tried to avoid a long explanation but here it goes.

I have a form with a number of controls on it.

One of the controls is a combobox whose Row Source is a table that contains,
among other data, state abbreviations.

That combobox is used to select a state.

When a state is selected, a subform on the form is populated with records (if they exist)
pertaining to the selected state.

The subform's record source is a query that retrieves data from a table (a different table than the one used for the combobox's row source) based on the combobox selection.

So, for example, let's say the user selects 'NY' from the combobox. New York records, if they exist, will be retrieved and displayed in the subform.

So, at this point, what would be on the screen is 'NY' displaying in the combobox and 'New York' records displaying in the subform.  By the way, the leftmost field in the subform contains the state abbreviation, so the subform might look something like this:

Field1   OTHER FIELDS IN SUBFORM
NY                 misc data
NY                 misc data
NY                 misc data
NY                 misc data

The user may add records, delete records or edit records (using controls I have on the form). When the user does any of these things, the results will then be displayed in the subform.

The user may only work on one state at a time; that being the state that is displayed in the combobox AND subform.

Let's say 'NY' is currently displayed in the combobox and 'New York' records are currently displayed in the subform.

Now let's say the user selects 'AZ' (for Arizona) from the combobox.

Rather than just clear out the subform and have it populated with 'Arizona' records, I want to give the user the opportunity to cancel the selection in the event (s)he made a mistake, changed his/her mind or whatever. Also, only one state's records are allowed to be edited at a time based on whatever state is in the combobox and subform.
In other words, if 'New York' records are currently displayed, the user is not allowed to say, ADD an Arizona record. (S)he would first have to select 'AZ', which would trigger the query to run and would populate the subform with Arizona records (if they exist). THEN the user would be able to work on Arizona records.

But back to the instance where, with 'New York' records currently displayed in the subform, the user changes the combobox selection from 'NY' to 'AZ'. I want a message to pop up and an opportunity to cancel the selection to be triggered (which I currently have working).

So, if AZ was selected, it would be something like this:

"You have selected a different state than the one currently being worked on. Are you sure you want to change states?"

Now, if the user selects no or cancel, the subform records do not change. It continues displaying the New York records. That part I have working.  The problem is the combobox isn't going back to 'NY'.  So I wind up with 'AZ' displayed in the combobox and 'New York' records displayed  in the subform. All I'm trying to do is get the darn combobox value
to match the state displayed in the subform when cancel is selected.

You asked why I'm trying to call an event procedure.  Well, that's because I couldn't get the combobox.undo command to do what I was expecting it to do. So, with me just clicking around looking for some other way, I thought maybe if I can trigger the Form_Undo event (the same operation as clicking the reverse arrow button on the command bar), that that might be an alternate way of accomplishing what I'm trying to do. I know I posted the combobox's undo event but I could have posted any event. I was just asking how to call AN event.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.