Type Mismatch

[John]

For some reason I am getting a type mistch error message when I try to open
the recordset. I even went to the immediate window to get the SQL statement
I am supplying and put it into a query and it worked fine. I am not sure
when it is not working. Any suggestions would be greatly appreciated.

Thanks in advance,

CODE:
Dim SQL2 As String
Dim SQL3 As String
Dim sql4 As String
Dim sql5 As String
Dim sql6 As String
Dim sql7 As String
Dim SqlStmt As String
Dim CurrBidNum As Double
Dim db As Database
Dim RD As Recordset
Dim SQL As String

Set db = CurrentDb()

CurrBidNum = [Forms]![Main]![SpecDefaults]![BidNum]

SQL2 = "SELECT BidNum FROM BidInfo "
SQL3 = "WHERE GCJob = '" & GCJob & "' "
sql4 = "AND Phase = '" & Phase & "' "
sql5 = "AND SpecGroup = '" & SpecGroup & "' "
sql6 = "ORDER BY BidNum DESC"
SqlStmt = SQL2 & SQL3 & sql4 & sql5 & sql6

Set RD = db.OpenRecordset(SqlStmt, dbOpenDynaset, dbSeeChanges)

If RD.Fields(0).Value = CurrBidNum Then
MsgBox ("Success")
Else
MsgBox ("Fail")
End If

 

[John Nurick]

Hi John,

If you have a reference to the ADODB library as well as to DAO,
Dim RD As Recordset
will refer to ADODB.Recordset or DAO.Recordset depending which is first
in the list, but
Set RD = db.OpenRecordset(SqlStmt, dbOpenDynaset, dbSeeChanges)
returns a DAO.Recordset.

Either get rid of the ADODB reference, or disambiguate the declaration:
Dim RD As DAO.Recordset
(or both).

For some reason I am getting a type mistch error message when I try to open
the recordset. I even went to the immediate window to get the SQL statement
I am supplying and put it into a query and it worked fine. I am not sure
when it is not working. Any suggestions would be greatly appreciated.

Thanks in advance,

CODE:
Dim SQL2 As String
Dim SQL3 As String
Dim sql4 As String
Dim sql5 As String
Dim sql6 As String
Dim sql7 As String
Dim SqlStmt As String
Dim CurrBidNum As Double
Dim db As Database
Dim RD As Recordset
Dim SQL As String

Set db = CurrentDb()

CurrBidNum = [Forms]![Main]![SpecDefaults]![BidNum]

SQL2 = "SELECT BidNum FROM BidInfo "
SQL3 = "WHERE GCJob = '" & GCJob & "' "
sql4 = "AND Phase = '" & Phase & "' "
sql5 = "AND SpecGroup = '" & SpecGroup & "' "
sql6 = "ORDER BY BidNum DESC"
SqlStmt = SQL2 & SQL3 & sql4 & sql5 & sql6

Set RD = db.OpenRecordset(SqlStmt, dbOpenDynaset, dbSeeChanges)

If RD.Fields(0).Value = CurrBidNum Then
MsgBox ("Success")
Else
MsgBox ("Fail")
End If