Setting ControlSource for a textbox on report

  • Thread starter Thread starter ng
  • Start date Start date
N

ng

I have a pivot table query that returns data let's say
quantity based on dates. The user inputs the date range.

e.g.

2003-01 550
2003-02 500
....

I have created a report based on this query. Since the
dates can change I want to change the control source of
the text box on report.

In the "On Open" event I want to set the controlsource as

Me!Month1.ControlSource = fldQueryDef.name

I get a run-time error that this is not supported.

If I do
Me!Month1.ControlSource = "2003-01" I don't get an error.

Any help is appreciated.

Thanks,
ng
 
Here is a response from a similar posting a while back. You should be able
to adapt it to your needs. If you can't figure it out, come on back:
First, I would create a form with a text box that allows users to enter just
the Start Date. If this is a six week forecast then you should not need the
user to enter the End Date. Assume a Form "frmRptDate" and text box
"txtStartDate".

Then create a calculated "relative" date for the column headings of the
crosstab

ColHead:"Wk" & DateDiff("w",Forms!frmRptDate!txtStartDate,[Delivery Date])
This should calculate columns with headings like:
"Wk0";"Wk1";"Wk2";"Wk3";..."Wk5"
Wk0 will be deliveries on the Start Date week and Wk5 will be 5 weeks out.
Set the column headings property to "Wk0";"Wk1";"Wk2";"Wk3";..."Wk5" and set
the query parameter
Forms!frmRptDate!txtStartDate Date/Time
You can set a criteria on the [Delivery Date] field to
Between Forms!frmRptDate!txtStartDate and
DateAdd("w",5,Forms!frmRptDate!txtStartDate)
You can add a column to the crosstab to count all deliveries for the report.
[Delivery Date]
Count
Row Heading

To create column labels in the report, use text boxes
=Forms!frmRptDate!txtStartDate
=DateAdd("w",1,Forms!frmRptDate!txtStartDate)
=DateAdd("w",2,Forms!frmRptDate!txtStartDate)
=DateAdd("w",3,Forms!frmRptDate!txtStartDate)
=DateAdd("w",4,Forms!frmRptDate!txtStartDate)
=DateAdd("w",5,Forms!frmRptDate!txtStartDate)
 
Back
Top