Results 1 to 12 of 12
  1. #1
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067

    VBA Function error for a report


    I have a couple of fields in a report that are populated via VBA


    Code:
    Public Function StarsBenchmark(ByRef msr As String, msryear As Long) As Double
        Dim strsql As String, rs As New ADODB.Recordset
        
        strsql = "Select Beg, End from IStar_Matrix where submeasureCode = """ & _
        msr & """ and star = 5 and Measureyear = " & msryear
    
        rs.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
        If Not rs.EOF Then
            If Left(msr, 3) = "PCR" Then
                StarsBenchmark = rs!high
            Else
                StarsBenchmark = rs!Low
            End If
        Else
            StarsBenchmark = 0
        End If
        rs.Close
        Set rs = Nothing
    End Function
    However Everytime I run the report I get a Run-Time error in the rs.open line in the Function above: Method 'Open' of object '_Recordset' failed.
    I've checked the SQL and it runs perfectly in the Query Analyzer. What am I missing?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    theres no reason to use vb to fill a report.
    EVERYTHING in the report can come from a query.
    including your IF statment on PCR.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'd use this method to check the finished SQL:

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

  4. #4
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067
    I don't use vb to fill a report I use vb to fill 2 fields on the report. The report is based on a query that returns a number of measures including the numerator (completed measures) and denominator (Total measures) and calculates a percentage. Then that percentage is compared to a table to determine which tier that person is in for that measure. 5 possible tiers.

  5. #5
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067
    pbaldy I did that and copied the result to the query analyzer where it functioned exactly as it's supposed to. That's why I posted here. I should have included that in my original post.

    Here's the sql produced by that function

    Code:
    Select Beg, End from IStar_Matrix where submeasureCode = "CDC - Nephropathy" and star = 5 and Measureyear = 2016

  6. #6
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067
    Click image for larger version. 

Name:	Istar_Matrix_table_structure.jpg 
Views:	11 
Size:	94.4 KB 
ID:	26656

    Here's the table structure for that table.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Your picture shows msryear to be a text field, so would need single quotes around it. The query designer is probably doing that for you.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sorry, I looked at the wrong field. I don't normally use ADO. Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067
    Don't think it would help. Access is only the front end all of the data is in SQL Server and is not available outside of our company.

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI Paul,

    Very strange.... I was going to create a dB & table in SQL Server Express to test and saw
    (See Post#6)
    \\192......\istar\istar_measures_iqho.accdb
    Table: IStar_Matrix
    Sure looks like the table is in an Access ACCDB. I would use DAO...........

    Code:
    Public Function StarsBenchmark(ByVal msr As String, msryear As Long) As Double
        Dim rs As DAO.Recordset
        Dim strsql As String
    
        strsql = "SELECT Beg, End"  '<<-- Note: "END" is a reserved word in Access
        strsql = strsql & " FROM IStar_Matrix"
        strsql = strsql & " WHERE submeasureCode = """ & msr & """"
        strsql = strsql & " AND star = 5"
        strsql = strsql & " AND Measureyear = " & msryear & ";"
        '    Debug.Print strsql
        
        Set rs = CurrentDb.OpenRecordset(strsql)
        If Not rs.EOF Then
            If Left(msr, 3) = "PCR" Then
                StarsBenchmark = rs!high   ' <<--"high" is not in the table/rs IStar_Matrix
            Else
                StarsBenchmark = rs!Low   ' <<--"Low" is not in the table/rs IStar_Matrix
            End If
        Else
            StarsBenchmark = 0
        End If
        rs.Close
        Set rs = Nothing
    End Function

  11. #11
    RayMilhon is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2011
    Location
    Southern California
    Posts
    1,067
    Thanks I did convert the code to use DAO and it is now working. Still don't know why the ADO wasn't working it's been working for months now all of a sudden it doesn't work anymore but just in those 2 functions. I use ADO Code in other reports and queries that still works fine. but it works now so ok.

  12. #12
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Glad you got it working.....

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

Similar Threads

  1. Function error
    By emir in forum Queries
    Replies: 3
    Last Post: 11-23-2015, 12:54 PM
  2. Replies: 8
    Last Post: 01-31-2014, 01:45 PM
  3. Replies: 7
    Last Post: 08-14-2013, 03:57 PM
  4. Error in Function
    By Alex Motilal in forum Programming
    Replies: 2
    Last Post: 10-11-2011, 06:30 AM
  5. aggregate error due to function
    By boutwater in forum Access
    Replies: 2
    Last Post: 09-26-2011, 03:39 PM

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