Need filter recordsource and change report name for each unique value

[legolas75]

I have a report based on a table, 'DATA1'. For each unique groupnbr in 'DATA1' I want to export a report to show only the data for that group number and save the name of the report to be based on the value on the group number.

For example if group number = 1234 then I want the
recordsource of the report to be "DATA1 where grp_nbr = '1234' "

I want the report saved as rpt_grpnbr or rpt_1234
I've been trying to do this via OutputTo method but I'm not an experienced programmer. Thanks for your help. Mike

 

[Remou]

Similar problems come up quite frequently. Have a look in the Microsoft: Access Reports Forum, forum703.

 

[legolas75]

Hi Remou,
Thanks for replying. I have looked at the forum you suggested even before posting my thread and while many are similar they are not the same. All of the filtering I've seen deals with Previewing a report not exporting which I already know how to do. Also the filter always refers to a value from a listbox or something on a form not from a table or query underlying the report itself. If you know of a specific thread that can help please let me know. Thanks.
Mike

 

[Remou]

I *remember* such a post, but can't find it, so:

'Typed, not tested
'Needs reference to Microsoft DAO 3.x Object Library
Dim rs As DAO.Recordset

strReport="rptReport"

Set rs=CurrentDB.Openrecordset("DATA1")
Do While Not rs.EOF
   DoCmd.OpenReport strReport, acDesign
   Reports(strReport).RecordSource="Select * From DATA1 " _
   & "WHERE grp_nbr = '" & rs!grp_nbr "'"
   DoCmd.Save acReport, strReport
   DoCmd.OutputTo acOutputReport, strReport, acFormatRTF, rs!grp_nbr & ".txt"
   rs.MoveNext
Loop

Or there abouts.

 

[legolas75]

Here is the latest incarnation of the code which I think is the easiest to understand. I have two tables. One table, tblEmail is a has a unique list of the field IPA_NUM. The other, DATA1 has all of the data from which the report is built which many rows of data per IPA_NUM. I'm stilling getting the same error : "sub or function not defined " so this remains a mystery as to why it doesn't work.

Private Sub cmd_test_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim vIPA_NUM As String
Dim strReport As String
Dim strReportPath As String

strReportPath = "c:\"
strReport = "1 rpt_Summary"

Set db = CurrentDb
Set rs = db.OpenRecordset("tblEmail")
rs.MoveFirst
Do

vIPA_NUM = rs.Fields("IPA_NUM")
   
    DoCmd.OpenReport strReport, acDesign
    Reports(strReport).RecordSource = "Select * From DATA1 " & "WHERE IPA_NUM = '" & vIPA_NUM & "'"
DoCmd.Save acReport, strReport
DoCmd.OutputTo acOutputReport, strReport, _
acFormatSNP, strReportPath & "Rpt1" & "_" & vIPA_NUM & ".snp", True

   rs.MoveNext

Loop Until rs.EOF
rs.Close
db.Close

End Sub

 

[Remou]

Which line is giving the error?


Change this:
[tt]DoCmd.Save acReport, strReport[/tt]

To:
[tt]DoCmd.Close acReport, strReport, acSaveYes[/tt]