Conditional Formatting of Text Boxes 1

[Tiggaa]

I have been tasked to create an in-house resource allocation plan for my team. As we have multiple masters requesting the same resources.

The Recording of tasks, who they came from and their current status I have managed to sort out. However, next task is to plan allocation for the next year. I have decided to allocate on a weekly basis and so created 52 fields for each year.

In order to produce an easy to reference report I have created a report with 52 unbound text boxes in the detail section. I intend to change the color of if the box if their is planned activity for that task in that week. I have written the following code to change the textbox color:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.W01 = "AR" Then Me.T01.BackColor = DLookup("BackColor", "tblCalCode", "Code = 'AR'")
If Me.W01 = "CI" Then Me.T01.BackColor = DLookup("BackColor", "tblCalCode", "Code = 'CI'")
If Me.W01 = "CR" Then Me.T01.BackColor = DLookup("BackColor", "tblCalCode", "Code = 'CR'")
If Me.W01 = "DS" Then Me.T01.BackColor = DLookup("BackColor", "tblCalCode", "Code = 'DS'")
If Me.W01 = "QA" Then Me.T01.BackColor = DLookup("BackColor", "tblCalCode", "Code = 'QA'")
If Me.W01 = "QC" Then Me.T01.BackColor = DLookup("BackColor", "tblCalCode", "Code = 'QC'")
If Me.W01 = "QD" Then Me.T01.BackColor = DLookup("BackColor", "tblCalCode", "Code = 'QD'")

If Me.W02 = "AR" Then Me.T02.BackColor = DLookup("BackColor", "tblCalCode", "Code = 'AR'")
If Me.W02 = "CI" Then Me.T02.BackColor = DLookup("BackColor", "tblCalCode", "Code = 'CI'")
If Me.W02 = "CR" Then Me.T02.BackColor = DLookup("BackColor", "tblCalCode", "Code = 'CR'")
If Me.W02 = "DS" Then Me.T02.BackColor = DLookup("BackColor", "tblCalCode", "Code = 'DS'")
If Me.W02 = "QA" Then Me.T02.BackColor = DLookup("BackColor", "tblCalCode", "Code = 'QA'")
If Me.W02 = "QC" Then Me.T02.BackColor = DLookup("BackColor", "tblCalCode", "Code = 'QC'")
If Me.W02 = "QD" Then Me.T02.BackColor = DLookup("BackColor", "tblCalCode", "Code = 'QD'")

End Sub

Is this the most effect way of acheiving my goal, or have I missed a trick. (I have obviously only included the first two boxes to be formatted)

Thankyou in advance.

Tiggaa

 

[dhookom]

First, IMHO, your table structure is un-normalized. I would change it to store 52 records per year rather than a 52 fields for weeks. I would actually use start and end dates rather than a week number as this would provide better granularity in the future. Someone is bound to request resources for 3 or 10 days which would not fit your current structure. Begin and End dates could still be used for weekly.

I would then create a schedule similar to the Calendar Reports sample . Take a look at the crew rotation schedule which is a gantt type chart.

If you don't want to change your structure, you could use code like:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   Dim intWk as Integer
   Dim ctl as Control
   For intWk = 1 to 52
      ctl = Me("W" & Format(intWk,"00")
      ctl.BackColor = DLookup("BackColor", "tblCalCode", "Code = '" & ctl.value & "'")
   Next
End Sub

I believe you could pull all the colors into an array only once in the On Open of the report so you didn't have to use the very slow DLookup() multiple times.

Duane
Hook'D on Access
MS Access MVP

 

[Tiggaa]

Thank you so much for your assistance.

I used the code you linked to and amended it slightly to:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo MyErrorHandler 'body of code here

Dim lngDuration As Long 'days of tour
Dim lngStart As Long 'start date of tour
Dim lngLMarg As Long
Dim dblFactor As Double
Dim actstart As Long
Dim actend As Long

'put a line control in your page header that starts 1/1 and goes to 12/31

If Me.Start_Date < #1/1/2008# Then actstart = #1/1/2008# Else actstart = [Start Date]
If Me.End_Date > #12/31/2008# Then actend = #12/31/2008# Else actend = [End Date]

lngLMarg = Me.boxTimeLine.Left
dblFactor = Me.boxTimeLine.Width / 365
lngStart = DateDiff("d", #1/1/2008#, actstart)
lngDuration = DateDiff("d", Me.[Start Date], actend)

'set the color of the bar based on a data value
Me.txtName.BackColor = Me.ActivityColor
Me.txtName.Width = 10 'avoid the positioning error
Me.txtName.Left = (lngStart * dblFactor) + lngLMarg
Me.txtName.Width = (lngDuration * dblFactor)
Me.MoveLayout = False

MyErrorHandlerExit:
Exit Sub
MyErrorHandler:
Select Case Err.Number
Case 2427
MsgBox "There are no Activities Planned for this Year. Please Close Report", vbCritical, "No Record"
Case Else
MsgBox "Error Number: " & Err.Number & " Error Description: " & Err.Description
End Select
Resume MyErrorHandlerExit

End Sub

I do need a seperate report for each year, but it is so much better than I anticipated it would by.

Thank you