SubForm Crashes

[gray]

HI

I'm having real horrors with my forms. I'm converting my forms to use ADODB RS's because I was plagued by problems with SQL Transactions in DAO (begintrans/commit etc).

I have Access 2007, using local-machine native Access Db (but want to go FE/BE eventually). It is a .MDB project. All my forms are opened with optimistic locks and static, client side cursors. Actually, as I understand it, the latter two options are the only ones available for native Access?

So my main and subs are all opened thus:-

Option Compare Database
Public Addrs_ABCD_rstADO As New ADODB.Recordset
Public Addrs_ABCD_Cnn As ADODB.Connection
 
SQLLine = "SELECT TBL1.* FROM Addrs_To_Cntcts_Home AS TBL1 " _
            & "WHERE (TBL1.Deleted=False Or TBL1.Deleted=True)"
 
Set Me.Form.Recordset = Nothing
 
Me.Form.OnCurrent = "[Event Procedure]"
Me.Parent.Form.OnCurrent = "[Event Procedure]"
 
If Addrs_ABCD_rstADO.State = adStateOpen Then
    Addrs_ABCD_rstADO.Close
End If
 
Set Addrs_ABCD_rstADO = Nothing
 
If Not Addrs_ABCD_Cnn Is Nothing Then
    Addrs_ABCD_Cnn.Close
    Set Addrs_ABCD_Cnn = Nothing
End If
 
Set Addrs_ABCD_Cnn = CurrentProject.Connection
 
With Addrs_ABCD_rstADO
    Set .ActiveConnection = Addrs_ABCD_Cnn
        .Source = SQLLine
        .LockType = adLockOptimistic
        .CursorType = adOpenStatic
        .CursorLocation = adUseClient
        .Open
End With
 
Set Me.Form.Recordset = Addrs_ABCD_rstADO
Me.Form.OnCurrent = "[Event Procedure]"
Me.Parent.Form.OnCurrent = "[Event Procedure]"

The forms all hav this in their unload events:-

Private Sub Form_Unload(Cancel As Integer)
 
'IF RS NOT COMMITED ASSUME IT'S A FAILURE AND ROLLBACK
If Addrs_ABCD_Trx_Begun Then
   Addrs_ABCD_Cnn.RollbackTrans
   Addrs_ABCD_Trx_Begun = False
End If
 
If Not Addrs_ABCD_rstADO Is Nothing Then
   If Addrs_ABCD_rstADO.State = adStateOpen Then
       Addrs_ABCD_rstADO.Close
       Set Addrs_ABCD_rstADO = Nothing
   End If
End If
 
If Not Addrs_ABCD_Cnn Is Nothing Then
     Addrs_ABCD_Cnn.Close
     Set Addrs_ABCD_Cnn = Nothing
End If
End Sub

The suffix _ABCD is changed per form to identify RSs and CNXs more easily. The example is a subform... the parent references are removed for the mainform.

I have custom buttons for new, edit, delete. When New or Edit is pressed, the relevat subform is wrapped in a BeginTrans/Commit/Rollback scheme. This all works well. When a new record has been added the main-form is re-queried with "Set me.form.recordset = me.form.recordset". This needs to be done due to the static cursor... it picks up the new record(s).

This all worked well with just the main and 1st subform. Then I introduced my 2nd subform. When "Set me.form.recordset = me.form.recordset" is called, it unloads the subforms and then reloads them.
At the point where my 2nd subform is re-loaded it fails
at this line in it's open event:-

Set Me.Form.Recordset = Addrs_ABCD_rstADO

In fact Access crashes........ What is going wrong I wonder? I know there seems to be debate on using
CurrentProject.Connection
or
CurrentProject.AccessConnection

But got to admit I don;t really understand the difference?

Desperate to get to the bottom of this, I've spent weeks and weeks on it...

Thanks for your assistance....

 

[gray]

Think I may have solved this.... I changed the cursor type to 'adopenkeyset' and my connections to:-
CurrentProject.AccessConnection

I also set the Rs and CNx to Nothing after setting the form recordset....i.e.

Set Me.Form.Recordset = Addrs_ABCD_rstADO
Set Addrs_ABCD_rstADO = Nothing
Set Addrs_ABCD_Cnn = Nothing

No more crashes! Fingers crossed....!