When does MS-Access Report VBA Code run

Question

I have been searching for a few days for a simple answer to this question:

Does the VBA code behind/in a report run when the report is open in Preview mode AND hidden?

I have inherited a P-O-S (Not Point-Of-Sale) Access 2003 database (Written mostly in German) that has a report with a very long and complicated VBA module attached to it. I need the code to execute when the report is run in Preview mode but it does not.

The report is called from a form with the following line:

DoCmd.OpenReport "Fertigungszettel",acPreview, , , acHidden

I then close the form with the following line

DoCmd.Close acReport, "Fertigungszettel", acSaveNo

The problem is that the code in/behind/attached to the report does not seem to run. I thought maybe I needed to delay the closing so that Access had time to run code so I added the following to the report vba

Private Declare Sub Sleep Lib "kernel32" (ByVal dwmilliseconds As Long)

Public SleepPls(ByVal ISecs As Long)
  Sleep(ISecs * 1000)
End Sub

And I added the line to call it like this:

SleepPls(10)

But it made no difference.

Can anyone either explain this to me or point me somewhere that will explain this in a simple way. I ask for a simple way because I will have to explain this to upper management who are not Dev's. (Not even close).

Thanks in advance.

If you open the report just to run some code, the code doesn't belong into the report. It should be in a general module to be run whenever needed. — Andre

Knox Knox · Accepted Answer · 2017-03-24T19:39:32

In MS Access, reports and other objects have a set of properties. If you go into design view on the report and select properties for the overall report, a list of will appear on the right side. At the top will be format, data, event, and so on. Under event, you will see a list of "On...'s" that trigger code to run. Like "On Current", "On Load", "On No Data", and so on.

VBA Code can be attached to one or more of these. So perhaps your code is attached to some event like "On Mouse Up" and thus wouldn't run if you programmatically open the report.

Find how the code is attached to an event, and that should lead you in the right direction. Perhaps you can attach the code to a different event, or your code could potentially directly call the code behind that you want to run.

Update: You indicate the code behind is attached to the "On click". I think that means the code behind looks like

Private Sub Report_Click()
...
End Sub

so i think in your code you can force the existing code behind to run by

DoCmd.OpenReport "Fertigungszettel",acPreview, , , acHidden
Call Forms("Your form name").Report_Click()

When does MS-Access Report VBA Code run

1 Answers