Access 2007 Recordset problem.

Question

I am trying to migrate Access 2003 (running fine) to Access 2007. The code written in VBA creates some issues. Like I have one main form and 2 subform. In main form recordsource set set by a Dynamic query but based on main form data when I am trying to set the Recordset of subform then access 2007 crash & close itself. Could you please help any one to find out why this occur in access 2007.

Public Sub LoadRecord(strRecId As String)

On Error Resume Next

Dim ctl As Access.Control
Dim strsql As String
Dim strID As String
Dim rs As ADODB.Recordset

    strsql = "SELECT * from vwContractsMainForm WHERE ID = '" & strRecId & "'"

    Call OLEDBConnect 
    Set rs = New ADODB.Recordset
    With rs
        Set .ActiveConnection = cnn
        .Source = strsql
        .CacheSize = 1
        .LockType = adLockOptimistic
        .CursorType = adOpenKeyset
        .CursorLocation = adUseServer
        .Open
    End With

    'Set the first sub form's Recordset property to the ADO recordset 
    Set Me.Recordset = rs   '-------- this part creates the problem.
    Me.UniqueTable = "tbl_contracts"
    Set rs = Nothing
    Set cnn = Nothing

Answer 1

I'll be darned. I learned something new about Access this month. I never realized you could change the recordset of a form like that.

I've never use an On Error Resume Next as you might be, likely are, hiding an error message on a line previous to the line which crashes.

Stupid question. Is strRecId a numeric or string field in the query? Are the quotes valid?

strRecoID and cnn are not Dimmed in your code. I see strID is dimmed so that's likely the field you meant to use. Please add Option Explicit as the top or second line of your module. Then go into every module and add those lines. Then do a compile and see how many other errors happen.

Also go into the VBA Editor then Tools >> Options >> and ensure the Require Variable Declaration box is checked.

Also to you and all lurkers. Please send in the crash information to Microsoft. While they, of course, don't like getting crash information, they do treat crashes as a very high priority when it comes to Service Packs.