Custom naming pdf reports generated using a loop

[lyatri]

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:

 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!

 

[Gasman]

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

 

[MajP]

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

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

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

 

[lyatri]

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

 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