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