Recordsets - one access report to multiple files

[tgleghorn58]

I have parameter form that ask for state and begining date. on click it runs a query and creates a report by state that has multiple pages.. I want to create a separate pdf file for is page (which would be by delegate_name).

This is the coding I have so far and am gettting the error '424' object required. This is the coding I currently have. I haven't worked with access in over 9 yrs so am a little rusty with it. I may be way off on this coding so any help would be appreciate.



StateAbb = Me.State
PeriodStartDate = Format(Me.Period_start_date, "MM-DD-YYYY")
Dim strSQL As String
Dim db As Database
Dim rs As Recordset
Dim strRptName As String
Dim strDelegate As String

strSQL = "PARAMETERS [Forms]![frm_parameters_by_state]![Period_start_date] DateTime, [Forms]![frm_parameters_by_state]![State] Text ( 255 );" & _
"SELECT Credentialing_wc.Period_Start_Date, Credentialing_wc.Period_End_Date, Credentialing_wc.Delegate_Name, Credentialing_wc.Effective_Date_of_Credentialing_Delegation_for_WellCare, " & _
"Credentialing_wc.Do_you_sub_delegate_any_part_of_Credentialing , Credentialing_wc.Are_you_or_your_sub_delegate_Accredited, Credentialing_wc.Do_you_use_a_Credentialing_Verification_Organization, " & _
"Credentialing_wc.Is_CVO_accredited , Credentialing_wc.What_Network_Type_or_Services_do_you_provide, Credentialing_wc.Did_WellCare_conduct_a_Pre_delegation_Audit_during_prior_six_mth, " & _
"Credentialing_wc.Was_a_Pre_delegation_Corrective_Action_Plan_issued, Credentialing_wc.Did_WellCare_conduct_an_Annual_Audit_during_the_prior_six_mths, Credentialing_wc.Was_an_Annual_Audit_Corrective_Action_Plan_issued, " & _
"Credentialing_wc.How_many_providers_were_Initial_Credentialed_for_WellCare_during, Credentialing_wc.[How_many_providers_were_Re_credentialed_for_WellCare _during_pri], " & _
"Credentialing_wc.Is_your_recredentialing_cycle_2_or_3_years, Credentialing_wc.Did_all_providers_Recredentialed_during_the_prior_six_mths_meet_ , " & _
"Credentialing_wc.How_many_WellCare_providers_were_identified_as_sanctioned_by_OIG, Credentialing_wc.How_many_WellCare_providers_termed_without_cause_during_prior_si, " & _
"Credentialing_wc.How_many_WellCare_providers_were_termed_With_cause_during_prior_, Credentialing_wc.If_termed_with_cause_what_was_the_reason, Credentialing_wc.[Lines_of_Business_(per_State)_(up_to_20_words)], " & _
"Credentialing_wc.reviewed_date, Credentialing_wc.reviewed_by, Credentialing_wc.[comment_section_(up_to_75_words)], qry_Omni_Flow_cnt.State, qry_Omni_Flow_cnt.NEW, qry_Omni_Flow_cnt.TERM, qry_Omni_Flow_cnt.Update " & _
"FROM Credentialing_wc LEFT JOIN qry_Omni_Flow_cnt ON (Credentialing_wc.Period_Start_Date = qry_Omni_Flow_cnt.From) AND (Credentialing_wc.Delegate_Name = qry_Omni_Flow_cnt.EntityName) AND (Credentialing_wc.State = qry_Omni_Flow_cnt.State) " & _
"WHERE (((Credentialing_wc.Period_start_date) =" & [Forms]![frm_parameters_by_state]![Period_start_date] And (qry_Omni_Flow_cnt.State) = [Forms]![frm_parameters_by_state]![State] And (Credentialing_wc.State) = [Forms]![frm_parameters_by_state]![State] & "))" & _
"ORDER BY Credentialing_wc.Delegate_Name;"
strRptName = "rpt_Credentialing_by_state"
Set db = CurrentDb
Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

Do While Not rs.EOF


DoCmd.OpenReport strRptName, acViewPreview, , "[Delegate_Name] = " & rs![Delegate_Name]

rptName = Path & "Credentialing_Report_" & removeSpecial(StateAbb) & "_" & rs!Delegate_Name & PeriodStartDate

DoCmd.OutputTo acOutputReport, "Rpt_Credentialing_by_state", "PDFFormat(*.pdf)", rptName, True, "", , acExportQualityPrint


DoCmd.Close acReport, "rpt_Credentialing_by_state", acSaveNo

rs.MoveNext

Loop

rs.Close
Set rs = Nothing
Set db = Nothing

Beep
MsgBox """Credentialing report has been exported successfully.""", vbOKOnly, ""



End Sub

 

[tgleghorn58]

when I enter the parameters in my form and click the button the whole strSQL is highlighted in yellow and I get the object required error.

 

[JHB]

You're missing quartos in then where clause:

"WHERE (((Credentialing_wc.Period_start_date) =" & [Forms]![frm_parameters_by_state]![Period_start_date] & " And (qry_Omni_Flow_cnt.State) =" & [Forms]![frm_parameters_by_state]![State] & " And (Credentialing_wc.State) =" & [Forms]![frm_parameters_by_state]![State] & "))" & _
"ORDER BY Credentialing_wc.Delegate_Name;"

If any of the field type is text, then you need a single quarto like:

"WHERE AtextField '" & AtextVariable & "' ...

And if any of the field type is date, then sometimes you need a # like:

"WHERE ADateField #" & ADateVariable & "# ...

And remember, dates in SQL must be in US format = mm/dd/yyyy.

 

[vbaInet]

All that opening and closing of the report is not necessary. See here:

http://www.access-programmers.co.uk/forums/showpost.php?p=1049034&postcount=10

... and of course you need to take note of JHB advice on handling dates and strings.

 

[tgleghorn58]

I made the changes to the strSQL statement that JBH suggested.. Now I am getting the error: too few parameters expected 2.


On the parameter form I am asking for period starte date and the state. These parameters are passed to the query (which is the strSQL statement).. The actual report is a pdf that creates a separate page by the delegate name.

Thank you for everyones input..

 

[tgleghorn58]

Removed this from the SQL statement "PARAMETERS [Forms]![frm_parameters_by_state]![Period_start_date] DateTime, [Forms]![frm_parameters_by_state]![State] Text ( 255 );" & _ and it corrected the errror.

 

[tgleghorn58]

I have it creating the report and exporting a separate pdf file with the delegate name in the name, however each pdf has every page in it. Both the prior suggestions were very helpful.. thank you so much!

this is what I have:
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

Do While Not rs.EOF
strRptFilter = "[Delegate_Name] = " & rs![Delegate_Name]


rptName = Path & "Credentialing_Report_" & removeSpecial(StateAbb) & "_" & rs![Delegate_Name] & PeriodStartDate

DoCmd.OutputTo acOutputReport, strRptName, "PDFFormat(*.pdf)", rptName, True, "", , acExportQualityPrint


DoCmd.Close acReport, "rpt_Credentialing_by_state", acSaveNo

rs.MoveNext

Loop

rs.Close
Set rs = Nothing
Set db = Nothing
strRptFilter = vbNullString

Beep
MsgBox """Credentialing report has been exported successfully.""", vbOKOnly, ""



End Sub

 

[vbaInet]

You're doing it the way I advised in the link but you've removed relevant lines of code and added some redundant lines of code.

 

[tgleghorn58]

I changed it and I am still getting the whole report in each pdf file.

et rs = db.OpenRecordset(strSQL, dbOpenSnapshot) 'this has the sql string defined above'

Do While Not rs.EOF
strRptFilter = "[Delegate_Name] = " & Chr(34) & rs![Delegate_Name] & Chr(34)

rptName = Path & "Credentialing_Report_" & removeSpecial(StateAbb) & "_" & rs![Delegate_Name] & PeriodStartDate & ".pdf"

DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, rptName
DoEvents

rs.MoveNext

Loop

rs.Close
Set rs = Nothing
Set db = Nothing
strRptFilter = vbNullString

Beep
MsgBox """Credentialing report has been exported successfully.""", vbOKOnly, ""



End Sub

 

[vbaInet]

That's because you've missed this part, "In the Open event of your report put this:"

Or your report doesn't contain those fields.

 

[tgleghorn58]

bare with me.. I have not worked with Access for years. I have put that IF Len statement in and it did not make a difference. Maybe I am putting in the wrong place. Each file still has the whole report..

 

[vbaInet]

Ok, upload a stripped down copy of your db and I'll have a quick look.

 

[tgleghorn58]

Dim strSQL As String
Dim db As Database
Dim rs As Recordset
Dim strRptName As String
Dim strDelegate As String

strSQL = "SELECT Credentialing_wc.Period_Start_Date... "FROM Credentialing_wc LEFT JOIN qry_Omni_Flow_cnt ... "WHERE ......"ORDER BY Credentialing_wc.Delegate_Name;"

strRptName = "rpt_Credentialing_by_state"
Set db = CurrentDb
Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

If Len(strRptFilter) <> 0 Then
Me.Filter = strRptFilter
Me.FilterOn = True
End If

Do While Not rs.EOF


strRptFilter = rs![Delegate_Name]
rptName = Path & "Credentialing_Report_" & removeSpecial(StateAbb) & "_" & strRptFilter & PeriodStartDate & ".pdf"

DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, rptName
DoEvents

rs.MoveNext

Loop

rs.Close
Set rs = Nothing
Set db = Nothing
strRptFilter = vbNullString

Beep
MsgBox """Credentialing report has been exported successfully.""", vbOKOnly, ""



End Sub



Function removeSpecial(sInput As String) As String
Dim sSpecialChars As String
Dim i As Long
sSpecialChars = "\/:*?""<>|"
For i = 1 To Len(sSpecialChars)
sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "_")
Next
removeSpecial = sInput
End Function

 

[tgleghorn58]

I was able to get it to work by adding

DoCmd.OpenReport "Rpt_Credentialing_by_state", _
acViewPreview, _
WhereCondition:="Delegate_Name = " & "'" & rs!Delegate_Name & "'", _
WindowMode:=acHidden

It creates the first file with one page.. getting an error on the second page..

Thanks again for all your help.

 

[vbaInet]

You've hit the 10 posts mark so you should be able to...

... upload a stripped down copy of your db and I'll have a quick look.

 

[tgleghorn58]

I was able to get it to work by adding

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

Do While Not rs.EOF

strDelegate = "Delegate_Name = " & "'" & rs!Delegate_Name & "'"

DoCmd.OpenReport "Rpt_Credentialing_by_state", _
acViewPreview, _
WhereCondition:=strDelegate, _
WindowMode:=acHidden

strRptFilter = rs![Delegate_Name]
RptName = Path & "Credentialing_Report_" & removeSpecial(StateAbb) & "_" & strRptFilter & "_" & PeriodStartDate & ".pdf"

DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, RptName, False
DoEvents

DoCmd.Close acReport, "Rpt_Credentialing_by_state", acSaveNo

rs.MoveNext

Loop

rs.Close
Set rs = Nothing
Set db = Nothing
strRptFilter = vbNullString

Beep
MsgBox """Credentialing report has been exported successfully.""", vbOKOnly, ""



End Sub



Function removeSpecial(sInput As String) As String
Dim sSpecialChars As String
Dim i As Long
sSpecialChars = "\/:*?""<>|"
For i = 1 To Len(sSpecialChars)
sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "_")
Next
removeSpecial = sInput
End Function

 

[vbaInet]

I suppose there was no point of me posting code that actually works.

 

[tgleghorn58]

I am sorry, I missed that... thank you for your help, but you need to get rid of your attitude.

 

[tgleghorn58]

Your code did help... but it did not totally work for me. If you can show me where to put the if len statement in my code.. I would be happy to use your suggestions.

 

[vbaInet]

... but you need to get rid of your attitude.

I've been here long enough to know how to address those we help.

You were given a link showing a step-by-step process of how to do it properly, you said it didn't work. You were then asked on a few occasions to post a mock up db so that I can advise were you're going wrong but instead you ignored both requests. The code you posted is a mixture of what I advised and what you got from elsewhere meaning there are couple of redundant lines of code you've used. Again, no acknowledgement to my request for help.