On running code now Getting error 13: type mismatch,

[justagrunt]

Hi,
I have copied and pasted the following code to a database.
When the switch board opens it calls up a routine called
OpenReminders to check if there are any current diary entries. The original code ran well and seems to be from I guess around 1997 , I have imported to a A2K database.
The running of the code gives me Error 13: Type mismatch.

Public Sub OpenReminders()
' Opens the Reminders form in dialog mode if there
' are any entries in the tickler table

On Error GoTo HandleErr

Dim db As Database
Dim rs As Recordset
Dim strSQL As String

strSQL = "select * from Tickler where TicklerDate = Date() " _
& "And TicklerDate=Date() And TicklerText Is Not Null " _
& "And Not TicklerText=''"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot, dbForwardOnly)
If rs.RecordCount > 0 Then
DoCmd.OpenForm "Reminders", , , "TicklerDate = Date()", acFormReadOnly, acDialog
End If

ExitHere:
On Error Resume Next
rs.close
Exit Sub

HandleErr:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description, _
, "OpenReminders()"
End Select
Resume ExitHere

End Sub

When the Reminders Form opens the VB code is as follows.

Private Sub Form_Open(Cancel As Integer)
' Check to see if there are any reminders

On Error Resume Next

Dim rs As Recordset
Set rs = Me.RecordsetClone
If rs.RecordCount = 0 Then
Me.Caption = "There are no reminders set"
End If

End Sub


Any help appreciated.
Kind Regards.

 

[PaulBricker]

You probably have the Microsoft DAO 3.6 Object library already selected. Now you need to change a few of your Dim statements.
Dim db as Database
becomes
Dim db as DAO.Database

Dim rs as Recordset
becomes
Dim rs as DAO.Recordset

That should take care of the error.

Paul

 

[MasterPO]

Since this was written in 1997, it's code which uses DAO as the default for data access. A2K and beyond uses ADO as the default. A2K is assuming your declarations are ADO objects and generating your error when it attempts to run.

So, when you move code from Access97 to A2K, you need to do one of two things:
1. Create a reference to DAO (Tools/References/Microsoft DAO 3.51 Object Library), then explicitly declare your objects as DAO objects:
Dim db as DAO.Database
Dim rs as DAO.Recordset
etc.

2. Rewrite the code to use an ADO recordset. It's worth investing some time to learn this method, since DAO has been all but abandoned in favor of ADO going forward.