Get RecordSource of closed report.

[PhilOfWalton]

I am trying to extract the field name from a report's RecordSource.

I can't open the report in Design View, as eventually I want the database saved as ACCDE.

I can't open the report hidden as it needs parameters to run.

So for example, suppose 1 report needs a date range. I want to save the name of the field (DateField) in a table, and on opening the report, it opens a form asking for the From Date and To date for "DateField"

Another report needs an Employee range, so save "EmployeeID" in the table and the same form opens asking for From Employee To Employee.

Don't think it can be done, but anyway, it's worth asking.

Phil

[nico5038]

The requirements are a bit confusing, but all report controls can be managed by VBA.
So I once created a dynamic report to fill crosstable fields by naming them from the fields collection of the DAO recordset. Also using the .tag property of the field attribute or fixed names could be worth investigating...

Nic;o)

[jforbes]

I understand most of the mechanics you are looking to perform but I'm not exactly sure what you are attempting to accomplish and why.

It sounds like you are creating a Form to dynamically provide a Filter for a Report and then launch the Report. What confuses me most is that you say the report takes parameters to run. Typically, mixing Filters and Parameters isn't done; it's usually one or the other.

Not only that, in my experience, when creating a Form that inspects another object in Access, like another Form or Report, it is done as an Administrative Form, to inspect the object and then save things off into table that would be then used by users at runtime. In this case opening the other object for inspection in Design mode or running a Form or Report with a filter of "1=2" so that nothing is returned, even though it acts all crazy, is usually OK.

Again, what are you attempting to accomplish?

[PhilOfWalton]

Thanks for replies. I have had a complete change of mind as a result

What is currently happening is I open the report from a "Menu" and set it's Visible to False

Private Sub Report_Open(Cancel As Integer)
 
     Dim Args As String
 
     ReportCancelled = False                ' Global variable
 
     Me.Visible = False
 
     DoCmd.OpenForm "DateEntry", , , , , acDialog, Me.Name & "~-32764" & "~SignInDate"    ' Name, Report & Date to filter on
 
     If ReportCancelled = True Then              ' Cancelled from Date Input form
         Cancel = True
     End If
 
End Sub
 

This then opens this form (It's title is passed from the Menu)

After entering both dates the Run button does this:

Private Sub Buttonrpt_Click()
 
    Dim Rpt As Report
 
     'Blah Blah Check Dates OK (Code omitted)
 
    If IsReportLoaded(RptName) Then
        Set Rpt = Reports(RptName)
        Rpt.Visible = True
        Rpt.Filter = DateField & " Between #" & Format(FromDate, "Medium Date") & "# AND #" & Format(ToDate, "Medium Date") & "#"
        Rpt.FilterOn = True
    End If
 
    If IsNull(FromDate) Or IsNull(ToDate) Then
        If IsReportLoaded(RptName) Then
            DoCmd.Close acReport, RptName
        End If
    End If
 
    DoCmd.Close acForm, Me.Name
 
End Sub
 

This works fine, but as I said, I have had a complete re-think and realised I can set the Arguments from the OpenReport to "Re-Design" the date form to show whatever fields I want to use as the Report Filter.

Your answer put me on the right track. This should be fun!!!

Phil