Type Mismatch Error in SQL Code

[kermit5]

I am getting error 13 "Type Mismatch" in the following code when I run the code:

set rs = db.openrecordset(strSQL)

Any idea why?

Here is my code:

On Error GoTo hypSupplyProposal_Click_Err

    Dim db As Database
    Dim rs As Recordset
    Dim strSQL, stDocName As String
    Dim intBidID As Long
    Dim Awarded As Boolean
    
    Awarded = False
    intBidID = Forms![Bid - Master Form]![Master Project ID]
    strSQL = "SELECT * FROM [Bid - Bid Items] " & _
                "WHERE [Master Bid ID] = " & intBidID & _
                " AND [Spec Section ID] Not Like '*Installation'"
    Debug.Print strSQL
    'check for supply sections
    Set db = CurrentDb
    [color=crimson]Set rs = db.OpenRecordset(strSQL)[/color]
    rs.MoveFirst
    
    Forms![Bid - Master Form].Requery
    DoCmd.RunCommand acCmdSpelling
    DoCmd.Minimize
    DoCmd.OpenReport "Bid - Supply Proposal", acPreview, "", "[Project Name]=[Forms]![Bid - Master Form]![Name]"

hypSupplyProposal_Click_Exit:
    Exit Sub
hypSupplyProposal_Click_Err:
    If Err.Number = 3021 Then           'no records found
        MsgBox "There are no supply sections currently bid for this project.", _
            vbOKOnly + vbCritical, "No Supply"
            Resume hypSupplyProposal_Click_Exit
    Else
        MsgBox "Error: " & Err.Number & vbCrLf & Err.Description
        Resume hypSupplyProposal_Click_Exit
    End If

Thanks is advance!

 

[Mile-O]

    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String, stDocName As String
    Dim intBidID As Long
    Dim Awarded As Boolean

You'll need to set a reference to DAO in your References collection, too.

So:

Open a module, select Tools -> References
Check Microsoft Data Access Objects 3.6
Move its priority above Microsoft ActiveX Data Objects

The code whould work.


One other note: the line: Dim intBidID As Long is a little dodgy. Dodgy, only, in that you have used a prefix used for Integers and not Long Integers. It won't cause any problems though; just an observation.

 

[dcx693]

Mile, I think the user must already have a DAO reference, else this line:
Dim db As Database
would not even compile.

I think you're onto something here:

Move its priority above Microsoft ActiveX Data Objects

If the user had both DAO and ADO selected and had ADO higher up in the list, then I think we might get the Dim db As Database to compile, but the set rs = db.openrecordset(strSQL) line to fail.

I'd suggest that the user remove the reference to ADO unless it's needed. It's confusing (and introduces overhead) to mix the two in the same database.

 

[kermit5]

Thanks. I appreciate the advice. I am still learning to effectively use the references. The advice you gave did the trick.