Type mismatch error.

[mdub]

When I run this code on one database, it executes:

Dim sSQL As String
Dim rsCheck As Recordset
Dim myDB As Database
Dim sSelect As String

'If FrMenu.Value = Null Then
'MsgBox "Please select a report or form to view.", vbCritical
'End If
'cmdPOC.Tag = "No"
'On Error GoTo Reports_Preview_Err

If IsNull(txtPONum) = False Then
sSQL = sSQL & "([PONumber] = " & Chr(34) & [txtPONum] & Chr(34) &
") And "
End If
If IsNull(txtTechCode1) = False Then
sSQL = sSQL & "([TechCode1] = " & Chr(34) & [txtTechCode1] &
Chr(34) & ") And "
End If
If IsNull(txtTransDate) = False Then
sSQL = sSQL & "([TransDate] = # " & [txtTransDate] & " #) And "
End If
If IsNull(txtItem) = False Then
sSQL = sSQL & "([Item] = " & Chr(34) & [txtItem] & Chr(34) & ")
And "
End If

'Clean SQL Statement
If Right(sSQL, 4) = "And " Then
sSQL = Left(sSQL, Len(sSQL) - 4)
End If

'Check for records
sSelect = "SELECT tblTest.* " & _
"FROM tblTest"

If sSQL <> "" Then
sSelect = sSelect & " WHERE " & "(" & sSQL & ")"
End If

Set myDB = CodeDb()
Set rsCheck = myDB.OpenRecordset(sSelect)

If rsCheck.recordCount <> 0 Then
rsCheck.Close
DoCmd.Close
DoCmd.OpenForm "frmTest", acNormal, "", sSQL
'Me!tblPOLinesubform.Form.sfrmLawsonSearch.Filter = sSQL
'Me!tblPOLinesubform.Form.sfrmLawsonSearch.FilterOn = True
Else
rsCheck.Close
MsgBox "There are no records for the criteria you entered",
vbOKOnly + vbInformation
End If

but in another database, it gives me a "Type Mismatch" error, when it
reached this line of code.

Set rsCheck = myDB.OpenRecordset(sSelect)

Do I need to do something to the second database? Thanks in advance.

Mike

 

[John W. Vinson]

but in another database, it gives me a "Type Mismatch" error, when it
reached this line of code.

Set rsCheck = myDB.OpenRecordset(sSelect)

Do I need to do something to the second database? Thanks in advance.

Open the VBA editor and select Tools... References from the menu. Be sure that
Microsoft DAO 3.6 Object Library is checked. Both the DAO and the ADO
libraries have "Recordset" objects but they are *different kinds* of
Recordsets; the OpenRecordset method uses a DAO recordset.

You should have a line

Dim rsCheck As DAO.Recordset

to specify that it's that kind of recordset you want - or else UNCHECK the
ActiveX Data Objects reference in References, if you're not intentionally
using ADO.