Form closes before Unload event?

[mor10]

I occasionally get customers reporting the error "The expression you entered has a field, control, or property name that ... can't find" on line 2568. This is the Unload event for the form "Workorders", where I confirm if the record exist, before it continues down the event code or exits the event.

This indicates to me that the form is no longer loaded when it comes to this step. On the other hand, an error report the system generates reports that the form is open. I have never been able to replicate this error, so I wondered if this is something others have seen?

Private Sub Form_Unload(Cancel As Integer)
Dim I As Integer
Dim newword As String
Dim CheckWord As String
Dim WorkorderID As Long

2566 On Error GoTo HandleErr
2567 DoCmd.Echo False

2568 If Nz(Forms!Workorders!WorkorderID, 0) = 0 Then
2569 GoTo ExitHere
2570 Else
2571 WorkorderID = Forms!Workorders!WorkorderID
2572 End If

 

[Pat Hartman]

I'm not sure what the point of this code is. But you are correct, the recordset is gone by this time so you can't reference form controls.

 

[mor10]

So why does it work 99% of the time, and have for years?

 

[mor10]

I'm not sure what the point of this code is. But you are correct, the recordset is gone by this time so you can't reference form controls.

This is just a small part of the Unload event, but sine it fails on the first step that references the form, that is all I figured was needed to show.

 

[Pat Hartman]

What is the purpose of the code in the unload event? The part you posted makes no sense. There should be NO code in the unload event that relates to any data or form controls. If this is some kind of validation code, it belongs in the form's BeforeUpdate event so that it can prevent saving invalid data.

If it works intermittently, it may be a timing issue.

 

[mor10]

What is the purpose of the code in the unload event? The part you posted makes no sense. There should be NO code in the unload event that relates to any data or form controls. If this is some kind of validation code, it belongs in the form's BeforeUpdate event so that it can prevent saving invalid data.

If it works intermittently, it may be a timing issue.

I can obviously also run BeforeUpdate, but there are certain events in the networked system that can only happen after the form has closed, hence the final processing when the form is closing.

I don't understand why running the Unload then works, and have worked for years, thousands of times a day, for my customers.

 

[Pat Hartman]

You're going to have to ask microsoft.

but there are certain events in the networked system that can only happen after the form has closed, hence the final processing when the form is closing.

That also makes no sense. When you close a form, it is closed. Apparently you think it works except when it doesn't. I don't think I can help you. good luck

 

[mor10]

Since I can't reliably replicate the problem, it does not make sense to pay to talk to Microsoft. I have reported errors to them in the past, and after hours on the phone, they more or less ignore the damage a bug have on the Access users around the world.

Just to show you that Unload does see the form data, I have WorkorderID 5108 open in the form "Workorders".

Debug.Print Timer, "Unload Starts", Me.WorkorderID
Debug.Print Timer, "Unload Ends", Me.WorkorderID
Debug.Print Timer, "Close", Me.WorkorderID

Gives

50769.94 Unload start 5108
50770.32 Unload Ends 5108
50770.32 Close 25

As can be seen, the focus at "Close" is on a different form, since it is giving a different value back to the debug.print.

 

[Pat Hartman]

Since I have no idea what you are trying to accomplish with the Unload event, I don't have anything to offer. The timing seems to be the issue. Sometimes it works and sometimes it doesn't. If the timing is the issue, the answer is, this is NOT the correct event to use for this code which is what I said originally AND, more importantly, it is not a bug because MS never intended for you to use this event for whatever t is you are using it for.

 

[The_Doc_Man]

Question: Are you trying to capture an auto-number ID from the form? That's the only thing I can imagine from the snippet you showed us. If that is so, there are other ways to do that. For instance, in the AfterUpdate event of the form. If there is some kind of auto-navigate to a new record, then capture the ID before your auto-navigation. Otherwise, you should be able to grab the ID after the update event. Once that event routine exits, that WorkOrderID variable vanishes because it is declared by DIM inside the event routine's scope. So the variable's memory is reclaimed when you execute the EXIT SUB or the implied EXIT SUB that is part of END SUB.

I'm trying to remember if there is a timing issue, but basically, the relevant events are Unload(Cancel) >> Deactivate >> Close and that's it. You try to close the form. Access gives you the one chance in the Unload to stop from doing that. Otherwise, bye-bye baby.

Pat, since the Unload event can be cancelled, I don't think the controls go away until AFTER you return to Access itself from the Unload and didn't set Cancel = 1. In particular, if you DO cancel an Unload, nothing goes away. So there is persistence of controls for that long. However, if it makes it past Unload and into Deactivate, at THAT point the controls are clearly toast.

This statement bothers me: "there are certain events in the networked system that can only happen after the form has closed." That is true if and only if you have something else going on behind the scenes that we don't know about. Are you doing something special with an output device? Are you talking about an event triggered in a form outside of the one that was unloading? Pat has already expressed confusion and I'm not clear on this myself. Can you be more specific about these events?

 

[MarkK]

It is possible to have multiple instances of a form open, and it is therefore possible that Forms!Workorders does not return the same instance as the Form_Workorders instance you are currently unloading. Consider changing your Forms!Workorders references--if they occur in Form_Workorders--to use the Me keyword instead, like...

Private Sub Form_Unload(Cancel As Integer)
        Dim I As Integer
        Dim newword As String
        Dim CheckWord As String
        Dim WorkorderID As Long
    
2566     On Error GoTo HandleErr
2567     DoCmd.Echo False

2568     If Nz(Me.WorkorderID, 0) = 0 Then
2569         GoTo ExitHere
2570     Else
2571         WorkorderID = Me.WorkorderID
2572     End If

If you want to test for this case, you can use code like...

Private Sub Form_Unload(Cancel As Integer)
    If Not Forms!Workorders Is Me Then
        MsgBox "Yikes, Me is not the same instance as Forms!Workorders"
    End If
End Sub

I am not certain this is what is failing in your case, but it is a vulnerability in your code.