Conditional formating VBA

[SoggyCashew]

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. 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

 

[Beetle]

The problem you're going to have with this is that a continuous form is really
just one row of controls repeated over and over with different data. So, if
you
use VBA to set, for example, a green background on "Text2" for the current
record, then "Text2" is going to have a green background on every row in
the form, whether the data in the other rows meets the condition or not.

You don't have this issue when you use the standard conditional formatting
but, as you mentioned, you've got too many conditions to use that. So yes,
you may be fighting a losing battle here.

Maybe someone else knows a work around. I believe Stephan Lebans has
a procedure for alternate colors of individual *rows* at his site, but I don't
recall ever seeing any procedure that will do what you are wanting.

 

[SoggyCashew]

I had found this site/ sample dabase but I dont quite understand how I can
incorporate it into mine.

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

 

[Beetle]

I hadn't seen that before. It's an interesting solution to the problem, but I
don't think it's going to work for you. Basically, you need an unbound textbox
for each possible back color and fore color, plus one for a standard back
color if no condition is met. Then you stack them all on top of one another
an programmatically change the tranparency depending on conditions.
(actually, in the text boxes for the "back color", what you are actually doing
is setting the fore color and using a special font that fills the whole text
box
so it gives the appearance that the back color has changed)

In your case (as far as I can tell) you have 16 possible back colors and 2
possible fore colors. Add in the other necessary text boxes and that's 20
text boxes boxes stacked for each of your fields. You have 31 fields, so you
would need over 600 controls. Even if you were able to manage the sheer
complexity of this, Access has a limit of 255 controls on a form, so it
wouldn't
be possible anyway.

 

[SoggyCashew]

Sean, What about the code below? It looks to me like it should work but im
learning so.... I tried it and it does nothing with no errors? It should go
threw each of the text boxes and if there is a "V" "PD" ect then it should
color the text box right?Thanks!

Public Sub SetCalendar()
Dim Bclr As Long, Fclr As Long
Dim i As Integer

For i = 1 To 31

Select Case Me.Controls("Text" & i)
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
Next i
End Sub

 

[Beetle]

That's not going to work. For starters, although you have declared the
variables Bclr and Fclr, nowhere in your code do you stipulate that you
want a particular controls BackColor/ForeColor property to be equal to
Bclr/Fclr. There may be other things wrong also, I haven't looked at it
that closely. I have posted some code below that will color the text boxes
how you want, however, you need to keep in mind that since this is a
continuous form, neither my code nor your code (if you can get it to work
the way you want) is going to solve the problem that the formatting for
the current row is going to be applied *all* rows, regardless of the data
in those other rows. You seem to be setting up some type of calendar, so
I'm curious why you don't use Single Form view and layout the text boxes
to look like an actual calendar. That would solve the problem.

Anyway, here is the code;

Dim ctl As Control

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
Select Case ctl
Case "V"
ctl.BackColor = 438366: ctl.ForeColor = 0
Case "PD"
ctl.BackColor = 16711680: ctl.ForeColor = 16777215
Case "UH"
ctl.BackColor = 16633344: ctl.ForeColor = 0
Case "ET"
ctl.BackColor = 8421504: ctl.ForeColor = 16777215
Case "EA"
ctl.BackColor = 65535: ctl.ForeColor = 0
Case "WH"
ctl.BackColor = 16777164: ctl.ForeColor = 0
Case "UA"
ctl.BackColor = 255: ctl.ForeColor = 16777215
Case "UT"
ctl.BackColor = 16711935: ctl.ForeColor = 16777215
Case "ELE"
ctl.BackColor = 65535: ctl.ForeColor = 0
Case "PC"
ctl.BackColor = 26367: ctl.ForeColor = 16777215
Case "DL"
ctl.BackColor = 16776960: ctl.ForeColor = 0
Case "ML"
ctl.BackColor = 128: ctl.ForeColor = 16777215
Case "FL"
ctl.BackColor = 65280: ctl.ForeColor = 0
Case "PL"
ctl.BackColor = 10092543: ctl.ForeColor = 0
Case "JD"
ctl.BackColor = 52479: ctl.ForeColor = 0
Case "FL"
ctl.BackColor = 13408767: ctl.ForeColor = 0
Case Else
ctl.BackColor = vbWhite: ctl.ForeColor = vbBlack
End Select
End If
Next ctl

 

[SoggyCashew]

Beetle, Yes it is a calendar but its only to view information that was
already placed in the database. Here is the database I uploaded to Access
World Forums "Last Post is up to date". It doent have what im wanting to do
though. I wanted to have a way I can view the entire year for the employee
that is selected. Mabe by a button or something...

http://www.access-programmers.co.uk/forums/showthread.php?p=749883#post749883

 

[Beetle]

I had a look at that db, so i understand what you are trying to do, but here
is something to consider.

What you seem to be trying right now is to set up a continuous form where
each record shows every day of a different month, and there would be data
in the fields where an employee was absent. The problem with this method
is that for the majority of days during the year the employee is not going
to be absent, so the majority of your fields would be empty.

Rather than doing that, why not create a query like the following;

"SELECT tblUser.UserID, tblInput.InputDate, tblInput.InputText,
tblInput.AttendanceReason FROM tblUser INNER JOIN tblInput
ON tblUser.UserID = tblInput.UserID WHERE tblUser.UserID="
& [forms]![frmCalendar]![cboUser]

This query would only show days where the employee was absent
with the records limited to the employee that is currently selected in
the User combo box on your Calendar form. Each record would be a
different date. The records could be further limited to only a specific
year by using an input box or some other method.

The only controls you would need on your form would be txtInputDate,
txtInputText and txtAttendanceReason. Then you probably could implement
the method at

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

to display different colors for InputText and AttendanceReason on each record.
It would still be tricky because you have so many possible conditions, but it
would at least be possible.

You could use a command button on frmCalendar to open this form.

 

[SoggyCashew]

Beetle, I submitted a test Year View to the forum as well. This is the view I
have been wanting. By reading your post I do have what you are talking about
in the DB already. Just select one of the buttons on the left but have the
year in question selected and employee. Or you can use the date range
selectoion at the bottom but you also have to select the employee in question
but you can choose a date you want. I wanted something that would show me a
view of the whole year in a grid like the example insted of a list like what
I already have. It would be nice to do this in a report instead of a form so
I can print out a view of a year for an employee.

Here is the link again..

http://www.access-programmers.co.uk/forums/showthread.php?p=750038#post750038
--
Thanks,
Chad

I had a look at that db, so i understand what you are trying to do, but here
is something to consider.

What you seem to be trying right now is to set up a continuous form where
each record shows every day of a different month, and there would be data
in the fields where an employee was absent. The problem with this method
is that for the majority of days during the year the employee is not going
to be absent, so the majority of your fields would be empty.

Rather than doing that, why not create a query like the following;

"SELECT tblUser.UserID, tblInput.InputDate, tblInput.InputText,
tblInput.AttendanceReason FROM tblUser INNER JOIN tblInput
ON tblUser.UserID = tblInput.UserID WHERE tblUser.UserID="
& [forms]![frmCalendar]![cboUser]

This query would only show days where the employee was absent
with the records limited to the employee that is currently selected in
the User combo box on your Calendar form. Each record would be a
different date. The records could be further limited to only a specific
year by using an input box or some other method.

The only controls you would need on your form would be txtInputDate,
txtInputText and txtAttendanceReason. Then you probably could implement
the method at

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

to display different colors for InputText and AttendanceReason on each record.
It would still be tricky because you have so many possible conditions, but it
would at least be possible.

You could use a command button on frmCalendar to open this form.

--
_________

Sean Bailey

Beetle, Yes it is a calendar but its only to view information that was
already placed in the database. Here is the database I uploaded to Access
World Forums "Last Post is up to date". It doent have what im wanting to do
though. I wanted to have a way I can view the entire year for the employee
that is selected. Mabe by a button or something...

http://www.access-programmers.co.uk/forums/showthread.php?p=749883#post749883