running a macro past a condition

[Guest]

I have a macro that runs a series of actions to update table data from
external csv text files. There is a point at which we need the macro to check
for errors. A make table query dumps any errors into a table.

You folks here were kind enough to introduce me to the DCOUNT condition to
check to see if the recordset of the made table is zero (no errors). The
worked beautifully.

The problem now is that the macro stops at the condition. No matter which
condition I use in the syntax (there are errors, or there are not...) the
macro seems to halt right there and go no further, although there are actions
that follow.

What I really need is more of an "If-Then-Else" kind of condition.
Specifically (although written here in rather informal syntax, of course):

If ... the table's recordset is not null ...
"DCount("*","tblAttendanceRollingErrors")>0" Then
...the macro stops and the table opens so folks can see the errors
Else ... if the table's recordset is null, meaning there are no errors
....
"DCount("*","tblAttendanceRollingErrors")=0"
...the macro continues to execute the remainder of its actions.

I hope this makes sense!

Thanks in advance for any advice or suggestions.

 

[Jeff Boyce]

Since you've already brought it up, what about the idea of doing this in a
code procedure instead of a macro? That way, you could use the If ... Then
statement.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Thank you for your reply, Jeff.

Please forgive my ignorance, but, exactly where and how should I write the
code procedure, and then do you mean for me to call the procedure from within
the macro and then have the procedure somehow return to the macro so as to
have it then continue to its completion?

Again, thanks for the help.
--
BJM
ACE Assistant
Gary Allan High School

Since you've already brought it up, what about the idea of doing this in a
code procedure instead of a macro? That way, you could use the If ... Then
statement.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ken Snell \(MVP\)]

You use three dots in the Condition column to indicate the actions that are
part of the "True" result of the Condition test expression. So, if you
wanted to run three actions based on a true result from a Condition
expression, you put the expression in the Condition column of the first
action, and then put three dots (...) in the Condition column for the second
and third actions.

 

[Guest]

Good grief, is it that simple!??! I'm embarrassed.

I have linked the first condition's five actions with a series of ellipses.
The condition returning true seems to execute fine now.

But, then, if I follow that condition's series of actions in the macro's
next row with another condition and the action I want to execute if it
returns a value of true, in the event the first condition returns false, will
that work?

To clarify (I hope!!!) the two conditions are DCOUNT functions, one, ...=0,
to check if a table's recordset is null, in which case there are no errors
found and I want the five actions to execute, and the second, ...>0, to check
if ithat same table's recordset is not null, in which case there are errors
so I need users to be alerted to the fact with a different action.

My problem now is that I can't really "fake" an error to test that if the
first condition returns false but the second condition returns true that the
second condition's action will execute. I guess what I need confirmed is that
it is okay to have more than one condition within the same macro.

--
BJM
ACE Assistant
Gary Allan High School

You use three dots in the Condition column to indicate the actions that are
part of the "True" result of the Condition test expression. So, if you
wanted to run three actions based on a true result from a Condition
expression, you put the expression in the Condition column of the first
action, and then put three dots (...) in the Condition column for the second
and third actions.

 

[Jeff Boyce]

I like Ken's approach better (can you tell I tend to use VBA instead of
macros?<g>).

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thank you for your reply, Jeff.

Please forgive my ignorance, but, exactly where and how should I write the
code procedure, and then do you mean for me to call the procedure from
within
the macro and then have the procedure somehow return to the macro so as to
have it then continue to its completion?

Again, thanks for the help.

 

[Ken Snell \(MVP\)]

When ACCESS gets to an action without the ellipses (meaning you have a new
Condition expression or the Condition column is blank), it continues with
that action if the Condition is true.

This ellipses feature works a bit like an If .. Then block, with the first
action containing the Condition being the If . Then part, and that first
action being the first step within the block, and the last action with ...
in Condition column "within that block" being the last action inside the If
.. Then block.

Or, this macro setup

Condition: Date() > DateSerial(2007,1,1)
Action: MsgBox
Message: "First Msg"
Condition: ...
Action: MsgBox
Message: "Last Msg"
Condition: < empty >
Action: MsgBox
Message: "New Msg"


would be like this VBA block:

If Date() > DateSerial(2007,1,1) Then
MsgBox "First Msg"
MsgBox "Last Msg"
End If
MsgBox "New Msg"

--

Ken Snell
<MS ACCESS MVP>

Good grief, is it that simple!??! I'm embarrassed.

I have linked the first condition's five actions with a series of
ellipses.
The condition returning true seems to execute fine now.

But, then, if I follow that condition's series of actions in the macro's
next row with another condition and the action I want to execute if it
returns a value of true, in the event the first condition returns false,
will
that work?

To clarify (I hope!!!) the two conditions are DCOUNT functions, one,
...=0,
to check if a table's recordset is null, in which case there are no errors
found and I want the five actions to execute, and the second, ...>0, to
check
if ithat same table's recordset is not null, in which case there are
errors
so I need users to be alerted to the fact with a different action.

My problem now is that I can't really "fake" an error to test that if the
first condition returns false but the second condition returns true that
the
second condition's action will execute. I guess what I need confirmed is
that
it is okay to have more than one condition within the same macro.

 

[Ken Snell \(MVP\)]

In a macro, if you want one set of actions to run if the Condition test is
True, and another set of actions to run if the Condition test is False, you
actually must test both "sides" of the condition as separate blocks:

Condition: Date() > DateSerial(2007,1,1)
Action: < action here when above is true >
Condition: ...
Action: < next action here when above is true>
Condition: Date() <= DateSerial(2007,1,1)
Action: < action here when above is true >
Condition: ...
Action: < next action here when above is true>

--

Ken Snell
<MS ACCESS MVP>

Good grief, is it that simple!??! I'm embarrassed.

I have linked the first condition's five actions with a series of
ellipses.
The condition returning true seems to execute fine now.

But, then, if I follow that condition's series of actions in the macro's
next row with another condition and the action I want to execute if it
returns a value of true, in the event the first condition returns false,
will
that work?

To clarify (I hope!!!) the two conditions are DCOUNT functions, one,
...=0,
to check if a table's recordset is null, in which case there are no errors
found and I want the five actions to execute, and the second, ...>0, to
check
if ithat same table's recordset is not null, in which case there are
errors
so I need users to be alerted to the fact with a different action.

My problem now is that I can't really "fake" an error to test that if the
first condition returns false but the second condition returns true that
the
second condition's action will execute. I guess what I need confirmed is
that
it is okay to have more than one condition within the same macro.

 

[Guest]

In a macro, if you want one set of actions to run if the Condition test is
True, and another set of actions to run if the Condition test is False, you
actually must test both "sides" of the condition as separate blocks:

Condition: Date() > DateSerial(2007,1,1)
Action: < action here when above is true >
Condition: ...
Action: < next action here when above is true>
Condition: Date() <= DateSerial(2007,1,1)
Action: < action here when above is true >
Condition: ...
Action: < next action here when above is true>

Which, in fact, with your generous help is just exactly what I've been able
to do.

I can't tell you what a Godsend these discussion groups have been to me over
the past four years, and will continue to be, I'm sure!