Possible to Export Reports to individual files based on Unique Data Field?

[shelzmike]

I have a colleague that asked me a question regarding Access that I am sure can be done, and am guessing with some VBScript, but am unsure where to start. So, she has a report that is essentially a performance review with kpi's and other data metrics that is 2 pages per each unique technician. The question is this - she wants to be able to save each tech's individual report in the following way: tech_A-07062012.pdf, tech_B-07062012.pdf, tech_C-07062012.pdf etc. Is this possible at all to do? I know VBScript but do not know all the ins and outs of Access to know where to start with this. Thanks!



Mike

[pbaldy]

Sure; start here:

http://www.granite.ab.ca/access/emai...recipients.htm

then use the looping code in the link to loop through the technicians. Instead of emailing (or in addition to), use OutputTo to export the file, and use the current technician from the loop in the file name.

[shelzmike]

Okay, so following the link, it does ALMOST what we want it to, except we cannot get it to only print the 2 pages per each tech and then save that file. I am assuming this has to do with the Filter but we cannot figure out where to actually put that in this code. Currently, it will print the entire 98 pages and save that as the first technician name, then it will print all 98 pages and save to the next technician's name, etc. etc. Note, this looks dirty as we are in the early stages of "playing" if you will .

Option Compare Database
Private Sub Command251_Click()
'On Local Error GoTo Some_Err

Dim MyDB As Database, RS As Recordset
Dim strBody As String, lngCount As Long, lngRSCount As Long

'DoCmd.RunCommand acCmdSaveRecord
Set MyDB = DBEngine.Workspaces(0).Databases(0)

'Me!txtProgress = Null

Set RS = MyDB.OpenRecordset _
("Report_Card_Query")
lngRSCount = RS.RecordCount
If lngRSCount = 0 Then
MsgBox "No Scorecard to save", vbInformation
Else
'RS.MoveLast
RS.MoveFirst
Do Until RS.EOF
lngCount = lngCount + 1
'lblStatus.Caption = "Saving Scorecard " & CStr(lngCount) _
& " of " & CStr(lngRSCount) & "..."
techName = RS!Current_Techs
intFileID = techName & "_ April-Jun 2012 Technician Performance Scorecard"
' Save file

DoCmd.OutputTo acOutputReport, "Score_Card", acFormatRTF, _
"S:\DNSC\DNS Meetings\SDREPORTS\PDF\" & intFileID, True



'RS.Edit
'RS("cpeDateTimeEmailed") = Now()
'RS.Update
RS.MoveNext
Loop

End If
RS.Close
MyDB.Close
Set RS = Nothing
Set MyDB = Nothing
Close

Me!txtProgress = "Saved " & CStr(lngRSCount) & " files."
lblStatus.Caption = "Summary"
MsgBox "Done saving files. ", vbInformation, "Done"
lblStatus.Caption = "Idle..."
Exit Sub

[pbaldy]

Presuming you have the filter code in the open event of the form, properly adapted to your data, you'd have a line in your loop before the OutputTo line:

Forms![YourFormName]![TextboxName] = RS!ApproriateFieldNameHere

[shelzmike]

Just to clarify and I am sure it doesn't matter but I was a little confused. We are using a report, not a form. So basically, we have tables, queries that get the info for the report, and then the report. We actually are using a form that has one button on it for now that actually runs this macro. Based on what you said above, will this work for report instead of form? I.e. for everything you said, replace form with report?

And I think that I am unsure as to what AppropriateFieldNameHere should be. Is this the field from the query that determines the different individual reports to be saved? (i.e. the Tech Name)? We are using 2007, so can we just do this using DoCommand? Instead of using the filter? I really appreciate your help. You seem to be a lone gun around here, lol.

Mike

[pbaldy]

The form referred to in the link is simply being used to help filter the report. Basically you place the current tech name there so that when the OutputTo runs, the report filters itself using that value in the code from the first link. Based on your description, I'd put a textbox on the form with the button on it. You can hide it after testing so the user doesn't see it.

OutputTo does not allow for a filter. You could use OpenReport to open the report filtered using the wherecondition argument, then OutputTo to export it, then close the report. I don't like doing that in a loop because it would slow it down, but it would work.

As to lone gun, thankfully not. There are a number of very good developers here. I was probably the only one around when you first posted.