Modifying reports in Access 2010 runtime (1 Viewer)

[kasmax]

Hi guys
I need your expert suggestion
I have a database in which I have VBA code to modify report by changing report source and source of text boxes on the report.
I use acdesignview in VBA

My understanding is that it will not work in runtime version to modify report.

However it does work on 2010 runtime version

So am I missing something? How does it modify report via VBA in runtime version?

Thanks for your help

 

[isladogs]

Runtime basically means what it says. You can use a database to enter or edit data. However you cannot use the free runtime version to create or modify database objects such as reports. To do that you need to purchase the full version of Access

 

[kasmax]

Yes i understand that.
but my question is that when i run the database in 2010 runtime version why does the vba code works and modify the reports?

 

[isladogs]

Ah I think I understand what you are asking.
The runtime version can use existing code though there may potentially be some issues depending on what your code is meant to do.

 

[kasmax]

Right, so basically thats what im doing in vba:
open report in acdesign
change recordsource of report
change controlsource of textboxes
copy report as new report

so you think in runtime version it will run all that vba?

 

[MajP]

so you think in runtime version it will run all that vba?

There are many properties of forms, reports, and controls that can be set at runtime. These are not design changes. This is different from design changes that require you to open the form/report in design view to make the change, or code that adds objects.
Recordsource, rowsource, controlsource can be set at runtime without the need to be in design view.
No you cannot copy or add any objects.

It is a Poor design to be copying reports that are exactly the same, but only differ in row and control sources. Should be one report with code that modifies these properties at runtime.

 

[isladogs]

Not at my computer so unable to test but its easy enough to try it yourself.
Save your ACCDB file with an ACCDR file type and it will simulate the runtime environment.
Let us know whether it does all work and if not which parts don't run.

 

[CJ_London]

My understanding is that it will not work in runtime version to modify report.

However it does work on 2010 runtime version

Right, so basically thats what im doing in vba:
open report in acdesign
change recordsource of report
change controlsource of textboxes
copy report as new report

I'd be very surprised if opening report in acdesign view worked in any runtime version let alone 2010.

 

[HiTechCoach]

Are you compiling your front end into an MDE or ACCDE?

A compiled front end will not allow design mode for reports, forms, or code modules, even with the full version of Access.

My suspicion is you are using a non-compiled database with the Access Runtime installation. I hope you plan on deploying the front end compiled. Have you tested with a compiled frontend (mde/accde)?

TIP: A compiled front end is more stable in the Access runtime when an unhandled error occurs. This comes from experience 10+ years of using the Access runtime with 1000's of desktops.

Since I only deploy Front Ends that are compiled (mde/accde), I have never tried using VBA code to open an object in design mode with the Access runtime. I am curious to test it with an mdb/accdb

FWIW: I have never had the need to open a form or report object in design mode in a deployed/production application. Too many ways for it to go wrong.

TIP: Renaming your accdb to accdr "simulates" the runtime environment with the full version, but it is not the same as the actual runtime installation.

 

[zeroaccess]

The VBE will just tell you that the project isn't available, or something to that effect.

 

[kasmax]

Yes its accdb and i have not created accde because accde wont run the vba code to modify report.
some have suggested here that its a poor design to copy reports but it depends on the situation. let me explain my situation why im doing so

these reports are the treatment charts generated based on each treatment plan. each page should not have more then 8 days of treatments and the treatment days are in columns (not in rows).
so depending on the numbers of days these charts (reports can be from 1 page to 40 or 50 pages depending on how many days are in treatment eg 5 days or 6 months.
so i dont think it can be achieved by just a single report.
what i am doing is i have a template and depending on how many chartes it need i copy and create temp charts(reports) and set their recordsource.
I am attaching an image of the charts.

Well let me if you guys think there is any better way of doing it in a single report.

thanks guys

 

[zeroaccess]

Are these treatment days rows or columns in the recordsource?

 

[kasmax]

In the table these are rows but i have to write alot of code to format them into columns on the report. its because data is enter as usual which is in rows

 

[Uncle Gizmo]

I answered a question about generating multiple reports and this was the answer I provided:-


https://www.niftyaccess.com/generate-multiple-reports/


It's not what a direct answer to your question, however some of the techniques used might be of interest to you.


Basically you add some custom properties to the report, when you open the report you pass into the report the name of the form that opened it and then the report uses the form name to interrogate the form that opened it. The report load?/open? event (I Think) extracts the information like the date range the report title, record source, things like this.

 

[MajP]

Well let me if you guys think there is any better way of doing it in a single report

I think that definitely can be done in a single report, with the treatment plan a subreport for the 8 day period. May require some preprocessing or even a saved temp table.
I think off the top of my head I would have a table. That I run code to update all 8 day periods. I also probably have to do set the period PK in the main table based on a startdate.

TblTreatement
PeriodID_FK
Day1
Day2
....
Day8

here that its a poor design to copy reports but it depends on the situation.

No it really is a bad design approach, begging to bloat and corrupt the db.

 

[HiTechCoach]

so depending on the numbers of days these charts (reports can be from 1 page to 40 or 50 pages depending on how many days are in treatment eg 5 days or 6 months.
so i dont think it can be achieved by just a single report.
what i am doing is i have a template and depending on how many chartes it need i copy and create temp charts(reports) and set their recordsource.
I am attaching an image of the charts.

From what you just described, it is definitely possible to use a single report (you are now when you copy a template) and never need to go into design mode at runtime.

I know it is possible since I have users that run reports daily doing what you need with a compile front end ACCDE with an actually Access runtime installation.

There is a way to optimize what you are doing to eliminate creating copies of the report.

The trick is to create a table for the report that holds the data for a single page, per row if possible. It is only used for passing data to the report so it will not follow any normalization rules.

The general idea is this:
1) Clear the report's table.
2) Append the data into the table
3) print report
4) repeat until all data has been printed.

If your data will fit on a single row, then you can append all data before opening the report.

The end result would be the same as what you are do7ng know but a lot simpler and probably a lot faster. Also with none of the database bloating from creating report objects.

FWIW: I learned this technique in the late 1980s