[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 419
  • Last Modified:

How to bypass AfterUpdate event of combo box when closing a form.

If I input text into a combo box on my form and then close the form by clicking on the upper right hand corner "X", I get a runtime error. I believe the error is caused by the AfterUpdate event of this combo box trying to run while or after the form has closed; because if I do not type anything into this particular combo box I can close the form without error... So, how can I prevent the AfterUpdate code from running in this scenario?

Thank you!
JA67
0
JA67
Asked:
JA67
  • 37
  • 22
  • 21
  • +1
3 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
What is the exact error ?

mx
0
 
stevbeCommented:
and what is the code that is running in the afterupdate?

<how can I prevent the AfterUpdate code from running in this scenario>
how do you want them to close the form ... if you use your own button you can trigger a module var that will let the form close, if they don;t use your button you can cancel the close of the form. That being said, you really should find out what the problem is.
0
 
JA67Author Commented:
At the moment, I am not getting the run-time error that I was first getting , for some reason I am now getting this error:

Run-time error 2501
"The close action was cancled."

The origianl run-time error would close Access after clicking on OK, but this one takes me to the below line of my code when I click Debug:

DoCmd.Close acForm, "frmUpdateExistingCase"

This line is in the After_Update code...
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
stevbeCommented:
so the afterupdate is trying to close the form itself or is it trying to close a different form?
0
 
mbizupCommented:
Try inserting a DoEvents statement before the close:

DoEvents
DoCmd.Close acForm, "frmUpdateExistingCase"
0
 
JA67Author Commented:
stevbe -- I am OK with using the upper right X for closing the form or disabling it and using a button. Just as long as they can close it without error.
0
 
JA67Author Commented:
stevbe -- yes, the AfterUpdate code does have a line it it (the one I pasted in) that closes this form.
0
 
JA67Author Commented:
mbizup -- The DoEvents did not work. Thanks though...
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
On Error Resume Next
DoCmd.Close acForm, "frmUpdateExistingCase"
Err.Clear

mx
0
 
mbizupCommented:
Do you have any other code in the after update event?  If so, please post it.  
0
 
mbizupCommented:
Also, what code do you have in the close event of frmUpdateExistingCase?
0
 
JA67Author Commented:
DatabaseMX - That's interesting. Your suggestion stops the error, but goes ahead and lets the rest of the AfterUpdate code run. I need to bypass all of the AfterUpdate code if the form is closed manually by the user. My AfterUpdate code will close the form, which is desired, but here I am wanting the user to be able to close the form with the X or a button, and not have the AfterUpdate code run.
0
 
JA67Author Commented:
mbizup --  I have 297 lines of code in this AfterUpdate. Do you want me to paste it all in here? It opens other forms and populates them based on the value that is put in the combo box that has the AfterUpdate event. I can post it if you think it will help you help me...

I do not have any code in the close event of frmUpdateExistingCase.
0
 
mbizupCommented:
On Error Resume Next does not actually stop the error.  It allows code to continue executing if there is an error.  Your error may be in response to a null value in your textbox.  

Don't post all the code.

Do you have any lines of code in this after update event that use the value of the textbox?

Is this code being called from frmUpdateExistingCase, or are there two different forms involved?

Do you have any close event code that might be running?
0
 
JA67Author Commented:
mbizup -- I've got some stuff like this. It's not pasted in the exact order that it appears in the code.
This code is being called from frmUpdateExistingCase only.
I am not aware of any close event code that is running at this time when I am trying to close this form manually or when the AfterUpdate code is running.

cboOpenCase = Nz([cboOpenCase], "")

If cboOpenCase <> "" Then

***** By Case Number ***
If frameSearchCriteria.Value = 1 Then
txtSelectedCaseID = cboOpenCase.Column(0)
End If
'***** By Investigating Officer ***
If frameSearchCriteria.Value = 2 Then
txtSelectedCaseID = cboOpenCase.Column(3)
End If
'***** By Offense ***
If frameSearchCriteria.Value = 3 Then
txtSelectedCaseID = cboOpenCase.Column(3)
End If
'***** By Suspect ***
If frameSearchCriteria.Value = 4 Then
txtSelectedCaseID = cboOpenCase.Column(3)
End If
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"yes, the AfterUpdate code does have a line it it (the one I pasted in) that closes this form."

Well ... that is a bit of a conflict possibly.  

In your On Close  how about adding:

Me.YourComboBox=Null

Seems the 'X" >>> triggers OnClose >>> triggers a BeforeUpdate ... then AfterUpdate ... then 2501 ...

mx
0
 
JA67Author Commented:
DatabaseMX -- that seemed real promising but it still chokes on the same line (DoCmd.Close acForm, "frmUpdateExistingCase") when I close the form via the X. The form is still sensing that an Update has occurred apparently.
0
 
mbizupCommented:
You can handle the nulls at the top of the After Update event:

If NZ(cboOpenCase,"") = ""
    Then Exit Sub
end if
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Here is a typical example where that error occurs, which may help you track it down:

You have button which opens a report ..
   DoCmd.OpenReport "YourReport"

But ... the report has No Data ... and in the OnOpen event of the report, you have a message box that pops up and says "Sorry, no Data"  if the NoData property = Truen ... and then you Cancel the opening of the report. Back at the Open command ... you will the get the message ""You cancelled the previous operaton".

So ... you have to trap that error around the OpenReport command.

So ... maybe this example will help you track it down?
0
 
JA67Author Commented:
mx -- I've got this at the top:

Private Sub cboOpenCase_AfterUpdate()
If Nz(cboOpenCase, "") = "" Then
Exit Sub
End If

And I have this in the Close Event of frmUpdateExistingCase:

Me.YourComboBox=Null

And I am still getting the same error on the same line of code...
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Refresh my memory now ... exactly which line of code ... and which form is that code in?

Do you have still have it wrapped with the On Error Resume Next ?

mx
0
 
mbizupCommented:
How about using error handling, like mx suggested, but specific to that message:

On Error Goto EH
DoCmd.Close acForm, "frmUpdateExistingCase"

and at the end of the sub:


Exit Sub
EH:
If Err.Number = 2501 then Exit Sub
Msgbox "Error: " & err.Number
0
 
mbizupCommented:
Those lines starting with the Exit Sub would appear together immediately before the End Sub statement...
0
 
JA67Author Commented:
More background info:
This combo box allows for the selecting of case numbers. I made it so that when a case number is selected (firing the AfterUpdate event) then that case that was selected immediately opens, in other words you don't have to select it and then click an open button. If you select it from the list, the case will open automatically; if you type in the case number then you have to hit Enter for the case to come up or simply leave the field. But now I am seeing that clicking on the X to close the form will also trigger the AfterUpdate event  and this is the problem.
0
 
mbizupCommented:
The code I just posted should exit out of the After Update event if that error is encountered, without running the rest of the code.
0
 
JA67Author Commented:
MX -- "Refresh my memory now ... exactly which line of code ... and which form is that code in? Do you have still have it wrapped with the On Error Resume Next ?"

The line of code that errors is:
DoCmd.Close acForm, "frmUpdateExistingCase"
And no - I removed the On Error Resume Next...

This line of code is in frmUpdateExistingCase, which is the same form that has the combo box in question.


0
 
JA67Author Commented:
mbizup --  I am excited to try your suggestion. It will take me a moment as I am slow at apprehending things from experts... I will try to implement...
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"like mx suggested, but specific to that message:"

Yes ... I forgot and left out the 2501 test ... but I mainly wanted to see if that would do it ... then I would have posted the entire bit ... which is basically what I do in my report example above.

good suggestion.

mx
0
 
JA67Author Commented:
mbizup -
Here is what I have. The result is that I can close the form with no error, but all of the AfterUpdate code runs. It opens the case that I typed in and I want it to simply close the form and not open the case. Maybe I did not implement correctly?

(Lots of code here...)

On Error GoTo EH
DoCmd.Close acForm, "frmUpdateExistingCase"

(bunch of code here...)

Exit Sub
EH:
If Err.Number = 2501 Then Exit Sub
MsgBox "Error: " & Err.Number
End Sub
0
 
mbizupCommented:
Hmm...
From the VBA editor:

Tools -> Options
There should be radio buttons related to errors handling.
Make sure "Break on Unhandled Errors" is selected.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
On Error GoTo EH
DoCmd.Close acForm, "frmUpdateExistingCase"
Stop                                '******************************** Try This
(bunch of code here...)

Exit Sub
EH:
If Err.Number = 2501 Then Exit Sub
MsgBox "Error: " & Err.Number
End Sub
0
 
JA67Author Commented:
mbizup  -- "Break on Unhandled Errors" is selected, i.e., it was already selected.

DatabaseMX - I added the "Stop" and it did not change anything. All of the AfterUpdate code still runs.
0
 
mbizupCommented:
Place some debugging breakpoints in the code... that should show you the flow of the code as it's executing.  I'm sure this will show you that the After update event is exiting as it should without running the rest of the code.

Do you have code in other Form level events that run when the form closes?
ie:
Form_beforeUpdate
Form_AfterUpdate
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Put a breakpoint on the Stop ... and see if that is even being executed?

mx
0
 
JA67Author Commented:
mbizup  -- There are no other form level events on this form.

OK -- now I've got to show my ignorance even further... I know how to use MsgBox to kind of test and check the flow of things, but I don't know how to use breakpoints - is that something different?
0
 
JA67Author Commented:
Here are some lines that are above the lines I have added to try and stop execution of the AfterUpdate code... Is the code that I am trying supposed to stop all execution of AfterUpdate code or just at/after the error is triggered? All these forms are opening up.

DoCmd.OpenForm "frmEvidense"
DoCmd.OpenForm "frmChainOfCustody"
DoCmd.OpenForm "frmCaseInfo"
Forms![frmCaseInfo].[txtCaseNumber].Value = "Loading case number..."
Forms![frmCaseInfo].[txtCaseDate] = Now()
Forms![frmCaseInfo].[cboOffense].SetFocus

(Lots of code here...)

On Error GoTo EH
DoCmd.Close acForm, "frmUpdateExistingCase"

(bunch of code here...)

Exit Sub
EH:
If Err.Number = 2501 Then Exit Sub
MsgBox "Error: " & Err.Number
End Sub
0
 
mbizupCommented:
Yes...
If you click on the far left of a line of code (at the very edge of the editor window), it will make a maroon line highlight that line of code.  When you run your code, it will "stop" at these lines and open the VBA editor, which will let you know that the preogram flow has reached that point.  You can then hover the mouse over your variables, and see thei values at that point in the code's execution.  

Hit F5 when you are ready to resume code execution.

You can unset break points by clicking to the left of that line of code again.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"but I don't know how to use breakpoints - is that something different?"

Similar, but no message.  The code stops at every breakpoint ... so can check values, single step through code.

In the vba editor window ... across from (most) any line of code - for example the Stop ... just click on the far left vertical bar in the window ... a red circle should appear ... indicating a break point.


mx
0
 
JA67Author Commented:
OK -- wow! I sure appreciate your patients! Anyway - I set a breakpoint at the Stop, and when I closed the form, no errors, and no stops anywhere. The code did not stop anywhere.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Guess we covered break points, lol.

mx
0
 
JA67Author Commented:
I am using Access 2007 and the way you apparently set breakpoints in 2007 is to click to the left, as you said, but then you go to "Debug" and click on "Toggle Breakpoint."
0
 
JA67Author Commented:
I set a breakpoint right above the Stop and closed the form and the code did stop at the breakpoint...

BreakPoint Here --> rstEditCase.Close

On Error GoTo EH
DoCmd.Close acForm, "frmUpdateExistingCase"
Stop
0
 
mbizupCommented:
>when I closed the form, no errors, and no stops anywhere. The code did not stop anywhere.

That means that the error handler is preventing the rest of the After_Update code from running, as it should.  If you are still having an  issue, it is produced by code elsewhere...
0
 
mbizupCommented:
Place breakpoints after that line...  That line is (and should be) run.  The error that it produces is what triggers the error handling that exits the Sub.
0
 
JA67Author Commented:
Well, maybe... But I need to stop all of the After_Update code because nearly all of it is running. All of the forms open and populate and I need for none of them to open. I need the After update at the very top of the code to know that the close button was clicked, and then Exit Sub. Can you think of any other way to do this?
0
 
mbizupCommented:
Place these lines at the very beginning of the event:
On Error GoTo EH
DoCmd.Close acForm, "frmUpdateExistingCase"
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
In the Declarations section of the form:
Dim fWeAreClosing as Boolean

In the OnClose event
fWeAreClosing = True

In the AfterUpdate event ... at the very top:

  If fWeAreClosing =True Exit Sub

mx
0
 
mbizupCommented:
Hmm... Placing those lines at the beginning, per my last post is a bad idea.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
What was EH ??

0
 
JA67Author Commented:
BINGO!!!!!!! Placing those lines at the top did the trick!!!!!

Only problem is now when I want the AfterUpdate code to run, by selecting a case number or typing it in and hitting enter, I get this error:  Error 2467....

0
 
mbizupCommented:
EH?

EH is the error handler I suggested.  All that is good info.  

>Placing those lines at the top did the trick!!!!!
> Error 2467....
:-(.  Thats why I came back and said that my suggestion was a bad one.  You need that form open for the rest of your code to populate the other forms when this one is closed "normally".
0
 
JA67Author Commented:
DatabaseMX -- I just tried your suggestion and it allows my to close the form without error but all AfterUpdate code runs as well. Boy -- that sure seems like it should have worked...
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
JA67:

did you try this:

In the Declarations section of the form:
Dim fWeAreClosing as Boolean

In the OnClose event
fWeAreClosing = True

In the AfterUpdate event ... at the very top:

  If fWeAreClosing =True Exit Sub

mx
0
 
JA67Author Commented:
I used a msgbox to see the value of fWeAreClosing when the code runs and it is False...
0
 
mbizupCommented:
You can post your DB, zipped, 4 meg max file size, and all sensitive data removed to:

www.ee-stuff.com


0
 
JA67Author Commented:
DatabaseMX - Yes, that's what I did, and that's what my previous post is referring to. If we can get the to be true, I'll be we'd have it fixed...
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"I used a msgbox to see the value of fWeAreClosing when the code runs and it is False..."

Did you put In the
fWeAreClosing = True

in the OnClose event of the form?  

I don't see how that could not trigger..

And now the dreaded error 2467 !!!  The saga continues ...

mx
0
 
JA67Author Commented:
Is it safe to say that the AfterUpdate event runs BEFORE the Onclose event?
0
 
JA67Author Commented:

Yes, I put in the  fWeAreClosing = True in the OnClose event.

Maybe the AfterUpdate runs first, before the Onclose....
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Is it safe to say that the AfterUpdate event runs BEFORE the Onclose event?"

Yes ... probably ...

What is in the BeforeUpdate event of the cbo or form - if anything?

mx
0
 
JA67Author Commented:
I don't have anything in the BeforeUpdate
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Did you upload per mbiz's suggestion ... that's the only way we are going to solve this at this point.

mx
0
 
JA67Author Commented:
Well, no I did not upload. This is a database for a law enforcement evidence room and I can't upload it. I greatly appreciate your help thus far though. I will leave this open for a bit in case either of you get a great idea you can share with me. If you don't come up with anything, I will still split the points between you. I'll check back here in an hour or so and then several times throughout the evening to see if you happen to have an answer or idea for me to try. Thank you both very much. I do believe you have both helped me greatly in the past. You folks are crazy smart and extremely helpful!!!!! I sure appreciate your time! did not implement correctly?

JA67
0
 
JA67Author Commented:
That last bit at the end of previous post got picked up when I grabbed the text out of Word where I spell checked it...
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Go ahead and upload ... I work for the CIA :-)

No problem on the help.  

One last thing ... IF ... you put a break point right on the AfterUpdate event ... then single step (F8) ... you can see where execution goes ... can you try that?

mx
0
 
JA67Author Commented:
DatabaseMX -- That's funny. ;-)

I did what you suggested (man that's cool!) and I stepped through the entire AfterUpdate code. When it got to the below code, it did skip over everything that came after it. Once it got to the Stop, the next thing it did was skip down to the end. Pretty cool. If I could just get it to skip from the very top.

On Error GoTo EH
DoCmd.Close acForm, "frmUpdateExistingCase"
Stop

Thanks,
JA67

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok ... well ... then remove the  On Error GoTo EH  and see where it goes.  

DOES it execute the DoCmd.Close below ???


DoCmd.Close acForm, "frmUpdateExistingCase"
Stop
0
 
mbizupCommented:
What other controls do you have on this form?
0
 
JA67Author Commented:
MX -- Yes, it does execute the DoCmd.Close.


mbizup --  I have text boxes and combo boxes for specifying various search criteria. The content of my cboOpenCase drop down will change based on selections from the other text boxes and drop downs on this form. This is a form where users find an existing case they want to open.
0
 
mbizupCommented:
Why not, similar to what Steve suggested early on, put this code into the click event of a command button (something like cmdOpenMyForms) instead of the afterupdate event of this combo?  This should eliminate that issue altogether.
0
 
mbizupCommented:
If you want to restrict the users from triggering this code until the combo box is updated, set the enabled property of the button to "False", and enable it in the After Update event of the combo.
0
 
JA67Author Commented:
mbizup -- I use to have this code in a button, but the person I built the app for wanted the case to open automatically upon selecting a case from the list and the only way I could figure out how to do that was to put the code in the AfterUpdate event.
0
 
JA67Author Commented:
It's getting dark, I have to get started cutting my grass, but I will keep checking this the rest of the evening. Thanks again! I'll be trying to think of a way to do it while cutting my yard.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"Yes, it does execute the DoCmd.Close.

Great. So ... if you continue to single step ... after that command executes ... where does it go?

mx
0
 
JA67Author Commented:
DatabaseMX -- I get the error right after it tries to close the form.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
So ... you are single stepping ... you hit the close line of code ... you hit F8 one more time ... and that's it" ... no more code is executed?  And you are closing the form you are in, right?

mx
0
 
mbizupCommented:
Do you have any code in the before update event of this combo, such as data validation code?  
Another work around might be to put all the code up to the Close Form statement in the Before Update event.  Place the code in such a way that it will not get run if data validatio checks (if there are any) in the Before Update event fail.

The close form statement should be the first executable line in the After Update event, and the rest of the code should remain there.

My reasoning is that we know closing the form at the start of the after update event works... to some extent.  You just need to have the form open to run the code before the close form statement.  Moving it to the before update event will ensure that it runs with the form open, and allow you to close the form at the beginning of the After Update event.

Like this:

Private Sub cboOpenCase_BeforeUpdate(cancel as integer)

' Your Data validation code here

If Data Validation fails  then
     Cancel = true
     Exit Sub
End IF
{dont worry about the code above if you do not have anything currently in the before update event}
DoCmd.OpenForm "frmEvidense"
DoCmd.OpenForm "frmChainOfCustody"
DoCmd.OpenForm "frmCaseInfo"
Forms![frmCaseInfo].[txtCaseNumber].Value = "Loading case number..."
Forms![frmCaseInfo].[txtCaseDate] = Now()
Forms![frmCaseInfo].[cboOffense].SetFocus

(Lots of code here...)

End Sub


Private Sub  cboOpenCase_AfterUpdate()
On Error GoTo EH                  '**** We know having this at the start of the after update event works... to some extent
DoCmd.Close acForm, "frmUpdateExistingCase"

(bunch of code here...)

Exit Sub
EH:
If Err.Number = 2501 Then Exit Sub
MsgBox "Error: " & Err.Number
End Sub
0
 
JA67Author Commented:
DatabaseMX -- Yes, I hit F8 and it generates an error when it executes the Close form code. If I have the EH routine in there, then I don't get the error, but of course all the forms still open.
0
 
JA67Author Commented:
mbizup --  Your suggestion generates the follow error:

"The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Exhibit-A from saving the data in the field."

Both of you have been overwhelmingly helpful. I need to let you off the hook. It seems like the behavior I'm trying to create is reasonable, but amazingly, as flexible as Access is, it does not have a good means of achieving this functionality...

I am going to split the points between you two. I am extremely impressed with you knowledge and willingness to help. Thank you both very much. I've learned from you, as I always do.

I am going to see if I can come up with a compromise on what the end user originally wanted. It will be fine. Thanks again!!!!

JA67
0
 
JA67Author Commented:
I tried to make one answer from each of you an assisted solution but somehow that didn't work, and MX - got the accepted solution. Anyhow -- thanks again. Hopefully next time I post here I won't have such a weir problem. ;-)

Thanks!!!
JA67
0
 
mbizupCommented:
No problem.  Unfortunately, there is nothing on the order of a Me.BuiltInClosedButtonWasClicked property, which would be exactly what you need.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
you are welcome.  sorry we couldn't do the upload ... probably could have dialed it in.  thanks for the kind words.

mx
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 37
  • 22
  • 21
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now