Conditional Macro assistance, please

[Colin Foster]

Hi,

I need to set up a conditional macro to help do the following:

If someone works in the Accounts department (AC01), their salary needs to be
in the range 10,000 - 30,000
If they work in the QC department, their salary needs to be 4,000 - 25,000
Anyone else can have any salary.

I also need to prompt with appropriate messages explaining what are
acceptable salaries for the department & whether the entered salary is too
high or too low.

So far I have tried this (the comments in the {} are what is in the Action
column, everything else is in the Condition column)...

[Forms]![frm Employees ex3 Mainform]![Department Number] Like "AC01"
.... {Message box explaining salary upper & lower limits}
[Forms]![frm Employees ex3 Mainform]![Salary]<10000 {Message box explaining
too low a level used}
.... {CancelEvent - to stop the low salary being input}
[Forms]![frm Employees ex3 Mainform]![Salary]>30000 {Message box
explaining too high a level used}
.... {CancelEvent - to stop the high salary being input}

[Forms]![frm Employees ex3 Mainform]![Department Number] Like "QC01"
[Forms]![frm Employees ex3 Mainform]![Salary]<4000{Message box explaining
too low a level used}
.... {CancelEvent - to stop the low salary being input}
[Forms]![frm Employees ex3 Mainform]![Salary]>25000 {Message box
explaining too high a level used}
.... {CancelEvent - to stop the low salary being input}


Needless to say, this doesn't work properly!! So any suggestions would be
useful as this is my first attempt at these sort of Macros.

regards
Colin Foster

 

[Steve Schapel]

Colin,

Try it like this...

Condition:
([Department Number]="AC01" And ([Salary]<10000 Or [Salary]>30000)) Or
([Department Number]="QC01" And ([Salary]<4000 Or [Salary]>25000))
Action: MsgBox
Condition: ...
Action: CancelEvent

 

[Colin Foster]

Bugger!!

That works absolutely perfectly

I have spent most of the afternoon on this & you solve it in minutes!

Looking at the code that you have written it is blindingly obvious... NOW,
but I've been racking what's left of my brains on this!

However, I have decided to blame my "manflu" so I will go and get a stiff
drink to make it go away & celebrate closing this one off.

Once again, many thanks for your help

Regards
Colin

Colin,

Try it like this...

Condition:
([Department Number]="AC01" And ([Salary]<10000 Or [Salary]>30000)) Or
([Department Number]="QC01" And ([Salary]<4000 Or [Salary]>25000))
Action: MsgBox
Condition: ...
Action: CancelEvent

--
Steve Schapel, Microsoft Access MVP

Hi,

I need to set up a conditional macro to help do the following:

If someone works in the Accounts department (AC01), their salary needs to
be in the range 10,000 - 30,000
If they work in the QC department, their salary needs to be 4,000 -
25,000
Anyone else can have any salary.

I also need to prompt with appropriate messages explaining what are
acceptable salaries for the department & whether the entered salary is
too high or too low.

So far I have tried this (the comments in the {} are what is in the
Action column, everything else is in the Condition column)...

[Forms]![frm Employees ex3 Mainform]![Department Number] Like "AC01"
... {Message box explaining salary upper & lower limits}
[Forms]![frm Employees ex3 Mainform]![Salary]<10000 {Message box
explaining too low a level used}
... {CancelEvent - to stop the low salary being input}
[Forms]![frm Employees ex3 Mainform]![Salary]>30000 {Message box
explaining too high a level used}
... {CancelEvent - to stop the high salary being input}

[Forms]![frm Employees ex3 Mainform]![Department Number] Like "QC01"
[Forms]![frm Employees ex3 Mainform]![Salary]<4000{Message box explaining
too low a level used}
... {CancelEvent - to stop the low salary being input}
[Forms]![frm Employees ex3 Mainform]![Salary]>25000 {Message box
explaining too high a level used}
... {CancelEvent - to stop the low salary being input}


Needless to say, this doesn't work properly!! So any suggestions would be
useful as this is my first attempt at these sort of Macros.

regards
Colin Foster

 

[Steve Schapel]

Colin,

Well, good that it works well for you. And I hope you're feeling better
soon .

I assume you will assign this macro on the Before Update event of the form.

 

[Colin Foster]

Hi Steve,

Yes I have... I got that bit right

I think that part of the problem is that, coming from an Excel background, I
have always found Access complicated & not very straightforward, so when it
comes to areas like this, I presume that it's going to be complicated when
it's far more straightforward!

Ah well, off to solve the rest, now.

Once again, thanks for the help.

Colin

ps.. yes, thanks... I'm feeling a little better this morning