Dynamic References (form and report names held in string variables)

[roaftech]

I work with an aid charity and am developing a database which keeps details of aid donations and where the principal output from the database is various standard reports. The common feature of most of these reports is that they require specific date ranges, so I prepared a parameter form which has buttons preset for the various date combinations. When a report is opened from the switchboard it opens the parameter form (if not already open) and switches focus to the form. The form has another button "Apply Filter" which closes the report and re-opens it using the date options as a filter. The form also has a "Close Report" button for ending the session tidily. So far, so good. Now I would like to use this form or a similar form with most of my reports.



In this instance the form fS1DateSelector assembles the filter 'strFilter' and passes it to the report as part of a OpenReport instruction The report_open code includes the following line

Me.Filter = Forms!fS1DateSelector.strFilter

Everything works fine, indicating that all the necessary declarations, etc, have been dealt with elsewhere in the code.
I would like to replace the line above with something like this, where the form name is dynamic

Me.Filter = "[forms]![" & strFrmName & "].[strFilter]"

However, this does not work as required. The code appears to resolve to the correct call but then it opens a dialog box with the prompt 'Enter [forms]![fS1DateSelector].[strFilter]' .
I have a similar problem with the following:

    With Reports![rStockList]
       .Filter = strFilter
       .FilterOn = True

where I want to replace '[rStockList]' with a dynamic reference but all my permutations have resulted in syntax errors.

What is the correct syntax for dynamic references in these contexts?
Many thanks.

[June7]

Why does the form name need to be dynamic? Is report opened from more than one form?

strFilter is a variable or a textbox on form?

Use the WHERE CONDITION argument of OpenReport and the Me.Filter code is not needed.

DoCmd.OpenReport "reportname", , , strFilter

[roaftech]

Many thanks for your reply. I was trying to find a balance between providing enough info for the context and overloading the first post with confusing detail.

Operating sequence
1: The report is opened from a switchboard menu item, and then it opens the form (if not already open);
2: The form controls set up the filter and then reopen the report;
3: Repeat 2 as often as necessary;
4: The form has a button which closes the report (if not closed by its own close button) and then closes the form.

Why does the form name need to be dynamic? Is report opened from more than one form?

I can anticipate a situation where the report could be called from another form, eg when the beneficiary is the criteria instead of the date. However, I haven't thought through the details of this yet and it may be that we go down a separate route.

strFilter is a variable or a textbox on form?

strFilter is a variable which is declared and populated in the form, in this sequence

Option Explicit
Option Compare Database

Private Const strFrmName As String = "fS1DateSelector"  'parameter form
Private Const strQryName As String = "qAvizDataAll"     'data source query
Public strRptName As String   'report name
Public intFirstDate As Date   'first date
Public intFinalDate As Date   'final date
Public strFilter As String    'assembled filter
------------------------------------------    
Private Sub cmdApplyFilter_Click()
' assemble the filter using the date ranges
    strFilter = "[data] between [forms].[" & strFrmName & "].[firstdate] and [forms].[" & strFrmName & "].[finaldate]"
    
' If the report is open then close it. Reopen the report with new filter.
    If SysCmd(acSysCmdGetObjectState, acReport, strRptName) = acObjStateOpen Then
        DoCmd.Close acReport, strRptName
    End If
        DoCmd.OpenReport strRptName, acViewPreview, strFilter
End Sub

Use the WHERE CONDITION argument of OpenReport and the Me.Filter code is not needed.
DoCmd.OpenReport "reportname", , , strFilter

Comparing your code with my version above, it seems that I have confused FilterName and WhereCondition, and I need another comma.
Do I still need the 'FilterOn=True' statement when using that process?

I'll come back to the Reports! context described in the first post in a separate post in order to avoid prolonging this one.
Many thanks for your assistance, not just with this but also with your responses to other questions, which I have read 'on the quiet'.

[June7]

Do not need the FilterOn=True code when using WHERE CONDITION.

Date values need # delimiter.

You don't want the form/control names in the strWHERE construct, just the date values that are in the controls for the form.

Is [data] the actual name of Date/Time field in the report RecordSource? Try:

strFilter = "[data] BETWEEN #" & Forms(strFrmName).[firstdate] & "# AND #" & Forms(strFrmName).[finaldate] & "#"

[roaftech]

Many thanks for your suggestions - I'll give them a go.

The forms and report are presented in Romanian whilst the coding is in English. I tend to use Romanian names for those fields which our staff will use and English abreviations for others. It does not help that 'data' is Romanian for 'date' and 'date' is Romanian for 'data'.

[June7]

Then don't use 'data' and 'date' for field names. Reserved words should not be used as field names anyway.

Better would be something like DateEnter (or the Romanian equivalent).