Dynamically open and filter different reports from a single form

Question

I'm trying to open and filter a report based on the report that a user selects from a combobox in a form. To simplify the example, here's what I'm dealing with:

First I'm opening the report:

Dim strReportName As String
strReportName = "General Information"
DoCmd.OpenReport strReportName, acViewPreview

This works fine, the problem arrises when I'm trying to apply a filter.

With Reports!strReportName
    .Filter = strFilter
    .FilterOn = True
End With

The problem I can't find my way around, is that it seems impossible to use a variable reportname in this syntax. Needless to say, the error thrown by VBA is as follows:

Run-time error '2451':

The report name 'strReportName' you entered is misspelled or refers to a report that 
isn't open or doesn't exist.

My question is: Is there any way to alter this statement into letting me filter the report that's currently open without needing to hardcode the reportnames into VBA?

the only way I can think of its to insert a module programmatically and replace the strReportName with the actual name. I wouldn't really recommend doing it that way but it's the only solution I am aware of — user2140173
– user2140173, Commented Nov 20, 2013 at 13:52

HansUp · Accepted Answer · 2013-11-20 23:38:07Z

0

When strReportName is a variable which contains the name of a report, you discovered this fails ..

With Reports!strReportName

Use this instead ...

With Reports(strReportName)

edited Nov 20, 2013 at 23:38

answered Nov 20, 2013 at 14:44

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3013200 Over a year ago

Why are the biggest braincrackers always so straightforward in the end... Thank you!

Collectives™ on Stack Overflow

Dynamically open and filter different reports from a single form

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related