Type Mismatch Error When Loop Through the Field Names in a Query

I am getting a type mismatch error when attempting to loop through the fields in a recordset:

Public Function fnCompareTableHeaders(strFirstQuery As String, strSecondQuery As String) As Boolean
On Error GoTo ErrHandler

    Dim db As Database
    Dim rstFirstTable As dao.Recordset
    Dim rstSecondTable As dao.Recordset
    Dim strFirstString As String
    Dim strSecondString As String
    Dim fld As Field

    DoCmd.Hourglass True
    DoCmd.SetWarnings False
    
    Set db = CurrentDb
    'create recordsets for each table table to be compared
    Set rstFirstTable = db.OpenRecordset(strFirstQuery, dbOpenDynaset)
    Set rstSecondTable = db.OpenRecordset(strSecondQuery, dbOpenDynaset)
    
    'loop through first table and write field names to string
    For Each fld In rstFirstTable.Fields
        If strFirstString = "" Then
            strFirstString = fld.Name
        Else
            strFirstString = strFirstString & "|" & fld.Name
        End If
    Next
    
    'loop through second table and write field names to string
    For Each fld In rstSecondTable.Fields
        If strSecondString = "" Then
            strSecondString = fld.Name
        Else
            strSecondString = strSecondString & "|" & fld.Name
        End If
    Next
    
    'compare field name strings to determine same fields in each table
    If strFirstString = strSecondString Then
        fnCompareTableHeaders = True
    Else
        fnCompareTableHeaders = False
    End If
    
    
ExitFunct:
  DoCmd.Hourglass False
  DoCmd.SetWarnings True
  Set rstFirstTable = Nothing
  Set rstSecondTable = Nothing
  db.Close
  Exit Function
      
ErrHandler:
    MsgBox Err.Description, vbExclamation, Err.Number & " - Error Message"
    Err.Clear
    fnCompareTableHeaders = False
    Resume ExitFunct

End Function

The error specifically says 'Type Mismatch'. The variables passed to the function are two queries, both with the same data (in theory). This function is the start of validating is that they do indeed match by verifying with both tables have the same header names.

The error is specifically thrown at

For Each fld In rstFirstTable.Fields
        If strFirstString = "" Then
            strFirstString = fld.Name
        Else
            strFirstString = strFirstString & "|" & fld.Name
        End If
    Next
4

1 Answer

I think that @June7 did answer in comment.
If you reference in same project two libraries that both have same named objects such as 'field' in them, like DAO and ADO libraries, you will get a Type Mismatch.

See if you have, at Tools >> References:

Microsoft Office 14.0 Access database engine Object Library
Microsoft ActiveX Data Objects 6.1 Library

That is why it is good practice to fully "dot" qualify the objects, to avoid breaking code that is functioning, if later you refer to second library. Do a Dim fld As ADODB.Field and it seems that you will be good to go.

I had a lot of problems in an Office project that dealt with Word and Outlook after added support to Excel; Excel's object model has a lot of names that collide with Word's object model, like Range; I had to fully qualify all of them.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Alexander Ross

Alexander Ross

Gaming, Esports & Interactive Media Writer

Alexander Ross has covered the video game industry for a decade, writing deep dives on game design, esports tournaments, VR developments, and gaming culture.

Share this article
Twitter Facebook Pinterest