Custom naming pdf reports generated using a loop (1 Viewer)

lyatri · Sep 25, 2018

I have a query called qryGrpMedHMMScript which collates the data for a report repScriptHMM.

I have a button on a form which when clicked triggers the code below:

Code:

 Private Sub Command8_Click()
 Dim rst As Recordset
 Dim db As Database
 Dim strSQL As String
 

 Set db = CurrentDb()

 Set rst = db.OpenRecordset("Select [Key] From [qryGrpMedHMMScript]")


rst.MoveFirst

Do Until rst.EOF
  DoCmd.OpenReport "repScriptHMM", acViewPreview, , "Key = " & rst!Key, acHidden
  DoCmd.OutputTo acOutputReport, "repScriptHMM", acFormatPDF, "C:\Users\tomiv\Desktop\File Dump\Scripts" & [Mill] & " " & "MFSP For" & " " & [Client Name] & " " & [Unit Name] & " " & [Diet] & " " & rst!Key & ".pdf"
  DoCmd.Close acReport, "repScriptHMM"
  rst.MoveNext
Loop

rst.Close
Set rst = Nothing
strSQL = ""

End Sub

The code successfully generates individual pdf reports for all the records I want it to. However the issue is with the file name. The key (number and primary key) is correct for each pdf however all the other information returns as identical and matches the first record.
I can see why this would happen but not how to fix it!

Uncle Gizmo · Sep 25, 2018

You need to loop through the record set as well. I'm pretty sure I covered it in my blog here without having a look! Generate Multiple Reports

Gasman · Sep 25, 2018

You are only selecting one field in the query.?
Select * and then use the relevant fields as you have done for the key?

MajP · Sep 25, 2018

Where are those fields / controls coming from? My guess it is pulling from the form or report.

Code:

[Mill] & " " & "MFSP For" & " " & [Client Name] & " " & [Unit Name] & " " & [Diet]

I would assume you mean Rst!Mil, Rst![Client Name], Rst![unit name] ?

lyatri · Sep 25, 2018

Uncle Gizmo said:
You need to loop through the record set as well. I'm pretty sure I covered it in my blog here without having a look! Generate Multiple Reports

Thank you for this. Although 99% of your video was very much out of my league I managed to gleen what I needed - I am a budding amateur with no more than a GCSE in ICT. For anyone who is interested my final code is below and it now works as I wanted it to

Code:

 Private Sub Command8_Click()
 Dim rst As Recordset
 Dim db As Database
 Dim strSQL As String
 

 Set db = CurrentDb()

 Set rst = db.OpenRecordset("Select qryGrpMedHMMScript.Key, qryGrpMedHMMScript.Mill, qryGrpMedHMMScript.Diet, qryGrpMedHMMScript.[Client Name], qryGrpMedHMMScript.[Unit Name] From [qryGrpMedHMMScript] Order By qryGrpMedHMMScript.Key")


rst.MoveFirst

Do Until rst.EOF
  DoCmd.OpenReport "repScriptHMM", acViewPreview, , "Key = " & rst!Key, acHidden
  DoCmd.OutputTo acOutputReport, "repScriptHMM", acFormatPDF, "C:\Users\tomiv\Desktop\File Dump\Scripts" & rst!Mill & " " & "MFSP For" & " " & rst![Client Name] & " " & rst![Unit Name] & " " & rst![Diet] & ".pdf"
  DoCmd.Close acReport, "repScriptHMM"
  rst.MoveNext
Loop

rst.Close
Set rst = Nothing
strSQL = ""

End Sub

Uncle Gizmo · Sep 25, 2018

lyatri said:
Thank you for this. Although 99% of your video was very much out of my league I managed to gleen what I needed -

I'm glad you managed to extract some useful information from it.

The problem is, I am explaining an answer to an individual question. I also use several different ideas, pulling them together to get the question answered for the OP.

It could do with at least two more presentations to separate out the other Stuff shoved in there, to clarify what's really going on. So it's not you, it's the way the information is presented. It's Not presented to answer a general question. I may well expand on it at some stage stage because there are some useful techniques in there.

You are under no obligation to but if you were to sign up to my newsletter I would be very grateful.

Also I will give you a Coupon Code so you can download many of my examples for free! This offer is open to everybody by the way!

Uncle Gizmo · Sep 25, 2018

If you have a look at my Nifty Tips on my website here:- http://www.niftyaccess.com/nifty-tips/ if you look at, I think it's the 4th tip down "Take out the Tables" I demonstrate how you can remove the table name against a field name in an SQL Statement.

Bare in mind you can only do this if the "From Clause" is for just one single table. If you've got several tables then you don't want to do this because it can cause problems.

I realise you've got a query, and I've never tried this technique on a query! So really you'd sort of be a guinea pig. No need to do it if you don't want to, but if you did I'd be very interested to know if it worked. In other words if the query SQL statement still works just the same...

Custom naming pdf reports generated using a loop (1 Viewer)

lyatri

Registered User.

Uncle Gizmo

Nifty Access Guy

Gasman

Enthusiastic Amateur

MajP

You've got your good things, and you've got mine.

lyatri

Registered User.

Uncle Gizmo

Nifty Access Guy

Uncle Gizmo

Nifty Access Guy

Similar threads

Users who are viewing this thread