VBA conditional formatting

[johnlatona123]

hi all!

im having some trouble doing some types of conditional formatting in VBA, let me lay out the scenario here and we can go from there.

i have a report that requeries itself every 30 seconds as an updating display type of deal.

there are forms within the database that contain pulldown values that currently control some color changing action based on nn-vba conditional formatting. the catch is, that your average user cant easily update the conditional formatting, so i am trying to do up the referencing in vba to incorporate additional tables/forms into the mix so that this can run in runtime mode.

heres what i am trying:

Private Sub Report_Timer()
n = n + 1
Me.Requery
SendKeys spacebar

If Me![job status] = [RED]![RED] Then
Me![Text58].BackColor = 16711680
End If

If Me![job status] = [GREEN]![GREEN] Then
Me![Text58].BackColor = 3394611
End If

End Sub

[job status] is the pulldown field that gets changed by users while in runtime mode

[text58] is the field i want to change the color of, dependant upon the value of [job status]

and my latest idea was to create tables that contain 1 column. these tables(one for each color) are named by the color name and contain a specific set of matching job status'.

so for example my table with form interface is named [RED] and the column within is named [RED] and contains in that column job status a, b, and c.

whereas table with form interface named [GREEN] has a column named [GREEN] and in that column contains job status job status d, e and f.

what i think the problem is that the code isnt recognizing the available values in the color named tables in order to be able to match it up with the [job status] field to perform the coloring.

so yea, thats a wierd mess im sure, but please let me know if there is any way to accomplish what i am doing. my code method is not working and i want to get away from things that cant be edited in runtime mode.

thanks!

 

[Trevor G]

Colouring a report can't be done on timer it should be done on the detail section and print, example I use to highlight something when it has been paid is as follows:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If Me.Date_Paid.Value <> 0 Then
Me.Date_of_Invoice.ForeColor = RGB(255, 0, 0)
Else
Me.Date_of_Invoice.ForeColor = vbBlack
End If
End Sub

 

[johnlatona123]

thank you for the reply.

i cant have this set up in print preview. it needs to remain in report view.

ok the not on timer thing i understand that, but i do need the report to continue to requery whereas maybe the coloring could then occur after its loaded or something?

the values in the [job status] field are words such as "running" or "setting up"