Many thanks arnelgp. I'll need to play around with it a bit more. If I want to select all reports with engagement text 1000, do I open the report, or click on form F_P1 first? Thanks again.manage to convert it back to A2007.
Open the 2 reports.
I see. So when I open the report, the first F_P1 appears (I know that form F_P1 asks me about engagement when I click on that form, but it's asks me which musician when I open the report). I'd like to select all musicians, so I press print. It then goes to report view. If I click on print preview, F_P1 appears again, and again I press print. However, if I only wanted engagement 1000 say, would that be possible?You open the report and the form will appear for you to choose.
If I click on R_RemittanceAdvice, I'm not given the option to select an engagement, just the musician.You Select 1000 from the combobox..
That works really well, thanks a lot for that.here is the tweak.
MsgBox Nz(![Description], "")
Set rs = CurrentDb.OpenRecordset("SELECT PlayerCode_PK FROM Q_Totals", dbOpenSnapshot)
Private Sub Command1_Click()
Dim rs As DAO.Recordset
Dim sFolder As String
Dim sFile As String
Dim answer As String
On Error GoTo Error_Handler
sFolder = "D:\Documents\Orchestra\Musician Payments\FY ending 2022\"
Set rs = CurrentDb.OpenRecordset("SELECT PlayerCode_PK FROM Q_Totals", dbOpenSnapshot)
With rs
.MoveFirst
Do While Not .EOF
DoCmd.OpenReport "R_RemittanceAdvice", acViewPreview, , "[PlayerCode_PK]='" & ![PlayerCode_PK] & "'", acHidden
sFile = Nz(![PlayerCode_PK], "") & ".pdf"
MsgBox Nz(![Description], "")
sFile = sFolder & sFile
DoCmd.OutputTo acOutputReport, "R_RemittanceAdvice", acFormatPDF, sFile
'If you wanted to create an e-mail and include an individual report, you would do so now
DoCmd.Close acReport, "R_RemittanceAdvice"
.MoveNext
Loop
End With
'Application.FollowHyperlink sFolder 'Optional / Open the folder housing the files
Error_Handler_Exit:
On Error Resume Next
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Exit Sub
Error_Handler:
If Err.Number <> 2501 Then 'Let's ignore user cancellation of this action!
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: cmd_GenPDFs_Click" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
End If
Resume Error_Handler_Exit
End Sub
Thanks Gasman for your reply. Yes, I asked if I should be adapting that code. I also said I don't know about the syntax. I haven't learnt SQL yet.Well the error message is self explanatory?
There is no field in the recordset called Description?
Hint:
Code:SELECT PlayerCode_PK FROM Q_Totals
Well I only suggested that as I had seen Q_TotalsMany thanks, that was the answer I was looking for!
Select PlayerCode_PK, Description, First_Name, Surname FROM
Thanks, that's even more helpful!Well I only suggested that as I had seen Q_Totals
However if a table/query had 100 fields and I just wanted 4 of them, then I'd use their names directly?
Code:Select PlayerCode_PK, Description, First_Name, Surname FROM
Many thanks Pat. I'll try to learn more about what you mentioned there.Can't tell if you have a solution so I'll jump in.
For starters, when you use a parameter query as the RecordSource for a report ALWAYS reference a control on a form or use a TempVar. If you ask for a variable, Access will prompt once to open the report and it will ask again if you print or save to PDF>