Setting filter before query or report can open (1 Viewer)

[neilsolaris]

Hi,

I have attached a sample of my database that'll hopefully make my question clearer. Basically, I have a report that's linked to a query. Before I open my report, I firstly have to manually filter the query based on a specific job number (otherwise the report would be meaningless). Also, because I have so much data in my normal database, if I don't filter the query first the report will probably crash, or take forever to load.

So my first question is, is it possible to set things up so that if I try to open the query or report, it asks the user (me) to enter a job number? I'm using Access 2007.

The relevant query is named Q_Totals, and the report is named R_RemittanceAdvice.

I have another question, but I'll save that one until I solve this one!

Many thanks for your help.

 

[bob fitz]

So my first question is, is it possible to set things up so that if I try to open the query or report, it asks the user (me) to enter a job number? I'm using Access 2007.

The relevant query is named Q_Totals, and the report is named R_RemittanceAdvice.

You could make your query a "Parramater Query". See: https://support.microsoft.com/en-us...-a-query-c2806d3d-d500-45a8-8507-ec6af351b6ed

 

[CJ_London]

Are you allowing users to open a report from the navigation window?

otherwise can’t see the problem, run the report from where the user enters the job number, disable the button or whatever users click to generate the report until a job number has been entered

Instead of a button, use a control that has conditional formatting functionality and you don’t need any code

 

[Gasman]

I would use your other logic, from your other post.
The open the report with the WHERE parameter.?

 

[neilsolaris]

You could make your query a "Parramater Query". See: https://support.microsoft.com/en-us...-a-query-c2806d3d-d500-45a8-8507-ec6af351b6ed

Thanks Bob. That seems to solve exactly what I was looking for. I'll see if I can make that work.

Edit. I tried it, and it worked perfectly for the query itself. However, when I open the report, it repeatedly asks me "enter the job number" (i.e. the question I set as the parameter). As soon as I enter the job number it asks me the same question!

 

[neilsolaris]

Are you allowing users to open a report from the navigation window?

otherwise can’t see the problem, run the report from where the user enters the job number, disable the button or whatever users click to generate the report until a job number has been entered

Instead of a button, use a control that has conditional formatting functionality and you don’t need any code

Thanks for your help. I'm the only user, and yes, I was allowing the report to open from the navigation window. I guess I could disable that though, I hadn't thought about that.

 

[neilsolaris]

I would use your other logic, from your other post.
The open the report with the WHERE parameter.?

Thanks Gasman. I'll have a think about how I can make that work.

Edit. I've copied the code below that saves the remittance advice files to PDF. I forgot to add, I created a form with a button on (F_Modules) to initiate the event procedure. As I mentioned, I'll try to make your suggestion work, but if you are able to give me some more pointers I'd be very grateful. I'm still not that advanced yet!

Thanks.

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"
            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

 

[Gasman]

If you just open the report, then when the report uses the query, it will prompt for the invoice number, but only once?

 

[neilsolaris]

If you just open the report, then when the report uses the query, it will prompt for the invoice number, but only once?

I wish that were the case, but it's asking me constantly. It asked me three times before I was able to click on print preview, then another 2 times before I could view both files. Then again when I closed print preview etc. Have you tried it your end?

By the way, this report contains a sub report. Could this be linked to the problem I'm getting?

 

[arnelgp]

parameter Query plus a Form that you can select which Musician/Engagement.

 

[neilsolaris]

parameter Query plus a Form that you can select which Musician/Engagement.

Many thanks arnelgp. Unfortunately I'm not able to open that file. It says "unrecognized database format". Was it in a later version perhaps? I'm using 2007.

 

[Gasman]

I wish that were the case, but it's asking me constantly. It asked me three times before I was able to click on print preview, then another 2 times before I could view both files. Then again when I closed print preview etc. Have you tried it your end?

By the way, this report contains a sub report. Could this be linked to the problem I'm getting?

Yes, just tried it again, but I have used this method in the past once realising what was happening.
You could set a tempvar to the inputbox value and use that so that the subreports do not ask for it.

My report was only a single report?

 

[Gasman]

Many thanks arnelgp. Unfortunately I'm not able to open that file. It says "unrecognized database format". Was it in a later version perhaps? I'm using 2007.

Welcome to my world.

 

[Gasman]

I cannot find job number in your report?
Plus no point having combos in reports?
I'd have a button on the relevant form F_Payments, that supplies the criteria to the OpenReport comand.?
Leave the query with no parameter that has to be requested.?

Edit: I now see your are using F_Modules.
Use that button to get an input and if it has a value, use that for the WHERE clause, if empty print all reports?

 

[neilsolaris]

I cannot find job number in your report?
Plus no point having combos in reports?
I'd have a button on the relevant form F_Payments, that supplies the criteria to the OpenReport comand.?
Leave the query with no parameter that has to be requested.?

Edit: I now see your are using F_Modules.
Use that button to get an input and if it has a value, use that for the WHERE clause, if empty print all reports?

Sorry, I called it by the wrong name. When I said job number, I was referring to EngagementsText_FK. Probably you're going to tell me that it should be a number, not text?! I can probably change that.

I didn't know I shouldn't be using combos in the report! What should I have used instead? I can change it.

I don't know how to go about adding the button on the F_Payments form to do what you suggested. Could you talk me through it please?

Edit. I just read your edit! How do I use that button to get an input? Do you mean add an InputBox in the VBA code?

 

[neilsolaris]

Here is my code again. I don't know how to add the where criteria, so I'm pretty sure this isn't going to work. However, are you able to suggest how I can adjust it to make it work please? Thanks.

Private Sub Command1_Click()


    Dim rs                    As DAO.Recordset
    Dim sFolder               As String
    Dim sFile                 As String
    Dim answer                As String
    
    answer = InputBox("Enter engagement number")




    On Error GoTo Error_Handler


    sFolder = "D:\Documents\Orchestra\Musician Payments\FY ending 2022\"


    Set rs = CurrentDb.OpenRecordset("SELECT PlayerCode_PK FROM Q_Totals", "SELECT answer 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"
            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

 

[Gasman]

I'd change the combos to textboxes, just right click and change to.
You had an input box to get a value for findfirst in the other thread, why can't you use that?

I think you need to step back and think of what you want, as this piecemeal approach is going to cause you to keep amending code?

 

[Gasman]

You already have where criteria for that report? It is the playercode_pk?

 

[arnelgp]

manage to convert it back to A2007.
Open the 2 reports.

 

[neilsolaris]

I'd change the combos to textboxes, just right click and change to.
You had an input box to get a value for findfirst in the other thread, why can't you use that?

I think you need to step back and think of what you want, as this piecemeal approach is going to cause you to keep amending code?

I might well be able to use it, I'll check. With the other thread I was looking to save just one record, but with this one, I'm looking to loop through all the records. The criteria I have is looping through all the records belonging to the same engagement (EngagementsText_FK). I'm clear on that bit at least, I'm just struggling on the implementation!