Run macro if query is not null

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

Guest

Hello
I am using a macro that runs on daily basis to send an email of a certain
query results.

The query results are sometimes empty.
Is it possible to cancel sending the mail , if the query result is null.

Thank you and beat regards
 
Thank you for your reply.

Can you help me write the condition which means, if query result is empty.
Best regards
 
Create a form with your query as record source. Name it TempForm1. Put a
field that will have data - like the key field. Set Visible property to No.

First row of macro Open the form.
Second row - condition - [Forms]![TempForm1]![YourKeyField] Is Not Null
- action - run your query
Third row of macro close TempForm1.
 
Can you help me write the condition which means, if query result is empty.

it would be easier to have the condition dictate when to send the email,
rather than when to not send it. try adding a condition to the macro action
that begins the "send email" process, as

DCount(1,"MyQueryName") > 0

if the number of records in the query is zero, then the action doesn't run.
if your "send email" process is several consecutive macro actions, make sure
that the subsequent actions have an ellipsis (...) in the condition column.

hth
 
Tina, I I hope you are still there and able to read my problem related to
what you wrote on the subject.
I am Karen and have read this subject and think this is similar as what I am
trying to do. So I tried what you wrote but I got an error message :
Miscrosoft Access can't parse the expression: 'DCount(1, "MyQueryName") > 0'
I also tried DCount(1, "MyQueryName")=0
My sequence of macros:
OpenQuery
MsgBox (with the above condition)
.... Cancel Event
StopMacro
I used the expression for MsgBox's condition. My query is prefixed as
uSys... and with [paremeter criteria], I hope this is not the problem.

I want: if the query result is zero row, the MsgBox pops up. Now the
problem is that it doesn't even accept my condition ?
Help please, anybody?
thanks
Karen
 
did you substitute the name of your query in place of "MyQueryName"?


Karen Yeung/Holland said:
Tina, I I hope you are still there and able to read my problem related to
what you wrote on the subject.
I am Karen and have read this subject and think this is similar as what I am
trying to do. So I tried what you wrote but I got an error message :
Miscrosoft Access can't parse the expression: 'DCount(1, "MyQueryName") > 0'
I also tried DCount(1, "MyQueryName")=0
My sequence of macros:
OpenQuery
MsgBox (with the above condition)
... Cancel Event
StopMacro
I used the expression for MsgBox's condition. My query is prefixed as
uSys... and with [paremeter criteria], I hope this is not the problem.

I want: if the query result is zero row, the MsgBox pops up. Now the
problem is that it doesn't even accept my condition ?
Help please, anybody?
thanks
Karen

tina said:
empty.

it would be easier to have the condition dictate when to send the email,
rather than when to not send it. try adding a condition to the macro action
that begins the "send email" process, as

DCount(1,"MyQueryName") > 0

if the number of records in the query is zero, then the action doesn't run.
if your "send email" process is several consecutive macro actions, make sure
that the subsequent actions have an ellipsis (...) in the condition column.

hth
Click to expand...
Click to expand...
 
Yes I did

tina said:
did you substitute the name of your query in place of "MyQueryName"?


Karen Yeung/Holland said:
Tina, I I hope you are still there and able to read my problem related to
what you wrote on the subject.
I am Karen and have read this subject and think this is similar as what I am
trying to do. So I tried what you wrote but I got an error message :
Miscrosoft Access can't parse the expression: 'DCount(1, "MyQueryName") > 0'
I also tried DCount(1, "MyQueryName")=0
My sequence of macros:
OpenQuery
MsgBox (with the above condition)
... Cancel Event
StopMacro
I used the expression for MsgBox's condition. My query is prefixed as
uSys... and with [paremeter criteria], I hope this is not the problem.

I want: if the query result is zero row, the MsgBox pops up. Now the
problem is that it doesn't even accept my condition ?
Help please, anybody?
thanks
Karen

tina said:
Can you help me write the condition which means, if query result is empty.

it would be easier to have the condition dictate when to send the email,
rather than when to not send it. try adding a condition to the macro action
that begins the "send email" process, as

DCount(1,"MyQueryName") > 0

if the number of records in the query is zero, then the action doesn't run.
if your "send email" process is several consecutive macro actions, make sure
that the subsequent actions have an ellipsis (...) in the condition column.

hth


Thank you for your reply.

Can you help me write the condition which means, if query result is empty.
Best regards
--
Bassel


:

In macro design view click on the menu VIEW - Conditions.
Add the condition.

:

Hello
I am using a macro that runs on daily basis to send an email of a
certain
query results.

The query results are sometimes empty.
Is it possible to cancel sending the mail , if the query result is
null.

Thank you and beat regards
Click to expand...
Click to expand...
Click to expand...
 
btw, "> 0" and "= 0" will give you opposite results. you need to figure out
which is the correct equation for what you're trying to do.

other than that, and if you did use the correct query name, perhaps Access
didn't like the DCount(1... in that case, substitute a fieldname that's
included in your query, as

DCount("[MyFieldName]","MyQueryName")

hth


Karen Yeung/Holland said:
Tina, I I hope you are still there and able to read my problem related to
what you wrote on the subject.
I am Karen and have read this subject and think this is similar as what I am
trying to do. So I tried what you wrote but I got an error message :
Miscrosoft Access can't parse the expression: 'DCount(1, "MyQueryName") > 0'
I also tried DCount(1, "MyQueryName")=0
My sequence of macros:
OpenQuery
MsgBox (with the above condition)
... Cancel Event
StopMacro
I used the expression for MsgBox's condition. My query is prefixed as
uSys... and with [paremeter criteria], I hope this is not the problem.

I want: if the query result is zero row, the MsgBox pops up. Now the
problem is that it doesn't even accept my condition ?
Help please, anybody?
thanks
Karen

tina said:
empty.

it would be easier to have the condition dictate when to send the email,
rather than when to not send it. try adding a condition to the macro action
that begins the "send email" process, as

DCount(1,"MyQueryName") > 0

if the number of records in the query is zero, then the action doesn't run.
if your "send email" process is several consecutive macro actions, make sure
that the subsequent actions have an ellipsis (...) in the condition column.

hth
Click to expand...
Click to expand...
 
I understand that, I did = 0 because I want the MsgBox to pop if the query
result is blank, no nothing generated.
I tried to put the fieldname in, the same error message came out. What did I
do wrong again?
karen


tina said:
btw, "> 0" and "= 0" will give you opposite results. you need to figure out
which is the correct equation for what you're trying to do.

other than that, and if you did use the correct query name, perhaps Access
didn't like the DCount(1... in that case, substitute a fieldname that's
included in your query, as

DCount("[MyFieldName]","MyQueryName")

hth


Karen Yeung/Holland said:
Tina, I I hope you are still there and able to read my problem related to
what you wrote on the subject.
I am Karen and have read this subject and think this is similar as what I am
trying to do. So I tried what you wrote but I got an error message :
Miscrosoft Access can't parse the expression: 'DCount(1, "MyQueryName") > 0'
I also tried DCount(1, "MyQueryName")=0
My sequence of macros:
OpenQuery
MsgBox (with the above condition)
... Cancel Event
StopMacro
I used the expression for MsgBox's condition. My query is prefixed as
uSys... and with [paremeter criteria], I hope this is not the problem.

I want: if the query result is zero row, the MsgBox pops up. Now the
problem is that it doesn't even accept my condition ?
Help please, anybody?
thanks
Karen

tina said:
Can you help me write the condition which means, if query result is empty.

it would be easier to have the condition dictate when to send the email,
rather than when to not send it. try adding a condition to the macro action
that begins the "send email" process, as

DCount(1,"MyQueryName") > 0

if the number of records in the query is zero, then the action doesn't run.
if your "send email" process is several consecutive macro actions, make sure
that the subsequent actions have an ellipsis (...) in the condition column.

hth


Thank you for your reply.

Can you help me write the condition which means, if query result is empty.
Best regards
--
Bassel


:

In macro design view click on the menu VIEW - Conditions.
Add the condition.

:

Hello
I am using a macro that runs on daily basis to send an email of a
certain
query results.

The query results are sometimes empty.
Is it possible to cancel sending the mail , if the query result is
null.

Thank you and beat regards
Click to expand...
Click to expand...
Click to expand...
 
DCount("*", "MyQueryName") = 0


--

Ken Snell
<MS ACCESS MVP>

Karen Yeung/Holland said:
Tina, I I hope you are still there and able to read my problem related to
what you wrote on the subject.
I am Karen and have read this subject and think this is similar as what I
am
trying to do. So I tried what you wrote but I got an error message :
Miscrosoft Access can't parse the expression: 'DCount(1, "MyQueryName") >
0'
I also tried DCount(1, "MyQueryName")=0
My sequence of macros:
OpenQuery
MsgBox (with the above condition)
... Cancel Event
StopMacro
I used the expression for MsgBox's condition. My query is prefixed as
uSys... and with [paremeter criteria], I hope this is not the problem.

I want: if the query result is zero row, the MsgBox pops up. Now the
problem is that it doesn't even accept my condition ?
Help please, anybody?
thanks
Karen

tina said:
it would be easier to have the condition dictate when to send the email,
rather than when to not send it. try adding a condition to the macro
action
that begins the "send email" process, as

DCount(1,"MyQueryName") > 0

if the number of records in the query is zero, then the action doesn't
run.
if your "send email" process is several consecutive macro actions, make
sure
that the subsequent actions have an ellipsis (...) in the condition
column.

hth
Click to expand...
Click to expand...
 
copy the expression from the macro's Condition column, and paste it here, so
we can look at it. (also, double check that you are spelling the name of the
query - and the field - correctly.)


KarenY said:
I understand that, I did = 0 because I want the MsgBox to pop if the query
result is blank, no nothing generated.
I tried to put the fieldname in, the same error message came out. What did I
do wrong again?
karen


tina said:
btw, "> 0" and "= 0" will give you opposite results. you need to figure out
which is the correct equation for what you're trying to do.

other than that, and if you did use the correct query name, perhaps Access
didn't like the DCount(1... in that case, substitute a fieldname that's
included in your query, as

DCount("[MyFieldName]","MyQueryName")

hth


message news:[email protected]...
Tina, I I hope you are still there and able to read my problem related to
what you wrote on the subject.
I am Karen and have read this subject and think this is similar as
Click to expand...
what I
am
trying to do. So I tried what you wrote but I got an error message :
Miscrosoft Access can't parse the expression: 'DCount(1,
Click to expand...
"MyQueryName") >
0'
I also tried DCount(1, "MyQueryName")=0
My sequence of macros:
OpenQuery
MsgBox (with the above condition)
... Cancel Event
StopMacro
I used the expression for MsgBox's condition. My query is prefixed as
uSys... and with [paremeter criteria], I hope this is not the problem.

I want: if the query result is zero row, the MsgBox pops up. Now the
problem is that it doesn't even accept my condition ?
Help please, anybody?
thanks
Karen

:

Can you help me write the condition which means, if query result
Click to expand...
is
empty.
it would be easier to have the condition dictate when to send the email,
rather than when to not send it. try adding a condition to the macro action
that begins the "send email" process, as

DCount(1,"MyQueryName") > 0

if the number of records in the query is zero, then the action
Click to expand...
doesn't
run.
if your "send email" process is several consecutive macro actions,
Click to expand...
make
sure
that the subsequent actions have an ellipsis (...) in the condition column.

hth


Thank you for your reply.

Can you help me write the condition which means, if query result
Click to expand...
is
empty.
Best regards
--
Bassel


:

In macro design view click on the menu VIEW - Conditions.
Add the condition.

:

Hello
I am using a macro that runs on daily basis to send an email of a
certain
query results.

The query results are sometimes empty.
Is it possible to cancel sending the mail , if the query result is
null.

Thank you and beat regards
Click to expand...
Click to expand...
Click to expand...
 
DCount("*","Qry05userChkWkEntry") = 0
and I also tried
DCount("[TT]","Qry05userChkWkEntry") = 0

Both didn't let me go through, same error msg.
Karen

Ken Snell said:
DCount("*", "MyQueryName") = 0


--

Ken Snell
<MS ACCESS MVP>

Karen Yeung/Holland said:
Tina, I I hope you are still there and able to read my problem related to
what you wrote on the subject.
I am Karen and have read this subject and think this is similar as what I
am
trying to do. So I tried what you wrote but I got an error message :
Miscrosoft Access can't parse the expression: 'DCount(1, "MyQueryName") >
0'
I also tried DCount(1, "MyQueryName")=0
My sequence of macros:
OpenQuery
MsgBox (with the above condition)
... Cancel Event
StopMacro
I used the expression for MsgBox's condition. My query is prefixed as
uSys... and with [paremeter criteria], I hope this is not the problem.

I want: if the query result is zero row, the MsgBox pops up. Now the
problem is that it doesn't even accept my condition ?
Help please, anybody?
thanks
Karen

tina said:
Can you help me write the condition which means, if query result is
empty.

it would be easier to have the condition dictate when to send the email,
rather than when to not send it. try adding a condition to the macro
action
that begins the "send email" process, as

DCount(1,"MyQueryName") > 0

if the number of records in the query is zero, then the action doesn't
run.
if your "send email" process is several consecutive macro actions, make
sure
that the subsequent actions have an ellipsis (...) in the condition
column.

hth


Thank you for your reply.

Can you help me write the condition which means, if query result is
empty.
Best regards
--
Bassel


:

In macro design view click on the menu VIEW - Conditions.
Add the condition.

:

Hello
I am using a macro that runs on daily basis to send an email of a
certain
query results.

The query results are sometimes empty.
Is it possible to cancel sending the mail , if the query result is
null.

Thank you and beat regards
Click to expand...
Click to expand...
Click to expand...
 
btw, in order not to give confusion, I did mention in one of my previous
messages using prefixed Usys for my qry, I changed that already because I
thought that was the cause, so my qry now is without Usys-prefix. thanks,
Karen

KarenY said:
DCount("*","Qry05userChkWkEntry") = 0
and I also tried
DCount("[TT]","Qry05userChkWkEntry") = 0

Both didn't let me go through, same error msg.
Karen

Ken Snell said:
DCount("*", "MyQueryName") = 0


--

Ken Snell
<MS ACCESS MVP>

Karen Yeung/Holland said:
Tina, I I hope you are still there and able to read my problem related to
what you wrote on the subject.
I am Karen and have read this subject and think this is similar as what I
am
trying to do. So I tried what you wrote but I got an error message :
Miscrosoft Access can't parse the expression: 'DCount(1, "MyQueryName") >
0'
I also tried DCount(1, "MyQueryName")=0
My sequence of macros:
OpenQuery
MsgBox (with the above condition)
... Cancel Event
StopMacro
I used the expression for MsgBox's condition. My query is prefixed as
uSys... and with [paremeter criteria], I hope this is not the problem.

I want: if the query result is zero row, the MsgBox pops up. Now the
problem is that it doesn't even accept my condition ?
Help please, anybody?
thanks
Karen

:

Can you help me write the condition which means, if query result is
empty.

it would be easier to have the condition dictate when to send the email,
rather than when to not send it. try adding a condition to the macro
action
that begins the "send email" process, as

DCount(1,"MyQueryName") > 0

if the number of records in the query is zero, then the action doesn't
run.
if your "send email" process is several consecutive macro actions, make
sure
that the subsequent actions have an ellipsis (...) in the condition
column.

hth


Thank you for your reply.

Can you help me write the condition which means, if query result is
empty.
Best regards
--
Bassel


:

In macro design view click on the menu VIEW - Conditions.
Add the condition.

:

Hello
I am using a macro that runs on daily basis to send an email of a
certain
query results.

The query results are sometimes empty.
Is it possible to cancel sending the mail , if the query result is
null.

Thank you and beat regards
Click to expand...
Click to expand...
Click to expand...
 
is your query an "ordinary" query, that returns a set of records? or is it
an Action query, such as Update, Delete, Append, Make-Table?


KarenY said:
btw, in order not to give confusion, I did mention in one of my previous
messages using prefixed Usys for my qry, I changed that already because I
thought that was the cause, so my qry now is without Usys-prefix. thanks,
Karen

KarenY said:
DCount("*","Qry05userChkWkEntry") = 0
and I also tried
DCount("[TT]","Qry05userChkWkEntry") = 0

Both didn't let me go through, same error msg.
Karen

Ken Snell said:
DCount("*", "MyQueryName") = 0


--

Ken Snell
<MS ACCESS MVP>

message Tina, I I hope you are still there and able to read my problem related to
what you wrote on the subject.
I am Karen and have read this subject and think this is similar as what I
am
trying to do. So I tried what you wrote but I got an error message :
Miscrosoft Access can't parse the expression: 'DCount(1, "MyQueryName") >
0'
I also tried DCount(1, "MyQueryName")=0
My sequence of macros:
OpenQuery
MsgBox (with the above condition)
... Cancel Event
StopMacro
I used the expression for MsgBox's condition. My query is prefixed as
uSys... and with [paremeter criteria], I hope this is not the problem.

I want: if the query result is zero row, the MsgBox pops up. Now the
problem is that it doesn't even accept my condition ?
Help please, anybody?
thanks
Karen

:

Can you help me write the condition which means, if query result is
empty.

it would be easier to have the condition dictate when to send the email,
rather than when to not send it. try adding a condition to the macro
action
that begins the "send email" process, as

DCount(1,"MyQueryName") > 0

if the number of records in the query is zero, then the action doesn't
run.
if your "send email" process is several consecutive macro actions, make
sure
that the subsequent actions have an ellipsis (...) in the condition
column.

hth


Thank you for your reply.

Can you help me write the condition which means, if query result is
empty.
Best regards
--
Bassel


:

In macro design view click on the menu VIEW - Conditions.
Add the condition.

:

Hello
I am using a macro that runs on daily basis to send an email of a
certain
query results.

The query results are sometimes empty.
Is it possible to cancel sending the mail , if the query result is
null.

Thank you and beat regards
Click to expand...
Click to expand...
Click to expand...
 
Myy query is an ordinary query (select) but with 2 criteria (I put down my
real criteria) [Enter your ID No:] with field "employeeID" and [Enter
WeekNo:] with field "Week". I want to do: when there is no record for that
employee in that week, no message pops up and if there is/are record, the
message will pop up to ask him to proceed the action further...
I am sorry to take up so much of your time, I was hoping it wouldn't be that
difficult.
thanks
karen

tina said:
is your query an "ordinary" query, that returns a set of records? or is it
an Action query, such as Update, Delete, Append, Make-Table?


KarenY said:
btw, in order not to give confusion, I did mention in one of my previous
messages using prefixed Usys for my qry, I changed that already because I
thought that was the cause, so my qry now is without Usys-prefix. thanks,
Karen

KarenY said:
DCount("*","Qry05userChkWkEntry") = 0
and I also tried
DCount("[TT]","Qry05userChkWkEntry") = 0

Both didn't let me go through, same error msg.
Karen

:

DCount("*", "MyQueryName") = 0


--

Ken Snell
<MS ACCESS MVP>

message Tina, I I hope you are still there and able to read my problem related to
what you wrote on the subject.
I am Karen and have read this subject and think this is similar as what I
am
trying to do. So I tried what you wrote but I got an error message :
Miscrosoft Access can't parse the expression: 'DCount(1, "MyQueryName") >
0'
I also tried DCount(1, "MyQueryName")=0
My sequence of macros:
OpenQuery
MsgBox (with the above condition)
... Cancel Event
StopMacro
I used the expression for MsgBox's condition. My query is prefixed as
uSys... and with [paremeter criteria], I hope this is not the problem.

I want: if the query result is zero row, the MsgBox pops up. Now the
problem is that it doesn't even accept my condition ?
Help please, anybody?
thanks
Karen

:

Can you help me write the condition which means, if query result is
empty.

it would be easier to have the condition dictate when to send the email,
rather than when to not send it. try adding a condition to the macro
action
that begins the "send email" process, as

DCount(1,"MyQueryName") > 0

if the number of records in the query is zero, then the action doesn't
run.
if your "send email" process is several consecutive macro actions, make
sure
that the subsequent actions have an ellipsis (...) in the condition
column.

hth


Thank you for your reply.

Can you help me write the condition which means, if query result is
empty.
Best regards
--
Bassel


:

In macro design view click on the menu VIEW - Conditions.
Add the condition.

:

Hello
I am using a macro that runs on daily basis to send an email of a
certain
query results.

The query results are sometimes empty.
Is it possible to cancel sending the mail , if the query result is
null.

Thank you and beat regards
Click to expand...
Click to expand...
Click to expand...
 
I don't believe that the DCount function will do what you want with a
parameter query, as it won't ask you for the parameters' values in order to
run itself. I just tested a simple setup in a database here, and the DCount
condition did not cause the macro to post a message box for me (and it
didn't ask me for the parameters)...it also didn't throw an error at me.

You'll need to use a form for the user to put the ID number and the week
number into textboxes, and then let the query read the values from the form
instead of asking for the parameters. That way, the DCount function should
work just fine for you.

--

Ken Snell
<MS ACCESS MVP>



KarenY said:
Myy query is an ordinary query (select) but with 2 criteria (I put down my
real criteria) [Enter your ID No:] with field "employeeID" and [Enter
WeekNo:] with field "Week". I want to do: when there is no record for
that
employee in that week, no message pops up and if there is/are record, the
message will pop up to ask him to proceed the action further...
I am sorry to take up so much of your time, I was hoping it wouldn't be
that
difficult.
thanks
karen

tina said:
is your query an "ordinary" query, that returns a set of records? or is
it
an Action query, such as Update, Delete, Append, Make-Table?


KarenY said:
btw, in order not to give confusion, I did mention in one of my
previous
messages using prefixed Usys for my qry, I changed that already because
I
thought that was the cause, so my qry now is without Usys-prefix.
thanks,
Karen

:

DCount("*","Qry05userChkWkEntry") = 0
and I also tried
DCount("[TT]","Qry05userChkWkEntry") = 0

Both didn't let me go through, same error msg.
Karen

:

DCount("*", "MyQueryName") = 0


--

Ken Snell
<MS ACCESS MVP>

message Tina, I I hope you are still there and able to read my problem related to
what you wrote on the subject.
I am Karen and have read this subject and think this is similar
as what I
am
trying to do. So I tried what you wrote but I got an error
message :
Miscrosoft Access can't parse the expression: 'DCount(1, "MyQueryName") >
0'
I also tried DCount(1, "MyQueryName")=0
My sequence of macros:
OpenQuery
MsgBox (with the above condition)
... Cancel Event
StopMacro
I used the expression for MsgBox's condition. My query is
prefixed as
uSys... and with [paremeter criteria], I hope this is not the problem.

I want: if the query result is zero row, the MsgBox pops up. Now the
problem is that it doesn't even accept my condition ?
Help please, anybody?
thanks
Karen

:

Can you help me write the condition which means, if query
result is
empty.

it would be easier to have the condition dictate when to send
the email,
rather than when to not send it. try adding a condition to the macro
action
that begins the "send email" process, as

DCount(1,"MyQueryName") > 0

if the number of records in the query is zero, then the action doesn't
run.
if your "send email" process is several consecutive macro
actions, make
sure
that the subsequent actions have an ellipsis (...) in the
condition
column.

hth


Thank you for your reply.

Can you help me write the condition which means, if query
result is
empty.
Best regards
--
Bassel


:

In macro design view click on the menu VIEW - Conditions.
Add the condition.

:

Hello
I am using a macro that runs on daily basis to send an
email of a
certain
query results.

The query results are sometimes empty.
Is it possible to cancel sending the mail , if the query result is
null.

Thank you and beat regards
Click to expand...
Click to expand...
Click to expand...
 
Thanks, Ken.
I am sorry, I don't understand exactly how to work on the form you
suggested. Would you mind detail the procedures for me?

Let me explain what I was doing : I have a form for employees to fill out
his own week time-records and there is a CmdBut on this "form". After
filling out his own records, he has to submit his records. All those
submitted records were in a table which consists of all the IDs' records, if
the user is not sure about the records been submitted, he then click this
CmdBut by keying in his own ID and the weekno. to view his own records for
the week. These records are pulled out by this query through those
parameters. That's why I want to add a MsgBox to add to the Macro for that
CmdBut and if there is no record shown on the query, the MsgBox will tell the
user to proceed. If the query data-sheet shows the record, no MsgBox....etc

Because of what your saying about the DCount not working in a parameter
query, I've just created a form by using the same query.
Same thing, DCount doesn't let me go through the macro setup, i.e. I can't
even save the macro with DCount...
So I put the condition for the Macro with MsgBox as follows:

[Forms]![05frmChkWkEntry]![TT] Is Null

the parameters did come up for ID and Week, then the form comes up with or
without records for the input-week, but no msgbox comes up despite records
shown or not.

So If msgbox does not work with parameter criteria, I think I'd better drop
the idea what I want and I'll let msg box pop up in any case and I'll just
change the Text in the msgbox. (It took too much valuable time of you guys,
sorry.)

Ken, while I just finished the above form, I've discovered another problem
of the sequence of the field. I want to sort the form to display by date, so
I put in the property of that form with ON ORDER : [Date] ASC
but it didn't work.

Again, I would appreciate for all the help you guys offered.

thanks
karen

Ken Snell said:
I don't believe that the DCount function will do what you want with a
parameter query, as it won't ask you for the parameters' values in order to
run itself. I just tested a simple setup in a database here, and the DCount
condition did not cause the macro to post a message box for me (and it
didn't ask me for the parameters)...it also didn't throw an error at me.

You'll need to use a form for the user to put the ID number and the week
number into textboxes, and then let the query read the values from the form
instead of asking for the parameters. That way, the DCount function should
work just fine for you.

--

Ken Snell
<MS ACCESS MVP>



KarenY said:
Myy query is an ordinary query (select) but with 2 criteria (I put down my
real criteria) [Enter your ID No:] with field "employeeID" and [Enter
WeekNo:] with field "Week". I want to do: when there is no record for
that
employee in that week, no message pops up and if there is/are record, the
message will pop up to ask him to proceed the action further...
I am sorry to take up so much of your time, I was hoping it wouldn't be
that
difficult.
thanks
karen

tina said:
is your query an "ordinary" query, that returns a set of records? or is
it
an Action query, such as Update, Delete, Append, Make-Table?


btw, in order not to give confusion, I did mention in one of my
previous
messages using prefixed Usys for my qry, I changed that already because
I
thought that was the cause, so my qry now is without Usys-prefix.
thanks,
Karen

:

DCount("*","Qry05userChkWkEntry") = 0
and I also tried
DCount("[TT]","Qry05userChkWkEntry") = 0

Both didn't let me go through, same error msg.
Karen

:

DCount("*", "MyQueryName") = 0


--

Ken Snell
<MS ACCESS MVP>

"Karen Yeung/Holland" <[email protected]>
wrote in
message Tina, I I hope you are still there and able to read my problem
related to
what you wrote on the subject.
I am Karen and have read this subject and think this is similar
as
what I
am
trying to do. So I tried what you wrote but I got an error
message :
Miscrosoft Access can't parse the expression: 'DCount(1,
"MyQueryName") >
0'
I also tried DCount(1, "MyQueryName")=0
My sequence of macros:
OpenQuery
MsgBox (with the above condition)
... Cancel Event
StopMacro
I used the expression for MsgBox's condition. My query is
prefixed
as
uSys... and with [paremeter criteria], I hope this is not the
problem.

I want: if the query result is zero row, the MsgBox pops up. Now
the
problem is that it doesn't even accept my condition ?
Help please, anybody?
thanks
Karen

:

Can you help me write the condition which means, if query
result
is
empty.

it would be easier to have the condition dictate when to send
the
email,
rather than when to not send it. try adding a condition to the
macro
action
that begins the "send email" process, as

DCount(1,"MyQueryName") > 0

if the number of records in the query is zero, then the action
doesn't
run.
if your "send email" process is several consecutive macro
actions,
make
sure
that the subsequent actions have an ellipsis (...) in the
condition
column.

hth


Thank you for your reply.

Can you help me write the condition which means, if query
result
is
empty.
Best regards
--
Bassel


:

In macro design view click on the menu VIEW - Conditions.
Add the condition.

:

Hello
I am using a macro that runs on daily basis to send an
email
of a
certain
query results.

The query results are sometimes empty.
Is it possible to cancel sending the mail , if the query
result is
null.

Thank you and beat regards
Click to expand...
Click to expand...
Click to expand...
 
Let's confirm that you've put the DCount expression in the column named
"Condition" in the macro's design view? If you have done that, and you still
get the "parsing" error for the DCount function, then there is something
wrong with your database file. I've never seen this error before, so I could
only guess as to its cause -- but it might be the references in the
database. Confirm (as tina asked before) that you have the expression in the
correct place.

As for using a textbox on a form as the source of a parameter's value, where
you had a parameter such as [Enter your ID:] in the query, replace it with a
reference to the form's textbox:
[Forms]![NameOfYourForm]![NameOfYourTextbox]

The form must be open when the query runs.
--

Ken Snell
<MS ACCESS MVP>



KarenY said:
Thanks, Ken.
I am sorry, I don't understand exactly how to work on the form you
suggested. Would you mind detail the procedures for me?

Let me explain what I was doing : I have a form for employees to fill out
his own week time-records and there is a CmdBut on this "form". After
filling out his own records, he has to submit his records. All those
submitted records were in a table which consists of all the IDs' records,
if
the user is not sure about the records been submitted, he then click this
CmdBut by keying in his own ID and the weekno. to view his own records for
the week. These records are pulled out by this query through those
parameters. That's why I want to add a MsgBox to add to the Macro for
that
CmdBut and if there is no record shown on the query, the MsgBox will tell
the
user to proceed. If the query data-sheet shows the record, no
MsgBox....etc

Because of what your saying about the DCount not working in a parameter
query, I've just created a form by using the same query.
Same thing, DCount doesn't let me go through the macro setup, i.e. I can't
even save the macro with DCount...
So I put the condition for the Macro with MsgBox as follows:

[Forms]![05frmChkWkEntry]![TT] Is Null

the parameters did come up for ID and Week, then the form comes up with or
without records for the input-week, but no msgbox comes up despite records
shown or not.

So If msgbox does not work with parameter criteria, I think I'd better
drop
the idea what I want and I'll let msg box pop up in any case and I'll just
change the Text in the msgbox. (It took too much valuable time of you
guys,
sorry.)

Ken, while I just finished the above form, I've discovered another problem
of the sequence of the field. I want to sort the form to display by date,
so
I put in the property of that form with ON ORDER : [Date] ASC
but it didn't work.

Again, I would appreciate for all the help you guys offered.

thanks
karen

Ken Snell said:
I don't believe that the DCount function will do what you want with a
parameter query, as it won't ask you for the parameters' values in order
to
run itself. I just tested a simple setup in a database here, and the
DCount
condition did not cause the macro to post a message box for me (and it
didn't ask me for the parameters)...it also didn't throw an error at me.

You'll need to use a form for the user to put the ID number and the week
number into textboxes, and then let the query read the values from the
form
instead of asking for the parameters. That way, the DCount function
should
work just fine for you.

--

Ken Snell
<MS ACCESS MVP>



KarenY said:
Myy query is an ordinary query (select) but with 2 criteria (I put down
my
real criteria) [Enter your ID No:] with field "employeeID" and [Enter
WeekNo:] with field "Week". I want to do: when there is no record for
that
employee in that week, no message pops up and if there is/are record,
the
message will pop up to ask him to proceed the action further...
I am sorry to take up so much of your time, I was hoping it wouldn't be
that
difficult.
thanks
karen

:

is your query an "ordinary" query, that returns a set of records? or
is
it
an Action query, such as Update, Delete, Append, Make-Table?


btw, in order not to give confusion, I did mention in one of my
previous
messages using prefixed Usys for my qry, I changed that already
because
I
thought that was the cause, so my qry now is without Usys-prefix.
thanks,
Karen

:

DCount("*","Qry05userChkWkEntry") = 0
and I also tried
DCount("[TT]","Qry05userChkWkEntry") = 0

Both didn't let me go through, same error msg.
Karen

:

DCount("*", "MyQueryName") = 0


--

Ken Snell
<MS ACCESS MVP>

"Karen Yeung/Holland"
<[email protected]>
wrote in
message
Tina, I I hope you are still there and able to read my problem
related to
what you wrote on the subject.
I am Karen and have read this subject and think this is
similar
as
what I
am
trying to do. So I tried what you wrote but I got an error
message :
Miscrosoft Access can't parse the expression: 'DCount(1,
"MyQueryName") >
0'
I also tried DCount(1, "MyQueryName")=0
My sequence of macros:
OpenQuery
MsgBox (with the above condition)
... Cancel Event
StopMacro
I used the expression for MsgBox's condition. My query is
prefixed
as
uSys... and with [paremeter criteria], I hope this is not the
problem.

I want: if the query result is zero row, the MsgBox pops up.
Now
the
problem is that it doesn't even accept my condition ?
Help please, anybody?
thanks
Karen

:

Can you help me write the condition which means, if query
result
is
empty.

it would be easier to have the condition dictate when to send
the
email,
rather than when to not send it. try adding a condition to
the
macro
action
that begins the "send email" process, as

DCount(1,"MyQueryName") > 0

if the number of records in the query is zero, then the
action
doesn't
run.
if your "send email" process is several consecutive macro
actions,
make
sure
that the subsequent actions have an ellipsis (...) in the
condition
column.

hth


Thank you for your reply.

Can you help me write the condition which means, if query
result
is
empty.
Best regards
--
Bassel


:

In macro design view click on the menu VIEW - Conditions.
Add the condition.

:

Hello
I am using a macro that runs on daily basis to send an
email
of a
certain
query results.

The query results are sometimes empty.
Is it possible to cancel sending the mail , if the
query
result is
null.

Thank you and beat regards
Click to expand...
Click to expand...
Click to expand...
 
I am completely lost now. I did study what you suggest and laid out for the
last couple of hours and I couldn't get it. Perhaps like you said, there is
something wrong in my DB, I am confused now, I guess I have to stop now, it's
almost 12 midnight. I will try to figure out tomorrow with a fresh mind...
till later, thanks again
Karen

Ken Snell said:
Let's confirm that you've put the DCount expression in the column named
"Condition" in the macro's design view? If you have done that, and you still
get the "parsing" error for the DCount function, then there is something
wrong with your database file. I've never seen this error before, so I could
only guess as to its cause -- but it might be the references in the
database. Confirm (as tina asked before) that you have the expression in the
correct place.

As for using a textbox on a form as the source of a parameter's value, where
you had a parameter such as [Enter your ID:] in the query, replace it with a
reference to the form's textbox:
[Forms]![NameOfYourForm]![NameOfYourTextbox]

The form must be open when the query runs.
--

Ken Snell
<MS ACCESS MVP>



KarenY said:
Thanks, Ken.
I am sorry, I don't understand exactly how to work on the form you
suggested. Would you mind detail the procedures for me?

Let me explain what I was doing : I have a form for employees to fill out
his own week time-records and there is a CmdBut on this "form". After
filling out his own records, he has to submit his records. All those
submitted records were in a table which consists of all the IDs' records,
if
the user is not sure about the records been submitted, he then click this
CmdBut by keying in his own ID and the weekno. to view his own records for
the week. These records are pulled out by this query through those
parameters. That's why I want to add a MsgBox to add to the Macro for
that
CmdBut and if there is no record shown on the query, the MsgBox will tell
the
user to proceed. If the query data-sheet shows the record, no
MsgBox....etc

Because of what your saying about the DCount not working in a parameter
query, I've just created a form by using the same query.
Same thing, DCount doesn't let me go through the macro setup, i.e. I can't
even save the macro with DCount...
So I put the condition for the Macro with MsgBox as follows:

[Forms]![05frmChkWkEntry]![TT] Is Null

the parameters did come up for ID and Week, then the form comes up with or
without records for the input-week, but no msgbox comes up despite records
shown or not.

So If msgbox does not work with parameter criteria, I think I'd better
drop
the idea what I want and I'll let msg box pop up in any case and I'll just
change the Text in the msgbox. (It took too much valuable time of you
guys,
sorry.)

Ken, while I just finished the above form, I've discovered another problem
of the sequence of the field. I want to sort the form to display by date,
so
I put in the property of that form with ON ORDER : [Date] ASC
but it didn't work.

Again, I would appreciate for all the help you guys offered.

thanks
karen

Ken Snell said:
I don't believe that the DCount function will do what you want with a
parameter query, as it won't ask you for the parameters' values in order
to
run itself. I just tested a simple setup in a database here, and the
DCount
condition did not cause the macro to post a message box for me (and it
didn't ask me for the parameters)...it also didn't throw an error at me.

You'll need to use a form for the user to put the ID number and the week
number into textboxes, and then let the query read the values from the
form
instead of asking for the parameters. That way, the DCount function
should
work just fine for you.

--

Ken Snell
<MS ACCESS MVP>



Myy query is an ordinary query (select) but with 2 criteria (I put down
my
real criteria) [Enter your ID No:] with field "employeeID" and [Enter
WeekNo:] with field "Week". I want to do: when there is no record for
that
employee in that week, no message pops up and if there is/are record,
the
message will pop up to ask him to proceed the action further...
I am sorry to take up so much of your time, I was hoping it wouldn't be
that
difficult.
thanks
karen

:

is your query an "ordinary" query, that returns a set of records? or
is
it
an Action query, such as Update, Delete, Append, Make-Table?


btw, in order not to give confusion, I did mention in one of my
previous
messages using prefixed Usys for my qry, I changed that already
because
I
thought that was the cause, so my qry now is without Usys-prefix.
thanks,
Karen

:

DCount("*","Qry05userChkWkEntry") = 0
and I also tried
DCount("[TT]","Qry05userChkWkEntry") = 0

Both didn't let me go through, same error msg.
Karen

:

DCount("*", "MyQueryName") = 0


--

Ken Snell
<MS ACCESS MVP>

"Karen Yeung/Holland"
<[email protected]>
wrote in
message
Tina, I I hope you are still there and able to read my problem
related to
what you wrote on the subject.
I am Karen and have read this subject and think this is
similar
as
what I
am
trying to do. So I tried what you wrote but I got an error
message :
Miscrosoft Access can't parse the expression: 'DCount(1,
"MyQueryName") >
0'
I also tried DCount(1, "MyQueryName")=0
My sequence of macros:
OpenQuery
MsgBox (with the above condition)
... Cancel Event
StopMacro
I used the expression for MsgBox's condition. My query is
prefixed
as
uSys... and with [paremeter criteria], I hope this is not the
problem.

I want: if the query result is zero row, the MsgBox pops up.
Now
the
problem is that it doesn't even accept my condition ?
Help please, anybody?
thanks
Karen

:

Can you help me write the condition which means, if query
result
is
empty.

it would be easier to have the condition dictate when to send
the
email,
rather than when to not send it. try adding a condition to
the
macro
action
that begins the "send email" process, as

DCount(1,"MyQueryName") > 0

if the number of records in the query is zero, then the
action
doesn't
run.
if your "send email" process is several consecutive macro
actions,
make
sure
that the subsequent actions have an ellipsis (...) in the
condition
column.

hth


Thank you for your reply.

Can you help me write the condition which means, if query
result
is
empty.
Best regards
--
Bassel


:

In macro design view click on the menu VIEW - Conditions.
Add the condition.

:

Hello
I am using a macro that runs on daily basis to send an
email
of a
certain
query results.

The query results are sometimes empty.
Is it possible to cancel sending the mail , if the
query
result is
null.

Thank you and beat regards
Click to expand...
Click to expand...
Click to expand...
 
Back
Top