By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,180 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

Using OR in If statement, please help

P: 56
Hi everyone,
I am trying to write a code so that when the user checks on one of those 3 fields, lblARF turn into red color, else stay black. I've placed the following code in the "On Current" event of the form, and also on the "On Click" event for all 3 fields. However, for some reasons, it didn't work quite well as I expected.
Can someone please help? Thanks!

Expand|Select|Wrap|Line Numbers
  1. Me.lblARF.ForeColor = IIf((Me.ARF_HYPO = Yes Or Me.ARF_NEW_OLI = Yes Or Me.ARF_AMINO_USE = Yes), vbRed, vbBlack)
  2.  
.
lblARF is the lable
ARF_HYPO, ARF_NEW_OLI, ARF_AMINO_USE are Yes/No field.

thanks!
Oct 8 '08 #1
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
Hi, and Welcome to Bytes!

In VBA boolean values are represented by the constants True and False, not Yes and No (which are treated as the names of variables - undeclared ones in this case). Rewriting your IIF using True to replace Yes and bracketing to make clear which part is which:
Expand|Select|Wrap|Line Numbers
  1. Me.lblARF.ForeColor = IIf(((Me.ARF_HYPO = True) Or (Me.ARF_NEW_OLI = True) Or (Me.ARF_AMINO_USE = True)), vbRed, vbBlack)
However, as the checkboxes return True or False we don't need to compare those to True at all:
Expand|Select|Wrap|Line Numbers
  1. Me.lblARF.ForeColor = IIf((Me.ARF_HYPO  Or  Me.ARF_NEW_OLI  Or Me.ARF_AMINO_USE), vbRed, vbBlack)
In VBA it is stylistically better not to use in-line IFs at all, so how's about using
Expand|Select|Wrap|Line Numbers
  1. IF (Me.ARF_HYPO  Or  Me.ARF_NEW_OLI  Or Me.ARF_AMINO_USE) THEN
  2.     Me.lblARF.ForeColor = vbRed
  3. ELSE 
  4.     Me.lblARF.ForeColor = vbBlack
  5. END IF
If you want to do away with repeatedly referring to the control for the forecolor property you could use a With statement as follows:
Expand|Select|Wrap|Line Numbers
  1. With Me.lblARF
  2.   IF (Me.ARF_HYPO  Or  Me.ARF_NEW_OLI  Or Me.ARF_AMINO_USE) THEN
  3.       .ForeColor = vbRed
  4.   ELSE 
  5.       .ForeColor = vbBlack
  6.   END IF
  7. END WITH
Finally, instead of placing the code in each event make it a private procedure and call that from each of the other event procedures - the After Update events for each of the controls (instead of On Click), and the Form Current event for the form itself.

-Stewart

ps should you find references to them at some stage the VB constants vbYes and vbNo are used for message boxes etc and are NOT synonyms for the boolean values True and False.
Oct 8 '08 #2

NeoPa
Expert Mod 15k+
P: 31,709
It is always a good idea to ensure that variable name checking is enabled, AND your code compiles (at least compilation has been attempted), before submitting a question.

This avoids asking questions which are much more easily resolved on your own PC than on a forum.

To ensure variable name checking is enabled for all new modules, go to - Tools / Options / Editor (from the VBA Editor window) and set Require Variable Declaration to True (checked). For existing modules, ensure that the Option lines at the very top include :
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
To compile your project, select (again from the VBA Editor window) Debug / Compile Project Name.

We ARE generally happy to help with compilation problems too (If you find an error reported and you can't resolve it, let us know), but we do expect members to have tried compiling before submitting a question. That way we have a better idea of the sort of problem we're looking at.

Welcome to Bytes!
Oct 8 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.