One thing I've always struggled with in Access is how to firstly keep form instances alive when instantiating from VBA (i.e. not using DoCmd), and secondly, how to access form-level variables after the form has been closed by the user.
For example, I want to open a form from code, have the user input some data, close the form, then I read that data before the form instance is disposed of.
I've got a handle on the form lifetime problem - that's solved thanks to some handy tips I found on the 'net. However, I'm still clueless as to how I can get my hands on form-level data once the form instance is closed by the user.
Here's some example code to illustrate:
Public Sub DemonstrateMyProblem()
Dim pfrmTemp As New Form_frmSomething, pstrSomeInput As String
pstrSomeInput = pfrmTemp.mstrSomeVariable
And in the form itself:
Dim mThisForm As Form_frmSomething, mstrSomeVariable As String
Private Sub Form_Load()
Set mThisForm = Me 'This is the trick I found for keeping a form instance alive
Private Sub SomeControl_AfterUpdate
mstrSomeVariable = SomeControl.Value
Private Sub Show()
Set mThisForm = New Form_frmTemp
mThisForm.Visible = True
Private Sub Form_Unload()
Set mThisForm = Nothing
So, as you can see, I want to show the form, it keeps itself alive, then I read some variable from it when it's closed. In the unload event I'm destroying the instance of the form so it would make sense that I can't read from it once it's been closed. However, even if I removed that and destroyed the form instance from the calling sub, then how do I pause code execution so the variable isn't read immediately (i.e. before the form is used and/or closed).
Any tips gratefully received!