X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Unexpected "Type Mismatch" Errors    
post Jul 5 2004, 05:49 AM

UA Forum + Wiki Administrator
Posts: 14,079
Joined: 26-September 02
From: Sudbury, Ontario, Canada

When you are developing VBA code and suddenly begin to get Type Mismatch errors in sections of your code that previously compiled without a problem, a possible cause of the error is that you have begun to use a mixture of DAO and ADO references. There is a Microsoft Knowledge Base Article dealing with some of the issues here . The article suggests that changing the priority of the DAO reference so that it is higher than the ADO reference will solve the problem.
While it is true that changing the priority of the references will solve the problem, a preferable solution is to fully qualify recorset and other data related declarations. So instead of declaring
dim rs as recordset
dim fld as Field
write the declaration by prefixing the relevant object library name to the object name you are using
dim rs as DAO.Recordset
Dim fld as DAO.Recordset
Dim rs as ADODB.Recordset
Dim fld as ADODB.Field

To duplicate the problem, try this
Dim rs As DAO.Recordset   'qualified reference
Dim FldName As Field    'unqualfied reference
Dim Result As String    
Set rs = Screen.ActiveForm.Recordset.Clone    
With rs    
   For Each FldName In .Fields        
      Result = Result & "Name: " & FldName.Name & "Attributes: " & FldName.Attributes & "Type: " & FldName.Type & vbCrLf    
   Next FldName    
End With        
Debug.Print Result    
Set rs = Nothing

Make sure that you have set the ADO reference higher in priority than the DAO refence and compile the code. The compile should fail with a "Type Mismatch" error on the For Each FldName in Fields statement.
Now modified the fld declaration to make it qualified.
Dim fld as DAO.Field
Now, even though the reference priority has not changed, the code will now compile and run (providing that the active screen form is bound to a recordset.
Conclusion: always use fully qualified dao or ado references. The libraries have many identical object names. Qualifying the declaration ensures you are using the object you want and not a similarily named object in the 'wrong' library.
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    22nd October 2018 - 02:05 PM