Results 1 to 11 of 11
  1. #1
    nightowl128 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2017
    Posts
    7

    Data Type Conversion Error on OpenRecordset

    I'm trying to open a recordset based on a query that uses TempVars. I get the Data Type Conversion Error and can't figure why. Any help would be appreciated.

    CODE:

    Sub test()
    iRet = UniqueCount("EmployeeID", "qryERRsForReviewBonusRound")
    MsgBox iRet
    End Sub

    Public Sub CreateTempVariables()
    TempVars.Add "FacLevel", ""
    End Sub

    Public Function UniqueCount(sFieldName As String, sDomain As String)


    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim prm As DAO.Parameter
    Dim strSql As String

    'Initialize to return Null on error.
    UniqueCount = Null
    'Set db = DBEngine(0)(0)

    'Count distinct values.
    Set qdf = CurrentDb.QueryDefs(sDomain)
    For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
    Next prm
    strSql = "SELECT " & sFieldName & " FROM " & sDomain & " GROUP BY " & sFieldName & ";"
    Set rs = qdf.OpenRecordset(strSql)
    If rs.RecordCount > 0& Then
    rs.MoveLast
    End If
    UniqueCount = rs.RecordCount 'Return the number of distinct records.
    rs.Close
    End Function

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What line throws the error? If the recordset, use this to see what the finished SQL looks like:

    BaldyWeb-Immediate window
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    why do you have an ampersand after your 0 check

    if rs.recordcount >0&

    and where is this bombing out

    You may want to change this to something like this to handle a count of 0 (your code as displayed does not do it)

    Code:
    if rs.recordcount = 0 then 
        rs.movelast
        uniquecount = rs.recordcount
        rs.close
        set rs = nothing
    else
        uniquecount = 0
    endif

  4. #4
    nightowl128 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2017
    Posts
    7
    The qdf.openrecordset(sSQL) is the line that is blowing up. The SQL resolves to a proper sql statment. The problem is the query uses TempVars. If I use another query that doesn't use TempVars it works fine.

  5. #5
    nightowl128 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2017
    Posts
    7
    The 0& is a cut a paste error.

  6. #6
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    can you supply a sample database so I don't have to re-create everything to test with.

  7. #7
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    it might have something to do with this code

    Code:
    Set qdf = CurrentDb.QueryDefs(sDomain)
        For Each prm In qdf.Parameters
            prm.Value = Eval(prm.Name)
        Next prm
    which doesn't seem to do anything, other than assign qdf to an existing query

  8. #8
    nightowl128 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2017
    Posts
    7

    Test Database attached

    Quote Originally Posted by rpeare View Post
    can you supply a sample database so I don't have to re-create everything to test with.

    I've attached a test database that is failing on the openrecordset line. Start the database and Open the test form to see the error. Thanks for the help.
    Attached Files Attached Files

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by Ajax View Post
    it might have something to do with this code

    Code:
    Set qdf = CurrentDb.QueryDefs(sDomain)
        For Each prm In qdf.Parameters
            prm.Value = Eval(prm.Name)
        Next prm
    which doesn't seem to do anything, other than assign qdf to an existing query

    With form parameters, that would resolve them and allow the recordset to be opened. I haven't used TempVars, but I wouldn't think this was needed with them...emphasis on "think".
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    nightowl128 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2017
    Posts
    7
    Quote Originally Posted by Ajax View Post
    it might have something to do with this code

    Code:
    Set qdf = CurrentDb.QueryDefs(sDomain)
        For Each prm In qdf.Parameters
            prm.Value = Eval(prm.Name)
        Next prm
    which doesn't seem to do anything, other than assign qdf to an existing query

    I'm new to access and I found this code to supposedly set the values of the TempVars prior to running the query. My understanding that when a query that has TempVars is executed from VBA this is required. If the query is run directly from the query navigation panel TempVars are handled differently and the query runs fine.

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    It was just a thought

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Data Type Conversion Error - WHY?
    By Datamulcher in forum Modules
    Replies: 2
    Last Post: 03-13-2017, 06:00 PM
  2. Replies: 4
    Last Post: 11-11-2013, 12:39 AM
  3. Replies: 7
    Last Post: 07-24-2013, 02:01 PM
  4. Type conversion error
    By corymw in forum Access
    Replies: 1
    Last Post: 07-25-2012, 11:55 AM
  5. Data Type Conversion Error
    By graviz in forum Forms
    Replies: 7
    Last Post: 06-04-2012, 11:34 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums