Question: Access 2010 VBA syntax for conditional formating

  • (2 Pages)
  • +
  • 1
  • 2

20 Replies - 14197 Views - Last Post: 05 March 2012 - 11:17 PM

#1 Ken1950  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 13-November 09

Question: Access 2010 VBA syntax for conditional formating

Posted 11 November 2011 - 02:14 PM

I need to change the BackColor of a text box when the value is between two different ranges.
One is an upper range (upperLimit1 - upperLimit2) and the other is a lower range (lowerLimit1 - lowerLimit2).
Access has a Conditional Formating wizard but will not accept 2 between parameters so I am trying to write the VBA code.
I found some sample code on MSDN and tried to modify that. I started out with a simple > UpperLimit.
This is what I have so far.
My text box is InspectedValue

Private Sub Form_Current()

    Dim curInspValue As Double ' represents the inspected value entered by inspector
    Dim lngRed As Long, lngYellow As Long, lngWhite As Long
    
    If Not IsNull(Me!InspectedValue.Value) Then
        curInspValue = Me!InspectedValue.Value
        Me!InspectedValue.BackColor = lngWhite
        Else
        Exit Sub
    End If
    
    lngRed = RGB(255, 0, 0)
    lngBlack = RGB(0, 0, 0)
    lngYellow = RGB(255, 255, 0)
    lngWhite = RGB(255, 255, 255)
    
    If InspectedValue.Value > UpperLim.Value Then
        Me!InspectedValue.BackColor = lngRed
        Else
        Me!InspectedValue.BackColor = lngWhite
        
    End If

    
End Sub


I have used brackets [] and no brackets. Dot Value (.Value) or not.

Thanks for any help or suggestions.
Ken

Is This A Good Question/Topic? 0
  • +

Replies To: Question: Access 2010 VBA syntax for conditional formating

#2 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Question: Access 2010 VBA syntax for conditional formating

Posted 12 November 2011 - 12:49 AM

Not sure I understand either. Show the expressions you attempted.

Maybe:

If (InspectedValue >= UpperLim1 And InspectedValue <= UpperLim2) AND (InspectedValue >= LowerLim1 And InspectedValue <= LowerLim2) Then

This post has been edited by June7: 12 November 2011 - 12:51 AM

Was This Post Helpful? 0
  • +
  • -

#3 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

Re: Question: Access 2010 VBA syntax for conditional formating

Posted 12 November 2011 - 02:21 PM

It's been a while since I've used VBA, but some pointers here:
  • On line 8 you use the variable lngWhite, but it is not yet assigned a value
  • On line 14 you assign a value to lngBlack, but lngBlack is not Dim'med
  • On line 18 you refer to InspectedValue.Value, whereas elsewhere you refer to it as Me!InspectedValue.Value (not sure if this is an issue, as I said it's been a while since I used VBA)
  • On line 18 you refer to UpperLim.Value. Do you definitely have an element with the name UpperLim, and is it assigned a value? (I assume you are using a hidden input element.)

I'll keep an eye on this thread. If those pointers don't help out, I'll boot up Access and have a tinker.
Was This Post Helpful? 0
  • +
  • -

#4 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Question: Access 2010 VBA syntax for conditional formating

Posted 12 November 2011 - 03:30 PM

Good points e_i_pi.

Omitting the Me or object name qualifier will still usually work but is a good idea to use. I use Me! when referring to fields of a recordset. I use Me. when referring to objects and controls because the dot will provoke intellisense popup tips. The Me qualifier can only be used in code behind the object it references. If you need to refer to another object then use the object class reference, like:
Forms!formname.controlname
Reports!reportname.controlname

Instead of declaring and setting variables for these colors, can use VB constants, there are 8:
vbWhite
vbBlack
vbBlue
vbRed
vbYellow
vbGreen
vbMagenta
vbCyan

This post has been edited by June7: 12 November 2011 - 03:30 PM

Was This Post Helpful? 0
  • +
  • -

#5 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

Re: Question: Access 2010 VBA syntax for conditional formating

Posted 12 November 2011 - 03:38 PM

Ah okay cool. As I said, it's been a while since I've used VBA/Access, so I'm a bit rusty. I'll have a look when I get a chance, I'm moving house today so I might be without internet for a couple of days.
Was This Post Helpful? 0
  • +
  • -

#6 Ken1950  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 13-November 09

Re: Question: Access 2010 VBA syntax for conditional formating

Posted 14 November 2011 - 12:53 PM

View PostJune7, on 12 November 2011 - 12:49 AM, said:

Not sure I understand either. Show the expressions you attempted.

Maybe:

If (InspectedValue >= UpperLim1 And InspectedValue <= UpperLim2) AND (InspectedValue >= LowerLim1 And InspectedValue <= LowerLim2) Then


The first expression I attempted was within the Conditional Formatting Rules Manager. I had already deleted the rules so I recreated them as follows.
Formating rules for: InspectedValue
Value Is Between [LowerLim2] and [LowerLim1] Format = Yellow background
Value is Between [UpperLim1] and [UpperLim2] Format = Yellow background

Interesting thing happened as I was recreating the rules. As I entered each rule, I would then test it and the formate would work. Once I saved the form and closed the database. The formats would not work when the database was re-opened. I have the database set to open w/o the Navigation pain or ribbons (other than Home and File)

I also tried your sample code several ways. First, I put this in the sub routine Form_Current () exactly as you have above.
Then I change the second And to Or and used the vbYellow
Private Sub Form_Current()
'set the conditional formating for text box Inspected Value

If (InspectedValue >= U75 And InspectedValue <= UpperLim) Or (InspectedValue <= L75 And InspectedValue >= LowerLim) Then
    Me.InspectedValue.BackColor = vbYellow
    Esle
    Exit Sub
    
End If
    
End Sub



Then I added the .Value
Private Sub Form_Current()
'set the conditional formating for text box Inspected Value

If (InspectedValue.Value >= U75 And InspectedValue.Value <= UpperLim) Or (InspectedValue.Value <= L75 And InspectedValue.Value >= LowerLim) Then
    Me.InspectedValue.BackColor = vbYellow
    Esle
    Exit Sub
    
End If

End Sub



I am obviously unclear of the concept. Since Acces hides (or I can't find) the code for the conditional formating I don't know the proper syntax.
Was This Post Helpful? 0
  • +
  • -

#7 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Question: Access 2010 VBA syntax for conditional formating

Posted 14 November 2011 - 01:06 PM

You misspelled Else. VBA should give you an error message on this, especially if you run Compile. The Else Exit Sub lines are not necessary.

What are U7, L75, UpperLim, LowerLim - variables, controls?

VBA code is not setting conditional formatting. If the criteria in the conditional statement met then the Backcolor property will be set.

Should work. Do you want to provide the project for analysis?
Was This Post Helpful? 0
  • +
  • -

#8 Ken1950  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 13-November 09

Re: Question: Access 2010 VBA syntax for conditional formating

Posted 14 November 2011 - 02:09 PM

View PostJune7, on 14 November 2011 - 01:06 PM, said:

You misspelled Else. VBA should give you an error message on this, especially if you run Compile. The Else Exit Sub lines are not necessary.


Thank you

Corrected my spelling and compiled the code (I am so not a programer - yet)

View PostJune7, on 14 November 2011 - 01:06 PM, said:

What are U7, L75, UpperLim, LowerLim - variables, controls?

These are text boxes on my form.
I meant to edit the names for clarity.
From my original post -
LowerLim = LowerLim2
L75 = LowerLim1
U75 = UpperLim1
UpperLim = UpperLim2

Now we're getting closer. The back color is now defaults to yellow. Here is the new code.
Private Sub Form_Current()
'set the conditional formating for text box Inspected Value

If (InspectedValue.Value >= U75 And InspectedValue.Value <= UpperLim) Or (InspectedValue.Value <= L75 And InspectedValue.Value >= LowerLim) Then
    Me.InspectedValue.BackColor = vbYellow
    Else
    
    Exit Sub
    
End If

    
End Sub


Do I need .Value after the control names? ie UpperLim.Value

I will eventually want to add formating of vbRed when the values are greater than UpperLim or Less than LowerLim.

Thanks very again for your input.
Was This Post Helpful? 0
  • +
  • -

#9 Ken1950  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 13-November 09

Re: Question: Access 2010 VBA syntax for conditional formating

Posted 14 November 2011 - 02:36 PM

Another clarification

My form is a continuous sub-form. So when one value meets the criteria, all the text boxes in the continuous form change. How do I isolate the single value. Some will remain white, some could be yellow and some could be red.

Thanks
Was This Post Helpful? 0
  • +
  • -

#10 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Question: Access 2010 VBA syntax for conditional formating

Posted 15 November 2011 - 01:07 AM

Value is default property, it will be assumed if no other property is referenced.

So the Else might have a use here. Try code to set to vbWhite. What conditions are for red? Might need ElseIf.
Was This Post Helpful? 0
  • +
  • -

#11 Ken1950  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 13-November 09

Re: Question: Access 2010 VBA syntax for conditional formating

Posted 15 November 2011 - 11:47 AM

The conditions for Red are < LowerLim and > UpperLim.

I added an Else InspectedValue.BackColor = vbWhite

The results: If a value is entered that meets the conditions, ALL InspectedValue text boxes change to yellow regardless of preveious values. If the next value does not meet the conditions, the text box reverts to white.

Now for the "Access is dumb" part. And this is for others that might view this thread.
As I stated at the start, I had been trying to use the Conditional Formating built into the design view. I now have all four conditions working - 2 for yellow and 2 for red.
I had to set the first between condition for yellow. Save the form. Close the database. Open and test the condition. Go to Design view and set the second contidtion. Save and close, then repeat for each condition.

If I didn't follow this proceedure, my formatting was lost as soon as I reopened the database as a user. Very strange.

This project has me thinking the old saying re: "Old dogs and new tricks" just might be true in my case.

Thank you very much for all your help.

Ken
Was This Post Helpful? 0
  • +
  • -

#12 CaptainKen  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 11-August 10

Re: Question: Access 2010 VBA syntax for conditional formating

Posted 01 March 2012 - 12:46 PM

I have a form frmRMA_Summary with a sub form sbfRMAs. I have been successful using the Conditional Formatting Rules Manager with this statement and desired formatting.

([bolRepair]=True And (Abs([txtReceivedDate]-Date())>14) And IsNull([txtShipDate]))



When I delete the rule and attempt this in VB of the sbfRMAs Form_Current the formatting doesn't work. However, the applicable Msgbox appears for each time a record meets the criteria.

Private Sub Form_Current()

    If ([bolRepair] = True And (Abs([txtReceivedDate] - Date) > 14) And IsNull([txtShipDate])) Then
'MsgBox "Overdue, so color should change"
        Me.txtShipDate.BackColor = vbRed
        Me.txtShipDate.ForeColor = vbWhite
    Else
'MsgBox "Not overdue, so no color change"
        Me.txtShipDate.BackColor = vbWhite
        Me.txtShipDate.ForeColor = vbBlack
    End If

End Sub



Thank you.

Ken
Was This Post Helpful? 0
  • +
  • -

#13 CaptainKen  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 11-August 10

Re: Question: Access 2010 VBA syntax for conditional formating

Posted 01 March 2012 - 02:05 PM

Oops, I should have mentioned that my sub form sbfRMAs is set to Datasheet view.
Was This Post Helpful? 0
  • +
  • -

#14 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Question: Access 2010 VBA syntax for conditional formating

Posted 01 March 2012 - 04:39 PM

Why using VBA? The Conditional Formatting is adequate and less problematic.

What do you mean 'doesn't work' - what happens?
Was This Post Helpful? 0
  • +
  • -

#15 CaptainKen  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 11-August 10

Re: Question: Access 2010 VBA syntax for conditional formating

Posted 01 March 2012 - 04:46 PM

We'd rather all settings, values, etc be controlled from VB. Possibility also exists that we implement more complex code at a later date.

It doesn't work in that the backcolor and forecolor settings do not take effect.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2