Access Report into multiple files depending on a grouped field

[visitnag]

Hi,



I have a code which is running fine in creating a single file on a grouped field. The problem is I have more than 20000 records having
DeptID, empId, JoinDt as 3 fields(i have kept sample fields).

I have grouped on DeptId in which at least 20 records of employees for each Department.
I am trying to print a separate file for each department as file name.

When I execute the code every time its asking to enter the DeptId for each and every record...and creating only one DeptID file which appears first.

As a newbie I dont know how to modify the code. Please help. Thank you



Private Sub Command7_Click()

Dim rst As Recordset
Dim db As Database
Dim strSQL As String


Set db = CurrentDb()




strSQL = "SELECT [MyTbl].DeptID, [MyTbl].empID, [MyTbl].joindt FROM [MyTbl]"
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst


Do Until rst.EOF
DoCmd.OpenReport "DeptRpt", acViewPreview, , "DeptID = " & rst!DeptID
DoCmd.OutputTo acOutputReport, "DeptRpt", acFormatPDF, "C:\test" & rst!DeptID & ".pdf"
DoCmd.Close acReport, "DeptRpt"
rst.MoveNext
Loop


rst.Close
Set rst = Nothing
strSQL = ""



End Sub

[Micron]

Even after one coffee I'm not seeing why. Suggest you step through the code and watch your variables to ensure they are what you think. Also, if DeptID is text data type then you're missing delimiters, but I don't see that as being the reason. Also make sure you get records by testing the sql if you have not already.

Please post code within code tags (# button on posting toolbar) to maintain indentation and readability.

[Gicu]

Does this work?

Private Sub Command7_Click()


Dim rst As Recordset
Dim db As Database
Dim strSQL As String


Set db = CurrentDb()


strSQL = "SELECT DISTINCT [MyTbl].DeptID FROM [MyTbl];"
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst


Do Until rst.EOF
	DoCmd.OpenReport "DeptRpt", acViewPreview, , "DeptID = " & rst!DeptID
	DoCmd.OutputTo acOutputReport, "DeptRpt", acFormatPDF, "C:\test" & rst!DeptID & ".pdf"
	DoCmd.Close acReport, "DeptRpt"
rst.MoveNext
Loop


rst.Close
Set rst = Nothing
Set db=Nothing




End Sub

Cheers,

[visitnag]

Yesss, Its working. Thank you..
I am trying to accept the DeptID automatically by adding the following lines, but
its writing the files on DeprtID but the records of the employees are same in
all the files.

Do Until rst.EOF 
    MyField = rst("DeptID")
    MyPath = "C:\test\"
    OutPutFileName = MyField & ".pdf" 
        DoCmd.OpenReport "tmpRpt", acViewPreview, , MyField
        DoCmd.OutputTo acOutputReport, "tmpRpt", acFormatPDF, MyPath & OutPutFileName
        DoCmd.Close acReport, "tmpRpt"
    rst.MoveNext
Loop
rst.Close
Set rst = Nothing
strSql = ""

[Welshgasman]

I would not have the MoveFirst in there. If you get an empty recordset, you will get an error.

I have never heard of opening a recordset/file and not being on the first record (if one exists?)

[Gicu]

In the latest code you posted you lost the Where clause of the OpenReport method, so the report contains all the records:

DoCmd.OpenReport "DeptRpt", acViewPreview, , "DeptID = " & MyField

Cheers,

[visitnag]

In the latest code you posted you lost the Where clause of the OpenReport method, so the report contains all the records:

DoCmd.OpenReport "DeptRpt", acViewPreview, , "DeptID = " & MyField

Cheers,

Thank you for the reply,

I have gone through the syntax of DoCmd.OpenReprt and applied the where cluase too,
then its asking the group field name again whenever group Id changes. Its not
taking automatically.

[visitnag]

Thank you,
Yes, even if the MoveFirst is commented out the result is same.

[June7]

Other than unnecessary MoveFirst, I don't see anything wrong with original posted code. So why is there a parameter input popup?

If you want to provide db for analysis, follow instructions at bottom of my post.

[visitnag]

Yes parameter value window is popping up...but i could not find any error in fields naming in the report
Here I am attaching a very minimum sample db. Please check and help. Thank you

[Gicu]

Sorry, we all assumed your DeptID being a number when in fact it is a string. Can you check this updated version?
I strongly recommend you enable the Require Variable Declaration=Option Explicit in you Access client for VBA;
https://www.fmsinc.com/microsoftacce...ons/index.html

Cheers,

[visitnag]

I should say sorry for not telling the DeptID is a string. Thank you a lot. Its working perfectly...

[June7]

Now I understand cause of popup. Code passes filter criteria to report Filter property. But without text delimiters, report can't process the criteria and query engine kicks out a popup prompt. Place a breakpoint in code on Output line and view report in design view. Filter property show DeptID = D23568 when it needs to be DeptID = 'D23568'.