Using OR in If statement, please help

[bluemoon9]

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!

Me.lblARF.ForeColor = IIf((Me.ARF_HYPO = Yes Or Me.ARF_NEW_OLI = Yes Or Me.ARF_AMINO_USE = Yes), vbRed, vbBlack)
 

.
lblARF is the lable
ARF_HYPO, ARF_NEW_OLI, ARF_AMINO_USE are Yes/No field.

thanks!

[Stewart Ross]

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:

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:

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

IF (Me.ARF_HYPO  Or  Me.ARF_NEW_OLI  Or Me.ARF_AMINO_USE) THEN
    Me.lblARF.ForeColor = vbRed
ELSE 
    Me.lblARF.ForeColor = vbBlack
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:

With Me.lblARF
  IF (Me.ARF_HYPO  Or  Me.ARF_NEW_OLI  Or Me.ARF_AMINO_USE) THEN
      .ForeColor = vbRed
  ELSE 
      .ForeColor = vbBlack
  END IF
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.

[NeoPa]

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 :

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!