Writing VBA Code to control the background color of a form field

[kjonescci]

I am new at writting VBA Code. I am building a database to track employee
trainings. I have the form set up with the training, the date taken and the
date due for re-training. I want to write a VBA Code so that 30 days befor
the date due the field will have it's fill color turn yellow. When the date
due is passed I want the field color to turn red. Like I said I am new to
VBA, I don't even know where to start writting a code that will perform this
function. Any suggestions? Any help is appreciated.

 

[Linq Adams via AccessMonster.com]

In Form Design View, select the textbox then goto

Format - Conditional Formatting

In the first dropdown box select "Expression Is"

in the nexrt box enter

DateDiff("d",[T_R_Date],Date())<30 And DateDiff("d",[T_R_Date],Date())>-1

and select the formatting you want (yellow for the BackColor)

Hit "Add"

Again select "Expression Is"

and in the next box enter

[T_R_Date] >Date()

Now set the BackColor to green and hit OK.

substitute your actual control name for T_R_Date and be sure to place the
square brackets around the name, as shown.

 

[Jack Leach]

This probably won't be all-inclusive, but here's some of the fundementals.

First off, you need an event of some sort that will fire the code you want
to run. Access forms (and controls) have a number of events that fire when
certain things happen. The one you are probably going to need here is the
OnCurrent event. Any code inside this procedure will run every time the
current record is changed. So when you switch to a new record, you'll have
code inside the Current event procedure that will evaluate your dates and and
set the color of the control accordingly. You can find Events in the
properties popup of forms and controls in design view (the OnCurrent event
will be under the Form properties, not in the events for contorls). Select
OnCurrent and hit the little "..." button to the right. The VBE window
should pop up with a couple lines like so:

Private Sub Form_Current()

End Sub



Anything between these two lines will run when the form records are navigated.

Next, you need to be able to refer to your control and its properties. Use
the Me keyword in VBA to refer to the current form (notice that this code is
located in the form's module). The line will look something like this:

Me.ControlName

When you type "Me." you should see a list of all available controls, methods
and properties that pertain to this particular form. So if your Name
property is ctlDate, you would type:

Me.ctlDate

Then type another "." after ctlDate, and you should see a list of properties
that you can access. I'm not positive what the property you need is called,
but for this example we'll pretend it's FillColor. So...

Me.ctlDate.Fillcolor = 01562485

This would set the FillColor property to 01562485 (I don't think this is a
valid color though... you'll want to pull the correct value from the
properties page in design view).

So if all you wanted to do was set a color, you would wind up with something
like this:

Private Sub Form_Current()
Me.ctlDate.FillColor = 04652154
End Sub



Now, you need a conditional (If/Then) statement to decide if the color
should be changed or not. Here's the syntax:

If <expression> = <value> Then
'Do something
Else
'Do something else
End If


(Else is an optional statement)

Consider the following:

Private Sub Form_Current()
If <current date> = <somedate> Then
Me.ctlDate.Fillcolor = 0465185
End If
End Sub


Now you need to fill in <current date> with the current date (which is
returned by the Date() function), and <somedate> with the date in your
control. You may end up with and evaluator such as "<=" (less than or equal
to) rather than "=". <your date> is going to be the control that holds the
current date for that record. So then we have:


Private Sub Form_Current()
If Date() = <somedate> Then
Me.ctlDate.Fillcolor = 0465185
End If
End Sub



In your case, you want this to change If the current date is greater than
the due date:


Private Sub Form_Current()
If Date() > Me.ctlDate Then
Me.ctlDate = 01565485
End If
End Sub



Finding the one within 30 days is a little more complicated... I think
you'll need to use the DateAdd() function to see if the current date falls
within the 30 days of the date in your control. Date values are pretty
tricky to work with... use the built in help, it's a great resource.

Like I said, this hardly covers everything, but it may give some ground for
starting off. Here's a link that's highly recommended by many:

http://www.accessmvp.com/Strive4Peace/


Read this, buy a book, practice, and feel free to come back with any
specific questions that you can't seem to get. There's plenty of people here
willing to help.


hth

--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)

 

[Lynn Trapp]

Put something like this UNTESTED code in the OnCurrent event of your form.

If DateDiff("d",[DueDate],Date()) <=30 AND DateDiff("d",[DueDate],Date())

=0 Then

Me.txtDueDate.BackColor = vbYellow
ElseIf DateDiff("d",[DueDate],Date()) < 0 Then
Me.txtDueDate.BackColor = vbRed
Else
Me.txtDueDate.BackColor = vbWhite
End If