MS Access 2010 - Save Form as Report automatically, with code on Format

[VivDenham]

I have a Form, the fields of which I also use to copy and paste into a Report. However, each time I make design changes to the Form, I have to remember to copy and past the changes into the Report.

I cannot just save the Form as a Report, because the Report has some VBA code on the On Format event.

Is there any way I can do VBA code to tell it that if any changes have been made to the DESIGN of the form, then Save As a Report, with the On Format code of xxxxxxx?

Many thanks
Viv

[Stewart Ross]

Sorry Viv, but there is no easy way to do what you ask. Forms and reports are entirely separate objects, and even if there was an event which could be triggered by a design change of a form (which there isn't) there is no in-built method which can update your report from your form.

-Stewart

[VivDenham]

Thanks, Stewart

Thanks for your reply. I thought I may be trying to do the impossible, when looking down the Events.

I thought I might be able to do something on the On Close event, where I could tell it to Save the form as a Report. I know this is possible manually, but did not know what VBA code to use.

And then I thought, 'Well, if I could tell it to add the VBA code on the On Format event of the Report it will be creating....... .........' but I think that is just wishful thinking.

I will just have to remember to do all this myself, manually.

Anyway, thanks for having a think about it.

Viv

[Stewart Ross]

Hi Viv. Just to avoid possible confusion, the events such as On Close occur when the form is in use and is closed by the user or the application.

These same events don't actually occur in design mode, so closing a form you have opened in design mode does not trigger an On Close event that you can trap.

Of course, switching an open form to design mode will trigger form events as a result of the open form losing focus, but the trappable event sequence then stops once you have the form in design mode.

-Stewart

[VivDenham]

Design Mode

As you can probably tell, I am not at all "up" with this VBA thingy.

I would not mind if, every time I close the form, it ran some code to say Save this Form as a Report - at least that way I would know that the Report is totally up to date, design-wise. Or are you saying I could do that on the On Losing Focus event?

Either way, that would be a help, but what code do I use?

Then, would it be possible to create a Macro to tell the Report that say, On Open, run some code On Format?

I'm probably still asking for ths impossible..... .

Viv

[Stewart Ross]

Whilst not impossible to achieve with some level of bespoke coding, Viv, you're still showing some confusion over the events and how they are triggered. The events are used by designers to provide a programmed response to users opening forms, closing them, updating fields and so on. They are not intended to fire when designers are modifying the underlying design, as this is just a very small part of the lifecycle of a form or report.

I would say at this stage that the effort you could expend for what is likely to be a very small gain in overall productivity is far outweighed by the time it would take for what is in effect a one-off application.

I find that designing an Access report is a time-intensive process - regardless of what the built-in wizards can do to start you off. Resizing fields, aligning them, and doing all the other bits and pieces that make the report readable to an end user takes a lot of effort.

In general, there is simply no easy way that a form can be used as the basis for an automated translation to a workable report. I can't say that what you want to achieve is impossible; what I can say for sure is that if you decide to design a new wizard to do it for you the time you will spend perfecting it will far outweigh the time it would have taken you to do it manually.

The cost-benefit question you have to ask is 'am I likely to need this tool so often that I can afford to spend several months learning how to set it up and then perfecting it?'. If you can answer Yes then go ahead; if not, and my advice would definitely be NOT, continue doing it manually!

-Stewart

[VivDenham]

Think I've gone as far as I can

Hi Stewart

Thanks for all your time and thought on this matter. Looks like I'm asking for the impossible.

The nearest I've got is to create a Command Button which Saves the Form as a Report. This lets me over-write the original report, and from there I can put an Event on the On Format line, adding in my VBA code manually.

Once again, thanks for your time.

Viv