Error Accesing database 1

[radubalmus]

Hy i keep getting this error "Data type mismatch in criteria expression. (Error 3464)" on the red line. have any ideea why?

Public Sub initPCR()
Dim ordersheet1 As Worksheet
Dim query As String
Dim pcrNr, paragraphId, samples, partsvizual, partsdimensional, partstests As Integer
Dim table, partname, partnoindex, editieData, desen, pozitie, descriere, mijlocControl, observatii, test, caract, SPCs As String
Dim partno As String

Set ordersheet1 = Application.ActiveWorkbook.Worksheets("New Incoming Test Order")

'Clear contents
ordersheet1.Range(Cells(14, 1), Cells(99, 12)).clearContents

'get entries
table = "PCR"

    If ordersheet1.Range("C9").Text <> "" Then
        partno = ordersheet1.Range("C9").Text

        'Assemble the query
        query = "SELECT partnoindex FROM " + table + " WHERE partno=" + partno 
        
        'Get results
        Dim rs As DAO.Recordset
        Dim count As Integer
        Set rs = Module6.selectFromDB(query)
            ordersheet1.Range("E9") = rs.Fields(0).Value
            ordersheet1.Range("H9") = rs.Fields(1).Value
            ordersheet1.Range("K5") = rs.Fields(2).Value
            ordersheet1.Range("K7") = rs.Fields(3).Value
            ordersheet1.Range("K9") = rs.Fields(4).Value
            ordersheet1.Range("K11") = rs.Fields(5).Value
            ordersheet1.Range("B11") = rs.Fields(6).Value
            ordersheet1.Range("D11") = rs.Fields(7).Value
            ordersheet1.Range("H11") = rs.Fields(8).Value
            count = 14
            Do Until rs.EOF
                ordersheet1.Cells(count, 1) = rs.Fields(9).Value
                ordersheet1.Cells(count, 2) = rs.Fields(10).Value
                ordersheet1.Cells(count, 3) = rs.Fields(11).Value
                ordersheet1.Cells(count, 4) = rs.Fields(12).Value
                ordersheet1.Cells(count, 8) = rs.Fields(13).Value
                ordersheet1.Cells(count, 9) = rs.Fields(14).Value
                ordersheet1.Cells(count, 10) = rs.Fields(15).Value
                ordersheet1.Cells(count, 11) = rs.Fields(16).Value
                ordersheet1.Cells(count, 12) = rs.Fields(17).Value
                rs.MoveNext
                count = count + 1
            Loop
            rs.Close
    End If
End Sub

Public Function selectFromDB(query As String) As DAO.Recordset
Dim db As DAO.Database
Dim condb As Connection
Dim wrkMain As DAO.Workspace
Dim dbPath As String
Dim qdf As QueryDef
dbPath = getDBPath
Set wrkMain = CreateWorkspace("", "", "", dbUseODBC)
Set db = DAO.DBEngine.OpenDatabase(dbPath)
Set qdf = db.CreateQueryDef("", query)
[COLOR=red]Set selectFromDB = qdf.OpenRecordset(dbOpenSnapshot)[/color]
End Function

There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[Remou]

There seems to be a mux up. Do you want:

[tt]qdf.SQL="Select * From tblTable"[/tt]

Or

[tt]Dim rs As DAO.Recordset
Set db=CurrentDb
Set rs = db.OpenRecordset("tblTable") [/tt]

 

[radubalmus]

???
i want to write data in execel from acess using a query like in function i posted

i am using this similar code in another part of my project and it is working.., but here i am getting this error and i don't see from where

There are simple solutions for almost every problem! The hard part is to see them!!!!

 

[Remou]

Sorry. Is partno a text field? If so, you need single quotes:

[tt]query = "SELECT partnoindex FROM " + table + " WHERE partno='" + partno + "'"[/tt]

 

[radubalmus]

thanks a lot remou
didn't saw that allthough i used it before..

i will give you a star(by the way: to them use for anything)

There are simple solutions for almost every problem! The hard part is to see them!!!!