Turn off Messages at bottom of display

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

During the execution of a program I use the following to display the step the
programs is on the status line:

DoCmd.Echo -1, "Create Actual Data for Branch Ranking Total."

When the program executes a query, the message is covered by the following:

Running Query

Is there a way to turn off all of these type of messages? DoCmd.SetWarnings
does not effect them.
 
How are you executing the query?

Try:
dbEngine(0)(0).Execute "Query1", dbFailOnError
 
Thanks for the help. I have been executing the query with
DoCmd.OpenQuery("Query"). I tried the code below and it worked. Is there
anything else I need to now if I convert all of my DoCmd.OpenQuery commands
to the code listed below ? Do they work exactly the same for all types of
queries, i.e. Select, Append, Make table, etc. Also is there any advantage
other than the fact that it does not cancel the DoCmd.Echo error message to
using the code listed below ? Do they both execute at the same speed ?
 
You can only use the Execute method to execute Action Queries ...
The main benefit is that the Warnings do not need to be turned of .. so if
the query encounters an error .. it will be displayed to let the user know
there was a problem.
And ... the Execute method is faster than the OpenQuery and RunSQL methods
....
 
Thank you. The method seems to be working except on some Make Table queries.
Some work but others say:

3061 Too Few Parameters Expected 1.

These queries do not take parameters and work with DoCmd. They are Action
queries so should work with dbEngine(0)(0).Execute "Query1", dbFailOnError.
Do you know why the system thinks that it needs parameters supplied ?
 
Chances are, these queries contain a reference such as:
[Forms].[Form1].[Text0]

Strictly, this is a parameter, but OpenQuery or RunSQL calls the Expression
Service (ES) to resolve the parameter, so you do not get the Parameter
dialog unless the reference does not resolve (e.g. if Form1 is not open.)

The ES is not available to resolve these references for the Execute method.
Your options are:
a) Explicitly assign the value to the parameter
Dim qdf As QueryDef
Set qdf = dbEngine(0)(0).QueryDefs("Query1")
qdf.Parameters("[Forms].[Form1].[Text0]") = [Forms].[Form1].[Text0]
qdf.Execute

b) Concatenate the value from the text box into the SQL string, instead of
using a saved query, e.g.:
Dim strSql As String
strSql = "DELETE FROM Table1 WHERE ID = " & [Forms].[Form1].[Text0] &
";"
dbEngine(0)(0).Execute strSql, dbFailOnError

Personally, I almost always use (b), because this method is so flexible. You
can examine the text boxes at runtime, ignore any that do not have a value,
and so the string you build is the most efficient execution possible.
Additionally, the code is independent of a stored query, which means:
- reduced dependencies (able to reuse the code elsewhere without worrying
about what queries it depends on);
- easier maintenance (not having to worry about somebody altering/removing
the query);
- far fewer saved queries.

But the *main* reason to use Execute rather than RunSQL is that you know if
it succeeded. With the dbFailOnError switch, you get a trappable error if
anything goes wrong. In 99% of cases, you really need to know if your action
query succeeded before you go on to the next thing you need to achieve.
 
I used method a and it seems to work fine. Thank you. The queries did have
parameters, and in some cases they were queries of queries and the second
query had the parameter. I keyed in the criterion as you indicated and it
seems to work. In the case of one query as listed below, it had 2 parameters
so I issued the command 2 times. I am trying to understand the process. The
form with the date information is open when the queries are run. With DoCmd
there is no issue. In the code listed below the date information from the
form is populated into the parameter which can then be used by the query.
However shouldn't the left side of the equation list the fields used on the
query and not repeat the same code as the right side ?

qdef.Parameters("[Forms]![FRMrEports]![cboEffdate]") =
[Forms]![FRMrEports]![cboEffdate]
qdef.Parameters("[Forms]![frmReports]![cbopoints]") =
[Forms]![FRMrEports]![cboPoints]


Allen Browne said:
Chances are, these queries contain a reference such as:
[Forms].[Form1].[Text0]

Strictly, this is a parameter, but OpenQuery or RunSQL calls the Expression
Service (ES) to resolve the parameter, so you do not get the Parameter
dialog unless the reference does not resolve (e.g. if Form1 is not open.)

The ES is not available to resolve these references for the Execute method.
Your options are:
a) Explicitly assign the value to the parameter
Dim qdf As QueryDef
Set qdf = dbEngine(0)(0).QueryDefs("Query1")
qdf.Parameters("[Forms].[Form1].[Text0]") = [Forms].[Form1].[Text0]
qdf.Execute

b) Concatenate the value from the text box into the SQL string, instead of
using a saved query, e.g.:
Dim strSql As String
strSql = "DELETE FROM Table1 WHERE ID = " & [Forms].[Form1].[Text0] &
";"
dbEngine(0)(0).Execute strSql, dbFailOnError

Personally, I almost always use (b), because this method is so flexible. You
can examine the text boxes at runtime, ignore any that do not have a value,
and so the string you build is the most efficient execution possible.
Additionally, the code is independent of a stored query, which means:
- reduced dependencies (able to reuse the code elsewhere without worrying
about what queries it depends on);
- easier maintenance (not having to worry about somebody altering/removing
the query);
- far fewer saved queries.

But the *main* reason to use Execute rather than RunSQL is that you know if
it succeeded. With the dbFailOnError switch, you get a trappable error if
anything goes wrong. In 99% of cases, you really need to know if your action
query succeeded before you go on to the next thing you need to achieve.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

rmcompute said:
Thank you. The method seems to be working except on some Make Table
queries.
Some work but others say:

3061 Too Few Parameters Expected 1.

These queries do not take parameters and work with DoCmd. They are Action
queries so should work with dbEngine(0)(0).Execute "Query1",
dbFailOnError.
Do you know why the system thinks that it needs parameters supplied ?
Click to expand...
Click to expand...
 
To the query, any name it cannot resolve is a parameter. It could be called
"Fred" if you wanted. If your code, you have to supply the value of the
parameter, so you would then code:
qdf.Parameters("Fred") = [Forms]![FRMrEports]![cboEffdate]
or:
Dim dt As Date
dt = [Forms]![FRMrEports]![cboEffdate]
qdf.Parameters("Fred") = dt
or even refer to the parameter by its index number:
qdf.Parameters(0) = dt
as it is just a collection of parameters as far as the QueryDef is
concerned.

The number of parameters the query needs to know is:
qdf.Parameters.Count

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

rmcompute said:
I used method a and it seems to work fine. Thank you. The queries did
have
parameters, and in some cases they were queries of queries and the second
query had the parameter. I keyed in the criterion as you indicated and it
seems to work. In the case of one query as listed below, it had 2
parameters
so I issued the command 2 times. I am trying to understand the process.
The
form with the date information is open when the queries are run. With
DoCmd
there is no issue. In the code listed below the date information from the
form is populated into the parameter which can then be used by the query.
However shouldn't the left side of the equation list the fields used on
the
query and not repeat the same code as the right side ?

qdef.Parameters("[Forms]![FRMrEports]![cboEffdate]") =
[Forms]![FRMrEports]![cboEffdate]
qdef.Parameters("[Forms]![frmReports]![cbopoints]") =
[Forms]![FRMrEports]![cboPoints]


Allen Browne said:
Chances are, these queries contain a reference such as:
[Forms].[Form1].[Text0]

Strictly, this is a parameter, but OpenQuery or RunSQL calls the
Expression
Service (ES) to resolve the parameter, so you do not get the Parameter
dialog unless the reference does not resolve (e.g. if Form1 is not open.)

The ES is not available to resolve these references for the Execute
method.
Your options are:
a) Explicitly assign the value to the parameter
Dim qdf As QueryDef
Set qdf = dbEngine(0)(0).QueryDefs("Query1")
qdf.Parameters("[Forms].[Form1].[Text0]") = [Forms].[Form1].[Text0]
qdf.Execute

b) Concatenate the value from the text box into the SQL string, instead
of
using a saved query, e.g.:
Dim strSql As String
strSql = "DELETE FROM Table1 WHERE ID = " & [Forms].[Form1].[Text0] &
";"
dbEngine(0)(0).Execute strSql, dbFailOnError

Personally, I almost always use (b), because this method is so flexible.
You
can examine the text boxes at runtime, ignore any that do not have a
value,
and so the string you build is the most efficient execution possible.
Additionally, the code is independent of a stored query, which means:
- reduced dependencies (able to reuse the code elsewhere without worrying
about what queries it depends on);
- easier maintenance (not having to worry about somebody
altering/removing
the query);
- far fewer saved queries.

But the *main* reason to use Execute rather than RunSQL is that you know
if
it succeeded. With the dbFailOnError switch, you get a trappable error if
anything goes wrong. In 99% of cases, you really need to know if your
action
query succeeded before you go on to the next thing you need to achieve.

rmcompute said:
Thank you. The method seems to be working except on some Make Table
queries.
Some work but others say:

3061 Too Few Parameters Expected 1.

These queries do not take parameters and work with DoCmd. They are
Action
queries so should work with dbEngine(0)(0).Execute "Query1",
dbFailOnError.
Do you know why the system thinks that it needs parameters supplied ?


:

You can only use the Execute method to execute Action Queries ...
The main benefit is that the Warnings do not need to be turned of ..
so
if
the query encounters an error .. it will be displayed to let the user
know
there was a problem.
And ... the Execute method is faster than the OpenQuery and RunSQL
methods
....

--
Ricky Hicks - Access MVP

Thanks for the help. I have been executing the query with
DoCmd.OpenQuery("Query"). I tried the code below and it worked.
Is
there
anything else I need to now if I convert all of my DoCmd.OpenQuery
commands
to the code listed below ? Do they work exactly the same for all
types
of
queries, i.e. Select, Append, Make table, etc. Also is there any
advantage
other than the fact that it does not cancel the DoCmd.Echo error
message
to
using the code listed below ? Do they both execute at the same
speed ?

:

How are you executing the query?

Try:
dbEngine(0)(0).Execute "Query1", dbFailOnError

During the execution of a program I use the following to display
the
step
the
programs is on the status line:

DoCmd.Echo -1, "Create Actual Data for Branch Ranking Total."

When the program executes a query, the message is covered by the
following:

Running Query

Is there a way to turn off all of these type of messages?
DoCmd.SetWarnings
does not effect them.
Click to expand...
Click to expand...
Click to expand...
 
Thank you.

Allen Browne said:
To the query, any name it cannot resolve is a parameter. It could be called
"Fred" if you wanted. If your code, you have to supply the value of the
parameter, so you would then code:
qdf.Parameters("Fred") = [Forms]![FRMrEports]![cboEffdate]
or:
Dim dt As Date
dt = [Forms]![FRMrEports]![cboEffdate]
qdf.Parameters("Fred") = dt
or even refer to the parameter by its index number:
qdf.Parameters(0) = dt
as it is just a collection of parameters as far as the QueryDef is
concerned.

The number of parameters the query needs to know is:
qdf.Parameters.Count

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

rmcompute said:
I used method a and it seems to work fine. Thank you. The queries did
have
parameters, and in some cases they were queries of queries and the second
query had the parameter. I keyed in the criterion as you indicated and it
seems to work. In the case of one query as listed below, it had 2
parameters
so I issued the command 2 times. I am trying to understand the process.
The
form with the date information is open when the queries are run. With
DoCmd
there is no issue. In the code listed below the date information from the
form is populated into the parameter which can then be used by the query.
However shouldn't the left side of the equation list the fields used on
the
query and not repeat the same code as the right side ?

qdef.Parameters("[Forms]![FRMrEports]![cboEffdate]") =
[Forms]![FRMrEports]![cboEffdate]
qdef.Parameters("[Forms]![frmReports]![cbopoints]") =
[Forms]![FRMrEports]![cboPoints]


Allen Browne said:
Chances are, these queries contain a reference such as:
[Forms].[Form1].[Text0]

Strictly, this is a parameter, but OpenQuery or RunSQL calls the
Expression
Service (ES) to resolve the parameter, so you do not get the Parameter
dialog unless the reference does not resolve (e.g. if Form1 is not open.)

The ES is not available to resolve these references for the Execute
method.
Your options are:
a) Explicitly assign the value to the parameter
Dim qdf As QueryDef
Set qdf = dbEngine(0)(0).QueryDefs("Query1")
qdf.Parameters("[Forms].[Form1].[Text0]") = [Forms].[Form1].[Text0]
qdf.Execute

b) Concatenate the value from the text box into the SQL string, instead
of
using a saved query, e.g.:
Dim strSql As String
strSql = "DELETE FROM Table1 WHERE ID = " & [Forms].[Form1].[Text0] &
";"
dbEngine(0)(0).Execute strSql, dbFailOnError

Personally, I almost always use (b), because this method is so flexible.
You
can examine the text boxes at runtime, ignore any that do not have a
value,
and so the string you build is the most efficient execution possible.
Additionally, the code is independent of a stored query, which means:
- reduced dependencies (able to reuse the code elsewhere without worrying
about what queries it depends on);
- easier maintenance (not having to worry about somebody
altering/removing
the query);
- far fewer saved queries.

But the *main* reason to use Execute rather than RunSQL is that you know
if
it succeeded. With the dbFailOnError switch, you get a trappable error if
anything goes wrong. In 99% of cases, you really need to know if your
action
query succeeded before you go on to the next thing you need to achieve.

Thank you. The method seems to be working except on some Make Table
queries.
Some work but others say:

3061 Too Few Parameters Expected 1.

These queries do not take parameters and work with DoCmd. They are
Action
queries so should work with dbEngine(0)(0).Execute "Query1",
dbFailOnError.
Do you know why the system thinks that it needs parameters supplied ?


:

You can only use the Execute method to execute Action Queries ...
The main benefit is that the Warnings do not need to be turned of ..
so
if
the query encounters an error .. it will be displayed to let the user
know
there was a problem.
And ... the Execute method is faster than the OpenQuery and RunSQL
methods
....

--
Ricky Hicks - Access MVP

Thanks for the help. I have been executing the query with
DoCmd.OpenQuery("Query"). I tried the code below and it worked.
Is
there
anything else I need to now if I convert all of my DoCmd.OpenQuery
commands
to the code listed below ? Do they work exactly the same for all
types
of
queries, i.e. Select, Append, Make table, etc. Also is there any
advantage
other than the fact that it does not cancel the DoCmd.Echo error
message
to
using the code listed below ? Do they both execute at the same
speed ?

:

How are you executing the query?

Try:
dbEngine(0)(0).Execute "Query1", dbFailOnError

During the execution of a program I use the following to display
the
step
the
programs is on the status line:

DoCmd.Echo -1, "Create Actual Data for Branch Ranking Total."

When the program executes a query, the message is covered by the
following:

Running Query

Is there a way to turn off all of these type of messages?
DoCmd.SetWarnings
does not effect them.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top