Pressing a Command Button

[Sharon]

What macro commands do I use to press a command button
within a form? The command button runs VB code when
clicked.

Form Name: frmProcessExceptionGroup
command button name: cmdProcessExcptGroup

 

[Steve Schapel]

Sharon,

Could you please clarify what you mean? I normally use a mouse to press
a command button. What do you want the macro to do?

 

[Guest]

Steve,

I need several things to happen for my users "behind the
scenes" using a macro. These are the steps:

1. Run a query
2. Run a second query
3. Open a form, press the command button (which processes
my VB code)
4. Close the form
5. Run a third query using the new information that the
VB code created.
6. Open the report based on the third query.

I know how to use the macro for everything except
processing the VB code in step 3. I am now pressing the
command button, (cmdProcessExcptCode), in a form to start
the following code.

Select Case [excpt_rate]

Case Is = 0
[ExcptPer] = "=0%"
Case 0.01 To 0.1
[ExcptPer] = "> 0% and =< 10%"
Case 0.101 To 0.25
[ExcptPer] = "> 10% and =< 25%"
Case 0.251 To 1
[ExcptPer] = "> 26% and =< 100%"
Case Else
[ExcptPer] = "null"
End Select

By the way, explain to me what a mouse is??? (just
kidding!)

 

[Douglas J. Steele]

Macros cannot simulate events (although they can call the code that the
event triggers)

Try putting logic in your form's Open event to automatically run the code,
or else create a function that you can run.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Steve,

I need several things to happen for my users "behind the
scenes" using a macro. These are the steps:

1. Run a query
2. Run a second query
3. Open a form, press the command button (which processes
my VB code)
4. Close the form
5. Run a third query using the new information that the
VB code created.
6. Open the report based on the third query.

I know how to use the macro for everything except
processing the VB code in step 3. I am now pressing the
command button, (cmdProcessExcptCode), in a form to start
the following code.

Select Case [excpt_rate]

Case Is = 0
[ExcptPer] = "=0%"
Case 0.01 To 0.1
[ExcptPer] = "> 0% and =< 10%"
Case 0.101 To 0.25
[ExcptPer] = "> 10% and =< 25%"
Case 0.251 To 1
[ExcptPer] = "> 26% and =< 100%"
Case Else
[ExcptPer] = "null"
End Select

By the way, explain to me what a mouse is??? (just
kidding!)

-----Original Message-----
Sharon,

Could you please clarify what you mean? I normally use a mouse to press
a command button. What do you want the macro to do?

--
Steve Schapel, Microsoft Access MVP


.

 

[Steve Schapel]

Sharon,

I agree 100% with Doug's comments.

However, a closer look at what you are doing suggests there might be a
simpler way anyway.

It looks to me that the VBA procedure is simply updating the value of
the ExcptPer field according to the value of the excpt_rate on the
current record on the form. Is this correct? And based on the way you
have done this, I also assume that the form is showing the only record
that exists in its record source. Am I right? Well, if you *must*
update the value of one field based on the value in another, you can
simply do this via an Update Query, and run this in your macro, if you
like, and forget all about the form and the VBA code. But, the chances
are, in any case, that the existence of the ExcptPer field is not valid
anyway... certainly not if it is simply a reflection of the value of
excpt_rate. Is your purpose in doing this to provide the appropriate
ExcptPer for display on your report? In that case, I would suggest that
it is simplest to trash the stored Excpt_Per field altogether, and do
this in a calculated field in the query that the report is based on, e.g.
ExcptPer: Switch([excpt_rate]=0,"=0%",[excpt_rate]<=.1,"> 0% and =<
10%",[excpt_rate]<=.25,"> 10% and =< 25%",[excpt_rate]<=1,"> 25% and =<
100%",True,"null")

By the way, I was a bit confused about your steps 5. and 6. Run a query
means an action query, e.g. append, update, delete... right? So the
report can't be based on the third report which you run in step 5.
because a report can't be based on an action query.

 

[Guest]

Awsome! I deleted the form and got rid of an extra
query. Now, Steve, I need to know the following:
My first query includes the criteria and creates a New
table "tblExceptionGroupings". I then created a crosstab
query to get the report format. My macro has the
following steps:

1. Open query: "MakeGroupingsTable" Create Table
2. Open query: "Groupings Crosstab" Create Crosstab
3. Open report: "Exception Groupings: Create Report

Is there a way to create a step in this macro to Enter Yes
to the prompt "You are about to delete 550 records in the
Groupings Table'? so my user only has to wait for the
final report to appear? Yes, I know that you can use a
mouse as well.....

-----Original Message-----
Sharon,

I agree 100% with Doug's comments.

However, a closer look at what you are doing suggests there might be a
simpler way anyway.

It looks to me that the VBA procedure is simply updating the value of
the ExcptPer field according to the value of the excpt_rate on the
current record on the form. Is this correct? And based on the way you
have done this, I also assume that the form is showing the only record
that exists in its record source. Am I right? Well, if you *must*
update the value of one field based on the value in another, you can
simply do this via an Update Query, and run this in your macro, if you
like, and forget all about the form and the VBA code. But, the chances
are, in any case, that the existence of the ExcptPer field is not valid
anyway... certainly not if it is simply a reflection of the value of
excpt_rate. Is your purpose in doing this to provide the appropriate
ExcptPer for display on your report? In that case, I would suggest that
it is simplest to trash the stored Excpt_Per field altogether, and do
this in a calculated field in the query that the report is based on, e.g.
ExcptPer: Switch([excpt_rate]=0,"=0%",[excpt_rate]<=.1,"> 0% and =<
10%",[excpt_rate]<=.25,"> 10% and =< 25%",[excpt_rate]

 

[Steve Schapel]

Sharon,

Put a SetWarnings,No action in your macro before the first OpenQuery
action. Or, as you say, there's always the mouse