Run Macro with Iff Statement

  • Thread starter Thread starter Steve
  • Start date Start date
Graham,
Thanks for all your help , much appricated
Regards
Steve
--
Thanks from Down Under


Graham R Seach said:
Steve,

No, you don't need it, unless for some other purpose, because if there are
no actions after the Stop Macro action, the macro will stop when it finishes
anyway. Macros aren't called "Forrest". :-)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Steve said:
Graham,
It works a treat, Thank you very much.
I haveStop Macros on all the rest of my macros, is this not required?
Should
I remove this from the rest?
regards
Steve
--
Thanks from Down Under


Graham R Seach said:
Steve,

Get rid of the Stop Macro actions.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Morning Graham,
Macro (Wood)
1st Line (Action) Set Value, (Argument) [Forms]![Quoting
Master]![Hidden].Visible,Yes
2nd Line (Action) Stop Macro

Macro (Synthetic)
1st Line (Action) Open Form, (Argument) Synthetic,Form,,,,Normal
2nd Line (Action) Stop Macro

Regads
Steve
--
Thanks from Down Under


:

Steve,

OK, what do the individual macros do. I think something's happening in
one
of the macros, so let's start looking there. Write out what they do,
line-by-line.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Evening Graham (I hope you get this in the morning)
This is a direct paste from visual basic.

Private Sub Password_AfterUpdate()
If Nz(Forms!Lanes![Type], "") = "wood" Then
DoCmd.RunMacro "Wood"
Else
DoCmd.RunMacro "Synthetic"
End If

I selected Compile out of the Debug menu and no messages were
received.
Operated the control source and the correct macro worked but I also
received
the following error message.
Run-time error 2501:
The RunMacro action was canceled.
Pressed Debug on error message and the macro that had just run was
highlighted in yellow.
I then then removed the [ ] brackets and tested again, with the same
results.
Real frustrating I bet !! especially for you.
Thanks
Steve


--
Thanks from Down Under


:

Steve,

OK, let's split this up to make it easier to debug. And let's also
remove
any reserved word problem by enclosing the word "Type" in square
brackets.

If Nz(Forms!Lanes![Type], "") = "wood" Then
DoCmd.RunMacro "Wood"
Else
DoCmd.RunMacro "Synthetic"
End If

Of course, the spelling of the two macros are correct, aren't they?
"Wood"
and "Synthetic"?

Before running any code, select Compile from the Debug menu, and
fix
any
problems that arise from that. Then we can move on.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Graham , When double clicked both macros worked fine. Triedto put
LCase
in
but didn't know where to put the closing bracket . Whe I put it
at
the
end
it
did not allow me.
By the way thanks for helping me out.
Steve
Thanks from Down Under


:

Steve,

What happens if you double-click each macro on its own? Do they
complete
successfully?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


I must be a bit' D' but still the same, this is how it reads
now'
Private Sub Command2_Click()
If Nz(Forms!Lanes!Type, "") = "wood" Then DoCmd.RunMacro
("Wood")
Else
DoCmd.RunMacro ("Synthetic") (all on one line)
Same error message as before When when I debug what ever
action
it
has
done
will be highlighted in yellow, and it is only doing that
action
where
as
before it was doing both.
Thanks from Down Under


:

Steve,

Why do you have the If statement enclosed in brackets? Delete
the
outer
brackets:
If Nz(Forms!Lanes!Type, "") = "wood" Then DoCmd.RunMacro
"Wood"
Else
DoCmd.RunMacro "Synthetic"

If that doesn't fix it (and this shouldn't really make any
difference
at
all), but try enclosing the macro names in brackets.
If Nz(Forms!Lanes!Type, "") = "wood" Then
DoCmd.RunMacro("Wood")
Else
DoCmd.RunMacro("Synthetic")

Also just to be sure you don't run into any other problems,
change
the
expression to cater for the fact that users may enter
uppercase,
lowercase
or mixed case.
If LCase(Nz(Forms!Lanes!Type, "") = "wood") Then ...

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Thanks Graham, I did as you said and put it all on one
line,BUT,
I
am
now
recieving an error message
Run-time error'2501'
The run macro action was cancelled. I pushed debug and
(If Nz(Forms!Lanes!Type, "") = "wood" Then DoCmd.RunMacro
"Wood"
Else
DoCmd.RunMacro "Synthetic") the "DoCmd.RunMacro "Wood"
Else"
is
highlighted in yellow.
Cheers
Steve Lismore NSW

--
Thanks from Down Under


:

Steve,

The following two lines MUST appear on the SAME line!
If Nz(Forms!Lanes!Type, "") = "wood" Then DoCmd.RunMacro
"Wood"
Else
DoCmd.RunMacro "Synthetic"

There are two ways to write an If..Then statement:
If x = y Then
'blah
Else
'blech
End If

....or...

If x = y Then blah Else blech

Notice the second option does not require an 'End If'
clause.
This
option
is
the one I recommended.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Guys Im back,Graham I gave the code a go and It worked
(feel
like a
hero)
but with one small problem, it did both operations.
Here is what the sub looked like.
Private Sub Command2_Click()
If Nz(Forms!Lanes!Type, "") = "wood" Then DoCmd.RunMacro
"Wood"
Else
DoCmd.RunMacro "Synthetic"


End Sub
Can you see what I've done wrong
Steve
Click to expand...
Click to expand...
Click to expand...
 
Grahaam Im Back!!!
Shut the program down overnight, came back this morning and here we go again
but with a different result.
I am receiving runtime errors 3021 No current record or 2760 An error
occured while referencing the object. They happen at random.
The only change I made was to remove the Stop macro command in 2 seperate
macros.
Steve
--
Thanks from Down Under


Graham R Seach said:
Steve,

No, you don't need it, unless for some other purpose, because if there are
no actions after the Stop Macro action, the macro will stop when it finishes
anyway. Macros aren't called "Forrest". :-)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Steve said:
Graham,
It works a treat, Thank you very much.
I haveStop Macros on all the rest of my macros, is this not required?
Should
I remove this from the rest?
regards
Steve
--
Thanks from Down Under


Graham R Seach said:
Steve,

Get rid of the Stop Macro actions.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Morning Graham,
Macro (Wood)
1st Line (Action) Set Value, (Argument) [Forms]![Quoting
Master]![Hidden].Visible,Yes
2nd Line (Action) Stop Macro

Macro (Synthetic)
1st Line (Action) Open Form, (Argument) Synthetic,Form,,,,Normal
2nd Line (Action) Stop Macro

Regads
Steve
--
Thanks from Down Under


:

Steve,

OK, what do the individual macros do. I think something's happening in
one
of the macros, so let's start looking there. Write out what they do,
line-by-line.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Evening Graham (I hope you get this in the morning)
This is a direct paste from visual basic.

Private Sub Password_AfterUpdate()
If Nz(Forms!Lanes![Type], "") = "wood" Then
DoCmd.RunMacro "Wood"
Else
DoCmd.RunMacro "Synthetic"
End If

I selected Compile out of the Debug menu and no messages were
received.
Operated the control source and the correct macro worked but I also
received
the following error message.
Run-time error 2501:
The RunMacro action was canceled.
Pressed Debug on error message and the macro that had just run was
highlighted in yellow.
I then then removed the [ ] brackets and tested again, with the same
results.
Real frustrating I bet !! especially for you.
Thanks
Steve


--
Thanks from Down Under


:

Steve,

OK, let's split this up to make it easier to debug. And let's also
remove
any reserved word problem by enclosing the word "Type" in square
brackets.

If Nz(Forms!Lanes![Type], "") = "wood" Then
DoCmd.RunMacro "Wood"
Else
DoCmd.RunMacro "Synthetic"
End If

Of course, the spelling of the two macros are correct, aren't they?
"Wood"
and "Synthetic"?

Before running any code, select Compile from the Debug menu, and
fix
any
problems that arise from that. Then we can move on.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Graham , When double clicked both macros worked fine. Triedto put
LCase
in
but didn't know where to put the closing bracket . Whe I put it
at
the
end
it
did not allow me.
By the way thanks for helping me out.
Steve
Thanks from Down Under


:

Steve,

What happens if you double-click each macro on its own? Do they
complete
successfully?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


I must be a bit' D' but still the same, this is how it reads
now'
Private Sub Command2_Click()
If Nz(Forms!Lanes!Type, "") = "wood" Then DoCmd.RunMacro
("Wood")
Else
DoCmd.RunMacro ("Synthetic") (all on one line)
Same error message as before When when I debug what ever
action
it
has
done
will be highlighted in yellow, and it is only doing that
action
where
as
before it was doing both.
Thanks from Down Under


:

Steve,

Why do you have the If statement enclosed in brackets? Delete
the
outer
brackets:
If Nz(Forms!Lanes!Type, "") = "wood" Then DoCmd.RunMacro
"Wood"
Else
DoCmd.RunMacro "Synthetic"

If that doesn't fix it (and this shouldn't really make any
difference
at
all), but try enclosing the macro names in brackets.
If Nz(Forms!Lanes!Type, "") = "wood" Then
DoCmd.RunMacro("Wood")
Else
DoCmd.RunMacro("Synthetic")

Also just to be sure you don't run into any other problems,
change
the
expression to cater for the fact that users may enter
uppercase,
lowercase
or mixed case.
If LCase(Nz(Forms!Lanes!Type, "") = "wood") Then ...

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Thanks Graham, I did as you said and put it all on one
line,BUT,
I
am
now
recieving an error message
Run-time error'2501'
The run macro action was cancelled. I pushed debug and
(If Nz(Forms!Lanes!Type, "") = "wood" Then DoCmd.RunMacro
"Wood"
Else
DoCmd.RunMacro "Synthetic") the "DoCmd.RunMacro "Wood"
Else"
is
highlighted in yellow.
Cheers
Steve Lismore NSW

--
Thanks from Down Under


:

Steve,

The following two lines MUST appear on the SAME line!
If Nz(Forms!Lanes!Type, "") = "wood" Then DoCmd.RunMacro
"Wood"
Else
DoCmd.RunMacro "Synthetic"

There are two ways to write an If..Then statement:
If x = y Then
'blah
Else
'blech
End If

....or...

If x = y Then blah Else blech

Notice the second option does not require an 'End If'
clause.
This
option
is
the one I recommended.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Guys Im back,Graham I gave the code a go and It worked
(feel
like a
hero)
but with one small problem, it did both operations.
Here is what the sub looked like.
Private Sub Command2_Click()
If Nz(Forms!Lanes!Type, "") = "wood" Then DoCmd.RunMacro
"Wood"
Else
DoCmd.RunMacro "Synthetic"


End Sub
Can you see what I've done wrong
Steve
Click to expand...
Click to expand...
Click to expand...
 
Steve,

They don't have anything to do with removing the Stop Macro action from the
the macros. They indicate problems elsewhere. You have to work out which
error relates to which macro, because that'll tell you where to look for the
real problem(s).

My guess is that it occurs when macro2 gets run. That being the assumption,
I'd try opening the "Synthetic" form without the macro and see what happens.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Steve said:
Grahaam Im Back!!!
Shut the program down overnight, came back this morning and here we go
again
but with a different result.
I am receiving runtime errors 3021 No current record or 2760 An error
occured while referencing the object. They happen at random.
The only change I made was to remove the Stop macro command in 2 seperate
macros.
Steve
--
Thanks from Down Under


Graham R Seach said:
Steve,

No, you don't need it, unless for some other purpose, because if there
are
no actions after the Stop Macro action, the macro will stop when it
finishes
anyway. Macros aren't called "Forrest". :-)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Steve said:
Graham,
It works a treat, Thank you very much.
I haveStop Macros on all the rest of my macros, is this not required?
Should
I remove this from the rest?
regards
Steve
--
Thanks from Down Under


:

Steve,

Get rid of the Stop Macro actions.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Morning Graham,
Macro (Wood)
1st Line (Action) Set Value, (Argument) [Forms]![Quoting
Master]![Hidden].Visible,Yes
2nd Line (Action) Stop Macro

Macro (Synthetic)
1st Line (Action) Open Form, (Argument) Synthetic,Form,,,,Normal
2nd Line (Action) Stop Macro

Regads
Steve
--
Thanks from Down Under


:

Steve,

OK, what do the individual macros do. I think something's happening
in
one
of the macros, so let's start looking there. Write out what they
do,
line-by-line.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Evening Graham (I hope you get this in the morning)
This is a direct paste from visual basic.

Private Sub Password_AfterUpdate()
If Nz(Forms!Lanes![Type], "") = "wood" Then
DoCmd.RunMacro "Wood"
Else
DoCmd.RunMacro "Synthetic"
End If

I selected Compile out of the Debug menu and no messages were
received.
Operated the control source and the correct macro worked but I
also
received
the following error message.
Run-time error 2501:
The RunMacro action was canceled.
Pressed Debug on error message and the macro that had just run
was
highlighted in yellow.
I then then removed the [ ] brackets and tested again, with the
same
results.
Real frustrating I bet !! especially for you.
Thanks
Steve


--
Thanks from Down Under


:

Steve,

OK, let's split this up to make it easier to debug. And let's
also
remove
any reserved word problem by enclosing the word "Type" in square
brackets.

If Nz(Forms!Lanes![Type], "") = "wood" Then
DoCmd.RunMacro "Wood"
Else
DoCmd.RunMacro "Synthetic"
End If

Of course, the spelling of the two macros are correct, aren't
they?
"Wood"
and "Synthetic"?

Before running any code, select Compile from the Debug menu, and
fix
any
problems that arise from that. Then we can move on.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Graham , When double clicked both macros worked fine. Triedto
put
LCase
in
but didn't know where to put the closing bracket . Whe I put
it
at
the
end
it
did not allow me.
By the way thanks for helping me out.
Steve
Thanks from Down Under


:

Steve,

What happens if you double-click each macro on its own? Do
they
complete
successfully?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


I must be a bit' D' but still the same, this is how it
reads
now'
Private Sub Command2_Click()
If Nz(Forms!Lanes!Type, "") = "wood" Then DoCmd.RunMacro
("Wood")
Else
DoCmd.RunMacro ("Synthetic") (all on one line)
Same error message as before When when I debug what ever
action
it
has
done
will be highlighted in yellow, and it is only doing that
action
where
as
before it was doing both.
Thanks from Down Under


:

Steve,

Why do you have the If statement enclosed in brackets?
Delete
the
outer
brackets:
If Nz(Forms!Lanes!Type, "") = "wood" Then DoCmd.RunMacro
"Wood"
Else
DoCmd.RunMacro "Synthetic"

If that doesn't fix it (and this shouldn't really make any
difference
at
all), but try enclosing the macro names in brackets.
If Nz(Forms!Lanes!Type, "") = "wood" Then
DoCmd.RunMacro("Wood")
Else
DoCmd.RunMacro("Synthetic")

Also just to be sure you don't run into any other
problems,
change
the
expression to cater for the fact that users may enter
uppercase,
lowercase
or mixed case.
If LCase(Nz(Forms!Lanes!Type, "") = "wood") Then ...

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Thanks Graham, I did as you said and put it all on one
line,BUT,
I
am
now
recieving an error message
Run-time error'2501'
The run macro action was cancelled. I pushed debug and
(If Nz(Forms!Lanes!Type, "") = "wood" Then
DoCmd.RunMacro
"Wood"
Else
DoCmd.RunMacro "Synthetic") the "DoCmd.RunMacro
"Wood"
Else"
is
highlighted in yellow.
Cheers
Steve Lismore NSW

--
Thanks from Down Under


:

Steve,

The following two lines MUST appear on the SAME line!
If Nz(Forms!Lanes!Type, "") = "wood" Then
DoCmd.RunMacro
"Wood"
Else
DoCmd.RunMacro "Synthetic"

There are two ways to write an If..Then statement:
If x = y Then
'blah
Else
'blech
End If

....or...

If x = y Then blah Else blech

Notice the second option does not require an 'End If'
clause.
This
option
is
the one I recommended.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


message
Guys Im back,Graham I gave the code a go and It
worked
(feel
like a
hero)
but with one small problem, it did both operations.
Here is what the sub looked like.
Private Sub Command2_Click()
If Nz(Forms!Lanes!Type, "") = "wood" Then
DoCmd.RunMacro
"Wood"
Else
DoCmd.RunMacro "Synthetic"


End Sub
Can you see what I've done wrong
Steve
Click to expand...
Click to expand...
Click to expand...
 
Graham,
I found the cause I was in the macroBut macro 1 the dot before visible was
missing buggered if I know how that happened .
Alls well that ends well
Steve
It--
Thanks from Down Under


Graham R Seach said:
Steve,

They don't have anything to do with removing the Stop Macro action from the
the macros. They indicate problems elsewhere. You have to work out which
error relates to which macro, because that'll tell you where to look for the
real problem(s).

My guess is that it occurs when macro2 gets run. That being the assumption,
I'd try opening the "Synthetic" form without the macro and see what happens.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Steve said:
Grahaam Im Back!!!
Shut the program down overnight, came back this morning and here we go
again
but with a different result.
I am receiving runtime errors 3021 No current record or 2760 An error
occured while referencing the object. They happen at random.
The only change I made was to remove the Stop macro command in 2 seperate
macros.
Steve
--
Thanks from Down Under


Graham R Seach said:
Steve,

No, you don't need it, unless for some other purpose, because if there
are
no actions after the Stop Macro action, the macro will stop when it
finishes
anyway. Macros aren't called "Forrest". :-)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Graham,
It works a treat, Thank you very much.
I haveStop Macros on all the rest of my macros, is this not required?
Should
I remove this from the rest?
regards
Steve
--
Thanks from Down Under


:

Steve,

Get rid of the Stop Macro actions.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Morning Graham,
Macro (Wood)
1st Line (Action) Set Value, (Argument) [Forms]![Quoting
Master]![Hidden].Visible,Yes
2nd Line (Action) Stop Macro

Macro (Synthetic)
1st Line (Action) Open Form, (Argument) Synthetic,Form,,,,Normal
2nd Line (Action) Stop Macro

Regads
Steve
--
Thanks from Down Under


:

Steve,

OK, what do the individual macros do. I think something's happening
in
one
of the macros, so let's start looking there. Write out what they
do,
line-by-line.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Evening Graham (I hope you get this in the morning)
This is a direct paste from visual basic.

Private Sub Password_AfterUpdate()
If Nz(Forms!Lanes![Type], "") = "wood" Then
DoCmd.RunMacro "Wood"
Else
DoCmd.RunMacro "Synthetic"
End If

I selected Compile out of the Debug menu and no messages were
received.
Operated the control source and the correct macro worked but I
also
received
the following error message.
Run-time error 2501:
The RunMacro action was canceled.
Pressed Debug on error message and the macro that had just run
was
highlighted in yellow.
I then then removed the [ ] brackets and tested again, with the
same
results.
Real frustrating I bet !! especially for you.
Thanks
Steve


--
Thanks from Down Under


:

Steve,

OK, let's split this up to make it easier to debug. And let's
also
remove
any reserved word problem by enclosing the word "Type" in square
brackets.

If Nz(Forms!Lanes![Type], "") = "wood" Then
DoCmd.RunMacro "Wood"
Else
DoCmd.RunMacro "Synthetic"
End If

Of course, the spelling of the two macros are correct, aren't
they?
"Wood"
and "Synthetic"?

Before running any code, select Compile from the Debug menu, and
fix
any
problems that arise from that. Then we can move on.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Graham , When double clicked both macros worked fine. Triedto
put
LCase
in
but didn't know where to put the closing bracket . Whe I put
it
at
the
end
it
did not allow me.
By the way thanks for helping me out.
Steve
Thanks from Down Under


:

Steve,

What happens if you double-click each macro on its own? Do
they
complete
successfully?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


I must be a bit' D' but still the same, this is how it
reads
now'
Private Sub Command2_Click()
If Nz(Forms!Lanes!Type, "") = "wood" Then DoCmd.RunMacro
("Wood")
Else
DoCmd.RunMacro ("Synthetic") (all on one line)
Same error message as before When when I debug what ever
action
it
has
done
will be highlighted in yellow, and it is only doing that
action
where
as
before it was doing both.
Thanks from Down Under


:

Steve,

Why do you have the If statement enclosed in brackets?
Delete
the
outer
brackets:
If Nz(Forms!Lanes!Type, "") = "wood" Then DoCmd.RunMacro
"Wood"
Else
DoCmd.RunMacro "Synthetic"

If that doesn't fix it (and this shouldn't really make any
difference
at
all), but try enclosing the macro names in brackets.
If Nz(Forms!Lanes!Type, "") = "wood" Then
DoCmd.RunMacro("Wood")
Else
DoCmd.RunMacro("Synthetic")

Also just to be sure you don't run into any other
problems,
change
the
expression to cater for the fact that users may enter
uppercase,
lowercase
or mixed case.
If LCase(Nz(Forms!Lanes!Type, "") = "wood") Then ...

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Thanks Graham, I did as you said and put it all on one
line,BUT,
I
am
now
recieving an error message
Run-time error'2501'
The run macro action was cancelled. I pushed debug and
(If Nz(Forms!Lanes!Type, "") = "wood" Then
DoCmd.RunMacro
"Wood"
Else
DoCmd.RunMacro "Synthetic") the "DoCmd.RunMacro
"Wood"
Else"
is
highlighted in yellow.
Cheers
Steve Lismore NSW

--
Thanks from Down Under


:

Steve,

The following two lines MUST appear on the SAME line!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Iff statement to show pictures 4
Need help on Iff Statement 1
Need Help with an IF statement 3
Execute a macro inside an IIF statement 3
Can anyone tell me what is wrong with this IIf statement 3
Access Macro Repeat Expression using value of a field in table 0
Subject: Need help on Iff Statement 1
Compare Fields with < But Get #Error 4

Back
Top