Problem runing Access 2016 query Defs

Question



Hello, I am not a programmer I appreciate assistant with this. Using access 2016 and running several query to get the data. Setting ADO as a reference. Trying to get just one field 2 digit number and show it on a open form txtReviewScore.

Dim dbs             As Database    'current database
Dim rst             As Recordset   'holds query Defs
Dim qdf             As QueryDef
Dim sQueryScore     As String

Set dbs = CurrentDb()

'DoCmd.OpenQuery "qryPpsScore", acViewNormal, acReadOnly

'run query Defs
Set qdf = dbs.QueryDefs("qryPpsScore")


sQueryScore = "SELECT qryPpsTotal.Score FROM qryPpsTotal;"

Set rst = qdf.OpenRecordset(sQueryScore)
'MsgBox sQueryScore

txtReviewScore = rst.Fields("Score").Value



---

JayZ

Answer 1

The code uses DAO, not ADO. So clear the reference to the Microsoft ActiveX Data Objects library, and set a reference to the Microsoft Office 16.0 Access Database Engine Object Library instead.

But apart from that, you set a variable sQueryScore, then use sQueryOnsite.

Try this version:

    Dim dbs             As DAO.Database
    Dim rst             As DAO.Recordset
    Dim sQueryScore     As String

    Set dbs = CurrentDb
    sQueryScore = "SELECT Convert FROM qryPpsTotal;"
    Set rst = dbs.OpenRecordset(sQueryScore)
    txtReviewScore = rst.Fields("Convert").Value

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)