• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 385
  • Last Modified:

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
 Cancel = False
 End If

End Sub
  • 4
  • 3
1 Solution
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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well, I can't see that makes any sense to do this.  Why are you wanting to do this?
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.
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Can you post what you have in the Combo BeforeUpdate event now ...
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.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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 ...
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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)
    Cancel = True
End Sub

Will prevent selecting No ... although I don't see why you would do this ....
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:

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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now