How to nest IF statements Plz

  • Thread starter Thread starter Bonnie
  • Start date Start date
B

Bonnie

Using A02. I am on the road to learning and loving every
minute of this! I can now write IF's so I can validate
data on a form's BeforeUpdate event procedure. How can I
check for a number of things and then (and only then) save
the record. My IF's have CancelEvent if any rule is
broken. I'd rather not put Else: DoCmd.RunC...SaveRecord
in every IF because it will save if only 1 of 3 rules were
not broken. How can I check for more than one IF and then
only when ALL rules check out, save the record? I now have:

If Me.WireAmt > 0 And IsNull(Me.WireAcct) Then
MsgBox "Blah Blah Blah"
DoCmd.CancelEvent
End If
If Me.Journal = "-1" And Not IsNull(Me.Payee) Then
MsgBox "Blah Blah Blah"
DoCmd.CancelEvent
End If
If Me.CkReq = "-1" And IsNull(Me.Payee) Then
MsgBox "Blah Blah Blah"
DoCmd.CancelEvent
End If

Me.LastChgd = Now()
Me.LastUser = CurrentUser()
DoCmd.RunCommand acCmdSaveRecord

I was hoping if it got stuck before the bottom, it would
cancel and not save the record. However, I get Runtime
Error 2001 saying I cancelled the previous operation.

This website has been responsible for the great leaps in
my ability to work in Access over the last 3-4 years.
Thanks in advance for any help or advice. I LOVE YOU
GUYS!!! Thanks also for taking the time to help others.
 
Hi Bonnie,

You can nest your If statements such as:

If condition 1 Then
If condition 2 Then
If condition 3 Then
Do this if All 3 Conditions True
Else
Do this if conditions 1 and 2 true, but 3 false
EndIf
Else
Do this if Condition 1 true, but condition 2 false
EndIf
Else
Do this if condition 1 false
EndIf

However, in your case I think you would likely be better
off to leave your If statements as they are, and just add
an Exit Sub command after each of your CancelEvent
commands. This way, if a condition fails it will notify
the user, cancel the change, and not exit the sub to
avoid ever getting to the save record point.

-Ted Allen
 
Instead of DoCmd.CancelEvent, try using

Cancel = True

Also, it shouldn't be necessary to save the record at the end. If you don't
cancel, the save should be what happens anyway.

For the If statements, just do them one at a time. If you find a user error,
inform the user, cancel, set focus to the control with the error (if
desired) and exit the sub. The routine will get run again the next time the
user tries to save the data. If they have it fixed, the If statements won't
do anything and the record will be save. If they don't have it fixed, then
the error (or different error) will be caught and the save will be aborted
again.

Example:
If Me.WireAmt > 0 And IsNull(Me.WireAcct) Then
MsgBox "Blah Blah Blah"
Cancel = True
Me.WireAmt.SetFocus
Exit Sub
End If
If Me.Journal = "-1" And Not IsNull(Me.Payee) Then
MsgBox "Blah Blah Blah"
Cancel = True
Me.Journal.SetFocus
Exit Sub
End If
 
THANKS BUNCHES!!! I have learned so much in the last few
years and it is exciting to start to understand what I've
been looking at for so long. Your advice makes great
sense. I've wondered about Cancel=True for awhile but your
message put it in perspective for me. The light bulb is
on!...woohoo! Thanks for taking the time to 'pass it
forward'.
 
Thanks alot for the nesting tree below, I can think of a
special case that it will take care of wonderfully! And
you made ExitSub make sense. (Don't laugh, there must have
been a time when you didn't know all the commands and
stuff!) Thanks for taking the time to help folks like me
that need it. I'm starting to understand what's going on
here and the possibilities are endless! I love this!
 
Back
Top