DoCmd.Quit, Dynamic Array, Form_Unload, Variable Cleanup

[Wayne Pearson]

Hi,





I have a simple form with one Exit button, cmdExit, and three Subs -
cmdExit_Click, Form_Load, and Form_Unload. The Form also has a member
variable, m_Numbers, that is a dynamic array of type Long.



m_Numbers is dimensioned to be 2 elements long and then populated in
Form_Load. m_Numbers is checked in cmdExit_Click to ensure that it has its
initial values. After the DoCmd.Quit in cmdExit_Click, Form_Unload is
called. Form_Unload attempts to access m_Numbers and gets a subscript out
of range error. It would appear that m_Numbers is already destroyed before
Form_Unload is called.



I would be interested in any comments on what would cause this behavior.





Thanks,



Wayne Pearson





Form code follows .





Option Compare Database

Option Explicit





'm_Numbers is a dynamic array of longs

Dim m_Numbers() As Long







Private Sub cmdExit_Click()

Dim idx As Long





On Error GoTo cmdExitClick_Err



'm_Numbers has values here

For idx = LBound(m_Numbers) To UBound(m_Numbers)

Debug.Print "cmdExit Before Quit " & CStr(m_Numbers(idx))

Next idx



DoCmd.Quit



cmdExitClick_Exit:

On Error GoTo 0

Exit Sub



cmdExitClick_Err:

MsgBox Err.Description

Resume cmdExitClick_Exit

End Sub





Private Sub Form_Load()

Dim idx As Long





On Error GoTo FormLoad_Err



'm_Numbers dimensioned to 2 elements in Form_Load

ReDim m_Numbers(0 To 1)



'm_Numbers gets populated here

For idx = LBound(m_Numbers) To UBound(m_Numbers)

m_Numbers(idx) = idx

Next idx



FormLoad_Exit:

On Error GoTo 0

Exit Sub



FormLoad_Err:

MsgBox Err.Description

Resume FormLoad_Exit

End Sub





Private Sub Form_Unload(Cancel As Integer)

Dim idx As Long





On Error GoTo FormUnload_Err



'Error here - Subscript out of range - why?

For idx = LBound(m_Numbers) To UBound(m_Numbers)

Debug.Print "Form Unload " & CStr(m_Numbers(idx))

Next idx



FormUnload_Exit:

On Error GoTo 0

Exit Sub



FormUnload_Err:

MsgBox Err.Description

Resume FormUnload_Exit

End Sub

 

[Douglas J. Steele]

It's difficult for anyone to comment without seeing the code in question.

 

[Wayne Pearson]

Hi,


I included the Form code in the original post just below my name. Guess I
should have placed it above - Sorry...

 

[Douglas J. Steele]

Ooops, so you did. Sorry about that: I obviously didn't scroll down far
enough.

I think the problem is caused by the fact that you're trying to shut down
Access in your cmdExit_Click event. If you replace DoCmd.Quit with
DoCmd.Close (so that just the form closes), it works fine. When Access is
shutting down, I don't believe you can make any assumptions about what order
things happen.

Try putting the DoCmd.Quit in the Form_Unload or Form_Close event.