Setting Controlsource for unbound textbox

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

deodev - 15 Oct 2005 15:29 GMT

Hello,

My question is, can I have a set of 12 unbound textboxes and set their
controlsource to the appropriate month, this way I don't need to open the
report every month to add a new month field.

Here is what I have currently...

I have a report with 13 unbound textboxes in the detail section - one for
name and the other 12 for the months of the year. My company Fiscal year is
from Nov through Oct.

In the OnOpen event I am able to determine the #of months that have elapsed
in the fiscal year - as of today it will be 11 - and my recordsource for the
report will also have only 11 months- so Oct will not be available at this
time.
I also have code that populate the text boxes accordingly - this is done for
some special formatting etc

ex Text1 = [Nov]
Text2 = [Dec] etc

However, before I can run the report, I also have to add to the report
detail section the actual fields from the report recordsource that is the
Nov, Dec, Jan etc

I then make these Not Visible and the report runs ok.

So if I need to include Oct I will have to add the Oct field first.

My issue is, can I add another set of 12 unbound textboxes and set their
controlsource to the appropriate month, this way I don't need to open the
report every month to add the new month field.

Sorry for the long e- mail

Deo.

Signature

deodev

Reply to this Message

Marshall Barton - 15 Oct 2005 17:27 GMT

>My question is, can I have a set of 12 unbound textboxes and set their
>controlsource to the appropriate month, this way I don't need to open the
[quoted text clipped - 27 lines]
>controlsource to the appropriate month, this way I don't need to open the
>report every month to add the new month field.

I did not follow all that, but, yes, you can set the text
box's control source in the report's open event.

Signature

Marsh
MVP [MS Access]

Reply to this Message

Duane Hookom - 15 Oct 2005 17:30 GMT

Here is a typical method that I use:

Try not to use "absolute" column headings for dates. You could possibly use
"relative" dates. For instance:
Form: frmA
Text Box: txtEndDate
Table: tblSales
Field: SaleDate
You want to show 12 months of sales in columns of a crosstab report.
Set the with menuing: Query|Parameter
Forms!frmA!txtEndDate Date/Time

Use this expression for your Column Headings:
ColHead:"Mth" & DateDiff("m",[SaleDate],Forms!frmA!txtEndDate)

This will produce Mth0, Mth1, Mth2, Mth3,... where Mth0 is sales from the
same month as the ending date on your form. Mth1 is the previous month etc.

Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
Build your report based on these "relative" months. If you need column
labels in your report, use text boxes with control sources of:
=DateAdd("m",0,Forms!frmA!txtEndDate)
=DateAdd("m",-1,Forms!frmA!txtEndDate)
=DateAdd("m",-2,Forms!frmA!txtEndDate)
=DateAdd("m",-3,Forms!frmA!txtEndDate)
...
This solution requires no code and will run fairly quickly.

Signature

Duane Hookom
MS Access MVP

>>My question is, can I have a set of 12 unbound textboxes and set their
>>controlsource to the appropriate month, this way I don't need to open the
[quoted text clipped - 34 lines]
> I did not follow all that, but, yes, you can set the text
> box's control source in the report's open event.

Reply to this Message