Conditional Formating VBA 2

[oxicottin]

Hello, I have a row (1-31) of text boxes in a continious form starting with
Text1, Text2, Text3 and so on. now I need to change the color of the Text box
to whatever attendance code is in it. Lets say it has a V in the box then it
would change the Text box to green or if it had EA in it then I want the
color to be yellow and so on. I would use conditional formating but I have
several options. Something like below is what im looking for but I cant figure out how t ogo about it or write it. If I use
something like Me.Controls ("Text") would that work or is this a loosing
battle?

Select Case
           Case "V"
              Bclr = 438366: Fclr = 0
           Case "PD"
              Bclr = 16711680: Fclr = 16777215
           Case "UH"
              Bclr = 16633344: Fclr = 0
           Case "ET"
              Bclr = 8421504: Fclr = 16777215
           Case "EA"
              Bclr = 65535: Fclr = 0
           Case "WH"
              Bclr = 16777164: Fclr = 0
           Case "UA"
              Bclr = 255: Fclr = 16777215
           Case "UT"
              Bclr = 16711935: Fclr = 16777215
           Case "ELE"
              Bclr = 65535: Fclr = 0
           Case "PC"
              Bclr = 26367: Fclr = 16777215
           Case "DL"
              Bclr = 16776960: Fclr = 0
           Case "ML"
              Bclr = 128: Fclr = 16777215
           Case "FL"
              Bclr = 65280: Fclr = 0
           Case "PL"
              Bclr = 10092543: Fclr = 0
           Case "JD"
              Bclr = 52479: Fclr = 0
           Case "FL"
              Bclr = 13408767: Fclr = 0
        End Select

--
Thanks,
Chad

 

[NXMold]

I have the same issue, I need to set five colors (white, cyan, yellow, orange, and red) but only three conditional formats exist, plus the base format. At the moment I skipped the cyan color because I cannot find a workable solution.

Setting the background color in code in a continuous form will set every row to that color. You must use conditional formatting, and submit to its limitations.

The only other option I came across in my searching was an archaic workaround, I don't have a link handy so I'll describe it. Using a textbox with a transparent background, set the FONT color with conditional formatting, and use the format property to fill the control with a repeating character, using a large font that will "fill in" the whole control. Then you can stack these text boxes gaining access to additional conditional formatting conditions. When the value does not meet one of your three formats, the fill character is eliminated allowing the next control to be visible.

 

[Remou]

Depending on you requirements, it may be possible to use a single form with a suitable number of textboxes. The form can be filled and updated via code.

 

[oxicottin]

I had found this site with an example but I dont know how to incorperate it into mine? Thanks!

http://www.accessdb.info/content/view/116/40/

 

[oxicottin]

Here is a sample DB that im trying to do this with....

 

[MajP]

If it was me, I think I would make this an unbound form. I would name my controls like

J1, J2...J31
F1,F2...F31 (?f29)
MR1,M2...

D1,D2...D31


Its a lot of controls but then you have a lot of flexibility.

A couple of loops and you can read and write from these controls pretty easily. It looks like it is only for display purposes. If that is correct then this would be very easy.

 

[PHV]

MajP, are you sure you may have 365 (?366) controls in a single form ?

 

[MajP]

I do not think it would be too bad unless there is going to be a lot of functionality to this form.

1) I would definitely build the form in code, at least to put the 12 X 31 textboxes on the form and give them a good naming convention. This way once I build the form I can ensure that I am not exceeding the control limit of visible + deleted controls by starting with a clean form.

2) If these controls are going to react to events I have a faq on simulating a control array.

If the naming convention is good everything could be done with loops.

 

[fneily]

From the table names, I recognize it as the Event Calendar that's referenced alot. In fact, I'm been using it for 5 years.
You deviated from the original code. Agreeing with MajP, I'd recreate your Yearly View form with unbound textboxes - a complete layout - sort of like the original monthly form. Then on the AfterUpdate event of the combobox, still do your pickoff. But this time open up a recordset. Loop through the recordset of the user's records and, using the dates, then fill in and change the color of the date boxes. Actually, you if still have the original code of the Event Calendar, most of the code and date functions are there.

 

[fneily]

Forgot. Get rid of the tblDisplay table. So wrong. Your tables will be:
tblCalInput
AttendanceCode
Description

tblUser
UserID
other info fields

tblDaysoff
DOID
UserID
Date
AttendanceCode
AttendanceDes

tblDaysoff will be the main table to loop through.

 

[MajP]

Here is some code to build your grid of 12X31 properly named textboxes

Public Sub buildForm()
  Dim frm As Access.Form
  Dim cntl As Access.TextBox
  Dim lft As Double
  Dim tp As Double
  Dim wth As Double
  Dim ht As Double
  Dim intCount As Integer
  Dim monthCounter As Integer
  Dim aMonths() As String
  aMonths = Split("JN,FB,MR,AP,MY,JU,JY,AG,SP,OT,NV,DC", ",")
  
  lft = 0
  tp = 0
  wth = 0.2
  ht = 0.2
  
  Set frm = Application.CreateForm
  frm.Visible = True
  frm.Width = getTwips(11)
  For monthCounter = LBound(aMonths) To UBound(aMonths)
   'Debug.Print monthCounter
    For intCount = 1 To 31
      Set cntl = Application.CreateControl(frm.Name, acTextBox, acDetail, , , getTwips(lft), getTwips(tp), getTwips(wth), getTwips(ht))
      cntl.Visible = True
      cntl.Name = aMonths(monthCounter) & intCount
      lft = lft + wth + 0.05
    Next intCount
  lft = 0
  tp = tp + (ht) + 0.05
  MsgBox tp
  Next monthCounter
End Sub

Public Function getTwips(dblInches As Double) As Long
  getTwips = dblInches * 1440
End Function

 

[MajP]

If you build the form above using the naming convention this code fills the form.

Public Sub fillForm()
  Dim rs As DAO.Recordset
  Dim strMonth As String
  Dim intDay As Integer
  Dim strText As String
  Set rs = CurrentDb.OpenRecordset("qryInput")
  
  Do While Not rs.EOF
    strMonth = Format(rs!inputDate, "MMM")
    intDay = Day(rs!inputDate)
    strText = rs!AttendanceCode
    Forms("frmUnbound").Controls(strMonth & intDay) = strText
    rs.MoveNext
  Loop
  
End Sub

change this in the original code

aMonths = Split("JAN,FEB,MAR,APR,MAY,JUN,JUL,AUG,SEP,OCT,NOV,DEC", ",")

The trick is to modify qryInput before calling the code using the year and user as criteria

 

[oxicottin]

Majp thank you .......................

 

[MajP]

Not sure if you figured out the "formatting", but here is how I would do it.

1)Select your grid
2)In the tag property put "?" or some other text for each control in the grid
3)Use the controls collection of the form
The below is untested so there may be some syntax errors. I do not have access, but the concept works.

Dim cntrl as access.control
for each cntrl in me.controls
if me.cntrl.tag = ? then
'your select case here
cntrl.backcolor = bclr
cntrl.forecolor = fclr
end if
next cntrl

 

[fneily]

The original can be found here:

http://rapidshare.com/files/123940087/VacTracker2000.zip.html

So then you can look at the code.
I add code to highlight dates in red when certain conditions were met. If you keep the texboxes names consistent, then a simple loop will do. Here's a snipett of the code that changes a date to red:

sql2 = "SELECT * FROM tblInput WHERE (((month([InputDate])) = " & f!Month & " AND ((Year([InputDate])) = " & f!Year & ")" _ & " And ((InputText) = '" & dday & "' ))) ORDER BY InputDate;"

Set db2 = CurrentDb()
Set rs2 = db2.OpenRecordset(sql2, dbOpenSnapshot)
If rs2.RecordCount > 0 Then
For i = 1 To 37
strdept = ""
If IsDate(f("date" & i)) Then
rs2.FindFirst "inputdate=#" & f("date" & i) & "#"
If Not rs2.NoMatch Then
f("text" & i) = " "
f("text" & i).BackColor = 255
f("text" & i).FontBold = True
f("text" & i).FontSize = 9
If rs2![admin] = True Then f("text" & i) = strdept & " " & "Adm." & " "
strdept = f("text" & i)
If rs2![safety] = True Then f("text" & i) = strdept & " " & "Sfty" & " "
strdept = f("text" & i)
etc.
If rs2![all] = True Then f("text" & i) = "ALL"
End If
End If
Next i

 

[oxicottin]

fneily, The rapidshare example DB is what I based my Employee Attendance DB off of. I just wanted to somehow add a view of the year that was selected in my Attendance DB so you can see the whole year not just a month at a time. I just wanted to use it as a visual not a data entry. Here is a link to my Employee Attendance database. Its my last two posts that are the up to date versions. Thanks!

http://www.access-programmers.co.uk/forums/showthread.php?t=154067