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
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
Well, I can't see that makes any sense to do this. Why are you wanting to do this?
ASKER
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.
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.
Can you post what you have in the Combo BeforeUpdate event now ...
ASKER
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
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
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 ...
Are you saying the User cannot select No ?
Sorry I'm confused ...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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.