Using variables in control sources

[Mike]

how can i set a variable for the control source of a field in a report so
that i can use a form to determine what the control source should be. for
example, my report will show budgets for a select group of fiscal years. i
want use a form that will allow me to choose which fiscal years are shown.
My report is linked to a crosstab query that breaks down my information as
follows:

Budget Line FY1 FY2 FY3 FY4
FY5

So basically im asking how can i use a form to determine which fiscal years
(FY) are used

 

[strive4peace]

Base your report on a query

Before you render your report, replace the sql in your query and
redefine the fields -- be sure to use field aliases so you can refer to
them generically when you set up your report and redefine the SQL

ie:

SELECT FY1 as f1, FY2 as f2, FY3 as f3, etc

then, in the open event of the report, replace the column captions with
the correct years

me.label1.caption = "whatever"
etc
.... don't know how you are collecting the parameters...

this code goes into a general module:

'~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 6-29-06
'crystal
'strive4peace2006 at yahoo dot com
On Error GoTo Proc_Err

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume

Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~

since you have to replace the captions of your columns, you could just
replace the controlsource for your data controls too -- just depends if
you have calculations such as SUM with them... just might be easier to
redefine the query...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[Mike]

I'm lost

I'm a bit of anewbie to access. If you can break it down for me i'd really
appreciate it. I've include as much as I can below. If u need anything else
let me know.
------------------------------
Here is my crosstab query SQL

TRANSFORM Val(nz(Sum([Capital Job Database].[Estimated Cost]),0)) AS
[SumOfEstimated Cost]
SELECT [Budget Line].[Budget Line]
FROM [Budget Line] LEFT JOIN [Capital Job Database] ON [Budget Line].[Budget
Line] = [Capital Job Database].[Budget Line]
WHERE ((([Budget Line].[Budget Line]) Like "WP-112*") AND (([Capital Job
Database].[Contract Status])<>"(6) Cancelled" Or ([Capital Job
Database].[Contract Status]) Is Null))
GROUP BY [Budget Line].[Budget Line]
PIVOT [Capital Job Database].[Scheduled Fiscal Year] In
(1900,2006,2007,2008,2009,2010); <<<---I don't always want these as the
column headers. Again I want to be able to choose from a form.
------------------------------------------

This is my open event for the report which opens the form


Private Sub Report_Open(Cancel As Integer)

' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open BEPA Reports Form
DoCmd.OpenForm "WP-112 Summary Reports", , , , , acDialog

' Cancel Report if User Clicked the Cancel Button
If IsLoaded("WP-112 Summary Reports") = False Then Cancel = True

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False
End Sub
-----------------------------------
The name of the form i want to use is "WP-112 Summary Reports" and the field
captions I have set up to select the fiscal years are "Year 1", "Year 2",
"Year 3", "Year 4" and "Year 5". All five of these are unbound fields

Currently in the report the five fileds that are to take the 5 years are as
follows:

Caption: 2006 2007 2008 2009 2010
Control Source: 2006 2007 2008 2009 2010

PS when i make the module is there a specific name i should give it

Base your report on a query

Before you render your report, replace the sql in your query and
redefine the fields -- be sure to use field aliases so you can refer to
them generically when you set up your report and redefine the SQL

ie:

SELECT FY1 as f1, FY2 as f2, FY3 as f3, etc

then, in the open event of the report, replace the column captions with
the correct years

me.label1.caption = "whatever"
etc
.... don't know how you are collecting the parameters...

this code goes into a general module:

'~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 6-29-06
'crystal
'strive4peace2006 at yahoo dot com
On Error GoTo Proc_Err

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume

Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~

since you have to replace the captions of your columns, you could just
replace the controlsource for your data controls too -- just depends if
you have calculations such as SUM with them... just might be easier to
redefine the query...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

how can i set a variable for the control source of a field in a report so
that i can use a form to determine what the control source should be. for
example, my report will show budgets for a select group of fiscal years. i
want use a form that will allow me to choose which fiscal years are shown.
My report is linked to a crosstab query that breaks down my information as
follows:

Budget Line FY1 FY2 FY3 FY4
FY5

So basically im asking how can i use a form to determine which fiscal years
(FY) are used

 

[strive4peace]

Hi Mike,

If you were just reporting from the crosstab, it would be easy...

your column headers come from this line:

PIVOT [Capital Job Database].[Scheduled Fiscal Year] In
(1900,2006,2007,2008,2009,2010)

and they would change as your query did... but translating those changes
to a report requires a few more steps...

I am assuming, in your sample data, that the first column is to be
ignored? Seems you need a way to choose years for 5 columns... and
ignore years (make Visible=No) when they are not specified...

there are much more elegant ways, but this is easy to understand...<g>

on the form where you make your choices for the report (I will refer to
this form as ReportMenu)

make 5 comboboxes to get their values from the available fiscal years

If the same year is entered in 2 comboboxes, the user will see that on
the report and they, presumeably meant that or they will change the
criteria and generate the report again...

these comboboxes on the ReportMenu form, for purposes of this example,
will be named Y1, Y2, Y3, Y4, and Y5.

The 5 subreport controls comprising the columns of the main report will
be set up such that each is linked to one of 5 calculated controls
corresponding to the ReportMenu form choices

ie:

'~~~~~~~~~~~~~~~

ReportMenu unbound combobox control names:
Y1
Y2
Y3
Y4
Y5

'~~~~~~~~~~~~~~~

ReportMenu subreports:
ControlSource --> subReport_formname
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FiscalYear_controlname

ControlSource --> subReport_formname
Name --> subReport2
LinkMasterfields --> Y2
LinkChildFields --> FiscalYear_controlname

etc

'~~~~~~~~~~~~~~~

by doing things this way, you can use the same subreport for each case
-- just change the LinkMasterField and the caption for the column label


in the Open event for the report

'~~~~~~~~~~~~~~~
dim mBoo as Boolean

if Not IsLoaded("ReportMenu") then
msgbox "Aborting report...",, _
"ReportMenu is not open"
Cancel = true
end if

with forms!ReportMenu

mboo = IIF(IsNull(.Y1), False, True)
if mBoo then
me.Y1 = .Y1
me.Label_Y1.Caption = .Y1
end if
subReport1.visible = mBoo
me.Label_Y1.visible = mBoo

mboo = IIF(IsNull(.Y2), False, True)
if mBoo then
me.Y2 = .Y
me.Label_Y2.Caption = .Y2
end if
subReport2.visible = mBoo
me.Label_Y2.visible = mBoo

'etc


end with
end if
'~~~~~~~~~~~~~~~

Now we have set the LinkMasterfields for each subreport so the ones that
the user picked will show

For columns that have not been picked, their Visible property will be False

the caption has been changed

Pay attention to the controlnames in the code that are specified and
check (or change) your respective names (ie: Y1, Label_Y1). If you
stick to a sequential way of naming, you can actually cut out quite a
bit of code by looping, but I thought this might be easier to initially
understand...



General module:
'~~~~~~~~~~~
Function IsLoaded(ByVal pFormName As String) As Boolean

IsLoaded = False


If CurrentProject.AllForms(pFormName ).IsLoaded Then
If Forms(pFormName).CurrentView <> 0 Then
'True if form is open not in Design View
IsLoaded = True
End If
End If

End Function
'~~~~~~~~~~~

you might have to put some of the logic in other events, I did not test
this, just trying to help you think...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I'm lost

I'm a bit of anewbie to access. If you can break it down for me i'd really
appreciate it. I've include as much as I can below. If u need anything else
let me know.
------------------------------
Here is my crosstab query SQL

TRANSFORM Val(nz(Sum([Capital Job Database].[Estimated Cost]),0)) AS
[SumOfEstimated Cost]
SELECT [Budget Line].[Budget Line]
FROM [Budget Line] LEFT JOIN [Capital Job Database] ON [Budget Line].[Budget
Line] = [Capital Job Database].[Budget Line]
WHERE ((([Budget Line].[Budget Line]) Like "WP-112*") AND (([Capital Job
Database].[Contract Status])<>"(6) Cancelled" Or ([Capital Job
Database].[Contract Status]) Is Null))
GROUP BY [Budget Line].[Budget Line]
PIVOT [Capital Job Database].[Scheduled Fiscal Year] In
(1900,2006,2007,2008,2009,2010); <<<---I don't always want these as the
column headers. Again I want to be able to choose from a form.
------------------------------------------

This is my open event for the report which opens the form


Private Sub Report_Open(Cancel As Integer)

' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open BEPA Reports Form
DoCmd.OpenForm "WP-112 Summary Reports", , , , , acDialog

' Cancel Report if User Clicked the Cancel Button
If IsLoaded("WP-112 Summary Reports") = False Then Cancel = True

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False
End Sub
-----------------------------------
The name of the form i want to use is "WP-112 Summary Reports" and the field
captions I have set up to select the fiscal years are "Year 1", "Year 2",
"Year 3", "Year 4" and "Year 5". All five of these are unbound fields

Currently in the report the five fileds that are to take the 5 years are as
follows:

Caption: 2006 2007 2008 2009 2010
Control Source: 2006 2007 2008 2009 2010

PS when i make the module is there a specific name i should give it

Base your report on a query

Before you render your report, replace the sql in your query and
redefine the fields -- be sure to use field aliases so you can refer to
them generically when you set up your report and redefine the SQL

ie:

SELECT FY1 as f1, FY2 as f2, FY3 as f3, etc

then, in the open event of the report, replace the column captions with
the correct years

me.label1.caption = "whatever"
etc
.... don't know how you are collecting the parameters...

this code goes into a general module:

'~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 6-29-06
'crystal
'strive4peace2006 at yahoo dot com
On Error GoTo Proc_Err

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume

Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~

since you have to replace the captions of your columns, you could just
replace the controlsource for your data controls too -- just depends if
you have calculations such as SUM with them... just might be easier to
redefine the query...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

how can i set a variable for the control source of a field in a report so
that i can use a form to determine what the control source should be. for
example, my report will show budgets for a select group of fiscal years. i
want use a form that will allow me to choose which fiscal years are shown.
My report is linked to a crosstab query that breaks down my information as
follows:

Budget Line FY1 FY2 FY3 FY4
FY5

So basically im asking how can i use a form to determine which fiscal years
(FY) are used

 

[Mike]

Im a little lost still

I made the form as you said with 5 unbound columns named Y1 to Y5

I don't get the next part

ReportMenu subreports:
ControlSource --> subReport_formname
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FiscalYear_controlname

etc

Thanks for all the help.

Hi Mike,

If you were just reporting from the crosstab, it would be easy...

your column headers come from this line:

PIVOT [Capital Job Database].[Scheduled Fiscal Year] In
(1900,2006,2007,2008,2009,2010)

and they would change as your query did... but translating those changes
to a report requires a few more steps...

I am assuming, in your sample data, that the first column is to be
ignored? Seems you need a way to choose years for 5 columns... and
ignore years (make Visible=No) when they are not specified...

there are much more elegant ways, but this is easy to understand...<g>

on the form where you make your choices for the report (I will refer to
this form as ReportMenu)

make 5 comboboxes to get their values from the available fiscal years

If the same year is entered in 2 comboboxes, the user will see that on
the report and they, presumeably meant that or they will change the
criteria and generate the report again...

these comboboxes on the ReportMenu form, for purposes of this example,
will be named Y1, Y2, Y3, Y4, and Y5.

The 5 subreport controls comprising the columns of the main report will
be set up such that each is linked to one of 5 calculated controls
corresponding to the ReportMenu form choices

ie:

'~~~~~~~~~~~~~~~

ReportMenu unbound combobox control names:
Y1
Y2
Y3
Y4
Y5

'~~~~~~~~~~~~~~~

ReportMenu subreports:
ControlSource --> subReport_formname
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FiscalYear_controlname

ControlSource --> subReport_formname
Name --> subReport2
LinkMasterfields --> Y2
LinkChildFields --> FiscalYear_controlname

etc

'~~~~~~~~~~~~~~~

by doing things this way, you can use the same subreport for each case
-- just change the LinkMasterField and the caption for the column label


in the Open event for the report

'~~~~~~~~~~~~~~~
dim mBoo as Boolean

if Not IsLoaded("ReportMenu") then
msgbox "Aborting report...",, _
"ReportMenu is not open"
Cancel = true
end if

with forms!ReportMenu

mboo = IIF(IsNull(.Y1), False, True)
if mBoo then
me.Y1 = .Y1
me.Label_Y1.Caption = .Y1
end if
subReport1.visible = mBoo
me.Label_Y1.visible = mBoo

mboo = IIF(IsNull(.Y2), False, True)
if mBoo then
me.Y2 = .Y
me.Label_Y2.Caption = .Y2
end if
subReport2.visible = mBoo
me.Label_Y2.visible = mBoo

'etc


end with
end if
'~~~~~~~~~~~~~~~

Now we have set the LinkMasterfields for each subreport so the ones that
the user picked will show

For columns that have not been picked, their Visible property will be False

the caption has been changed

Pay attention to the controlnames in the code that are specified and
check (or change) your respective names (ie: Y1, Label_Y1). If you
stick to a sequential way of naming, you can actually cut out quite a
bit of code by looping, but I thought this might be easier to initially
understand...



General module:
'~~~~~~~~~~~
Function IsLoaded(ByVal pFormName As String) As Boolean

IsLoaded = False


If CurrentProject.AllForms(pFormName ).IsLoaded Then
If Forms(pFormName).CurrentView <> 0 Then
'True if form is open not in Design View
IsLoaded = True
End If
End If

End Function
'~~~~~~~~~~~

you might have to put some of the logic in other events, I did not test
this, just trying to help you think...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I'm lost

I'm a bit of anewbie to access. If you can break it down for me i'd really
appreciate it. I've include as much as I can below. If u need anything else
let me know.
------------------------------
Here is my crosstab query SQL

TRANSFORM Val(nz(Sum([Capital Job Database].[Estimated Cost]),0)) AS
[SumOfEstimated Cost]
SELECT [Budget Line].[Budget Line]
FROM [Budget Line] LEFT JOIN [Capital Job Database] ON [Budget Line].[Budget
Line] = [Capital Job Database].[Budget Line]
WHERE ((([Budget Line].[Budget Line]) Like "WP-112*") AND (([Capital Job
Database].[Contract Status])<>"(6) Cancelled" Or ([Capital Job
Database].[Contract Status]) Is Null))
GROUP BY [Budget Line].[Budget Line]
PIVOT [Capital Job Database].[Scheduled Fiscal Year] In
(1900,2006,2007,2008,2009,2010); <<<---I don't always want these as the
column headers. Again I want to be able to choose from a form.
------------------------------------------

This is my open event for the report which opens the form


Private Sub Report_Open(Cancel As Integer)

' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open BEPA Reports Form
DoCmd.OpenForm "WP-112 Summary Reports", , , , , acDialog

' Cancel Report if User Clicked the Cancel Button
If IsLoaded("WP-112 Summary Reports") = False Then Cancel = True

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False
End Sub
-----------------------------------
The name of the form i want to use is "WP-112 Summary Reports" and the field
captions I have set up to select the fiscal years are "Year 1", "Year 2",
"Year 3", "Year 4" and "Year 5". All five of these are unbound fields

Currently in the report the five fileds that are to take the 5 years are as
follows:

Caption: 2006 2007 2008 2009 2010
Control Source: 2006 2007 2008 2009 2010

PS when i make the module is there a specific name i should give it

Base your report on a query

Before you render your report, replace the sql in your query and
redefine the fields -- be sure to use field aliases so you can refer to
them generically when you set up your report and redefine the SQL

ie:

SELECT FY1 as f1, FY2 as f2, FY3 as f3, etc

then, in the open event of the report, replace the column captions with
the correct years

me.label1.caption = "whatever"
etc
.... don't know how you are collecting the parameters...

this code goes into a general module:

'~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 6-29-06
'crystal
'strive4peace2006 at yahoo dot com
On Error GoTo Proc_Err

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume

Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~

since you have to replace the captions of your columns, you could just
replace the controlsource for your data controls too -- just depends if
you have calculations such as SUM with them... just might be easier to
redefine the query...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Mike wrote:
how can i set a variable for the control source of a field in a report so
that i can use a form to determine what the control source should be. for
example, my report will show budgets for a select group of fiscal years. i
want use a form that will allow me to choose which fiscal years are shown.
My report is linked to a crosstab query that breaks down my information as
follows:

Budget Line FY1 FY2 FY3 FY4
FY5

So basically im asking how can i use a form to determine which fiscal years
(FY) are used

 

[strive4peace]

Hi Mike,

Good! You have a form (called ReportMenu?) with 5 comboboxes... we will
put this on hold now and use this later. Perhaps it would be best to
focus on the reports first -- make a subreport in one column and
hardcode in the value of the link until that works -- then we will add
columns and then add code...

correction: subReport_formname should have been subReportName

'~~~~~~~~~~~`

The next step is to make ONE report that will be used as a subreport in
one of the "columns" of your main report

in this report, use the report header but make VISIBLE=No (after you
test it). In this section, be sure to put the fiscal year field as well
as any other fields you want to use to link to the main report. To ease
confusion, open the property sheet and make the NAME property the same
as the ControlSource property for the linking control(s).

For reference, I will call the linking control for fiscal year:
FY_controlname

Make the width of the report the same as the width of a "column" on your
main report.

save this report (which I am referring to as subReportName -- so you
will have to substitute the name you actually give it (don't use spaces
or special characters except the underscore when you name it)

'~~~~~~~~~~~
Now, go to design view of the Main Report

make an unbound textbox control to hold the fiscal year for the
subreport in the first column

Name --> Y1
Visible --> No (change after testing)
ControlSource --> = 2006

(or use another value for the control source, I don't know what you data
is -- once you get the hardcoded values to work, we will work with you
on the code to make them variable)

make a subreport control for the first column

ControlSource --> subReportName
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FY_controlname

'~~~~~~~~~~~`

try this and let us know if it is successful!


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Im a little lost still

I made the form as you said with 5 unbound columns named Y1 to Y5

I don't get the next part

ReportMenu subreports:
ControlSource --> subReport_formname
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FiscalYear_controlname

etc

Thanks for all the help.

Hi Mike,

If you were just reporting from the crosstab, it would be easy...

your column headers come from this line:

PIVOT [Capital Job Database].[Scheduled Fiscal Year] In
(1900,2006,2007,2008,2009,2010)

and they would change as your query did... but translating those changes
to a report requires a few more steps...

I am assuming, in your sample data, that the first column is to be
ignored? Seems you need a way to choose years for 5 columns... and
ignore years (make Visible=No) when they are not specified...

there are much more elegant ways, but this is easy to understand...<g>

on the form where you make your choices for the report (I will refer to
this form as ReportMenu)

make 5 comboboxes to get their values from the available fiscal years

If the same year is entered in 2 comboboxes, the user will see that on
the report and they, presumeably meant that or they will change the
criteria and generate the report again...

these comboboxes on the ReportMenu form, for purposes of this example,
will be named Y1, Y2, Y3, Y4, and Y5.

The 5 subreport controls comprising the columns of the main report will
be set up such that each is linked to one of 5 calculated controls
corresponding to the ReportMenu form choices

ie:

'~~~~~~~~~~~~~~~

ReportMenu unbound combobox control names:
Y1
Y2
Y3
Y4
Y5

'~~~~~~~~~~~~~~~

ReportMenu subreports:
ControlSource --> subReport_formname
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FiscalYear_controlname

ControlSource --> subReport_formname
Name --> subReport2
LinkMasterfields --> Y2
LinkChildFields --> FiscalYear_controlname

etc

'~~~~~~~~~~~~~~~

by doing things this way, you can use the same subreport for each case
-- just change the LinkMasterField and the caption for the column label


in the Open event for the report

'~~~~~~~~~~~~~~~
dim mBoo as Boolean

if Not IsLoaded("ReportMenu") then
msgbox "Aborting report...",, _
"ReportMenu is not open"
Cancel = true
end if

with forms!ReportMenu

mboo = IIF(IsNull(.Y1), False, True)
if mBoo then
me.Y1 = .Y1
me.Label_Y1.Caption = .Y1
end if
subReport1.visible = mBoo
me.Label_Y1.visible = mBoo

mboo = IIF(IsNull(.Y2), False, True)
if mBoo then
me.Y2 = .Y
me.Label_Y2.Caption = .Y2
end if
subReport2.visible = mBoo
me.Label_Y2.visible = mBoo

'etc


end with
end if
'~~~~~~~~~~~~~~~

Now we have set the LinkMasterfields for each subreport so the ones that
the user picked will show

For columns that have not been picked, their Visible property will be False

the caption has been changed

Pay attention to the controlnames in the code that are specified and
check (or change) your respective names (ie: Y1, Label_Y1). If you
stick to a sequential way of naming, you can actually cut out quite a
bit of code by looping, but I thought this might be easier to initially
understand...



General module:
'~~~~~~~~~~~
Function IsLoaded(ByVal pFormName As String) As Boolean

IsLoaded = False


If CurrentProject.AllForms(pFormName ).IsLoaded Then
If Forms(pFormName).CurrentView <> 0 Then
'True if form is open not in Design View
IsLoaded = True
End If
End If

End Function
'~~~~~~~~~~~

you might have to put some of the logic in other events, I did not test
this, just trying to help you think...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I'm lost

I'm a bit of anewbie to access. If you can break it down for me i'd really
appreciate it. I've include as much as I can below. If u need anything else
let me know.
------------------------------
Here is my crosstab query SQL

TRANSFORM Val(nz(Sum([Capital Job Database].[Estimated Cost]),0)) AS
[SumOfEstimated Cost]
SELECT [Budget Line].[Budget Line]
FROM [Budget Line] LEFT JOIN [Capital Job Database] ON [Budget Line].[Budget
Line] = [Capital Job Database].[Budget Line]
WHERE ((([Budget Line].[Budget Line]) Like "WP-112*") AND (([Capital Job
Database].[Contract Status])<>"(6) Cancelled" Or ([Capital Job
Database].[Contract Status]) Is Null))
GROUP BY [Budget Line].[Budget Line]
PIVOT [Capital Job Database].[Scheduled Fiscal Year] In
(1900,2006,2007,2008,2009,2010); <<<---I don't always want these as the
column headers. Again I want to be able to choose from a form.
------------------------------------------

This is my open event for the report which opens the form


Private Sub Report_Open(Cancel As Integer)

' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open BEPA Reports Form
DoCmd.OpenForm "WP-112 Summary Reports", , , , , acDialog

' Cancel Report if User Clicked the Cancel Button
If IsLoaded("WP-112 Summary Reports") = False Then Cancel = True

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False
End Sub
-----------------------------------
The name of the form i want to use is "WP-112 Summary Reports" and the field
captions I have set up to select the fiscal years are "Year 1", "Year 2",
"Year 3", "Year 4" and "Year 5". All five of these are unbound fields

Currently in the report the five fileds that are to take the 5 years are as
follows:

Caption: 2006 2007 2008 2009 2010
Control Source: 2006 2007 2008 2009 2010

PS when i make the module is there a specific name i should give it


:

Base your report on a query

Before you render your report, replace the sql in your query and
redefine the fields -- be sure to use field aliases so you can refer to
them generically when you set up your report and redefine the SQL

ie:

SELECT FY1 as f1, FY2 as f2, FY3 as f3, etc

then, in the open event of the report, replace the column captions with
the correct years

me.label1.caption = "whatever"
etc
.... don't know how you are collecting the parameters...

this code goes into a general module:

'~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub MakeQuery( _
ByVal pSql As String, _
ByVal qName As String)

'modified 6-29-06
'crystal
'strive4peace2006 at yahoo dot com
On Error GoTo Proc_Err

'if query already exists, update the SQL
'if not, create the query

If Nz(DLookup("[Name]", "MSysObjects", _
"[Name]='" & qName _
& "' And [Type]=5"), "") = "" Then
CurrentDb.CreateQueryDef qName, pSql
Else
CurrentDb.QueryDefs(qName).sql = pSql
End If

Proc_exit:
CurrentDb.QueryDefs.Refresh
DoEvents
Exit Sub

Proc_error:
MsgBox Err.Description, , _
"ERROR " & Err.Number & " MakeQuery"

'Press F8 to step through code and find problem
'comment next line out when program is debugged
Stop: Resume

Resume Proc_exit
End Sub
'~~~~~~~~~~~~~~~~~~~~~~~~~~

since you have to replace the captions of your columns, you could just
replace the controlsource for your data controls too -- just depends if
you have calculations such as SUM with them... just might be easier to
redefine the query...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Mike wrote:
how can i set a variable for the control source of a field in a report so
that i can use a form to determine what the control source should be. for
example, my report will show budgets for a select group of fiscal years. i
want use a form that will allow me to choose which fiscal years are shown.
My report is linked to a crosstab query that breaks down my information as
follows:

Budget Line FY1 FY2 FY3 FY4
FY5

So basically im asking how can i use a form to determine which fiscal years
(FY) are used

 

[Mike]

First of I would like to say thank you for all ur help so far.

I really didn't understand what youve said (im kind of a newbie to access)
but by playing around with the codes i got it to work. I have my crosstab
query that i entered column headers from 1900 to 2100. I made 5 unbound
fields in my report named Year 1, Year 2 etc

in the open event I used the following:
----
Private Sub Report_Open(Cancel As Integer)

' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open BEPA Reports Form
DoCmd.OpenForm "WP-112 Summary Reports", , , , , acDialog

' Cancel Report if User Clicked the Cancel Button
If IsLoaded("WP-112 Summary Reports") = False Then Cancel = True

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False

Me.Year_1.ControlSource = [Forms]![WP-112 Summary Reports]![Year 1]
Me.Year_2.ControlSource = [Forms]![WP-112 Summary Reports]![Year 2]
Me.Year_3.ControlSource = [Forms]![WP-112 Summary Reports]![Year 3]
Me.Year_4.ControlSource = [Forms]![WP-112 Summary Reports]![Year 4]
Me.Year_5.ControlSource = [Forms]![WP-112 Summary Reports]![Year 5]


End Sub
----

This allowed my report, whos record source is the crosstab query, to use the
5 years that i selected on my form and use them as column headers in the
report (it will only work for years 1900 to 2100. but thats ok)

The only problem i still have is as follows: say i open the report. on open
the form pops up. (on the form i have an exit button to cancel the
operation). if i hit the exit button i get the following error: Microsoft
Office Access can't find the form "WP-112 Summary Reports" reffered to in a
marco or Visual Basic code.

It seems that its still running the code even though i exitted out of the
form how can i have the code stop if the exit button is pressed.

Hi Mike,

Good! You have a form (called ReportMenu?) with 5 comboboxes... we will
put this on hold now and use this later. Perhaps it would be best to
focus on the reports first -- make a subreport in one column and
hardcode in the value of the link until that works -- then we will add
columns and then add code...

correction: subReport_formname should have been subReportName

'~~~~~~~~~~~`

The next step is to make ONE report that will be used as a subreport in
one of the "columns" of your main report

in this report, use the report header but make VISIBLE=No (after you
test it). In this section, be sure to put the fiscal year field as well
as any other fields you want to use to link to the main report. To ease
confusion, open the property sheet and make the NAME property the same
as the ControlSource property for the linking control(s).

For reference, I will call the linking control for fiscal year:
FY_controlname

Make the width of the report the same as the width of a "column" on your
main report.

save this report (which I am referring to as subReportName -- so you
will have to substitute the name you actually give it (don't use spaces
or special characters except the underscore when you name it)

'~~~~~~~~~~~
Now, go to design view of the Main Report

make an unbound textbox control to hold the fiscal year for the
subreport in the first column

Name --> Y1
Visible --> No (change after testing)
ControlSource --> = 2006

(or use another value for the control source, I don't know what you data
is -- once you get the hardcoded values to work, we will work with you
on the code to make them variable)

make a subreport control for the first column

ControlSource --> subReportName
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FY_controlname

'~~~~~~~~~~~`

try this and let us know if it is successful!


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Im a little lost still

I made the form as you said with 5 unbound columns named Y1 to Y5

I don't get the next part

ReportMenu subreports:
ControlSource --> subReport_formname
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FiscalYear_controlname

etc

Thanks for all the help.

Hi Mike,

If you were just reporting from the crosstab, it would be easy...

your column headers come from this line:

PIVOT [Capital Job Database].[Scheduled Fiscal Year] In
(1900,2006,2007,2008,2009,2010)

and they would change as your query did... but translating those changes
to a report requires a few more steps...

I am assuming, in your sample data, that the first column is to be
ignored? Seems you need a way to choose years for 5 columns... and
ignore years (make Visible=No) when they are not specified...

there are much more elegant ways, but this is easy to understand...<g>

on the form where you make your choices for the report (I will refer to
this form as ReportMenu)

make 5 comboboxes to get their values from the available fiscal years

If the same year is entered in 2 comboboxes, the user will see that on
the report and they, presumeably meant that or they will change the
criteria and generate the report again...

these comboboxes on the ReportMenu form, for purposes of this example,
will be named Y1, Y2, Y3, Y4, and Y5.

The 5 subreport controls comprising the columns of the main report will
be set up such that each is linked to one of 5 calculated controls
corresponding to the ReportMenu form choices

ie:

'~~~~~~~~~~~~~~~

ReportMenu unbound combobox control names:
Y1
Y2
Y3
Y4
Y5

'~~~~~~~~~~~~~~~

ReportMenu subreports:
ControlSource --> subReport_formname
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FiscalYear_controlname

ControlSource --> subReport_formname
Name --> subReport2
LinkMasterfields --> Y2
LinkChildFields --> FiscalYear_controlname

etc

'~~~~~~~~~~~~~~~

by doing things this way, you can use the same subreport for each case
-- just change the LinkMasterField and the caption for the column label


in the Open event for the report

'~~~~~~~~~~~~~~~
dim mBoo as Boolean

if Not IsLoaded("ReportMenu") then
msgbox "Aborting report...",, _
"ReportMenu is not open"
Cancel = true
end if

with forms!ReportMenu

mboo = IIF(IsNull(.Y1), False, True)
if mBoo then
me.Y1 = .Y1
me.Label_Y1.Caption = .Y1
end if
subReport1.visible = mBoo
me.Label_Y1.visible = mBoo

mboo = IIF(IsNull(.Y2), False, True)
if mBoo then
me.Y2 = .Y
me.Label_Y2.Caption = .Y2
end if
subReport2.visible = mBoo
me.Label_Y2.visible = mBoo

'etc


end with
end if
'~~~~~~~~~~~~~~~

Now we have set the LinkMasterfields for each subreport so the ones that
the user picked will show

For columns that have not been picked, their Visible property will be False

the caption has been changed

Pay attention to the controlnames in the code that are specified and
check (or change) your respective names (ie: Y1, Label_Y1). If you
stick to a sequential way of naming, you can actually cut out quite a
bit of code by looping, but I thought this might be easier to initially
understand...



General module:
'~~~~~~~~~~~
Function IsLoaded(ByVal pFormName As String) As Boolean

IsLoaded = False


If CurrentProject.AllForms(pFormName ).IsLoaded Then
If Forms(pFormName).CurrentView <> 0 Then
'True if form is open not in Design View
IsLoaded = True
End If
End If

End Function
'~~~~~~~~~~~

you might have to put some of the logic in other events, I did not test
this, just trying to help you think...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Mike wrote:
I'm lost

I'm a bit of anewbie to access. If you can break it down for me i'd really
appreciate it. I've include as much as I can below. If u need anything else
let me know.
------------------------------
Here is my crosstab query SQL

TRANSFORM Val(nz(Sum([Capital Job Database].[Estimated Cost]),0)) AS
[SumOfEstimated Cost]
SELECT [Budget Line].[Budget Line]
FROM [Budget Line] LEFT JOIN [Capital Job Database] ON [Budget Line].[Budget
Line] = [Capital Job Database].[Budget Line]
WHERE ((([Budget Line].[Budget Line]) Like "WP-112*") AND (([Capital Job
Database].[Contract Status])<>"(6) Cancelled" Or ([Capital Job
Database].[Contract Status]) Is Null))
GROUP BY [Budget Line].[Budget Line]
PIVOT [Capital Job Database].[Scheduled Fiscal Year] In
(1900,2006,2007,2008,2009,2010); <<<---I don't always want these as the
column headers. Again I want to be able to choose from a form.
------------------------------------------

This is my open event for the report which opens the form


Private Sub Report_Open(Cancel As Integer)

' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open BEPA Reports Form
DoCmd.OpenForm "WP-112 Summary Reports", , , , , acDialog

' Cancel Report if User Clicked the Cancel Button
If IsLoaded("WP-112 Summary Reports") = False Then Cancel = True

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False
End Sub
-----------------------------------
The name of the form i want to use is "WP-112 Summary Reports" and the field
captions I have set up to select the fiscal years are "Year 1", "Year 2",
"Year 3", "Year 4" and "Year 5". All five of these are unbound fields

Currently in the report the five fileds that are to take the 5 years are as
follows:

Caption: 2006 2007 2008 2009 2010
Control Source: 2006 2007 2008 2009 2010

PS when i make the module is there a specific name i should give it


:

Base your report on a query

Before you render your report, replace the sql in your query and
redefine the fields -- be sure to use field aliases so you can refer to

 

[strive4peace]

you're welcome, Mike

"i got it to work"

I'll bet you are proud of yourself, great!

"(on the form i have an exit button to cancel the operation)"

what do you mean? you click a Cancel button (to close the menu form?)
after you open the report?

If you do not want to leave the report menu form open, then change:

Me.Year_1.ControlSource = [Forms]![WP-112 Summary Reports]![Year 1]
Me.Year_2.ControlSource = [Forms]![WP-112 Summary Reports]![Year 2]
Me.Year_3.ControlSource = [Forms]![WP-112 Summary Reports]![Year 3]
Me.Year_4.ControlSource = [Forms]![WP-112 Summary Reports]![Year 4]
Me.Year_5.ControlSource = [Forms]![WP-112 Summary Reports]![Year 5]

to:

Me.Year_1 = [Forms]![WP-112 Summary Reports]![Year 1]
Me.Year_2 = [Forms]![WP-112 Summary Reports]![Year 2]
Me.Year_3 = [Forms]![WP-112 Summary Reports]![Year 3]
Me.Year_4 = [Forms]![WP-112 Summary Reports]![Year 4]
Me.Year_5 = [Forms]![WP-112 Summary Reports]![Year 5]

so, instead of setting the ControlSource to a form you intend to close,
just set the initial value... then you can close your menu form while
the report is open.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

First of I would like to say thank you for all ur help so far.

I really didn't understand what youve said (im kind of a newbie to access)
but by playing around with the codes i got it to work. I have my crosstab
query that i entered column headers from 1900 to 2100. I made 5 unbound
fields in my report named Year 1, Year 2 etc

in the open event I used the following:
----
Private Sub Report_Open(Cancel As Integer)

' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open BEPA Reports Form
DoCmd.OpenForm "WP-112 Summary Reports", , , , , acDialog

' Cancel Report if User Clicked the Cancel Button
If IsLoaded("WP-112 Summary Reports") = False Then Cancel = True

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False

Me.Year_1.ControlSource = [Forms]![WP-112 Summary Reports]![Year 1]
Me.Year_2.ControlSource = [Forms]![WP-112 Summary Reports]![Year 2]
Me.Year_3.ControlSource = [Forms]![WP-112 Summary Reports]![Year 3]
Me.Year_4.ControlSource = [Forms]![WP-112 Summary Reports]![Year 4]
Me.Year_5.ControlSource = [Forms]![WP-112 Summary Reports]![Year 5]


End Sub
----

This allowed my report, whos record source is the crosstab query, to use the
5 years that i selected on my form and use them as column headers in the
report (it will only work for years 1900 to 2100. but thats ok)

The only problem i still have is as follows: say i open the report. on open
the form pops up. (on the form i have an exit button to cancel the
operation). if i hit the exit button i get the following error: Microsoft
Office Access can't find the form "WP-112 Summary Reports" reffered to in a
marco or Visual Basic code.

It seems that its still running the code even though i exitted out of the
form how can i have the code stop if the exit button is pressed.

Hi Mike,

Good! You have a form (called ReportMenu?) with 5 comboboxes... we will
put this on hold now and use this later. Perhaps it would be best to
focus on the reports first -- make a subreport in one column and
hardcode in the value of the link until that works -- then we will add
columns and then add code...

correction: subReport_formname should have been subReportName

'~~~~~~~~~~~`

The next step is to make ONE report that will be used as a subreport in
one of the "columns" of your main report

in this report, use the report header but make VISIBLE=No (after you
test it). In this section, be sure to put the fiscal year field as well
as any other fields you want to use to link to the main report. To ease
confusion, open the property sheet and make the NAME property the same
as the ControlSource property for the linking control(s).

For reference, I will call the linking control for fiscal year:
FY_controlname

Make the width of the report the same as the width of a "column" on your
main report.

save this report (which I am referring to as subReportName -- so you
will have to substitute the name you actually give it (don't use spaces
or special characters except the underscore when you name it)

'~~~~~~~~~~~
Now, go to design view of the Main Report

make an unbound textbox control to hold the fiscal year for the
subreport in the first column

Name --> Y1
Visible --> No (change after testing)
ControlSource --> = 2006

(or use another value for the control source, I don't know what you data
is -- once you get the hardcoded values to work, we will work with you
on the code to make them variable)

make a subreport control for the first column

ControlSource --> subReportName
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FY_controlname

'~~~~~~~~~~~`

try this and let us know if it is successful!


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Im a little lost still

I made the form as you said with 5 unbound columns named Y1 to Y5

I don't get the next part

ReportMenu subreports:
ControlSource --> subReport_formname
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FiscalYear_controlname

etc

Thanks for all the help.

:

Hi Mike,

If you were just reporting from the crosstab, it would be easy...

your column headers come from this line:

PIVOT [Capital Job Database].[Scheduled Fiscal Year] In
(1900,2006,2007,2008,2009,2010)

and they would change as your query did... but translating those changes
to a report requires a few more steps...

I am assuming, in your sample data, that the first column is to be
ignored? Seems you need a way to choose years for 5 columns... and
ignore years (make Visible=No) when they are not specified...

there are much more elegant ways, but this is easy to understand...<g>

on the form where you make your choices for the report (I will refer to
this form as ReportMenu)

make 5 comboboxes to get their values from the available fiscal years

If the same year is entered in 2 comboboxes, the user will see that on
the report and they, presumeably meant that or they will change the
criteria and generate the report again...

these comboboxes on the ReportMenu form, for purposes of this example,
will be named Y1, Y2, Y3, Y4, and Y5.

The 5 subreport controls comprising the columns of the main report will
be set up such that each is linked to one of 5 calculated controls
corresponding to the ReportMenu form choices

ie:

'~~~~~~~~~~~~~~~

ReportMenu unbound combobox control names:
Y1
Y2
Y3
Y4
Y5

'~~~~~~~~~~~~~~~

ReportMenu subreports:
ControlSource --> subReport_formname
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FiscalYear_controlname

ControlSource --> subReport_formname
Name --> subReport2
LinkMasterfields --> Y2
LinkChildFields --> FiscalYear_controlname

etc

'~~~~~~~~~~~~~~~

by doing things this way, you can use the same subreport for each case
-- just change the LinkMasterField and the caption for the column label


in the Open event for the report

'~~~~~~~~~~~~~~~
dim mBoo as Boolean

if Not IsLoaded("ReportMenu") then
msgbox "Aborting report...",, _
"ReportMenu is not open"
Cancel = true
end if

with forms!ReportMenu

mboo = IIF(IsNull(.Y1), False, True)
if mBoo then
me.Y1 = .Y1
me.Label_Y1.Caption = .Y1
end if
subReport1.visible = mBoo
me.Label_Y1.visible = mBoo

mboo = IIF(IsNull(.Y2), False, True)
if mBoo then
me.Y2 = .Y
me.Label_Y2.Caption = .Y2
end if
subReport2.visible = mBoo
me.Label_Y2.visible = mBoo

'etc


end with
end if
'~~~~~~~~~~~~~~~

Now we have set the LinkMasterfields for each subreport so the ones that
the user picked will show

For columns that have not been picked, their Visible property will be False

the caption has been changed

Pay attention to the controlnames in the code that are specified and
check (or change) your respective names (ie: Y1, Label_Y1). If you
stick to a sequential way of naming, you can actually cut out quite a
bit of code by looping, but I thought this might be easier to initially
understand...



General module:
'~~~~~~~~~~~
Function IsLoaded(ByVal pFormName As String) As Boolean

IsLoaded = False


If CurrentProject.AllForms(pFormName ).IsLoaded Then
If Forms(pFormName).CurrentView <> 0 Then
'True if form is open not in Design View
IsLoaded = True
End If
End If

End Function
'~~~~~~~~~~~

you might have to put some of the logic in other events, I did not test
this, just trying to help you think...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Mike wrote:
I'm lost

I'm a bit of anewbie to access. If you can break it down for me i'd really
appreciate it. I've include as much as I can below. If u need anything else
let me know.
------------------------------
Here is my crosstab query SQL

TRANSFORM Val(nz(Sum([Capital Job Database].[Estimated Cost]),0)) AS
[SumOfEstimated Cost]
SELECT [Budget Line].[Budget Line]
FROM [Budget Line] LEFT JOIN [Capital Job Database] ON [Budget Line].[Budget
Line] = [Capital Job Database].[Budget Line]
WHERE ((([Budget Line].[Budget Line]) Like "WP-112*") AND (([Capital Job
Database].[Contract Status])<>"(6) Cancelled" Or ([Capital Job
Database].[Contract Status]) Is Null))
GROUP BY [Budget Line].[Budget Line]
PIVOT [Capital Job Database].[Scheduled Fiscal Year] In
(1900,2006,2007,2008,2009,2010); <<<---I don't always want these as the
column headers. Again I want to be able to choose from a form.
------------------------------------------

This is my open event for the report which opens the form


Private Sub Report_Open(Cancel As Integer)

' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open BEPA Reports Form
DoCmd.OpenForm "WP-112 Summary Reports", , , , , acDialog

' Cancel Report if User Clicked the Cancel Button
If IsLoaded("WP-112 Summary Reports") = False Then Cancel = True

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False
End Sub
-----------------------------------
The name of the form i want to use is "WP-112 Summary Reports" and the field
captions I have set up to select the fiscal years are "Year 1", "Year 2",
"Year 3", "Year 4" and "Year 5". All five of these are unbound fields

Currently in the report the five fileds that are to take the 5 years are as
follows:

Caption: 2006 2007 2008 2009 2010
Control Source: 2006 2007 2008 2009 2010

PS when i make the module is there a specific name i should give it


:

Base your report on a query

Before you render your report, replace the sql in your query and
redefine the fields -- be sure to use field aliases so you can refer to

 

[Mike]

Not quite

When I open the report a form automatically pops up asking for the
parameters. I must enter them and hit ok prior to the form opening. I have
put a cancel button on this form so that if the report is mistakenly openned
the user does not have to go through the parameter entry and then have the
form load up. however when the button is clicked that is when i get the
message. How can i stop the open event of the report by clicking the cancel
button on my form?

you're welcome, Mike

"i got it to work"

I'll bet you are proud of yourself, great!

"(on the form i have an exit button to cancel the operation)"

what do you mean? you click a Cancel button (to close the menu form?)
after you open the report?

If you do not want to leave the report menu form open, then change:

Me.Year_1.ControlSource = [Forms]![WP-112 Summary Reports]![Year 1]
Me.Year_2.ControlSource = [Forms]![WP-112 Summary Reports]![Year 2]
Me.Year_3.ControlSource = [Forms]![WP-112 Summary Reports]![Year 3]
Me.Year_4.ControlSource = [Forms]![WP-112 Summary Reports]![Year 4]
Me.Year_5.ControlSource = [Forms]![WP-112 Summary Reports]![Year 5]

to:

Me.Year_1 = [Forms]![WP-112 Summary Reports]![Year 1]
Me.Year_2 = [Forms]![WP-112 Summary Reports]![Year 2]
Me.Year_3 = [Forms]![WP-112 Summary Reports]![Year 3]
Me.Year_4 = [Forms]![WP-112 Summary Reports]![Year 4]
Me.Year_5 = [Forms]![WP-112 Summary Reports]![Year 5]

so, instead of setting the ControlSource to a form you intend to close,
just set the initial value... then you can close your menu form while
the report is open.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

First of I would like to say thank you for all ur help so far.

I really didn't understand what youve said (im kind of a newbie to access)
but by playing around with the codes i got it to work. I have my crosstab
query that i entered column headers from 1900 to 2100. I made 5 unbound
fields in my report named Year 1, Year 2 etc

in the open event I used the following:
----
Private Sub Report_Open(Cancel As Integer)

' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open BEPA Reports Form
DoCmd.OpenForm "WP-112 Summary Reports", , , , , acDialog

' Cancel Report if User Clicked the Cancel Button
If IsLoaded("WP-112 Summary Reports") = False Then Cancel = True

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False

Me.Year_1.ControlSource = [Forms]![WP-112 Summary Reports]![Year 1]
Me.Year_2.ControlSource = [Forms]![WP-112 Summary Reports]![Year 2]
Me.Year_3.ControlSource = [Forms]![WP-112 Summary Reports]![Year 3]
Me.Year_4.ControlSource = [Forms]![WP-112 Summary Reports]![Year 4]
Me.Year_5.ControlSource = [Forms]![WP-112 Summary Reports]![Year 5]


End Sub
----

This allowed my report, whos record source is the crosstab query, to use the
5 years that i selected on my form and use them as column headers in the
report (it will only work for years 1900 to 2100. but thats ok)

The only problem i still have is as follows: say i open the report. on open
the form pops up. (on the form i have an exit button to cancel the
operation). if i hit the exit button i get the following error: Microsoft
Office Access can't find the form "WP-112 Summary Reports" reffered to in a
marco or Visual Basic code.

It seems that its still running the code even though i exitted out of the
form how can i have the code stop if the exit button is pressed.

Hi Mike,

Good! You have a form (called ReportMenu?) with 5 comboboxes... we will
put this on hold now and use this later. Perhaps it would be best to
focus on the reports first -- make a subreport in one column and
hardcode in the value of the link until that works -- then we will add
columns and then add code...

correction: subReport_formname should have been subReportName

'~~~~~~~~~~~`

The next step is to make ONE report that will be used as a subreport in
one of the "columns" of your main report

in this report, use the report header but make VISIBLE=No (after you
test it). In this section, be sure to put the fiscal year field as well
as any other fields you want to use to link to the main report. To ease
confusion, open the property sheet and make the NAME property the same
as the ControlSource property for the linking control(s).

For reference, I will call the linking control for fiscal year:
FY_controlname

Make the width of the report the same as the width of a "column" on your
main report.

save this report (which I am referring to as subReportName -- so you
will have to substitute the name you actually give it (don't use spaces
or special characters except the underscore when you name it)

'~~~~~~~~~~~
Now, go to design view of the Main Report

make an unbound textbox control to hold the fiscal year for the
subreport in the first column

Name --> Y1
Visible --> No (change after testing)
ControlSource --> = 2006

(or use another value for the control source, I don't know what you data
is -- once you get the hardcoded values to work, we will work with you
on the code to make them variable)

make a subreport control for the first column

ControlSource --> subReportName
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FY_controlname

'~~~~~~~~~~~`

try this and let us know if it is successful!


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Mike wrote:
Im a little lost still

I made the form as you said with 5 unbound columns named Y1 to Y5

I don't get the next part

ReportMenu subreports:
ControlSource --> subReport_formname
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FiscalYear_controlname

etc

Thanks for all the help.

:

Hi Mike,

If you were just reporting from the crosstab, it would be easy...

your column headers come from this line:

PIVOT [Capital Job Database].[Scheduled Fiscal Year] In
(1900,2006,2007,2008,2009,2010)

and they would change as your query did... but translating those changes
to a report requires a few more steps...

I am assuming, in your sample data, that the first column is to be
ignored? Seems you need a way to choose years for 5 columns... and
ignore years (make Visible=No) when they are not specified...

there are much more elegant ways, but this is easy to understand...<g>

on the form where you make your choices for the report (I will refer to
this form as ReportMenu)

make 5 comboboxes to get their values from the available fiscal years

If the same year is entered in 2 comboboxes, the user will see that on
the report and they, presumeably meant that or they will change the
criteria and generate the report again...

these comboboxes on the ReportMenu form, for purposes of this example,
will be named Y1, Y2, Y3, Y4, and Y5.

The 5 subreport controls comprising the columns of the main report will
be set up such that each is linked to one of 5 calculated controls
corresponding to the ReportMenu form choices

ie:

'~~~~~~~~~~~~~~~

ReportMenu unbound combobox control names:
Y1
Y2
Y3
Y4
Y5

'~~~~~~~~~~~~~~~

ReportMenu subreports:
ControlSource --> subReport_formname
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FiscalYear_controlname

ControlSource --> subReport_formname
Name --> subReport2
LinkMasterfields --> Y2
LinkChildFields --> FiscalYear_controlname

etc

'~~~~~~~~~~~~~~~

by doing things this way, you can use the same subreport for each case
-- just change the LinkMasterField and the caption for the column label


in the Open event for the report

'~~~~~~~~~~~~~~~
dim mBoo as Boolean

if Not IsLoaded("ReportMenu") then
msgbox "Aborting report...",, _
"ReportMenu is not open"
Cancel = true
end if

with forms!ReportMenu

mboo = IIF(IsNull(.Y1), False, True)
if mBoo then
me.Y1 = .Y1
me.Label_Y1.Caption = .Y1
end if
subReport1.visible = mBoo
me.Label_Y1.visible = mBoo

mboo = IIF(IsNull(.Y2), False, True)
if mBoo then
me.Y2 = .Y
me.Label_Y2.Caption = .Y2
end if
subReport2.visible = mBoo
me.Label_Y2.visible = mBoo

'etc


end with
end if
'~~~~~~~~~~~~~~~

Now we have set the LinkMasterfields for each subreport so the ones that
the user picked will show

For columns that have not been picked, their Visible property will be False

the caption has been changed

Pay attention to the controlnames in the code that are specified and
check (or change) your respective names (ie: Y1, Label_Y1). If you
stick to a sequential way of naming, you can actually cut out quite a
bit of code by looping, but I thought this might be easier to initially
understand...



General module:

 

[strive4peace]

Hi Mike,

you could do this on the Report Open event

'~~~~~~~~~~~~~~~~~~~~~~~~~~
if not CurrentProject.AllForms("[WP-112 Summary Reports]").IsLoaded then
cancel = true
exit sub
end if
'~~~~~~~~~~~~~~~~~~~~~~~~~~

optionally...

'~~~~~~~~~~~~~~~~~~~~~~~~~~
if len(trim(nz(me.OpenArgs,""))) = 0 then
cancel = true
exit sub
end if
'~~~~~~~~~~~~~~~~~~~~~~~~~~

then, when you call it...

DoCmd.OpenReport "ReportName", acPreview, , "criteria", , "anything"

where

"anything" is, literally... any character(s) in quotes. If you do not
pass any open arguments, the form will think it is being opened
independently of the menus and cancel itself...

"criteria" is optional --- any filtering criteria you wish to impose...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Not quite

When I open the report a form automatically pops up asking for the
parameters. I must enter them and hit ok prior to the form opening. I have
put a cancel button on this form so that if the report is mistakenly openned
the user does not have to go through the parameter entry and then have the
form load up. however when the button is clicked that is when i get the
message. How can i stop the open event of the report by clicking the cancel
button on my form?

you're welcome, Mike

"i got it to work"

I'll bet you are proud of yourself, great!

"(on the form i have an exit button to cancel the operation)"

what do you mean? you click a Cancel button (to close the menu form?)
after you open the report?

If you do not want to leave the report menu form open, then change:

Me.Year_1.ControlSource = [Forms]![WP-112 Summary Reports]![Year 1]
Me.Year_2.ControlSource = [Forms]![WP-112 Summary Reports]![Year 2]
Me.Year_3.ControlSource = [Forms]![WP-112 Summary Reports]![Year 3]
Me.Year_4.ControlSource = [Forms]![WP-112 Summary Reports]![Year 4]
Me.Year_5.ControlSource = [Forms]![WP-112 Summary Reports]![Year 5]

to:

Me.Year_1 = [Forms]![WP-112 Summary Reports]![Year 1]
Me.Year_2 = [Forms]![WP-112 Summary Reports]![Year 2]
Me.Year_3 = [Forms]![WP-112 Summary Reports]![Year 3]
Me.Year_4 = [Forms]![WP-112 Summary Reports]![Year 4]
Me.Year_5 = [Forms]![WP-112 Summary Reports]![Year 5]

so, instead of setting the ControlSource to a form you intend to close,
just set the initial value... then you can close your menu form while
the report is open.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

First of I would like to say thank you for all ur help so far.

I really didn't understand what youve said (im kind of a newbie to access)
but by playing around with the codes i got it to work. I have my crosstab
query that i entered column headers from 1900 to 2100. I made 5 unbound
fields in my report named Year 1, Year 2 etc

in the open event I used the following:
----
Private Sub Report_Open(Cancel As Integer)

' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open BEPA Reports Form
DoCmd.OpenForm "WP-112 Summary Reports", , , , , acDialog

' Cancel Report if User Clicked the Cancel Button
If IsLoaded("WP-112 Summary Reports") = False Then Cancel = True

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False

Me.Year_1.ControlSource = [Forms]![WP-112 Summary Reports]![Year 1]
Me.Year_2.ControlSource = [Forms]![WP-112 Summary Reports]![Year 2]
Me.Year_3.ControlSource = [Forms]![WP-112 Summary Reports]![Year 3]
Me.Year_4.ControlSource = [Forms]![WP-112 Summary Reports]![Year 4]
Me.Year_5.ControlSource = [Forms]![WP-112 Summary Reports]![Year 5]


End Sub
----

This allowed my report, whos record source is the crosstab query, to use the
5 years that i selected on my form and use them as column headers in the
report (it will only work for years 1900 to 2100. but thats ok)

The only problem i still have is as follows: say i open the report. on open
the form pops up. (on the form i have an exit button to cancel the
operation). if i hit the exit button i get the following error: Microsoft
Office Access can't find the form "WP-112 Summary Reports" reffered to in a
marco or Visual Basic code.

It seems that its still running the code even though i exitted out of the
form how can i have the code stop if the exit button is pressed.
:

Hi Mike,

Good! You have a form (called ReportMenu?) with 5 comboboxes... we will
put this on hold now and use this later. Perhaps it would be best to
focus on the reports first -- make a subreport in one column and
hardcode in the value of the link until that works -- then we will add
columns and then add code...

correction: subReport_formname should have been subReportName

'~~~~~~~~~~~`

The next step is to make ONE report that will be used as a subreport in
one of the "columns" of your main report

in this report, use the report header but make VISIBLE=No (after you
test it). In this section, be sure to put the fiscal year field as well
as any other fields you want to use to link to the main report. To ease
confusion, open the property sheet and make the NAME property the same
as the ControlSource property for the linking control(s).

For reference, I will call the linking control for fiscal year:
FY_controlname

Make the width of the report the same as the width of a "column" on your
main report.

save this report (which I am referring to as subReportName -- so you
will have to substitute the name you actually give it (don't use spaces
or special characters except the underscore when you name it)

'~~~~~~~~~~~
Now, go to design view of the Main Report

make an unbound textbox control to hold the fiscal year for the
subreport in the first column

Name --> Y1
Visible --> No (change after testing)
ControlSource --> = 2006

(or use another value for the control source, I don't know what you data
is -- once you get the hardcoded values to work, we will work with you
on the code to make them variable)

make a subreport control for the first column

ControlSource --> subReportName
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FY_controlname

'~~~~~~~~~~~`

try this and let us know if it is successful!


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Mike wrote:
Im a little lost still

I made the form as you said with 5 unbound columns named Y1 to Y5

I don't get the next part

ReportMenu subreports:
ControlSource --> subReport_formname
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FiscalYear_controlname

etc

Thanks for all the help.

:

Hi Mike,

If you were just reporting from the crosstab, it would be easy...

your column headers come from this line:

PIVOT [Capital Job Database].[Scheduled Fiscal Year] In
(1900,2006,2007,2008,2009,2010)

and they would change as your query did... but translating those changes
to a report requires a few more steps...

I am assuming, in your sample data, that the first column is to be
ignored? Seems you need a way to choose years for 5 columns... and
ignore years (make Visible=No) when they are not specified...

there are much more elegant ways, but this is easy to understand...<g>

on the form where you make your choices for the report (I will refer to
this form as ReportMenu)

make 5 comboboxes to get their values from the available fiscal years

If the same year is entered in 2 comboboxes, the user will see that on
the report and they, presumeably meant that or they will change the
criteria and generate the report again...

these comboboxes on the ReportMenu form, for purposes of this example,
will be named Y1, Y2, Y3, Y4, and Y5.

The 5 subreport controls comprising the columns of the main report will
be set up such that each is linked to one of 5 calculated controls
corresponding to the ReportMenu form choices

ie:

'~~~~~~~~~~~~~~~

ReportMenu unbound combobox control names:
Y1
Y2
Y3
Y4
Y5

'~~~~~~~~~~~~~~~

ReportMenu subreports:
ControlSource --> subReport_formname
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FiscalYear_controlname

ControlSource --> subReport_formname
Name --> subReport2
LinkMasterfields --> Y2
LinkChildFields --> FiscalYear_controlname

etc

'~~~~~~~~~~~~~~~

by doing things this way, you can use the same subreport for each case
-- just change the LinkMasterField and the caption for the column label


in the Open event for the report

'~~~~~~~~~~~~~~~
dim mBoo as Boolean

if Not IsLoaded("ReportMenu") then
msgbox "Aborting report...",, _
"ReportMenu is not open"
Cancel = true
end if

with forms!ReportMenu

mboo = IIF(IsNull(.Y1), False, True)
if mBoo then
me.Y1 = .Y1
me.Label_Y1.Caption = .Y1
end if
subReport1.visible = mBoo
me.Label_Y1.visible = mBoo

mboo = IIF(IsNull(.Y2), False, True)
if mBoo then
me.Y2 = .Y
me.Label_Y2.Caption = .Y2
end if
subReport2.visible = mBoo
me.Label_Y2.visible = mBoo

'etc


end with
end if
'~~~~~~~~~~~~~~~

Now we have set the LinkMasterfields for each subreport so the ones that
the user picked will show

For columns that have not been picked, their Visible property will be False

the caption has been changed

Pay attention to the controlnames in the code that are specified and
check (or change) your respective names (ie: Y1, Label_Y1). If you
stick to a sequential way of naming, you can actually cut out quite a
bit of code by looping, but I thought this might be easier to initially
understand...



General module:

 

[Mike]

Im lost. this is my current open event.

can u show me what it should look like
----------------
Private Sub Report_Open(Cancel As Integer)

' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open BEPA Reports Form
DoCmd.OpenForm "Summary Reports", , , , , acDialog

' Cancel Report if User Clicked the Cancel Button
If IsLoaded("Summary Reports") = False Then Cancel = True


' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False


Me.Year_1.ControlSource = [Forms]![Summary Reports]![Year 1]
Me.Year_2.ControlSource = [Forms]![Summary Reports]![Year 2]
Me.Year_3.ControlSource = [Forms]![Summary Reports]![Year 3]
Me.Year_4.ControlSource = [Forms]![Summary Reports]![Year 4]
Me.Year_5.ControlSource = [Forms]![Summary Reports]![Year 5]

End Sub
---------------------

Hi Mike,

you could do this on the Report Open event

'~~~~~~~~~~~~~~~~~~~~~~~~~~
if not CurrentProject.AllForms("[WP-112 Summary Reports]").IsLoaded then
cancel = true
exit sub
end if
'~~~~~~~~~~~~~~~~~~~~~~~~~~

optionally...

'~~~~~~~~~~~~~~~~~~~~~~~~~~
if len(trim(nz(me.OpenArgs,""))) = 0 then
cancel = true
exit sub
end if
'~~~~~~~~~~~~~~~~~~~~~~~~~~

then, when you call it...

DoCmd.OpenReport "ReportName", acPreview, , "criteria", , "anything"

where

"anything" is, literally... any character(s) in quotes. If you do not
pass any open arguments, the form will think it is being opened
independently of the menus and cancel itself...

"criteria" is optional --- any filtering criteria you wish to impose...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Not quite

When I open the report a form automatically pops up asking for the
parameters. I must enter them and hit ok prior to the form opening. I have
put a cancel button on this form so that if the report is mistakenly openned
the user does not have to go through the parameter entry and then have the
form load up. however when the button is clicked that is when i get the
message. How can i stop the open event of the report by clicking the cancel
button on my form?

you're welcome, Mike

"i got it to work"

I'll bet you are proud of yourself, great!

"(on the form i have an exit button to cancel the operation)"

what do you mean? you click a Cancel button (to close the menu form?)
after you open the report?

If you do not want to leave the report menu form open, then change:

Me.Year_1.ControlSource = [Forms]![WP-112 Summary Reports]![Year 1]
Me.Year_2.ControlSource = [Forms]![WP-112 Summary Reports]![Year 2]
Me.Year_3.ControlSource = [Forms]![WP-112 Summary Reports]![Year 3]
Me.Year_4.ControlSource = [Forms]![WP-112 Summary Reports]![Year 4]
Me.Year_5.ControlSource = [Forms]![WP-112 Summary Reports]![Year 5]

to:

Me.Year_1 = [Forms]![WP-112 Summary Reports]![Year 1]
Me.Year_2 = [Forms]![WP-112 Summary Reports]![Year 2]
Me.Year_3 = [Forms]![WP-112 Summary Reports]![Year 3]
Me.Year_4 = [Forms]![WP-112 Summary Reports]![Year 4]
Me.Year_5 = [Forms]![WP-112 Summary Reports]![Year 5]

so, instead of setting the ControlSource to a form you intend to close,
just set the initial value... then you can close your menu form while
the report is open.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Mike wrote:
First of I would like to say thank you for all ur help so far.

I really didn't understand what youve said (im kind of a newbie to access)
but by playing around with the codes i got it to work. I have my crosstab
query that i entered column headers from 1900 to 2100. I made 5 unbound
fields in my report named Year 1, Year 2 etc

in the open event I used the following:
----
Private Sub Report_Open(Cancel As Integer)

' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open BEPA Reports Form
DoCmd.OpenForm "WP-112 Summary Reports", , , , , acDialog

' Cancel Report if User Clicked the Cancel Button
If IsLoaded("WP-112 Summary Reports") = False Then Cancel = True

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False

Me.Year_1.ControlSource = [Forms]![WP-112 Summary Reports]![Year 1]
Me.Year_2.ControlSource = [Forms]![WP-112 Summary Reports]![Year 2]
Me.Year_3.ControlSource = [Forms]![WP-112 Summary Reports]![Year 3]
Me.Year_4.ControlSource = [Forms]![WP-112 Summary Reports]![Year 4]
Me.Year_5.ControlSource = [Forms]![WP-112 Summary Reports]![Year 5]


End Sub
----

This allowed my report, whos record source is the crosstab query, to use the
5 years that i selected on my form and use them as column headers in the
report (it will only work for years 1900 to 2100. but thats ok)

The only problem i still have is as follows: say i open the report. on open
the form pops up. (on the form i have an exit button to cancel the
operation). if i hit the exit button i get the following error: Microsoft
Office Access can't find the form "WP-112 Summary Reports" reffered to in a
marco or Visual Basic code.

It seems that its still running the code even though i exitted out of the
form how can i have the code stop if the exit button is pressed.
:

Hi Mike,

Good! You have a form (called ReportMenu?) with 5 comboboxes... we will
put this on hold now and use this later. Perhaps it would be best to
focus on the reports first -- make a subreport in one column and
hardcode in the value of the link until that works -- then we will add
columns and then add code...

correction: subReport_formname should have been subReportName

'~~~~~~~~~~~`

The next step is to make ONE report that will be used as a subreport in
one of the "columns" of your main report

in this report, use the report header but make VISIBLE=No (after you
test it). In this section, be sure to put the fiscal year field as well
as any other fields you want to use to link to the main report. To ease
confusion, open the property sheet and make the NAME property the same
as the ControlSource property for the linking control(s).

For reference, I will call the linking control for fiscal year:
FY_controlname

Make the width of the report the same as the width of a "column" on your
main report.

save this report (which I am referring to as subReportName -- so you
will have to substitute the name you actually give it (don't use spaces
or special characters except the underscore when you name it)

'~~~~~~~~~~~
Now, go to design view of the Main Report

make an unbound textbox control to hold the fiscal year for the
subreport in the first column

Name --> Y1
Visible --> No (change after testing)
ControlSource --> = 2006

(or use another value for the control source, I don't know what you data
is -- once you get the hardcoded values to work, we will work with you
on the code to make them variable)

make a subreport control for the first column

ControlSource --> subReportName
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FY_controlname

'~~~~~~~~~~~`

try this and let us know if it is successful!


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Mike wrote:
Im a little lost still

I made the form as you said with 5 unbound columns named Y1 to Y5

I don't get the next part

ReportMenu subreports:
ControlSource --> subReport_formname
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FiscalYear_controlname

etc

Thanks for all the help.

:

Hi Mike,

If you were just reporting from the crosstab, it would be easy...

your column headers come from this line:

PIVOT [Capital Job Database].[Scheduled Fiscal Year] In
(1900,2006,2007,2008,2009,2010)

and they would change as your query did... but translating those changes
to a report requires a few more steps...

I am assuming, in your sample data, that the first column is to be
ignored? Seems you need a way to choose years for 5 columns... and
ignore years (make Visible=No) when they are not specified...

there are much more elegant ways, but this is easy to understand...<g>

on the form where you make your choices for the report (I will refer to
this form as ReportMenu)

make 5 comboboxes to get their values from the available fiscal years

If the same year is entered in 2 comboboxes, the user will see that on
the report and they, presumeably meant that or they will change the
criteria and generate the report again...

these comboboxes on the ReportMenu form, for purposes of this example,
will be named Y1, Y2, Y3, Y4, and Y5.

The 5 subreport controls comprising the columns of the main report will
be set up such that each is linked to one of 5 calculated controls
corresponding to the ReportMenu form choices

ie:

'~~~~~~~~~~~~~~~

ReportMenu unbound combobox control names:
Y1
Y2
Y3
Y4
Y5

'~~~~~~~~~~~~~~~

ReportMenu subreports:
ControlSource --> subReport_formname
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FiscalYear_controlname

ControlSource --> subReport_formname
Name --> subReport2
LinkMasterfields --> Y2
LinkChildFields --> FiscalYear_controlname

etc

'~~~~~~~~~~~~~~~

 

[Mike]

It's Ok I got it I just did this

If Cancel = False Then
Me.Year_1.ControlSource = [Forms]![Summary Reports]![Year 1]
Me.Year_2.ControlSource = [Forms]![Summary Reports]![Year 2]
Me.Year_3.ControlSource = [Forms]![Summary Reports]![Year 3]
Me.Year_4.ControlSource = [Forms]![Summary Reports]![Year 4]
Me.Year_5.ControlSource = [Forms]![Summary Reports]![Year 5]
End If

The last question i got is if i leave the field that determines the
parameters of my report blank and hit the OK button I get an error. How can
i have a pop up message that says that a value must be entered.

Hi Mike,

you could do this on the Report Open event

'~~~~~~~~~~~~~~~~~~~~~~~~~~
if not CurrentProject.AllForms("[WP-112 Summary Reports]").IsLoaded then
cancel = true
exit sub
end if
'~~~~~~~~~~~~~~~~~~~~~~~~~~

optionally...

'~~~~~~~~~~~~~~~~~~~~~~~~~~
if len(trim(nz(me.OpenArgs,""))) = 0 then
cancel = true
exit sub
end if
'~~~~~~~~~~~~~~~~~~~~~~~~~~

then, when you call it...

DoCmd.OpenReport "ReportName", acPreview, , "criteria", , "anything"

where

"anything" is, literally... any character(s) in quotes. If you do not
pass any open arguments, the form will think it is being opened
independently of the menus and cancel itself...

"criteria" is optional --- any filtering criteria you wish to impose...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Not quite

When I open the report a form automatically pops up asking for the
parameters. I must enter them and hit ok prior to the form opening. I have
put a cancel button on this form so that if the report is mistakenly openned
the user does not have to go through the parameter entry and then have the
form load up. however when the button is clicked that is when i get the
message. How can i stop the open event of the report by clicking the cancel
button on my form?

you're welcome, Mike

"i got it to work"

I'll bet you are proud of yourself, great!

"(on the form i have an exit button to cancel the operation)"

what do you mean? you click a Cancel button (to close the menu form?)
after you open the report?

If you do not want to leave the report menu form open, then change:

Me.Year_1.ControlSource = [Forms]![WP-112 Summary Reports]![Year 1]
Me.Year_2.ControlSource = [Forms]![WP-112 Summary Reports]![Year 2]
Me.Year_3.ControlSource = [Forms]![WP-112 Summary Reports]![Year 3]
Me.Year_4.ControlSource = [Forms]![WP-112 Summary Reports]![Year 4]
Me.Year_5.ControlSource = [Forms]![WP-112 Summary Reports]![Year 5]

to:

Me.Year_1 = [Forms]![WP-112 Summary Reports]![Year 1]
Me.Year_2 = [Forms]![WP-112 Summary Reports]![Year 2]
Me.Year_3 = [Forms]![WP-112 Summary Reports]![Year 3]
Me.Year_4 = [Forms]![WP-112 Summary Reports]![Year 4]
Me.Year_5 = [Forms]![WP-112 Summary Reports]![Year 5]

so, instead of setting the ControlSource to a form you intend to close,
just set the initial value... then you can close your menu form while
the report is open.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Mike wrote:
First of I would like to say thank you for all ur help so far.

I really didn't understand what youve said (im kind of a newbie to access)
but by playing around with the codes i got it to work. I have my crosstab
query that i entered column headers from 1900 to 2100. I made 5 unbound
fields in my report named Year 1, Year 2 etc

in the open event I used the following:
----
Private Sub Report_Open(Cancel As Integer)

' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open BEPA Reports Form
DoCmd.OpenForm "WP-112 Summary Reports", , , , , acDialog

' Cancel Report if User Clicked the Cancel Button
If IsLoaded("WP-112 Summary Reports") = False Then Cancel = True

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False

Me.Year_1.ControlSource = [Forms]![WP-112 Summary Reports]![Year 1]
Me.Year_2.ControlSource = [Forms]![WP-112 Summary Reports]![Year 2]
Me.Year_3.ControlSource = [Forms]![WP-112 Summary Reports]![Year 3]
Me.Year_4.ControlSource = [Forms]![WP-112 Summary Reports]![Year 4]
Me.Year_5.ControlSource = [Forms]![WP-112 Summary Reports]![Year 5]


End Sub
----

This allowed my report, whos record source is the crosstab query, to use the
5 years that i selected on my form and use them as column headers in the
report (it will only work for years 1900 to 2100. but thats ok)

The only problem i still have is as follows: say i open the report. on open
the form pops up. (on the form i have an exit button to cancel the
operation). if i hit the exit button i get the following error: Microsoft
Office Access can't find the form "WP-112 Summary Reports" reffered to in a
marco or Visual Basic code.

It seems that its still running the code even though i exitted out of the
form how can i have the code stop if the exit button is pressed.
:

Hi Mike,

Good! You have a form (called ReportMenu?) with 5 comboboxes... we will
put this on hold now and use this later. Perhaps it would be best to
focus on the reports first -- make a subreport in one column and
hardcode in the value of the link until that works -- then we will add
columns and then add code...

correction: subReport_formname should have been subReportName

'~~~~~~~~~~~`

The next step is to make ONE report that will be used as a subreport in
one of the "columns" of your main report

in this report, use the report header but make VISIBLE=No (after you
test it). In this section, be sure to put the fiscal year field as well
as any other fields you want to use to link to the main report. To ease
confusion, open the property sheet and make the NAME property the same
as the ControlSource property for the linking control(s).

For reference, I will call the linking control for fiscal year:
FY_controlname

Make the width of the report the same as the width of a "column" on your
main report.

save this report (which I am referring to as subReportName -- so you
will have to substitute the name you actually give it (don't use spaces
or special characters except the underscore when you name it)

'~~~~~~~~~~~
Now, go to design view of the Main Report

make an unbound textbox control to hold the fiscal year for the
subreport in the first column

Name --> Y1
Visible --> No (change after testing)
ControlSource --> = 2006

(or use another value for the control source, I don't know what you data
is -- once you get the hardcoded values to work, we will work with you
on the code to make them variable)

make a subreport control for the first column

ControlSource --> subReportName
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FY_controlname

'~~~~~~~~~~~`

try this and let us know if it is successful!


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Mike wrote:
Im a little lost still

I made the form as you said with 5 unbound columns named Y1 to Y5

I don't get the next part

ReportMenu subreports:
ControlSource --> subReport_formname
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FiscalYear_controlname

etc

Thanks for all the help.

:

Hi Mike,

If you were just reporting from the crosstab, it would be easy...

your column headers come from this line:

PIVOT [Capital Job Database].[Scheduled Fiscal Year] In
(1900,2006,2007,2008,2009,2010)

and they would change as your query did... but translating those changes
to a report requires a few more steps...

I am assuming, in your sample data, that the first column is to be
ignored? Seems you need a way to choose years for 5 columns... and
ignore years (make Visible=No) when they are not specified...

there are much more elegant ways, but this is easy to understand...<g>

on the form where you make your choices for the report (I will refer to
this form as ReportMenu)

make 5 comboboxes to get their values from the available fiscal years

If the same year is entered in 2 comboboxes, the user will see that on
the report and they, presumeably meant that or they will change the
criteria and generate the report again...

these comboboxes on the ReportMenu form, for purposes of this example,
will be named Y1, Y2, Y3, Y4, and Y5.

The 5 subreport controls comprising the columns of the main report will
be set up such that each is linked to one of 5 calculated controls
corresponding to the ReportMenu form choices

ie:

'~~~~~~~~~~~~~~~

ReportMenu unbound combobox control names:
Y1
Y2
Y3
Y4
Y5

'~~~~~~~~~~~~~~~

ReportMenu subreports:
ControlSource --> subReport_formname
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FiscalYear_controlname

ControlSource --> subReport_formname
Name --> subReport2
LinkMasterfields --> Y2
LinkChildFields --> FiscalYear_controlname

etc

'~~~~~~~~~~~~~~~

 

[strive4peace]

Hi Mike,

"The last question i got is if i leave the field that determines the
parameters of my report blank and hit the OK button I get an error. How
can i have a pop up message that says that a value must be entered."

good question... this is the last piece of the puzzle...

if isnull([Forms]![Summary Reports]![Year 1]) then
me.subReport1.visible = false
else
me.subReport1.visible = true
end if

where subReport1 is the Name of your subreport control

Now, because you have 5 of these to do, you can actually loop and set
your LinkMasterField source at the same time

'~~~~~~~~~~~~~~~~~~~~
dim i as integer
for i = 1 to 5
if isnull([Forms]![Summary Reports]("[Year " & i & "]) then
me("Year_" & i).ControlSource = 0
'or something that won't match anything
me("subReport" & i).visible = false
else
me("Year_" & i).ControlSource _
= [Forms]![Summary Reports]("[Year " & i & "]")
me("subReport" & i).visible = true
end if
next i
'~~~~~~~~~~~~~~~~~~~~

it would be much better not to use spaces in your names... [Year 1] -->
Year1

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

It's Ok I got it I just did this

If Cancel = False Then
Me.Year_1.ControlSource = [Forms]![Summary Reports]![Year 1]
Me.Year_2.ControlSource = [Forms]![Summary Reports]![Year 2]
Me.Year_3.ControlSource = [Forms]![Summary Reports]![Year 3]
Me.Year_4.ControlSource = [Forms]![Summary Reports]![Year 4]
Me.Year_5.ControlSource = [Forms]![Summary Reports]![Year 5]
End If

The last question i got is if i leave the field that determines the
parameters of my report blank and hit the OK button I get an error. How can
i have a pop up message that says that a value must be entered.

Hi Mike,

you could do this on the Report Open event

'~~~~~~~~~~~~~~~~~~~~~~~~~~
if not CurrentProject.AllForms("[WP-112 Summary Reports]").IsLoaded then
cancel = true
exit sub
end if
'~~~~~~~~~~~~~~~~~~~~~~~~~~

optionally...

'~~~~~~~~~~~~~~~~~~~~~~~~~~
if len(trim(nz(me.OpenArgs,""))) = 0 then
cancel = true
exit sub
end if
'~~~~~~~~~~~~~~~~~~~~~~~~~~

then, when you call it...

DoCmd.OpenReport "ReportName", acPreview, , "criteria", , "anything"

where

"anything" is, literally... any character(s) in quotes. If you do not
pass any open arguments, the form will think it is being opened
independently of the menus and cancel itself...

"criteria" is optional --- any filtering criteria you wish to impose...

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Not quite

When I open the report a form automatically pops up asking for the
parameters. I must enter them and hit ok prior to the form opening. I have
put a cancel button on this form so that if the report is mistakenly openned
the user does not have to go through the parameter entry and then have the
form load up. however when the button is clicked that is when i get the
message. How can i stop the open event of the report by clicking the cancel
button on my form?

:

you're welcome, Mike

"i got it to work"

I'll bet you are proud of yourself, great!

"(on the form i have an exit button to cancel the operation)"

what do you mean? you click a Cancel button (to close the menu form?)
after you open the report?

If you do not want to leave the report menu form open, then change:

Me.Year_1.ControlSource = [Forms]![WP-112 Summary Reports]![Year 1]
Me.Year_2.ControlSource = [Forms]![WP-112 Summary Reports]![Year 2]
Me.Year_3.ControlSource = [Forms]![WP-112 Summary Reports]![Year 3]
Me.Year_4.ControlSource = [Forms]![WP-112 Summary Reports]![Year 4]
Me.Year_5.ControlSource = [Forms]![WP-112 Summary Reports]![Year 5]

to:

Me.Year_1 = [Forms]![WP-112 Summary Reports]![Year 1]
Me.Year_2 = [Forms]![WP-112 Summary Reports]![Year 2]
Me.Year_3 = [Forms]![WP-112 Summary Reports]![Year 3]
Me.Year_4 = [Forms]![WP-112 Summary Reports]![Year 4]
Me.Year_5 = [Forms]![WP-112 Summary Reports]![Year 5]

so, instead of setting the ControlSource to a form you intend to close,
just set the initial value... then you can close your menu form while
the report is open.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Mike wrote:
First of I would like to say thank you for all ur help so far.

I really didn't understand what youve said (im kind of a newbie to access)
but by playing around with the codes i got it to work. I have my crosstab
query that i entered column headers from 1900 to 2100. I made 5 unbound
fields in my report named Year 1, Year 2 etc

in the open event I used the following:
----
Private Sub Report_Open(Cancel As Integer)

' Set public variable to true to indicate that the report
' is in the Open event
bInReportOpenEvent = True

' Open BEPA Reports Form
DoCmd.OpenForm "WP-112 Summary Reports", , , , , acDialog

' Cancel Report if User Clicked the Cancel Button
If IsLoaded("WP-112 Summary Reports") = False Then Cancel = True

' Set public variable to false to indicate that the
' Open event is completed
bInReportOpenEvent = False

Me.Year_1.ControlSource = [Forms]![WP-112 Summary Reports]![Year 1]
Me.Year_2.ControlSource = [Forms]![WP-112 Summary Reports]![Year 2]
Me.Year_3.ControlSource = [Forms]![WP-112 Summary Reports]![Year 3]
Me.Year_4.ControlSource = [Forms]![WP-112 Summary Reports]![Year 4]
Me.Year_5.ControlSource = [Forms]![WP-112 Summary Reports]![Year 5]


End Sub
----

This allowed my report, whos record source is the crosstab query, to use the
5 years that i selected on my form and use them as column headers in the
report (it will only work for years 1900 to 2100. but thats ok)

The only problem i still have is as follows: say i open the report. on open
the form pops up. (on the form i have an exit button to cancel the
operation). if i hit the exit button i get the following error: Microsoft
Office Access can't find the form "WP-112 Summary Reports" reffered to in a
marco or Visual Basic code.

It seems that its still running the code even though i exitted out of the
form how can i have the code stop if the exit button is pressed.
:

Hi Mike,

Good! You have a form (called ReportMenu?) with 5 comboboxes... we will
put this on hold now and use this later. Perhaps it would be best to
focus on the reports first -- make a subreport in one column and
hardcode in the value of the link until that works -- then we will add
columns and then add code...

correction: subReport_formname should have been subReportName

'~~~~~~~~~~~`

The next step is to make ONE report that will be used as a subreport in
one of the "columns" of your main report

in this report, use the report header but make VISIBLE=No (after you
test it). In this section, be sure to put the fiscal year field as well
as any other fields you want to use to link to the main report. To ease
confusion, open the property sheet and make the NAME property the same
as the ControlSource property for the linking control(s).

For reference, I will call the linking control for fiscal year:
FY_controlname

Make the width of the report the same as the width of a "column" on your
main report.

save this report (which I am referring to as subReportName -- so you
will have to substitute the name you actually give it (don't use spaces
or special characters except the underscore when you name it)

'~~~~~~~~~~~
Now, go to design view of the Main Report

make an unbound textbox control to hold the fiscal year for the
subreport in the first column

Name --> Y1
Visible --> No (change after testing)
ControlSource --> = 2006

(or use another value for the control source, I don't know what you data
is -- once you get the hardcoded values to work, we will work with you
on the code to make them variable)

make a subreport control for the first column

ControlSource --> subReportName
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FY_controlname

'~~~~~~~~~~~`

try this and let us know if it is successful!


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Mike wrote:
Im a little lost still

I made the form as you said with 5 unbound columns named Y1 to Y5

I don't get the next part

ReportMenu subreports:
ControlSource --> subReport_formname
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FiscalYear_controlname

etc

Thanks for all the help.

:

Hi Mike,

If you were just reporting from the crosstab, it would be easy...

your column headers come from this line:

PIVOT [Capital Job Database].[Scheduled Fiscal Year] In
(1900,2006,2007,2008,2009,2010)

and they would change as your query did... but translating those changes
to a report requires a few more steps...

I am assuming, in your sample data, that the first column is to be
ignored? Seems you need a way to choose years for 5 columns... and
ignore years (make Visible=No) when they are not specified...

there are much more elegant ways, but this is easy to understand...<g>

on the form where you make your choices for the report (I will refer to
this form as ReportMenu)

make 5 comboboxes to get their values from the available fiscal years

If the same year is entered in 2 comboboxes, the user will see that on
the report and they, presumeably meant that or they will change the
criteria and generate the report again...

these comboboxes on the ReportMenu form, for purposes of this example,
will be named Y1, Y2, Y3, Y4, and Y5.

The 5 subreport controls comprising the columns of the main report will
be set up such that each is linked to one of 5 calculated controls
corresponding to the ReportMenu form choices

ie:

'~~~~~~~~~~~~~~~

ReportMenu unbound combobox control names:
Y1
Y2
Y3
Y4
Y5

'~~~~~~~~~~~~~~~

ReportMenu subreports:
ControlSource --> subReport_formname
Name --> subReport1
LinkMasterfields --> Y1
LinkChildFields --> FiscalYear_controlname

ControlSource --> subReport_formname
Name --> subReport2
LinkMasterfields --> Y2
LinkChildFields --> FiscalYear_controlname

etc

'~~~~~~~~~~~~~~~