Solved

Trying to turn font bold and red if certain criteria exists

Posted on 2014-09-04
9
178 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 37

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 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 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

724 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