Runtime error '13'

[Robin Chapple]

I am using XP and Access 2002

I have this code in OnCurrent event in an application:

Dim rst As Recordset, intCurRec As Integer, _
intTotalRec As Integer

Set rst = Me.RecordsetClone

With rst
If rst.RecordCount > 0 Then
.MoveLast
.MoveFirst
End If
End With

intCurRec = Me.CurrentRecord

If NewRecord = True Then
intTotalRec = rst.RecordCount + 1
Else
intTotalRec = rst.RecordCount
End If

I have copied it to a new application and i get

"Runtime error 13. Type mismatch"

What have I missed?

Thanks,

Robin Chapple

 

[Ken Snell]

You don't say which line of code is throwing the error, but I'll assume that
it's the Dim rst As Recordset and the Set rst = Me.RecordsetClone together
that are causing this.

In ACCESS 2002, DAO library isn't set as a reference by default; ADO is.
RecordsetClone is a DAO-type recordset. Thus, your Dim statement is setting
rst as an ADODB-type recordset, while the RecordsetClone is a DAO-type
recordset. Thus, data type mismatch.

You'll need to set a reference to DAO 3.6 library in the VBE ... and then
(unless you deselect the ADO library), disambiguate the Dim statement this
way:

Dim rst As DAO.Recordset

 

[Robin Chapple]

I had checked the application that was working and found the DAO 3.6
library was missing and ticked it in the list.

At that stage I no longer had an error but the data did not appear in
the box.

Now I have added your code:

Dim rst As DAO.Recordset in place of the original and at:

intTotalRec As Integer

I get error message:

Statement invalid outside type block.

Total VBA is now:

Private Sub Form_Current()

Dim rst As DAO.Recordset

intTotalRec As Integer

Set rst = Me.RecordsetClone

With rst
If rst.RecordCount > 0 Then
.MoveLast
.MoveFirst
End If
End With

intCurRec = Me.CurrentRecord

If NewRecord = True Then
intTotalRec = rst.RecordCount + 1
Else
intTotalRec = rst.RecordCount
End If

DoCmd.GoToControl "LastName"
DoCmd.Maximize

End Sub

Thanks for your help.

Robin Chapple

 

[Van T. Dinh]

I think you meant

Dim intTotalRec As Integer

 

[Robin Chapple]

Please remember that I am "Getting Started"

Is this what you meant? Because I now have no error message but no
data displayed.

Private Sub Form_Current()

Dim rst As DAO.Recordset

Dim intTotalRec As Integer

Set rst = Me.RecordsetClone

With rst
If rst.RecordCount > 0 Then
.MoveLast
.MoveFirst
End If
End With

intCurRec = Me.CurrentRecord

If NewRecord = True Then
intTotalRec = rst.RecordCount + 1
Else
intTotalRec = rst.RecordCount
End If

DoCmd.GoToControl "LastName"
DoCmd.Maximize

End Sub

Thanks,

Robin Chapple

 

[Van T. Dinh]

Well... I am not sure what you are trying to do with the posted code.

You seemed to define/assign intTotalRec and then didn't use the value later
in your code.

For the Form, the code simply sets Focus on the Control [LastName] then
maximizes the Form. Basically, the code didn't have any action/effect on
the Form's Recordset so I am not sure why you expected the code to change
the data displayed on the Form???

 

[Robin Chapple]

The plan is to replace the default record counter: "Record 1 of 2134"
and have it on the form rather than in the border.

I have this running in one application and copied the code to the neww
application. I have made changes as prompted by messages in the
thread.

Here is the code that works in another application:

Private Sub Form_Current()

Dim rst As Recordset, intCurRec As Integer, _
intTotalRec As Integer

Set rst = Me.RecordsetClone

With rst
If rst.RecordCount > 0 Then
.MoveLast
.MoveFirst
End If
End With

intCurRec = Me.CurrentRecord

If NewRecord = True Then
intTotalRec = rst.RecordCount + 1
Else
intTotalRec = rst.RecordCount
End If

Me![RecNum] = "Record " & intCurRec & " of " & intTotalRec

If IsNull([FirstEntered]) Then
[FirstEntered] = Date
End If

End Sub

Thanks for your help

 

[Van T. Dinh]

You left out the statement:

Me![RecNum] = "Record " & intCurRec & " of " & intTotalRec

in your new code (see your previous posts).

 

[Robin Chapple]

Thanks for better eyesight than mine. Even spectacles don't help.


"Peace, Health and Happiness in 2004"

 

[George Nicholson]

The CurrentRecord property returns a Long Integer, not an Integer. So:

Dim intCurRec As Integer .......
intCurRec = Me.CurrentRecord

Is technically a TypeMismatch. VBA will sometimes, but not always, coerce
values to fit if it can, so it is either "fixing" this for you or it may be
causing unpredictable results. It could certainly return a TypeMismatch
error under the right circumstances (or an Overflow error for sure if you
had more than 32,767 records).

Dim intCurRec As Long, intTotalRec As Long
would eliminate any doubt.