Issue with Runtime Type Mismatch (1 Viewer)

[spectrolab]

Hi All,

I have jst swapped to a mySQl backend (long story) and it seemed to be working fine, however the following code is giving me an error:

Private Sub cmdMakeJHCSV_Click()


Dim Accuracy As Single
    Dim AllowNeg As Boolean
    Dim FileName As String
    Dim i, J, TBLLoop, intRecs As Integer
    Dim sql, strColName As String
    Dim strResult As String
    Dim rst, rst2 As Recordset
    Dim db As Database
    If IsNull([cboRepSelect]) Then
        MsgBox "Please select a jobnumber", vbExclamation, "No job number selected"
        DoCmd.GoToControl "cboRepSelect"
        Exit Sub
    End If
    
    FileName = "Z:\Jack Hills CSV\" & Me.cboRepSelect & ".CSV"
    
        Set fs = CreateObject("Scripting.FileSystemObject")
        Set A = fs.CreateTextFile(FileName, True)
       


    Set db = CurrentDb
    Set rst = db.OpenRecordset("tmpMMLGCTable")
    rst.MoveLast
    J = rst.recordcount
'Write header lines

    
     
    'Line 1
        
        'Write result type column headings
       
        A.Write "Sample ,"
        [COLOR="Red"]Set rst2 = db.OpenRecordset("LU_ColumnHeadingsFull", dbOpenDynaset)[/COLOR]        rst2.MoveLast
        intRecs = rst2.recordcount
        rst2.MoveFirst
        
             For TBLLoop = 1 To intRecs - 1
                strColName = rst2![ColumnName]
                A.Write strColName & ","
                rst2.MoveNext
             Next TBLLoop
                strColName = rst2![ColumnName]
                A.WriteLine strColName
 
    'End line 1
    
     'Line 2
        A.Write "UNITS," 'The word UNITS LEFT  Justified
        'Write detection limits
        rst2.MoveFirst

             For TBLLoop = 1 To intRecs - 1
                strColName = rst2![units]
                A.Write strColName & ","
                rst2.MoveNext

             Next TBLLoop
                strColName = rst2![units]
                A.WriteLine strColName
                
    'End line 2
    
    
    
    'Write results
'******************************************************************************************

    rst.MoveFirst
    For i = 1 To J - 1
        
       
        A.Write rst.Fields(0) & ","
        rst2.MoveFirst
        For TBLLoop = 1 To intRecs - 1
           Accuracy = rst2!DETECTION
           AllowNeg = rst2!AllowNegs
           strResult = MakeResultJH(rst.Fields(TBLLoop), Accuracy, AllowNeg)
           A.Write strResult & ","
           rst2.MoveNext
        Next TBLLoop
           Accuracy = rst2!DETECTION
           AllowNeg = rst2!AllowNegs
           strResult = MakeResultJH(rst.Fields(intRecs), Accuracy, AllowNeg)
           A.WriteLine strResult & ","
           rst.MoveNext
   Next i
        A.Write rst.Fields(0) & ","
        rst2.MoveFirst
        For TBLLoop = 1 To intRecs - 1
           Accuracy = rst2!DETECTION
           AllowNeg = rst2!AllowNegs
           strResult = MakeResultJH(rst.Fields(TBLLoop), Accuracy, AllowNeg)
           A.Write strResult & ","
           rst2.MoveNext
        Next TBLLoop
           Accuracy = rst2!DETECTION
           AllowNeg = rst2!AllowNegs
           strResult = MakeResultJH(rst.Fields(intRecs), Accuracy, AllowNeg)
           A.WriteLine strResult & ","
   A.Close
    rst.Close
    rst2.Close
    Set db = Nothing
    MsgBox FileName & " created"
Exit_MakeSIF_Click:
    Exit Sub

Err_MakeSIF_Click:
    MsgBox Err.Description
   
End Sub

When it tries to bring up Recordset2 it give me a type mismatch error, it is a local table and the table is accessible, the table does not change during the process, as you can see it is just a lookup. Any ideas? Worked perfectly fine before the change and it is using local tables in access format, so I am kinda stumped!

 

[boblarson]

Try declaring your stuff explicitly as:

Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim rst2 As DAO.Recordset

The DAO part is important because with recordsets, depending on your Access version, it could be thinking it is an ADO recordset.

And, I think I've heard that you can't do in VBA what you can do in VB6:

Dim rst, rst2 As Recordset will declare rst as Variant and rst2 as Recordset so to make sure they are the right datatypes, put them each on their own line.

 

[spectrolab]

Thanks Bob,

That seemed to be the issue. Curious how it occurred in Access2007 only after converting the backend to mySQL.

Have added to your reputation.