Please help conditional formatting via vb

[mcclunyboy]

Hi,

I have a report - tried a lot of different ways, tried searching the web but I am clearly not getting something.....

I would like to turn the back colour of a rectangel red/green depending on the value of 1 of the textboxes for each record. If the value is higher than another text box then it will be red, if it is lower then it will show the rectangle as green.

I have opened the Page Load event procedure VB ...stuff....and have procceded to type the following:

Private Sub Report_Open(Cancel As Integer)
    Me.rect.BackColor = vbRed
End Sub

This turns it red....so now I want to add an IF statement -this is where I am going wrong, Access can't find the controls - WHY...I have spelt it right - should I be using the control name (text box) or the control source name (query field)

Private Sub Report_Open(Cancel As Integer)
        If Me.txt_app_scores.Value > 1000 Then
            Me.rect.BackColor = vbGreen
        End If

I have tried to use conditional formatting the usual way but for some reason it doesn't colour the boxes unless you click on them - I would prefer to fix it using VB anyway.

 

[jdraw]

See if this helps

http://www.techrepublic.com/blog/ms...l-formatting-in-access-forms-and-reports/7299

 

[pbaldy]

The appropriate place for code based formatting is the format event of the section containing the control (typically the detail section). You'll also need an Else clause for the base format.

 

[mcclunyboy]

Hi,

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
        If Me.txt_app_scores.Value > 1000 Then
            Me.rect.BackColor = vbGreen
        ElseIf Me.txt_app_scores < 1000 Then
            Me.rect.BackColor = vbRed
        End If
End Sub

In reality I want something like:

        If Me.txt_app_scores.Value > me.txt_orig_scores.value Then
            Me.rect.BackColor = vbGreen
        ElseIf Me.txt_app_scores < me.txt_orig_scores .value Then
            Me.rect.BackColor = vbRed
        End If

 

[pbaldy]

That test should work fine, and you don't need an ElseIf test, just an Else, given your either/or situation.

 

[mcclunyboy]

I just tried again with the following code - nothing happens but the report opens without error:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    
    If Me.txt_app_scores.Value > Me.txt_orig_scores.Value Then
            Me.rect.BackColor = vbGreen
    Else
        Me.rect.BackColor = vbRed
        End If

End Sub

A bit more detail perhaps:
- Report record source is a query
- Under the detail section I have the following txt boxes (report created via a wizrd)
- txt_app_scores
- txt_orig_scores
- The above text boxes have the sources from the query titled
- appeal scores total
- original scores total
- Both the fields are numerical, just normal numbers

 

[pbaldy]

Did you set a breakpoint and check the values at runtime?

 

[mcclunyboy]

I can see the value's of the textboxes at run time - the report produces them correctly. I tried using a breakpoint and it simply didn't complain.

The problem seems to stem around the fact the report can't see the text boxes ( I think...)...yet I am running the event within the format of the design section of the report and almost fully qualifying the controls...(me.txt_....) Am I missing something here?

 

[pbaldy]

Can you post the db here?