weekday colour formatting

[hair]

Hi people

I try to change the color of a data field on a form depending on weekday, so it would be friendlier and easier to manage.
I am talking about a form with like 10-20 records. Now, its working but instead of changinf just the field that I want it changes all. I wrote this test code, maybe you see what's wrong with it:

Why cant I do this directly from conditional formating saying something like: if the value of the field =vbmonday then blabla

Sub colours()
Dim lngRed As Long, lngYellow As Long, lngWhite As Long

Dim db As DAO.Database
Dim s As DAO.QueryDef
Dim r As DAO.Recordset
Set db = CurrentDb

lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)


Set r = Forms!frmplanclient.Form.Recordset
r.MoveFirst
If r.EOF = False Then
Do While r.EOF = False

If Weekday(r(5)) = 1 Then 'or vbmonday or something
Me!dateplan.BackColor = lngYellow

End If

r.MoveNext
'??
Me.dateplan.BackColor = lngWhite

Loop

End If
r.MoveFirst
Set r = Nothing


End Sub

 

[namliam]

if you want to change the current record on the current form there is no need to do all that with the recordset....

1) Your form is in table (continious) view, and your using A2K or later use Conditional formatting.
Using A97 or earlier, to bad, cant be done.... (Maybe with addins or something, but i wouldnt know about that)

2) Your form is in form view (single record visible)....
In your on current use something like...
Dim lngRed As Long, lngYellow As Long, lngWhite As Long
lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)

If Weekday(me.dateplan) = 1 Then 'or vbmonday or something
Me.dateplan.BackColor = lngYellow
End If

NOTE the Me. instead of Me!, its preverable to use Me. try it you will like it....!!!!

Regards

 

[Mile-O]

Dim lngRed As Long, lngYellow As Long, lngWhite As Long
lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)

Make Public Constants

 

[hair]

thank you guys

Dim lngRed As Long, lngYellow As Long, lngWhite As Long
lngRed = RGB(255, 0, 0)
lngBlack = RGB(0, 0, 0)
lngYellow = RGB(255, 255, 0)
lngWhite = RGB(255, 255, 255)
If Weekday(me.dateplan) = 1 Then 'or vbmonday or something
Me.dateplan.BackColor = lngYellow
End If

ain't work. I used it on opening event and it didn't change nothing. I already tried it like this..

Actualy where is "on curent use", Mailman??
I have the french version of office xp and I looked everywhere except under its skirt for this event.

Id prefere to do it with conditional formating, I feel like its better to take the easy way if its possible...

And about Me. against Me! (the bang, right) its obviously better cause you see the properties..I read an article about it, I think the source I found it on this forum..

Thanks again ppl

 

[hair]

I forgot to tell you: it just colors everything in red or whatever colour I tell ...

 

[jfgambit]

Here is a dirty example of what I believe you are looking for...

To show the change over I added a hidden textbox date field to the form with different dates of the week.

You can change the hidden textbox Default value to be Date() in your form.

Then the colors will change based on the day of the week.

HTH

 

[hair]

Actualy the form looks like this one that I send you now

Thanks for help

 

[Rich]

Post a 97 ver of your db

 

[hair]

right away Rich

 

[Rich]

You cannot use vba to change individual record colours on a continuous form, you have to overlay textboxes and use Iif statements. I've done three as an example, you can do the rest

 

[hair]

You cannot use vba to change individual record colours on a continuous form ... said Rich

Frustrating

Thanks a lot

 

[namliam]

Unless offcourse your talking A2K or newer, then you have conditional formating (not sure if that applies to background as wel as individual textboxes/objects...

Regards

 

[hair]

I have Office xp and I tried but it didn't work - it might be wrong what I've tried