Results 1 to 7 of 7
  1. #1
    kthorson16 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2016
    Posts
    3

    Select Distinct VBA issues when trying to loop and create a report for each specific type of record

    I am having an issue with my code.
    My goal is the following.....I have 4900 provider_group_id's that need to have an individual report generated based on the data located in the table 'ACV_Final_NoPay_2016. that table was used to generate queries, workhorse and reportshorse which workhorse was used to create a report.



    I have created the following VBA macro to generate the reports but what I am running into is needing it to loop thru and create a report for each provider_group_id within my query but I keep getting any error when I try and use 'Select Distinct' Can anyone please assist with my code?

    Code:
    Option Compare Database
    Sub getprovider_group_id()
    Dim bGood As Boolean
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb
    rst = selectdistinct("provider_group_id")
    from workhorse
    OpenRecordset rst
    With rst
    Do Until .EOF
    bGood = makereport("provider_group_id" & .Fields(0)).MoveNext
    Loop
    Close
    End With
    End Function
    
    Function makereport(provider_group_id As Variant) As Boolean
        Dim qdfsql As QueryDef
        Dim strsql As String
        
        On Error GoTo FAILURE
        makereport = False
        
        strsql = Replace(CurrentDb.QueryDefs("workhorse").SQL, "999999999", provider_group_id)
        CurrentDb.QueryDefs("reportshorse").SQL = strsql
        DoCmd.OpenQuery "reportshorse", acViewNormal
        DoCmd.OutputTo acOutputReport, "rtpACVNoPay 2", acFormatRTF, "C:\ACV\rpt_providerid_" & _
            CStr(provider_group_id) & ".doc"
        
        makereport = True
        
       Exit Function
    FAILURE:
        
        
    End Function

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Add Option Explicit to the top of you code

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum.

    Besides the missing Option Explicit line that orange addressed, the code for the sub getprovider_group_id() has major errors.
    Code:
    Sub getprovider_group_id()
        Dim bGood As Boolean
        Dim db As DAO.Database
        Dim rst As DAO.Recordset
        Set db = CurrentDb
        rst = selectdistinct("provider_group_id")
        from workhorse
        OpenRecordset rst
        With rst
            Do Until .EOF
                bGood = makereport("provider_group_id" & .Fields(0)).MoveNext
            Loop
            Close
        End With
    End Function

    This is how I would code the procedure:
    ("workhorse" is a query??)

    Code:
    Sub getprovider_group_id()
        Dim bGood As Boolean
        Dim db As DAO.Database
        Dim rst As DAO.Recordset
        Dim sSQL As String
    
        Set db = CurrentDb
    
        sSQL = "SELECT DISTINCT provider_group_id FROM workhorse ORDER BY provider_group_id"
        Set rst = db.OpenRecordset(sSQL)
    
        With rst
            Do Until .EOF
                bGood = makereport(.Fields(0))
                .MoveNext
            Loop
            .Close
        End With
    
        Set rst = Nothing
        Set db = Nothing
    End Sub
    I'm still looking at the function makereport.......

  4. #4
    kthorson16 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2016
    Posts
    3
    "workhorse" is a query.

    I made t he changes you stated but it is not generating the individual reports as needed to my C drive.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  6. #6
    kthorson16 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2016
    Posts
    3
    Function makereport(provider_group_id As Variant) As Boolean
    Dim qdfsql As QueryDef
    Dim strsql As String

    On Error GoTo FAILURE
    makereport = False

    strsql = Replace(CurrentDb.QueryDefs("workhorse").SQL, "999999999", provider_group_id)
    CurrentDb.QueryDefs("reportshorse").SQL = strsql
    DoCmd.OpenQuery "reportshorse", acViewNormal
    DoCmd.OutputTo acOutputReport, "rtpACVNoPay 2", acFormatRTF, "C:\ACV\rpt_providerid_" & _
    CStr(provider_group_id) & ".doc"

    makereport = True

    Exit Function
    FAILURE:


    End Function

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Any chance you would post your dB for analysis?

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

Similar Threads

  1. Issues with create a sum for a report?
    By ayupchap in forum Reports
    Replies: 2
    Last Post: 12-18-2015, 11:34 AM
  2. Specific Type of record shown if box contains!
    By gebmiller1984 in forum Queries
    Replies: 1
    Last Post: 03-06-2015, 08:48 PM
  3. Replies: 2
    Last Post: 11-15-2013, 09:58 AM
  4. Replies: 12
    Last Post: 08-21-2012, 02:40 PM
  5. Replies: 5
    Last Post: 08-21-2012, 12:30 PM

Tags for this Thread

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