Setting filter before query or report can open

See what arnel has supplied.
 
manage to convert it back to A2007.
Open the 2 reports.
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.
 
You open the report and the form will appear for you to choose.
 
You open the report and the form will appear for you to choose.
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 Select 1000 from the combobox..
 
You Select 1000 from the combobox..
If I click on R_RemittanceAdvice, I'm not given the option to select an engagement, just the musician.

I'm happy to not have the option to select an individual musician by the way. But the option to select an engagement is useful. I feel like it should work with a little tweaking!
 
Thanks for everyone's help yesterday. Here's my second question, which I'm hoping will be very simple.

I've posted my VBA code below. I'd like to adapt the file names based on other fields in the query (Q_Totals). The problem is, I don't know how to reference them. As you can see, I've started by inserting the following line, but it brings up an error "item not found in this collection".

Code:
MsgBox Nz(![Description], "")
(Obviously I'll delete this code after it works, this is just for testing purposes)

The fields I'd like to be referencing from the query (in addition to PlayerCode_PK) are Description and VATReg.

In order to make it possible, should I be adapting this line of code?

Code:
Set rs = CurrentDb.OpenRecordset("SELECT PlayerCode_PK FROM Q_Totals", dbOpenSnapshot)

If so, I'm just not sure of the syntax.

Thanks again for your help.

Here's the full code as it stands.

Code:
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
 
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 the error message is self explanatory?
There is no field in the recordset called Description? :(

Hint:
Code:
SELECT PlayerCode_PK FROM Q_Totals
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.

I'll keep researching on Google, but in the meantime, if you or anyone else can tell me how to adapt the code I'll be very grateful.

Thanks.
 
Simplest and quickest way is to change PlayerCode_PK to *
 
Many thanks, that was the answer I was looking for!
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
 
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
Thanks, that's even more helpful!
 
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>
 
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>
Many thanks Pat. I'll try to learn more about what you mentioned there.
 

Users who are viewing this thread

Back
Top Bottom