Share via

I am getting a data type mismatch error when compiling my VBA code

Michael Burden 40 Reputation points
2025-08-27T13:19:33.76+00:00

When I try to compile my VBA Code for a sub-procedure, I get a Data Type Mismatch error when I get to the line:

lRecCounter = rsRecordset.RecordCount

I originally had lRecordCount identified as Inter. When that did not work, I did some research and read that the RecordCount Property returned a Long type.

I tried changing the identification of lRecordCount to Long and have the same issue.

Can anyone enlighten me as to how to correct this error?

I am including the Code Below

Public inMemberLookup As Integer

Public intMemberAdd As Integer

Const BUS_LOGIC = "modBusinessLogic"

Private Declare PtrSafe Function GetOpenFileName Lib _

      "comdlg32.dll" Alias _

      "GetOpenFileNameA" (pOpenFilename As OPENFILENAME) _

      As Long

Private Type OPENFILENAME

 lStructSize As Long

 hwndOwner As Long

 hInstance As Long

 lpstrFilter As String

 lpstrCustomFilter As String

 nMaxCustFilter As Long

 nFilterIndex As Long

 lpstrFile As String

 nMaxFile As Long

 lpstrFileTitle As String

 nMaxFileTitle As Long

 lpstrInitialDir As String

 lpstrTitle As String

 flags As Long

 nfileOffset As Integer

 nFileExtension As Integer

 lpstrDefExt As String

 lCustData As Long

 lpfnHook As Long

 lpTemplateName As String

End Type

Public Declare PtrSafe Function ShellExecute _

 Lib "shell32.dll" _

 Alias "ShellExecuteA" ( _

 ByVal hwnd As Long, _

 ByVal lpOperation As String, _

 ByVal lpFile As String, _

 ByVal lpParameters As String, _

 ByVal lpDirectory As String, _

 ByVal nShowCmd As Long) _

 As Long

Sub MoveToLastRecord(lRecCounter As Long, rsRecordset As _

 ADODB.Recordset, objObject As Object, _

 blnAddMode As Boolean)

 

 'Call the Error Handler sub-routine if encountering error

 On Error GoTo HandleError

 

 'Move to the last record in the local disconnected

 'recordset

 If Not rsRecordset.BOF And Not rsRecordset.EOF Then

      rsRecordset.MoveLast

      **lRecCounter = rsRecordset.RecordCount**

                

      'Add code to populate object with new current

      'Record

      objObject.PopulatePropertiesFromRecordset _

      rsRecordset

      

      blnAddMode = False

 End If

HandleError:

 GeneralErrorHandler Err.Number, Err.Description, _

 BUS_LOGIC, "MovetoLastRecord"

 Exit Sub

End Sub

Microsoft 365 and Office | Access | For home | Windows
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Flora-T 5,160 Reputation points Microsoft External Staff Moderator
    2025-08-27T14:34:26.9666667+00:00

    Hi Michael Burden

    Thank you for reaching out to the Microsoft Q&A community and sharing your issue in detail.  

    Based on the code you've posted and description of your error, “Type Mismatch” error may happen when the database provider doesn’t fully support the RecordCount property for the specific cursor type you’re using. In many cases, RecordCount might return -1, which usually works with a Long variable. However, the fact that you’re seeing this error specifically on the RecordCount line, especially after a MoveLast, suggests that the combination of provider and cursor type may not be compatible with this property at all. 

    Typically, this issue arises when the recordset is opened with the default cursor settings, which may not support certain operations like MoveLast or provide a reliable record count. For example, a forward-only cursor doesn’t allow moving backwards or jumping to the last record, and this could lead to unexpected errors. 

    To help ensure that RecordCount returns a valid number, you might consider explicitly setting the cursor to a client-side static cursor before opening your recordset. This approach is often more reliable, as it allows ADO to manage the records locally and provide accurate counts.

    rsRecordset.CursorLocation = adUseClient
rsRecordset.CursorType = adOpenStatic
rsRecordset.Open "SELECT * FROM YourTable", YourConnection

    With these settings, RecordCount is typically available immediately after opening the recordset. Even so, it’s still a good idea to write your code defensively, just in case the environment behaves differently than expected. Your use of MoveLast makes sense for navigating to the end of the recordset, and with a client-side cursor.

    Sub MoveToLastRecord(lRecCounter As Long, rsRecordset As ADODB.Recordset, _ objObject As Object, blnAddMode As Boolean)

    I hope this may help clarify the situation and give you a path forward. I truly appreciate your patience and understanding.

    Please understand that our initial response may not always resolve the issue immediately. However, with your further updates and more detailed information, we can work together to find a resolution.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.