Access VBA - Reference to Subform disappears after database has been open a while

Question

I have a Microsoft Access 2013 database that has a form with a hidden subform. There is a text box for a search value and a button to click to search. There is a subform on the form that is hidden until the search button is clicked. Once that button is clicked, the subform becomes visible and displays the results of the search.

This process works fine normally. However, last night I left the database open with the form displayed in its initial state with the subform hidden. I entered a search value and clicked search and I get an "object does not exist" error. I traced the VBA code and it fails on the first line that references the subform: SubForm.Form.RecordSource = "(some sql text)"

I read on one forum that Access can lose the reference to a hidden control after a period of time. I assume that is the case here, since when I reload the form, it works fine.

This is a database that may stay open on user's computers overnight so I would like to prevent this problem if possible. I'm wondering if there is a way to write some code that checks to see if the subform is still referenced in memory, and if it is not, reconnect the reference. Not sure if this is even possible in VBA. Something like:

If Not SubForm.IsLoaded Then
    SubForm.Reload
End If 
SubForm.Form.RecordSource = "(some sql text)"

Does anything like this exist in VBA...or is there a different way to go about solving this problem?

It's unclear from your code if you're referencing the actual form or a form variable that you've set to your subform. Is Subform a variable that you've set elsewhere in your code to point to your actual form.subform? — dbmitch

DanielG DanielG · Accepted Answer · 2016-09-22T19:16:05

This is really odd and I have never seen anything like this. Yet, it is happening to you.

One crude approach would be to handle with error handling. When the user clicks the Search button, add error handling that closes, reopens the form, then applies the search using the OpenArgs data in the OpenForm method. Rough code from memory, but something like:

Sub cmdSearch_Click()

Dim sSQL As String
sSQL = "MY SQL STRING WITH PARAMS"

On Error Goto SubForm_Lost_err

Subform.Form.Recordsource = sSQL 

Exit Sub

SubForm_Lost_err:

   DoCmd.Close acForm, Me.Name
   DoCmd.OpenForm "TheCurrentFormName", , , , , , sSQL 

End Sub

Then, Add OnLoad Event to handle if OpenArgs passed in

Private Sub Form_Load()
If Nz(Me.OpenArgs, "") <> "" Then
    Subform.Form.Recordsource = Me.OpenArgs
    Me.ParamsTextBox = "TheParams" 'Cosmetic so it looks right.
End If
End Sub

I would also try getting a handle on the form in different ways. I have no clue why it would lose the handle, but try things like this:

Forms("MyForm").subform.form.recordsource = "The SQL"
Me!subform.form.recordsource  = "The SQL"
Screen.ActiveForm.subform.form.recordsource = "The SQL"

etc.

Access VBA - Reference to Subform disappears after database has been open a while

1 Answers