Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Trying to turn font bold and red if certain criteria exists

Posted on 2014-09-04
9
Medium Priority
?
181 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:SteveL13
ID: 40303456
My reply was to Randy.  I'll try Rey's.
0
 

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 40

Assisted Solution

by:PatHartman
PatHartman earned 1000 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 120

Expert Comment

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

does it work now?
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 1000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

572 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