Setting Controlsource for unbound textbox

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
deodev said:
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.
Click to expand...


I did not follow all that, but, yes, you can set the text
box's control source in the report's open event.
 
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.

--
Duane Hookom
MS Access MVP


Marshall Barton said:
deodev said:
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.
Click to expand...


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