Dynamically adding fields to reports and changing orientation. 1

[1DMF]

Hi,

We have a report which a user has requested changes to.

The current report has a user input field for record selection, however they have asked that if the user input is a certain selection then they want an additional field to be displayed on the report and also the report to have its page orientation altered to accomodate the additional field from portrait to landscape.

Is this possible? or do I now simply copy the report add the changes and now have two reports depending on what info they wish displayed.

Cheers,
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[dhookom]

If these type of requests are infrequent, I would just create a new report.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

That's the way I was leaning!

But I take it from your answer it is possible.



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[dhookom]

But I take it from your answer it is possible.

What do you mean by "it"? Are you referring to the ability to use one report? I believe this is possible but it may require opening the report in design view to set some properties or create controls. I don't think this is worth the trouble.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

Yes!

I thought it might be along those lines.

And I'm guessing you'd need to know the coordinates on the report you want to place the controls etc..

I'm going for the easy option.. rewrite the queries and have two reports.

Thanks for the help Duanne.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

I disagree you would have to go into design view to do this. You can have a hidden extra control on the form. Only to add a control would you have to go to design view. You can show, move, change the recordsource of controls on open. So you could have a hidden control and then resize and move the controls to fit in the on open event. You can resize the form as well.

You can add and remove controls as Duane says. Open the form invisible in desingn view, make changes, close and save, open it in preview. This is not a good idea in Access. It tends to corrupt your database. Every form and report has a limit of around 700 controls during the lifetime of the form/report. This includes deleted controls. So if you routinely add and delete through code, I believe you will eventually max this limit and corrupt the form/report or at least lock it up.

 

[MajP]

FYI. Certain changes can be easily done in a form, but harder to do on a report. Changing the recordsource on a form can be done at any time, but doing it in a report is a little harder. I find the work around is to set a public variable/s prior to opening the report.

so prior to opening the report
glblReportRecordsource = "query1"

and then in the reports on open (do not use on load it is too late)
me.recordsource = glblReportRecordsource
or you could pull from a pop up form.

Making a new report at times is easier, but configuration management is much easier if you have a single form and report and alter it to meet certain conditions. I find this technique very useful when I have a complex form that can be a subform or a stand alone form. I check to see if it is opened as a subform/stand alone and then alter the properties accordingly.

 

[1DMF]

Thanks for the reply MajP, though it's not the record source that is the issue.

I have a query bound to the report which has IIF statements and selects the various records based on the user input.

The problem was adding an additional field to the report depnding on what the user selected for the IIF variable prompt.

I've nearly finished the new report and now just need to amend the old reports query and we have two report instead of one depending on what they wish to display.

But i hear you about forms and record source, infact using a similar process for the multiple tabs and embeded subforms in our main DB, I speeded the load time up but ten fold by setting each subform control source to blank and only when the relevant tab is selected, then the correct control source is set meaning the data is only retrieved for the current tab/screen being shown to the user.



"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

The point is that you can do what you were originally asking without going to design view. Access just does not like programatically opening and closing forms in design view. But you can make a report "dynamic" by modifying hidden controls instead of adding and deleting controls. These controls can be hidden, shrunk down to 0 width and height, and without any control source. So basically they have no overhead. Then show and configure them on open. It appears that the report is dynamic.

Some times it is just easier to make a copy, but other times it is easier to maintain configuration by having a single form/report and modifying it. The report is just a little tricker because certain things can not be done after the report opens and has to be done in the onopen event.

 

[1DMF]

gotcha, i was wondering about having controls on the report and then hiding them depdning on if the data was to be displayed or not.

If these controls are in the middle of the report, do the other columns / controls shrink to fill the space or could you end up with gaps in the report.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[MajP]

Unfortunately you have to position your controls so that is a factor to weigh as to how complicated that gets. You can set the control a control to shrink and grow which will change the height, and so you can push controls downwards, but not horizontally.

 

[1DMF]

Well it was a thought, but in this instance, two reports cetainly seem the best solution.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts