UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Open Recordset, Compare Fields, Change Label Color, Access 2016    
 
   
Vector1
post Apr 14 2018, 10:51 PM
Post#1



Posts: 66
Joined: 9-March 17



Hi,
Can someone help me with this code? I am trying to compare certain fields in a recordset and change multiple label colors based on the result(s)

Dim DB As DAO.Database
Dim rs As DAO.Recordset

On Error GoTo ColorIndicator_Error

Set DB = CurrentDb
Set rs = DB.OpenRecordset("qry1PPMKPI_Sites", dbOpenDynaset)
Do While Not rs.EOF
If rs.RecordCount > 0 Then
rs.MoveFirst
If rs!SiteName = "Kite" And rs!PPM >= 2500 Then
Me.lblKitePPM.BackColor = "255" 'red
ElseIf rs!SiteName = "Kite" And rs!PPM < 2500 Then
Me.lblKitePPM.BackColor = "65280" 'green
End If

If rs!SiteName = "Bella" And rs!PPM >= 2500 Then
Me.lblBellaPPM.BackColor = "255" 'red then
ElseIf rs!SiteName = "Bella" And rs!PPM < 2500 Then
Me.lblBellaPPM.BackColor = "65280" 'green
End If
End If
rs.MoveNext
Loop
Go to the top of the page
 
projecttoday
post Apr 15 2018, 12:55 AM
Post#2


UtterAccess VIP
Posts: 9,381
Joined: 10-February 04
From: South Charleston, WV


It looks like you're changing the same 2 labels over and over again.

--------------------
Robert Crouser

My company's website
Go to the top of the page
 
Phil_cattivocara...
post Apr 15 2018, 01:33 AM
Post#3



Posts: 55
Joined: 2-April 18



1) First very if the recordset is empty, then move in it. You use RecordCount property. I usually write code like this
CODE
If Not rs.EOF Then
      Do Until rs.EOF
          ...
          rs.MoveNext
      Loop
   End If

2) Use a "With rs" and you code gains in efficiency
3) If you do it in a continuos form you will never get what you want.
Where is it that code? What event? What is the RecordSource?

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
RJD
post Apr 15 2018, 01:54 AM
Post#4


UtterAccess VIP
Posts: 8,224
Joined: 25-October 10
From: Gulf South USA


Hi: Suggestion ... replace the labels with textboxes, properly protected, and use Conditional Formatting to adjust the colors.

Just an idea ...

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
zaxbat
post Apr 15 2018, 02:40 AM
Post#5



Posts: 652
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


CODE
Dim DB As DAO.Database
Dim rs As DAO.Recordset

On Error GoTo ColorIndicator_Error

Set DB = CurrentDb
Set rs = DB.OpenRecordset("qry1PPMKPI_Sites", dbOpenDynaset)
If rs.RecordCount > 0 Then
   rs.MoveFirst
   Do While Not rs.EOF
      If rs!SiteName = "Kite" Then
         if rs!PPM >= 2500 Then
            Me.lblKitePPM.BackColor = "255" 'red
         Else
            Me.lblKitePPM.BackColor = "65280" 'green
         End If
      else
         If rs!SiteName = "Bella" Then
            if rs!PPM >= 2500 Then
               Me.lblBellaPPM.BackColor = "255" 'red
            Else
               Me.lblBellaPPM.BackColor = "65280" 'green
            End If
         End if
      End If
      rs.MoveNext
   Loop
End if
rs.close
set rs = nothing
set DB = nothing
This is just air code but should do what you need
I am hoping that your table does not have many records since you are only changing the colors on two different labels.
This post has been edited by zaxbat: Apr 15 2018, 02:45 AM

--------------------
A picture is worth a thousand words and a zipped DB is worth a thousand pictures.

Cheers! Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
Vector1
post Apr 15 2018, 09:30 AM
Post#6



Posts: 66
Joined: 9-March 17



Hi everyone,
Zaxbat your solution worked like a charm. At any given time there will be maximum of 5 records. Is there a better way to improve effeciency?

Thank you again.
Go to the top of the page
 
zaxbat
post Apr 15 2018, 09:07 PM
Post#7



Posts: 652
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


If you already have the table open and you are in a form then you do not need to open it on the side like this....I worry that the label colors will get out of sync with the form the way you are doing it. Of course, I have to make a lot of assumptions about how this looks on your screen....and I could easily be wrong.

RJD's idea of turning the labels into textboxes and using conditional formatting could work very well assuming the table is already open and connected to the form. But if you do that, make sure to set the textboxs to locked and no edit and no tablstop....this so the user cannot overwrite what text you put into the box.

--------------------
A picture is worth a thousand words and a zipped DB is worth a thousand pictures.

Cheers! Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 
Vector1
post Apr 18 2018, 08:54 AM
Post#8



Posts: 66
Joined: 9-March 17



zaxbat,
The table/query is not open which is why I am opening it on the side.
I have a dashboard which pulls data from several temp tables/queries to create these gauges, buttons, and charts (since I can't change color of a button, I am using a label, which on click will take the user to another dashboard). Can a textbox be a better idea? How, what to do for a textbox?
I am attaching a picture of the dash for you see what is on the dashboard. Your Suggestions are most welcome to improve the efficiency.
BTW is there a way to make the colors of the column chart match the colors of the buttons?

Thank you all for your time, effort, and feedback!!! It is greatly appreciated.
Attached File(s)
Attached File  Capture.GIF ( 36.94K )Number of downloads: 2
 
Go to the top of the page
 
zaxbat
post Apr 19 2018, 08:21 AM
Post#9



Posts: 652
Joined: 26-January 06
From: .....the wiregrass (either you know or you don't)


I'd probably stick with the VBA then... Conditonal formatting works pretty well in a case like yours but it is hidden and later you will have a hard time figuring out how you got those boxes to change color. Also, the expresions allowed in the conditional format can get pretty tricky. Might take all day to get it right.

--------------------
A picture is worth a thousand words and a zipped DB is worth a thousand pictures.

Cheers! Oh, and....please don't disappear into the Twilight Zone.... Holler back with your results!
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd April 2018 - 05:41 AM