Flexgrid Colour Question

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
StephenJnrAsked:
Who is Participating?
 
Bob LambersonConnect With a Mentor Software EngineerCommented:
StephenJnr,
sorry, you should also set the colour right below skill.
Bob
0
 
Bob LambersonSoftware EngineerCommented:
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
 
StephenJnrAuthor Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
StephenJnrAuthor Commented:
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
 
Bob LambersonSoftware EngineerCommented:
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
 
StephenJnrAuthor Commented:
Thanks bob for the quck help, much Thanks

Stephen Jnr
0
 
Bob LambersonSoftware EngineerCommented:
StephenJnr,
glad to help - thanks for the acknowledgment
Bob
0
All Courses

From novice to tech pro — start learning today.