A little background: We have an Access 2002 based user interface to our
SQL Server 2000 database. The interface is a tabbed form with two of
the tabs containing a subform. Data is pulled from the SQL Server
using several stored procedures, one sproc for the main form and an
additional sproc for each subform. All data is pulled from the SQL
Server using ADO; the resulting client side (disconnected)
ADODB.Recordset is then applied to each of the forms/subforms Recordset
property.
Set Me.Recordset = Nothing
' build recordset
Dim rstUsers As ADODB.Recordset: Set rstUsers = New ADODB.Recordset
rstUsers.CursorLocation = adUseClient
rstUsers.CursorType = adOpenForwardOnly
rstUsers.LockType = adLockBatchOptimistic
' get sp results
rstUsers.Open "EXEC usp_SubForm1 @PKstrNum='" & strFindNum & "'", _
ClassCNN.cnn, _
ADODB.adOpenForwardOnly, _
ADODB.adLockBatchOptimistic
' Disconnect the recordset
rstUsers.ActiveConnection = Nothing
' Assign user rs to forms rs
Set Me.Recordset = rstUsers
Pulling the data from the server and displaying it in our Access db
front end works just fine, except one of the subforms in particular
will lose its recordset data at unspecified times. Me.Recordset
becomes Nothing when viewed in the watch window. This usually happens
when the user leaves the Access db open and starts using other
applications on the computer. When they come back after 5 minutes or
often times more, the subform loses its data and shows #Name? in all of
the text fields on the subform. The other forms seem unaffected by
this behavior.
This is very frustrating to track down as none of the users seem to
know how this happens. I've been trying to reproduce the issue for
the past 6 hours and have only had it happen to me twice.
I've looked the VBA code over and was unable to find any place in the
code that the subforms recordset property was getting set to nothing.
I also tried keeping a global reference to the ADO recordset rstUsers
for debugging purposes, and while me.Recordset was nothing, rstUsers
had all the data still.
Where is me.Recordset going?
