Report with no record source

[BilboBaggins]

Hi all
I am trying to get a report to work running with no record
source. Most fields on the report are Dlookup or Dcount
fields so no source needed. I am trying to enter a date
when opening the form which the Dlookup fields use as part
of their criteria. A number of subreports also use the
date entered to filter fields. A couple of other fields
also use the date entered to do simple calculations. I
have tried an InputBox on the OnOpen event but cannot get
this to work as I am not sure how to refer to the result
of the input box in the Dlookup field in the report and
elsewhere.
Any suggestions?

Bilbo

 

[fredg]

Hi all
I am trying to get a report to work running with no record
source. Most fields on the report are Dlookup or Dcount
fields so no source needed. I am trying to enter a date
when opening the form which the Dlookup fields use as part
of their criteria. A number of subreports also use the
date entered to filter fields. A couple of other fields
also use the date entered to do simple calculations. I
have tried an InputBox on the OnOpen event but cannot get
this to work as I am not sure how to refer to the result
of the input box in the Dlookup field in the report and
elsewhere.
Any suggestions?

Bilbo

Create an Unbound form with whatever controls you need to enter the
needed parameter data.
Set the format of the control to the correct formatting.
Add a command button.
Code it's click event:
Me.Visible = False

Code the report's Open event:
DoCmd.OpenForm "FormName", , , , , acDialog

Then in each report, whenever you need the data from one of the form
controls, refer to the form control like this:
forms!FormName!ControlName

So a DLookUp in your report will look like something like this:

=DLookUp("[FieldName]","TableName","[DateField] = #" &
forms!FormName!ControlName & "#")

Code the Main report's close event:
DoCmd.Close acForm, "FormName"

Open the Main report. It will open the form. Enter the parameters
where needed. Click the command button.
The form will hide but it is still open.

As long as the form is open, that data is available in all of your
reports.

When you close the main report, it will close the form.