Recodsets and looping

[GPSPOW]

I hope someone can tell me how to fix the following code. I am creating a query to designate the subset of physicians from a master list. From this subset I want to loop through the results, print a report to a pdf for that physician, loop to the next physician and repeat the process until I reach the end of the file.

When I process the code, the records do not loop through properly and I get either one report with all the physicians or just the first physician.

Here is the code:

Set MyDB = CurrentDb()
'Creates Table tbl_IPA_ICD9_Proc_Cnt for all data between txtFrom and txtTo
DoCmd.OpenQuery "qry_IPA_ICD9_Proc"
'Creates Table tbl_IPAVisitsConsultants for all data between txtFrom and txtTo
DoCmd.OpenQuery "qry_IPA_Visits_Consultants"

'Build the IN string by looping through the listbox

DoCmd.SetWarnings False

Set MyDB = CurrentDb

MyDB.Execute "Delete * from tbl_IPA_Phys_Select"
MyDB.QueryDefs.Delete ("qry_IPA_Phys_List")
'Create Physician List from Form List Selection
If lstSelect.Value = 4 Then
strSqlPhysSelect = "Select dbo_DMisProvider.ProviderID, dbo_DMisProvider.Name into tbl_IPA_Phys_Select" & _
"From dbo_DMisProvider;"
strSqlPhys = "Select dbo_DMisProvider.ProviderID, dbo_DMisProvider.Name " & _
"From dbo_DMisProvider;"
Else
strSqlPhys = " "
strWhere = " "
For i = 0 To lstRight.ListCount - 1
StrIn = StrIn & "'" & lstRight.Column(0, i) & "',"
Next i
If Len(StrIn) = 0 Then
MsgBox "No Physicians Selected!" _
, vbExclamation, "Returning to Selection Screen!"

Exit Sub
End If

strWhere = " WHERE dbo_DMisProvider.ProviderID in " & _
"(" & Left(StrIn, Len(StrIn) - 1) & ")"

strSqlPhys = "Select dbo_DMisProvider.ProviderID, dbo_DMisProvider.Name " & _
"FROM dbo_DMisProvider " & _
strWhere & ";"
End If

'Create "qry_IPA_Phys_List" '
' NEW CODE************
' Create Recordset
Dim rst As DAO.Recordset
Dim qlist As QueryDef
Set qlist = MyDB.CreateQueryDef("qry_IPA_Phys_List", strSqlPhys)
DoCmd.OpenQuery "qry_IPA_Phys_List"
Set rst = MyDB.OpenRecordset("qry_IPA_Phys_List", dbOpenDynaset)
Dim qdf As QueryDef

Do While Not rst.EOF
strSqlPhysSelect = " "
strSqlPhysSelect = "Select dbo_DMisProvider.ProviderID, dbo_DMisProvider.Name into tbl_IPA_Phys_Select " & _
"FROM dbo_DMisProvider " & _
" WHERE dbo_DMisProvider.ProviderID = rst!ProviderID;"
MyDB.QueryDefs.Delete ("qry_IPA_Phys_Select")
Set qdef = MyDB.CreateQueryDef("qry_IPA_Phys_Select", strSqlPhysSelect)
DoCmd.Close acTable, "tbl_IPA_Phys_Select"
DoCmd.OpenQuery "qry_IPA_Phys_Select"
DoCmd.OpenReport "rptIPAPhysStats_V5", acViewPreview, , "ProviderID= ProviderID"
DoCmd.OutputTo acReport, "rptIPAPhysStats_V5", "PDFFormat(*.pdf)", _
"\\pmcfs\groups\accounting\Physician Stats\PhysRpts\RptIPAPhysStats_" & rst!Name & ".PDF", False, "", 0

txtName = ""
txtProv = ""
rst.MoveNext
DoCmd.Close acQuery, "qry_IPA_Phys_Select"
DoCmd.Close acTable, "tbl_IPA_Phys_Select"
DoCmd.Close acReport, "rptIPAPhysStats_V5"
Loop

Me.lstRight.RowSource = " "
Me.txtChange = " "
DoCmd.Close acQuery, "qry_IPA_Phys_List"
strSqlPhys = " "
strSqlPhysSelect = " "
DoCmd.SetWarnings True


Thanks

GPSPOW

 

[pbaldy]

Well, this line

DoCmd.OpenReport "rptIPAPhysStats_V5", acViewPreview, , "ProviderID= ProviderID"

Would need to concatenate the value into the string, like here:

http://www.baldyweb.com/wherecondition.htm

Further, you'd have to refer to the Providor from the recordset. As is, you're referring to the form (most likely).

rst!ProviderID

 

[GPSPOW]

What would the syntax be in your 2nd example,
DoCmd.OpenForm "SecondFormName", , , "FieldName = '" & Me.ControlName & "'"

when I want to process a pdf report?

DoCmd.OutputTo acreport, "rptIPAPhysStats_V5","PDFFormat(*.pdf)", "Path", What goes here??

Where do I place the parameter for the "Field Name"?

Thanks

GPSPOW

 

[pbaldy]

OutputTo does not have that option, which is presumably why you're opening the report in Preview mode first. That is one of the workarounds. Personally I use this to avoid the overhead of opening and closing the report every time through the loop:

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

 

[GPSPOW]

Thanks

I think I have it now

 

[pbaldy]

Happy to help.