Saving a report with as filename the records out of the query

[huk250]

A good morning,


I am looking for the following solution
I have a tabelquery : "MCDQUERY"

with the following columns : MCDNumber Country Priority
DATA / MCD25 egypt p2
MCD23 Gabon p1

i would like to create a report with a selection on MCDNumber

so the result should be EXAMPLE : MCD23_Gabon_p1.pdf


enclosed below the vba that I already have created

**********************
Private Sub Knop15_Click()


Dim db As DAO.Database
Dim rs As DAO.QueryDef
Dim fs As DAO.Recordset
Dim MyFileName As String
Dim mypath As String
Dim temp As String




mypath = "C:\Users\ADMIN\Desktop"


Set db = CurrentDb()
Set rs = db.QueryDefs("MCDQuery")
rs.Parameters("MCDnumber") = Name

Set fs = rs.OpenRecordset()


Do While Not fs.EOF


temp = fs("MCDnumber")
MyFileName = fs("MCDnumber") & "_" & fs("Country") & "_" & fs("Priority") & ".PDF"

DoCmd.OpenReport "MCD Invoice", acViewReport, , "[MCDnumber]=" & temp & ""


DoCmd.OutputTo acOutputReport, "", acFormatPDF, mypath & MyFileName
DoCmd.Close acReport, "MCD INVOICE"
DoEvents


fs.MoveNext
Loop




fs.Close
Set rs = Nothing
Set db = Nothing
Set fs = Nothing


End Sub
************************************************** ********

Thanks for the assist
Koen

[Welshgasman]

Not sure your recordset stntax is correct, I have never seen that before.
As I always advise DEBUG.PRINT your variables and see what they are, not what you think they are.

I used to use the .Fields("FiieldName") syntax. Mypath does not appear to being set correctly? I would expect to see a trailing backslash. Again debug.print highlights common mistakes like these.

https://learn.microsoft.com/en-us/of...set-object-dao

If temp is indeed a string you need to surround that with single quotes when concatenating.
Name is a reserved word. Do not use Reserved words.
https://learn.microsoft.com/en-us/of...reserved-words