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...
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
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.