Improve company productivity with a Business Account.Sign Up

x
?
Solved

Flexgrid Colour Question

Posted on 2004-10-10
7
Medium Priority
?
239 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: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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

606 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