Solved

Trying to turn font bold and red if certain criteria exists

Posted on 2014-09-04
9
174 Views
Last Modified: 2014-09-04
I have this code in the oncurrent event of a form;

    If Me.cboHBA1Code <> DLookup("[CodeAbbreviation]", "tblTreatmentCodes", "[CodeAbbreviation]= 'R'") Then
        Me.cboHBA1Code.ForeColor = vbRed
        Me.cboHBA1Code.FontBold = True
        Else
        Me.cboHBA1Code = vbBlack
        Me.cboHBA1Code.FontBold = False
    End If

Open in new window


But there are 8 different "acceptable" codes in tblTreatmentCodes.  I tried replacing the "R" with '*' as a wildcard but that doesn't work.

Do I have to do a DLookup on all eight so the code looks like:

    If Me.cboHBA1Code <> DLookup("[CodeAbbreviation]", "tblTreatmentCodes", "[CodeAbbreviation]= 'R'") OR <> DLookup("[CodeAbbreviation]", "tblTreatmentCodes", "[CodeAbbreviation]= 'O'") OR <> DLookup("[CodeAbbreviation]", "tblTreatmentCodes", "[CodeAbbreviation]= 'C'") OR <> DLookup("[CodeAbbreviation]", "tblTreatmentCodes", "[CodeAbbreviation]= 'NM'") OR <> DLookup("[CodeAbbreviation]", "tblTreatmentCodes", "[CodeAbbreviation]= 'D'") OR <> DLookup("[CodeAbbreviation]", "tblTreatmentCodes", "[CodeAbbreviation]= 'X'") OR <> DLookup("[CodeAbbreviation]", "tblTreatmentCodes", "[CodeAbbreviation]= 'H'") OR <> DLookup("[CodeAbbreviation]", "tblTreatmentCodes", "[CodeAbbreviation]= 'Ref'") Then
        Me.cboHBA1Code.ForeColor = vbRed
        Me.cboHBA1Code.FontBold = True
        Else
        Me.cboHBA1Code = vbBlack
        Me.cboHBA1Code.FontBold = False
    End If

Open in new window

0
Comment
Question by:SteveL13
9 Comments
 
LVL 21

Expert Comment

by:Randy Poole
ID: 40303435
Try this:
If Me.cboHBA1Code <> DLookup("[CodeAbbreviation]", "tblTreatmentCodes", "[CodeAbbreviation]= '" & Me.cboHBA1Code & "'") Then
        Me.cboHBA1Code.ForeColor = vbRed
        Me.cboHBA1Code.FontBold = True
        Else
        Me.cboHBA1Code = vbBlack
        Me.cboHBA1Code.FontBold = False
End if

Open in new window

0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40303451
use this codes instead

select case Me.cboHBA1Code
       Case "R","O","C","NM","D","X","H","Ref"
        Me.cboHBA1Code.ForeColor = vbRed
        Me.cboHBA1Code.FontBold = True

       Case Else
        Me.cboHBA1Code = vbBlack
        Me.cboHBA1Code.FontBold = False
end select
0
 

Author Comment

by:SteveL13
ID: 40303453
For some reason that code is turning the data into a 0 (zero).  It is really a R in the table.
0
 

Author Comment

by:SteveL13
ID: 40303456
My reply was to Randy.  I'll try Rey's.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:SteveL13
ID: 40303470
Using Rey's code...  (note that the logic was reversed.  If the data is "R", "O", "C", "NM", "D", "X", "H", or "Ref" then it should be vbBlack and FontBold = False)

But if the data is not "R", "O", "C", "NM", "D", "X", "H", or "Ref" then the form shows "255"

????

Here is what I have now...

   
Select Case Me.cboHBA1Code
        Case "R", "O", "C", "NM", "D", "X", "H", "Ref"
        Me.cboHBA1Code.ForeColor = vbBlack
        Me.cboHBA1Code.FontBold = False
        Case Else
        Me.cboHBA1Code = vbRed
        Me.cboHBA1Code.FontBold = True
    End Select

Open in new window

0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 40303473
Me.cboHBA1Code = vbBlack
should be
Me.cboHBA1Code.ForeColor = vbBlack

vbBlack = 0.  That is why the field value is being changed to 0.  Fix the typo and the problem will go away.
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40303474
ohh sorry.. need more coffee..

does it work now?
0
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 250 total points
ID: 40303479
Select Case Me.cboHBA1Code
        Case "R", "O", "C", "NM", "D", "X", "H", "Ref"
        Me.cboHBA1Code.ForeColor = vbBlack
        Me.cboHBA1Code.FontBold = False
        Case Else
        Me.cboHBA1Code.ForeColor = vbRed
        Me.cboHBA1Code.FontBold = True
    End Select
0
 

Author Closing Comment

by:SteveL13
ID: 40303554
Perfect!!!!!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now