Dynamic filtered report.

[pikoy]

can someone guide me on how to dynamically change the output of a filtered report based on a combobox.

DoCmd.OpenReport "[U][I]Build by Status[/I][/U]", acViewPreview, , var

I want the user to be able to select which report (Build by Status, Build by Priority and Build by Contractor) to open and apply the filter.

any response is appreciated.

 

[boblarson]

See my sample here.

 

[pikoy]

Thank you for the reply Bob.

Based on what you had sent... used it as a guide and created this ( see code below ). Now i am getting a 2103 runtime error. Also, can you do stDoc="field name"???

Dim stDocName As String
Dim stWhere As String
Dim StDoc As String
StDoc = "Me.Report"
If Not IsNull(Me.Status) Then
    stWhere = "[Status] = """ & Me.Status & """"
End If
If Not IsNull(Me.Priority) Then
    stWhere = "[Priority] = """ & Me.Priority & """"
End If
If Not IsNull(Me.Project) Then
        stWhere = "[Project] = """ & Me.Project & """"
End If
If Not IsNull(Me.OpenedBy) Then
       stWhere = "Issues.[Opened By] = """ & Me.OpenedBy & """"
End If
DoCmd.OpenReport StDoc, acViewPreview, , stWhere

 

[boblarson]

So you are wanting a report name from a field in the record source of your form? Hopefully your field is not REPORT because that is an Access Reserved Word (see here for a comprehensive list). If you have a control named Report which is bound to that field rename the control txtReport for example and then you can use

stDoc = Me.txtReport
without the quotes.

 

[pikoy]

I didn't notice that.... cboReport is the field name.

Thanks a million. Happy New Year.

 

[Leathem]

Bob, Your dynamic report sounds like exactly what I'm looking for, but I'm too new to Access to understand how your solution works and therefore how to set up one myself. My problem is probably simpler: I am currently generating many individual reports, each based on an individual query where each of these queries differs only in the field it looks at in a table. I'd love it if I could have a form that would produce the reports based on the user's entry of the field name. The only other thing would be to have the header on the report reflect the user's choice of fields.

 

[pikoy]

Bob, Your dynamic report sounds like exactly what I'm looking for, but I'm too new to Access to understand how your solution works and therefore how to set up one myself. My problem is probably simpler: I am currently generating many individual reports, each based on an individual query where each of these queries differs only in the field it looks at in a table. I'd love it if I could have a form that would produce the reports based on the user's entry of the field name. The only other thing would be to have the header on the report reflect the user's choice of fields.

Leathem,

I would have one query that will have all the fields and build a report based on that one query and pull it via a filtered report.... you can look at Bob's sample of reports on how each one should be coded. eg. number, etc...

also, I had my header blank as i have multiple filters. You could code and call each one but that would be too long. Report Name: XXXX, YYYY, DDDD, you know.

my two cents at least.

 

[Leathem]

Thanks for the reply. How do I set up a filtered report, though? Where does one put the filter or ask for user input?

 

[pikoy]

Thanks for the reply. How do I set up a filtered report, though? Where does one put the filter or ask for user input?

Build the filter on a form...create unbound fields for fields that you want it to filter. Then create a button and put the filter code on the button's onclick field.