AUTOMATE CODE BEHIND A BUTTON

[rfuscjr via AccessMonster.com]

I inhereted a bunch of reports. They are located on a form (frmReports).
The form has a button for each of a dozen or so reports...none with prompts.
Behind each button is a varying combination of Open query statements, vba
statements, Open Report statements, xfr to Excel statements etc etc. My goal
is to automate the act of pressing each button. Can I create a new form
(frmRunAllReports) that essentially has a line of code specifying each
button be 'pushed'?

 

[Marshall Barton]

I inhereted a bunch of reports. They are located on a form (frmReports).
The form has a button for each of a dozen or so reports...none with prompts.
Behind each button is a varying combination of Open query statements, vba
statements, Open Report statements, xfr to Excel statements etc etc. My goal
is to automate the act of pressing each button. Can I create a new form
(frmRunAllReports) that essentially has a line of code specifying each
button be 'pushed'?

Probably, but what do you mean by "specifying each
button"?

If you want a button on the same form to perform the actions
of several other buttons, then just call each of the other
button's Click event procedure:
Me.button1_Click
Me.button2_Click
. . .

If you want the new button to be on a separate form, the old
form really should be open (even if it's invisible). In
this case the procedure calls would be:
Forms!oldform.button1_Click
Forms!oldform.button2_Click
. . .
If the code behind all the individual buttons on the old
form is totally independent of any controls, module level
variables or global variables, then you may be able do do it
without the form being open:
Form_oldform.button1_Click
Form_oldform.button2_Click
. . .

 

[rfuscjr via AccessMonster.com]

Wow, so many choices. Ideally I want to schedule (using Task Scheduler) the
db to open and fire off the vba to click each button. This is where I got
confused as to whether I needed another form or module or ?????? that would
house the vba calls to click the buttons.

I inhereted a bunch of reports. They are located on a form (frmReports).
The form has a button for each of a dozen or so reports...none with prompts.

[quoted text clipped - 3 lines]

(frmRunAllReports) that essentially has a line of code specifying each
button be 'pushed'?

Probably, but what do you mean by "specifying each
button"?

If you want a button on the same form to perform the actions
of several other buttons, then just call each of the other
button's Click event procedure:
Me.button1_Click
Me.button2_Click
. . .

If you want the new button to be on a separate form, the old
form really should be open (even if it's invisible). In
this case the procedure calls would be:
Forms!oldform.button1_Click
Forms!oldform.button2_Click
. . .
If the code behind all the individual buttons on the old
form is totally independent of any controls, module level
variables or global variables, then you may be able do do it
without the form being open:
Form_oldform.button1_Click
Form_oldform.button2_Click
. . .

 

[Marshall Barton]

If you want to run this automously, there may be no need to
use a master button approach. I suggest you explore
creating a macro (named RunReports) with a single action:
RunCode BatchReports
Then create a Public Function named BatchReports in a
standard module that does all the work.

This way the scheduler can initiate the reports by using the
command line argument /XRunReports and gives you a hook to
avoid the mdb from going into its usual user display,
activities. The macro (or the function) can use the Quit
action/method to end the mdb so no user interaction would be
needed.
--
Marsh
MVP [MS Access]

Wow, so many choices. Ideally I want to schedule (using Task Scheduler) the
db to open and fire off the vba to click each button. This is where I got
confused as to whether I needed another form or module or ?????? that would
house the vba calls to click the buttons.

I inhereted a bunch of reports. They are located on a form (frmReports).
The form has a button for each of a dozen or so reports...none with prompts.

[quoted text clipped - 3 lines]

(frmRunAllReports) that essentially has a line of code specifying each
button be 'pushed'?

Probably, but what do you mean by "specifying each
button"?

If you want a button on the same form to perform the actions
of several other buttons, then just call each of the other
button's Click event procedure:
Me.button1_Click
Me.button2_Click
. . .

If you want the new button to be on a separate form, the old
form really should be open (even if it's invisible). In
this case the procedure calls would be:
Forms!oldform.button1_Click
Forms!oldform.button2_Click
. . .
If the code behind all the individual buttons on the old
form is totally independent of any controls, module level
variables or global variables, then you may be able do do it
without the form being open:
Form_oldform.button1_Click
Form_oldform.button2_Click
. . .

 

[rfuscjr via AccessMonster.com]

Well, back from vacation and eager to try this. Since tisis a new arena for
me, I thought I'd investigate each of the suggestions outlined. I started
with this:

If you want a button on the same form to perform the actions
of several other buttons, then just call each of the other
button's Click event procedure:
Me.button1_Click
Me.button2_Click


Access does not recognize the ME. portion----I recall having to more formally
/ explictly issue the command somewhere else so I tried:

Forms!oldform.button1_Click
Forms!oldform.button2_Click
. . .

It also fails. I types: Forms! then the name of my form then the name after
the Sub in the first line of my VBA code; or: Forms!MonthEndProduction.
Command101_Click.

I got an error that said:

Application Defined or Object defined error. #2645.


I would think I should be able to do this. What am I missing here.....other
than a 3 month vba course!

I inhereted a bunch of reports. They are located on a form (frmReports).
The form has a button for each of a dozen or so reports...none with prompts.

[quoted text clipped - 3 lines]

(frmRunAllReports) that essentially has a line of code specifying each
button be 'pushed'?

Probably, but what do you mean by "specifying each
button"?

If you want a button on the same form to perform the actions
of several other buttons, then just call each of the other
button's Click event procedure:
Me.button1_Click
Me.button2_Click
. . .

If you want the new button to be on a separate form, the old
form really should be open (even if it's invisible). In
this case the procedure calls would be:
Forms!oldform.button1_Click
Forms!oldform.button2_Click
. . .
If the code behind all the individual buttons on the old
form is totally independent of any controls, module level
variables or global variables, then you may be able do do it
without the form being open:
Form_oldform.button1_Click
Form_oldform.button2_Click
. . .

 

[mcescher]

The first example he gives has to be in the code for the form that has
the buttons. (frmReports) The title of the VBA window should say
something like "Form_frmReports"

The second example would be used on a different form. The button will
point to the original form and buttons

Something like:
Forms!frmReports.cmdReport01_Click
Forms!frmReports.cmdReport02_Click

The third example would be useful if your reports don't tie back to the
orginal form.

Something like:
Form_frmReports.cmdReport01_Click
Form_frmReports.cmdReport02_Click


You have to supply the name of the form and the name of the buttons for
these examples to work.

It also fails. I types: Forms! then the name of my form then
the name after the Sub in the first line of my VBA code; or:
Forms!MonthEndProduction.Command101_Click.

My hunch is that MonthEndProduction is the form that will hold the
single button, and Command101 is the intended button to run everything.
That would cause an infinite loop.

You probably want something like this:

Private Sub Command101_Click()
Forms!frmReports.cmdReport01_Click
Forms!frmReports.cmdReport02_Click
...
End Sub

Well, back from vacation and eager to try this. Since tisis a new arena for
me, I thought I'd investigate each of the suggestions outlined. I started
with this:

If you want a button on the same form to perform the actions
of several other buttons, then just call each of the other
button's Click event procedure:
Me.button1_Click
Me.button2_Click


Access does not recognize the ME. portion----I recall having to more formally
/ explictly issue the command somewhere else so I tried:

Forms!oldform.button1_Click
Forms!oldform.button2_Click
. . .

It also fails. I types: Forms! then the name of my form then the name after
the Sub in the first line of my VBA code; or: Forms!MonthEndProduction.
Command101_Click.

I got an error that said:

Application Defined or Object defined error. #2645.


I would think I should be able to do this. What am I missing here.....other
than a 3 month vba course!

I inhereted a bunch of reports. They are located on a form (frmReports).
The form has a button for each of a dozen or so reports...none with prompts.

[quoted text clipped - 3 lines]

(frmRunAllReports) that essentially has a line of code specifying each
button be 'pushed'?

Probably, but what do you mean by "specifying each
button"?

If you want a button on the same form to perform the actions
of several other buttons, then just call each of the other
button's Click event procedure:
Me.button1_Click
Me.button2_Click
. . .

If you want the new button to be on a separate form, the old
form really should be open (even if it's invisible). In
this case the procedure calls would be:
Forms!oldform.button1_Click
Forms!oldform.button2_Click
. . .
If the code behind all the individual buttons on the old
form is totally independent of any controls, module level
variables or global variables, then you may be able do do it
without the form being open:
Form_oldform.button1_Click
Form_oldform.button2_Click
. . .

 

[rfuscjr via AccessMonster.com]

Thanks. I notice this works to...so far anyway.

CALL reportname_Click

The first example he gives has to be in the code for the form that has
the buttons. (frmReports) The title of the VBA window should say
something like "Form_frmReports"

The second example would be used on a different form. The button will
point to the original form and buttons

Something like:
Forms!frmReports.cmdReport01_Click
Forms!frmReports.cmdReport02_Click

The third example would be useful if your reports don't tie back to the
orginal form.

Something like:
Form_frmReports.cmdReport01_Click
Form_frmReports.cmdReport02_Click

You have to supply the name of the form and the name of the buttons for
these examples to work.

It also fails. I types: Forms! then the name of my form then
the name after the Sub in the first line of my VBA code; or:
Forms!MonthEndProduction.Command101_Click.

My hunch is that MonthEndProduction is the form that will hold the
single button, and Command101 is the intended button to run everything.
That would cause an infinite loop.

You probably want something like this:

Private Sub Command101_Click()
Forms!frmReports.cmdReport01_Click
Forms!frmReports.cmdReport02_Click
...
End Sub

Well, back from vacation and eager to try this. Since tisis a new arena for
me, I thought I'd investigate each of the suggestions outlined. I started

[quoted text clipped - 57 lines]

Message posted via AccessMonster.com