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
Solved

Flexgrid Colour Question

Posted on 2004-10-10
7
226 Views
Last Modified: 2011-09-20
Hi there, having some problems with setting the background colour for different rowns in a msh flexgrid, the data is taken from an access database and what I am trying to do is change the background colour for each row, depending upon the data in the first field (skills). Then it would search through the recordset and any data that matched the skills coloumn in the flex grid would change colour to the colour provided in the recordset. eg

SKILLS            COLOUR
ARTEX            vbred
ELECTRICAL    vbblue
PLUMBER        vbyellow

So when it goes through the flexgrid any rows with ARTEX would be red.

The code im trying to use is below, not having any luck at the moment. Any help would be great

Dim lRow As Long, lCol As Long
Dim lOrgRow As Long, lOrgCol As Long
Dim lColor As Long
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database\ClientDetails.mdb;"
conn.Open

sqlstr = "Colours"

rs.Open sqlstr, conn, adOpenKeyset, adLockOptimistic

Do Until rs.EOF
MSHFlexGrid1.Redraw = False

lOrgRow = MSHFlexGrid1.Row
lOrgCol = MSHFlexGrid1.Col

For lRow = MSHFlexGrid1.FixedRows To MSHFlexGrid1.Rows - 1
MSHFlexGrid1.Row = lRow

If MSHFlexGrid1.TextMatrix(lRow, 1) = rs.Fields("Skill") Then
MSHFlexGrid1.CellBackColor = rs.Fields("Colour")
End If

For lCol = MSHFlexGrid1.FixedCols To MSHFlexGrid1.Cols - 1
MSHFlexGrid1.Col = lCol
MSHFlexGrid1.CellBackColor = rs.Fields("Colour")
Next lCol
Next lRow

MSHFlexGrid1.Row = lOrgRow
MSHFlexGrid1.Col = lOrgCol
MSHFlexGrid1.Redraw = True

Loop

rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
0
Comment
Question by:StephenJnr
  • 4
  • 3
7 Comments
 
LVL 12

Expert Comment

by:BobLamberson
ID: 12273122
Hi StephenJnr,

Past this code into a forms code and add a msh flexgrid to the form.
Option Explicit
Dim lRow, lCol
Private Sub Form_Load()
MSHFlexGrid1.TextMatrix(0, 0) = "ARTEX"
MSHFlexGrid1.TextMatrix(1, 0) = "ELECTRICAL"
MSHFlexGrid1.TextMatrix(2, 0) = "PLUMBER"

For lRow = 0 To MSHFlexGrid1.Rows - 1
   Select Case MSHFlexGrid1.TextMatrix(lRow, 0)
      Case "ARTEX"
         For lCol = 0 To MSHFlexGrid1.Cols - 1
            MSHFlexGrid1.Row = lRow
            MSHFlexGrid1.Col = lCol
            MSHFlexGrid1.CellBackColor = vbRed
         Next
         lCol = 0
      Case "ELECTRICAL"
         For lCol = 0 To MSHFlexGrid1.Cols - 1
            MSHFlexGrid1.Row = lRow
            MSHFlexGrid1.Col = lCol
            MSHFlexGrid1.CellBackColor = vbBlue
         Next
         lCol = 0
      Case "PLUMBER"
         For lCol = 0 To MSHFlexGrid1.Cols - 1
            MSHFlexGrid1.Row = lRow
            MSHFlexGrid1.Col = lCol
            MSHFlexGrid1.CellBackColor = vbYellow
         Next
   End Select
Next
End Sub

Hope this is helpful
Bob
0
 

Author Comment

by:StephenJnr
ID: 12273139
Thanks Bob, but what im trying to do is to take any value in the recordset, say if the colour for a certain skill changes it will change the colour on the flexgrid. instead of nesting the colours, i would like to keep the option of changing them open.
0
 

Author Comment

by:StephenJnr
ID: 12273196
I tried this with limited success, I only seem to get one line of colour. I just need it to go through each line of the flexgrid and change the colour, to the one assigned to it in the recordset.

Dim lRow, lCol
Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Database\ClientDetails.mdb;"
conn.Open

sqlstr = "Colours order by skill"
rs.Open sqlstr, conn, adOpenKeyset, adLockOptimistic

skill = rs.Fields("Skill")
colour = rs.Fields("Colour")


For lRow = 0 To MSHFlexGrid1.Rows - 1

        If MSHFlexGrid1.TextMatrix(lRow, 1) = skill Then
            For lCol = 0 To MSHFlexGrid1.Cols - 1
            MSHFlexGrid1.Row = lRow
            MSHFlexGrid1.Col = lCol
            MSHFlexGrid1.CellBackColor = colour
         Next
         lCol = 0
         End If
         Next


rs.Close
Set rs = Nothing
conn.Close
Set conn = Nothing
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 12

Expert Comment

by:BobLamberson
ID: 12273255
StephenJnr,
> skill = rs.Fields("Skill")
> colour = rs.Fields("Colour")

You need to loop through the rs and test each skill against the value in the flexgrid then set the colour value to match.


with rs
    do
        skill = .fields("skill")
        For lRow = 0 To MSHFlexGrid1.Rows - 1

            If MSHFlexGrid1.TextMatrix(lRow, 1) = skill Then
                For lCol = 0 To MSHFlexGrid1.Cols - 1
                MSHFlexGrid1.Row = lRow
                MSHFlexGrid1.Col = lCol
                MSHFlexGrid1.CellBackColor = colour
            'Next
         lCol = 0
         End If
         Next

        rs. movenext
        if .eof then exit do
    loop
end with
Bob
0
 
LVL 12

Accepted Solution

by:
BobLamberson earned 250 total points
ID: 12273262
StephenJnr,
sorry, you should also set the colour right below skill.
Bob
0
 

Author Comment

by:StephenJnr
ID: 12273282
Thanks bob for the quck help, much Thanks

Stephen Jnr
0
 
LVL 12

Expert Comment

by:BobLamberson
ID: 12273419
StephenJnr,
glad to help - thanks for the acknowledgment
Bob
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

829 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