?
Solved

Flexgrid Colour Question

Posted on 2004-10-10
7
Medium Priority
?
230 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
[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
  • 4
  • 3
7 Comments
 
LVL 12

Expert Comment

by:Bob Lamberson
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 12

Expert Comment

by:Bob Lamberson
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:
Bob Lamberson earned 1000 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:Bob Lamberson
ID: 12273419
StephenJnr,
glad to help - thanks for the acknowledgment
Bob
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…
Suggested Courses
Course of the Month15 days, 15 hours left to enroll

741 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