change report label & recordsource

[phillip9]

Hello,

I'm hoping somone will be willing to help me with this question.

I have a generic report that could be used with various data sources and
queries, depending on what the user selects from a drop-down list. How can I
change the reports record source and label using VBA?

I have been trying to use the following, but get errors about the form not
being open or not using the correct name.

' error I get is:
run-time error '2451':
the report name 'projectschedule' you entered is misspelled or refers to a
report that isn't open or doesn't exist.


'---examples I have been trying to use ----
[Reports]![ProjectSchedule]!RecordSource = cboProjectList.Value

DoCmd.OpenReport "ProjectSchedule", acViewPreview
[Reports]![ProjectSchedule].Controls!ProjectScheduleLabel =
cboProjectList.Value


If I have left anything out, please let me know.

Thank you,

Phill


System information:
Windows xp pro
Access 2003
All service packs & patches installed for Windows xp & office 2003

 

[Marshall Barton]

I have a generic report that could be used with various data sources and
queries, depending on what the user selects from a drop-down list. How can I
change the reports record source and label using VBA?

I have been trying to use the following, but get errors about the form not
being open or not using the correct name.

' error I get is:
run-time error '2451':
the report name 'projectschedule' you entered is misspelled or refers to a
report that isn't open or doesn't exist.


'---examples I have been trying to use ----
[Reports]![ProjectSchedule]!RecordSource = cboProjectList.Value

DoCmd.OpenReport "ProjectSchedule", acViewPreview
[Reports]![ProjectSchedule].Controls!ProjectScheduleLabel =
cboProjectList.Value

Because the report opens in a relatively asyncronous
activity from the code that executes the OpenReport method,
you can not reliably set report properties outside the
report.

Use code within the report itself to to set the report and
control properties. To set things that apply to the entire
report, like the report's RecordSource, label Captions, etc,
use the report's Open event. To set a control's Value or
properties that may vary for each individual record, use the
containg section's Format event. In your example above, I
think it could be done in the report header's Format event:

Me.ProjectScheduleLabel = Forms!theform.cboProjectList

 

[phillip9]

Thank you,

Very good ideas, I completely missed those because I thought it would be
possible to do it similar to how I was trying, but I understand the reasoning.

Thanks for all the details and information, it was great to get a complete
answer.

thanks agian,

phil

I have a generic report that could be used with various data sources and
queries, depending on what the user selects from a drop-down list. How can I
change the reports record source and label using VBA?

I have been trying to use the following, but get errors about the form not
being open or not using the correct name.

' error I get is:
run-time error '2451':
the report name 'projectschedule' you entered is misspelled or refers to a
report that isn't open or doesn't exist.


'---examples I have been trying to use ----
[Reports]![ProjectSchedule]!RecordSource = cboProjectList.Value

DoCmd.OpenReport "ProjectSchedule", acViewPreview
[Reports]![ProjectSchedule].Controls!ProjectScheduleLabel =
cboProjectList.Value

Because the report opens in a relatively asyncronous
activity from the code that executes the OpenReport method,
you can not reliably set report properties outside the
report.

Use code within the report itself to to set the report and
control properties. To set things that apply to the entire
report, like the report's RecordSource, label Captions, etc,
use the report's Open event. To set a control's Value or
properties that may vary for each individual record, use the
containg section's Format event. In your example above, I
think it could be done in the report header's Format event:

Me.ProjectScheduleLabel = Forms!theform.cboProjectList

 

[rbb101]

I have been trying to figure out how to do this, but need a little more basic
help. I have mulitple generic reports that run from numerious queries. I
would like to set up a drop down box to select which query the report runs
from, ie change the recordsource to match that query. Currently I have to
change the recordsource each time I run the report off a different query. If
possible please give me some additional guidance in setting this up.

I have a generic report that could be used with various data sources and
queries, depending on what the user selects from a drop-down list. How can I
change the reports record source and label using VBA?

I have been trying to use the following, but get errors about the form not
being open or not using the correct name.

' error I get is:
run-time error '2451':
the report name 'projectschedule' you entered is misspelled or refers to a
report that isn't open or doesn't exist.


'---examples I have been trying to use ----
[Reports]![ProjectSchedule]!RecordSource = cboProjectList.Value

DoCmd.OpenReport "ProjectSchedule", acViewPreview
[Reports]![ProjectSchedule].Controls!ProjectScheduleLabel =
cboProjectList.Value

Because the report opens in a relatively asyncronous
activity from the code that executes the OpenReport method,
you can not reliably set report properties outside the
report.

Use code within the report itself to to set the report and
control properties. To set things that apply to the entire
report, like the report's RecordSource, label Captions, etc,
use the report's Open event. To set a control's Value or
properties that may vary for each individual record, use the
containg section's Format event. In your example above, I
think it could be done in the report header's Format event:

Me.ProjectScheduleLabel = Forms!theform.cboProjectList

 

[rbb101]

I have been trying to figure out how to run the same report against numerious
queries without going in to change the record source each time. I would like
to use a drop down box to select the query which will change the record
source. Your post seems to accomplish the same task, but I need more basic
information into how to set this up. Any additional information would be
appreciated. Thanks.

I have a generic report that could be used with various data sources and
queries, depending on what the user selects from a drop-down list. How can I
change the reports record source and label using VBA?

I have been trying to use the following, but get errors about the form not
being open or not using the correct name.

' error I get is:
run-time error '2451':
the report name 'projectschedule' you entered is misspelled or refers to a
report that isn't open or doesn't exist.


'---examples I have been trying to use ----
[Reports]![ProjectSchedule]!RecordSource = cboProjectList.Value

DoCmd.OpenReport "ProjectSchedule", acViewPreview
[Reports]![ProjectSchedule].Controls!ProjectScheduleLabel =
cboProjectList.Value

Because the report opens in a relatively asyncronous
activity from the code that executes the OpenReport method,
you can not reliably set report properties outside the
report.

Use code within the report itself to to set the report and
control properties. To set things that apply to the entire
report, like the report's RecordSource, label Captions, etc,
use the report's Open event. To set a control's Value or
properties that may vary for each individual record, use the
containg section's Format event. In your example above, I
think it could be done in the report header's Format event:

Me.ProjectScheduleLabel = Forms!theform.cboProjectList

 

[Marshall Barton]

Use the report's Open event procedure to set the report's
Recordsouce.

If the record source query's name is in an open form's combo
box, the code would be like this:

Me.RecordSource = Forms!thecombobox

I have been trying to figure out how to run the same report against numerious
queries without going in to change the record source each time. I would like
to use a drop down box to select the query which will change the record
source. Your post seems to accomplish the same task, but I need more basic
information into how to set this up. Any additional information would be
appreciated. Thanks.

I have a generic report that could be used with various data sources and
queries, depending on what the user selects from a drop-down list. How can I
change the reports record source and label using VBA?

I have been trying to use the following, but get errors about the form not
being open or not using the correct name.

' error I get is:
run-time error '2451':
the report name 'projectschedule' you entered is misspelled or refers to a
report that isn't open or doesn't exist.


'---examples I have been trying to use ----
[Reports]![ProjectSchedule]!RecordSource = cboProjectList.Value

DoCmd.OpenReport "ProjectSchedule", acViewPreview
[Reports]![ProjectSchedule].Controls!ProjectScheduleLabel =
cboProjectList.Value

Because the report opens in a relatively asyncronous
activity from the code that executes the OpenReport method,
you can not reliably set report properties outside the
report.

Use code within the report itself to to set the report and
control properties. To set things that apply to the entire
report, like the report's RecordSource, label Captions, etc,
use the report's Open event. To set a control's Value or
properties that may vary for each individual record, use the
containg section's Format event. In your example above, I
think it could be done in the report header's Format event:

Me.ProjectScheduleLabel = Forms!theform.cboProjectList

 

[rbb101]

I changed the open even procedure but get the following error message:
Microsoft Access can't find the macro 'Me'.

The macro (or its macro group) doesn't exist or the macro is new but hasn't
been saved.

Use the report's Open event procedure to set the report's
Recordsouce.

If the record source query's name is in an open form's combo
box, the code would be like this:

Me.RecordSource = Forms!thecombobox

I have been trying to figure out how to run the same report against numerious
queries without going in to change the record source each time. I would like
to use a drop down box to select the query which will change the record
source. Your post seems to accomplish the same task, but I need more basic
information into how to set this up. Any additional information would be
appreciated. Thanks.

phillip9 wrote:
I have a generic report that could be used with various data sources and
queries, depending on what the user selects from a drop-down list. How can I
change the reports record source and label using VBA?

I have been trying to use the following, but get errors about the form not
being open or not using the correct name.

' error I get is:
run-time error '2451':
the report name 'projectschedule' you entered is misspelled or refers to a
report that isn't open or doesn't exist.


'---examples I have been trying to use ----
[Reports]![ProjectSchedule]!RecordSource = cboProjectList.Value

DoCmd.OpenReport "ProjectSchedule", acViewPreview
[Reports]![ProjectSchedule].Controls!ProjectScheduleLabel =
cboProjectList.Value

Because the report opens in a relatively asyncronous
activity from the code that executes the OpenReport method,
you can not reliably set report properties outside the
report.

Use code within the report itself to to set the report and
control properties. To set things that apply to the entire
report, like the report's RecordSource, label Captions, etc,
use the report's Open event. To set a control's Value or
properties that may vary for each individual record, use the
containg section's Format event. In your example above, I
think it could be done in the report header's Format event:

Me.ProjectScheduleLabel = Forms!theform.cboProjectList

 

[Marshall Barton]

That message usually means you tried to put the line of code
in the report's OnOpen property. The OnOpen property should
have [Event Procedure] and the report's code module contains
the event's Sub procedure, which is where the line of code
belongs.

 

[rbb101]

That did the trick. Thank you.

That message usually means you tried to put the line of code
in the report's OnOpen property. The OnOpen property should
have [Event Procedure] and the report's code module contains
the event's Sub procedure, which is where the line of code
belongs.
--
Marsh
MVP [MS Access]

I changed the open even procedure but get the following error message:
Microsoft Access can't find the macro 'Me'.

The macro (or its macro group) doesn't exist or the macro is new but hasn't
been saved.

 

[Mike]

Is there a way I can change individual field's in the report control sources
based on a form that is opened when the report is opened?

 

[UpRider]

Mike, I think you would be better off modifying the report in the report's
own code module.
You can pass the name of the FORM to the report in the report's openargs.
Use a SELECT CASE in the report's code to identify and control each form's
required actions.
hth, UpRider

Is there a way I can change individual field's in the report control
sources
based on a form that is opened when the report is opened?

Hello,

I'm hoping somone will be willing to help me with this question.

I have a generic report that could be used with various data sources and
queries, depending on what the user selects from a drop-down list. How
can I
change the reports record source and label using VBA?

I have been trying to use the following, but get errors about the form
not
being open or not using the correct name.

' error I get is:
run-time error '2451':
the report name 'projectschedule' you entered is misspelled or refers to
a
report that isn't open or doesn't exist.


'---examples I have been trying to use ----
[Reports]![ProjectSchedule]!RecordSource = cboProjectList.Value

DoCmd.OpenReport "ProjectSchedule", acViewPreview
[Reports]![ProjectSchedule].Controls!ProjectScheduleLabel =
cboProjectList.Value


If I have left anything out, please let me know.

Thank you,

Phill


System information:
Windows xp pro
Access 2003
All service packs & patches installed for Windows xp & office 2003

 

[Mike]

sorry forgot to mention i'm a bit of a newbie to access. you think you can
break that down. I got the form to open when the report is open. on the form
you can enter the value. now how do i transefer that to a field control
source in the report. for example my report has column headers of years (a
series of 5 to be exact but there are more in the database) the form asks
you to enter the 5 years u want to show as column headers. i cant get the
input to change the column header in the report. can this even be done? if
so how? any help is greatly appreciatted.

Mike, I think you would be better off modifying the report in the report's
own code module.
You can pass the name of the FORM to the report in the report's openargs.
Use a SELECT CASE in the report's code to identify and control each form's
required actions.
hth, UpRider

Is there a way I can change individual field's in the report control
sources
based on a form that is opened when the report is opened?

Hello,

I'm hoping somone will be willing to help me with this question.

I have a generic report that could be used with various data sources and
queries, depending on what the user selects from a drop-down list. How
can I
change the reports record source and label using VBA?

I have been trying to use the following, but get errors about the form
not
being open or not using the correct name.

' error I get is:
run-time error '2451':
the report name 'projectschedule' you entered is misspelled or refers to
a
report that isn't open or doesn't exist.


'---examples I have been trying to use ----
[Reports]![ProjectSchedule]!RecordSource = cboProjectList.Value

DoCmd.OpenReport "ProjectSchedule", acViewPreview
[Reports]![ProjectSchedule].Controls!ProjectScheduleLabel =
cboProjectList.Value


If I have left anything out, please let me know.

Thank you,

Phill


System information:
Windows xp pro
Access 2003
All service packs & patches installed for Windows xp & office 2003

 

[Larry Linson]

Yes, it can be done. You will put VBA code in the Print (best) or Format
(alternative) event of the Report Section in which those column headers are
printed. If there are 5 separate Controls on the Form, each including a
year, then it is just a matter of transferring the values to the appropriate
label Control that is used as a column heading. If there is only a start
year, then you will have to calculate the other 4.

As an example, if your first column heading is in a TextBox named txtYearOne
Control on a Form named frmA, and is to be shown in a Label Control named
lblYearOne on the Report, then the code in the Report event would be:

Me.lblYearOne.Caption = Forms!frmA!txtYearOne

Larry Linson
Microsoft Access MVP

sorry forgot to mention i'm a bit of a newbie to access. you think you
can
break that down. I got the form to open when the report is open. on the
form
you can enter the value. now how do i transefer that to a field control
source in the report. for example my report has column headers of years (a
series of 5 to be exact but there are more in the database) the form asks
you to enter the 5 years u want to show as column headers. i cant get the
input to change the column header in the report. can this even be done?
if
so how? any help is greatly appreciatted.

Mike, I think you would be better off modifying the report in the
report's
own code module.
You can pass the name of the FORM to the report in the report's openargs.
Use a SELECT CASE in the report's code to identify and control each
form's
required actions.
hth, UpRider

Is there a way I can change individual field's in the report control
sources
based on a form that is opened when the report is opened?

:

Hello,

I'm hoping somone will be willing to help me with this question.

I have a generic report that could be used with various data sources
and
queries, depending on what the user selects from a drop-down list.
How
can I
change the reports record source and label using VBA?

I have been trying to use the following, but get errors about the form
not
being open or not using the correct name.

' error I get is:
run-time error '2451':
the report name 'projectschedule' you entered is misspelled or refers
to
a
report that isn't open or doesn't exist.


'---examples I have been trying to use ----
[Reports]![ProjectSchedule]!RecordSource = cboProjectList.Value

DoCmd.OpenReport "ProjectSchedule", acViewPreview
[Reports]![ProjectSchedule].Controls!ProjectScheduleLabel =
cboProjectList.Value


If I have left anything out, please let me know.

Thank you,

Phill


System information:
Windows xp pro
Access 2003
All service packs & patches installed for Windows xp & office 2003