Problem runing Access 2016 query Defs RRS feed

  • 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



    • Edited by Radio Fixer Wednesday, December 18, 2019 7:58 PM
    Wednesday, December 18, 2019 4:10 PM

All replies

  • 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)

    Wednesday, December 18, 2019 8:17 PM