coding question

[Little pete]

Afternoon
Is the below code correct? - I am wanting to colour cells depending on the
data in them. Currently set up for wk1 - week 1, rather than doing this for
wk2-wk53 individual can I group them together some how?

Cheers


Option Compare Database

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case Me.wk1
Case "AM"
Me![wk1].BackColor = 16764057
Case "AS"
Me![wk1].BackColor = 32768
Case "D"
Me![wk1].BackColor = 8421504
Case "IC"
Me![wk1].BackColor = 255
Case "L"
Me![wk1].BackColor = 65535
Case "P"
Me![wk1].BackColor = 8388736
Case "QS"
Me![wk1].BackColor = 16711680
Case "R"
Me![wk1].BackColor = 52377
Case "SC"
Me![wk1].BackColor = 12632256
Case "SS"
Me![wk1].BackColor = 39423
Case "CT"
Me![wk1].BackColor = 10079487
Case Else
Me.wk1.BackColor = vbWhite
End Select
End Sub

 

[SteveS]

I also saw your post about formatting the months(easy coding??) controls fo
rthe calendar year and I've been thinking about how I might do it. I'm not
sure what your report looks like, but maybe this will help.

It appears that the names of the controls on the report is the same as the
control source. I think it was in one of the MVPS posts that a control should
have a different name from its control source (field name).

So for the months rename the controls txtMth1, txtMth2, ... txtMth12

And for the weeks, rename them as: txtWk1, txtWk2, txtWk3, ... txtWk53

Paste the following (mostly untested) code in the appropriate report:

'----------------------------------------------------------------
' for MONTHS
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim BkColour As Long
Dim i As Integer

For i = 1 To 12
Select Case Me.Controls("txtMth" & i)
Case "AM"
BkColour = 16764057
Case "AS"
BkColourkColor = 32768
Case "D"
BkColour = 8421504
Case "IC"
BkColour = 255
Case "L"
BkColour = 65535
Case "P"
BkColour = 8388736
Case "QS"
BkColour = 16711680
Case "R"
BkColour = 52377
Case "SC"
BkColour = 12632256
Case "SS"
BkColour = 39423
Case "CT"
BkColour = 10079487
Case Else
BkColour = vbWhite
End Select
Me.Controls("txtMth" & i).BackColor = BkColour
Next
End Sub
'----------------------------------------------------------------

' for WEEKS
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim BkColour As Long
Dim i As Integer

For i = 1 To 53
Select Case Me.Controls("txtWk" & i)
Case "AM"
BkColour = 16764057
Case "AS"
BkColour = 32768
Case "D"
BkColour = 8421504
Case "IC"
BkColour = 255
Case "L"
BkColour = 65535
Case "P"
BkColour = 8388736
Case "QS"
BkColour = 16711680
Case "R"
BkColour = 52377
Case "SC"
BkColour = 12632256
Case "SS"
BkColour = 39423
Case "CT"
BkColour = 10079487
Case Else
BkColour = vbWhite
End Select

Me.Controls("txtWk" & i).BackColor = BkColour
Next
End Sub
'---------------------------------------------------------------

HTH

Steve
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Afternoon
Is the below code correct? - I am wanting to colour cells depending on the
data in them. Currently set up for wk1 - week 1, rather than doing this for
wk2-wk53 individual can I group them together some how?

Cheers


Option Compare Database

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case Me.wk1
Case "AM"
Me![wk1].BackColor = 16764057
Case "AS"
Me![wk1].BackColor = 32768
Case "D"
Me![wk1].BackColor = 8421504
Case "IC"
Me![wk1].BackColor = 255
Case "L"
Me![wk1].BackColor = 65535
Case "P"
Me![wk1].BackColor = 8388736
Case "QS"
Me![wk1].BackColor = 16711680
Case "R"
Me![wk1].BackColor = 52377
Case "SC"
Me![wk1].BackColor = 12632256
Case "SS"
Me![wk1].BackColor = 39423
Case "CT"
Me![wk1].BackColor = 10079487
Case Else
Me.wk1.BackColor = vbWhite
End Select
End Sub

 

[Marshall Barton]

That code should work, nut there has got to be a better way
that specifying the color code and possible week values in
your VBA procedure. Since Access is a database system, how
about using a ColorCodes table with two fields, WeekCode and
ColorNumber? Then all your code below could be reduced to
something like this air code:

Me!wk1.BackColor = DLookup("ColorNumber", _
"ColorCodes", "WeekCode = '" & Me!wk1 & "'")

You can do all 53 weeks using a loop:

For k = 1 To 53
Me("wk" & k).BackColor = DLookup("ColorNumber", _
"ColorCodes", "WeekCode = '" & Me("wk" & k) & "'")
Next k

 

[Sam Hobbs]

Another possibility is a dictionary with WeekCode as the key and ColorNumber
as the item. Then the code could be reduced to something such as:

Me!wk1.BackColor = ColorCodes(Me!wk1)