Form textbox change font colour

[chris-uk-lad]

Hi all,
Have a problem with a textbox in a form! I have several static textboxes which relates to a field inside linked table. In each of these i want them to change the text colour if the value is 100. Ive tried the below but doesnt work, assuming that syntax is slightly different for form text boxes?

Private Sub txtRatio1_BeforeUpdate(Cancel As Integer)
If (frmSummary.txtRatio1.Value = "100") Then
    txtRatio1.ForeColor = vbGreen
Else
    txtRatio1.ForeColor = vbRed
End Sub

Thanks

 

[namliam]

Have you tried using conditional formatting??

 

[chris-uk-lad]

Im afraid conditional formatting doesnt exist in Access 97 (of which im using, bit of a relic :x)

 

[missinglinq]

A couple of things. Without Conditional Formatting, you're only going to be able to do this in a Single View form. Without Conditional Formatting any change you make in formatting in a Continuous/Datasheet view form will be reflected in all instances of the control, i.e. all of the same named textboxes will format according to the value in the current record.

In a Single View form your code needs to be in the txtRatio1_AfterUpdate event and in the Form_Current event, not in the Before_Update event.

As a rule BeforeUpdate events are used to check values that have been entered and change them if necessary. AfterUpdate events are used for doing things according to what value has been entered, such as this type of formatting. The Form_Current event is necessary to insure that, when returning to the record, the text color is based on that record’s value rather than the value of the previous record.

Also, just to be sure, your code

frmSummary.txtRatio1.Value = "100"

indicates that txtRatio1 is defined as Text. If this field is Numeric instead of Text you'd need to lose the quotation marks around 100.

 

[chris-uk-lad]

Im a little confused with what you mention when you say:

"In a Single View form your code needs to be in the txtRatio1_AfterUpdate event and in the Form_Current event, not in the Before_Update event.In a Single View form your code needs to be in the txtRatio1_AfterUpdate event and in the Form_Current event, not in the Before_Update event."

As i see this as you replacing before_update with both afterupdate and form_current but how? The below is the code i have atm from the points your highlight (of course doesnt work still).

Private Sub txtRatio1_AfterUpdate(Cancel As Integer)
If (frmSummary.txtRatio1.Value = 100) Then
    txtRatio1.ForeColor = vbGreen
Else
    txtRatio1.ForeColor = vbRed
End Sub

 

[gemma-the-husky]

can i help?

what missinglinq is saying

1 form presentation

if you are presenting your data to show one item at a time, you can achieve what you want. If you are using a continous form, (which shows displays multiple items, normally a line at a time) then its harder

this is because you cannot show a field green on some rows, but red on others, in a continuous form, with A97 - with later versions, there is a mechanism to overcome this - conditional formatting

2. assuming you are only showing one item at a time, then after you evaluate the txtratio1

you can say (as you are doing)

[note that you dont need the form name - simply this syntax will work]

if txtratio1 = 100 then
etc
else
etc
end if

now, using the afterupdate event implies that you are physically typing a figure INTO the txtration box to do this - if you are populating the txtratio box in some other way, ie you are not actually manually changing the value on the current record (changing a record is not the same thing as merely navigating to a new record) then you will not have a afterupdate event. - even changing a value with program code does not produce an after update event. It is important to understand the different events, and the sequence in which they occur.

if you are storing this value in a table, as you move from record to record, you get instead a current event for each record ... which is why missinglinq was saying you may need this code in the currentevent as well as in the update event.


hope this helps

 

[chris-uk-lad]

The way my form works is 2 fields pull in values form a table, with the ratio working out the percentage. It is a report s no values will be manually edited so with i assume change the first line to read??

Private Sub txtRatio1_Form_Current(Cancel As Integer)

 

[BarryMK]

Sorry are you now saying that this is a report and not a form?

 

[chris-uk-lad]

Tis i form that i will be using to generate a summary, i used the term report in relation to what im using as

 

[gemma-the-husky]

the event you are looking for is the current event

click the top left of the form, then
properties, event, current (the first item)

if your ratio is calculated you may need to do the calc in here, to ensure the calc is done before it processes the event

it should just look like

Private Sub Form_Current()
txtratio = valuea/valueb
if txtratio = 100 then
... 'format red
else
... ' format green
end if
End Sub

you need to watch for
a) valueb being zero, as you will get a divide by zero run time error
b) calc value close to, but not exactly equal to 100, although you have probably considered this

------
the problem remains that if you are trying to deal with multiple examples within a form OR report you wil lhave formatting issues

 

[chris-uk-lad]

The above works fine, and upto now ive tested it a lil without coding the calc straight into the code.

Just a final little question, as i have 4 cells, id like to integrate the wildcard into txtRatio as to not have a procedure for each Ratio cell but i cant get * to work

 

[namliam]

To use a wildcard use Like instead of =

Tho wildcard work on REAL textvalues, not on numbers.
On numberfields use: If txtRatio >= 100 and txtRatio < 200 then
or: If txtRatio between 100 and 200

 

[chris-uk-lad]

I understand what your saying but i mean for the if statements with the Ratio, i.e txtRatio1 txtRatio2 txtRatio3 and txtRatio4 text boxes, applying the same colour change if statement without useing 4 statements.

 

[LPurvis]

I think it's just a slight misuse of terminology.
Is the concept you want to introduce as if you were using a wildcard on controlnames - e.g. txtRatio1, txtRatio2, txtRatio3, txtRatio4.

Two obvious choices spring to mind.
Create a generic procedure accepting a control or controlname as parameter and operate upon that (calling it multiple times for each control).
Or iterate through the controls in your single procedure.

e.g.

All in One

Sub OperateOnControls()
 
    Dim intI As Integer
 
    For intI = 1 To 4
        With Me("txtRatio" & intI)
            If .Value = 100 Then
                .ForeColor = vbRed
            Else
                .ForeColor = vbGreen
            End If
        End With
    Next
 
End Sub

Generic procedure

Sub OperateOnControl(pctl As Access.Control)
 
    If pctl.Value = 100 Then
        pctl.ForeColor = vbRed
    Else
        pctl.ForeColor = vbGreen
    End If
 
End Sub

which could be called as

OperateOnControl Me.txtRatio1
OperateOnControl Me.txtRatio2
OperateOnControl Me.txtRatio3
OperateOnControl Me.txtRatio4

or to blend the two

For intI = 1 to 4
    OperateOnControl Me("txtRatio" & intI)
Next

 

[chris-uk-lad]

As far as the colour aspect works, its great, yet io keep getting an overflow error on the Ratio, im guessing because im reducing the % worked out to 0 decimal places. Also shows #Num! when vales are 0 unless thats something to do with the sum im suing :x

Any workaround?

 

[LPurvis]

What code are you using for that aspect?

 

[chris-uk-lad]

In the Ratio cell i have the following control source:

=CInt([txtCompleted1])/(CInt([txtCarriedIn1])+CInt([txtReceived1]))*(100)

And included a format of fixed and decimal places of 0

 

[LPurvis]

Hi.

Yeah your format isn't relevant to the problem.
That you're using CInt means two things though. That you're prone to overflow for anything greater than 32K and are vulnerable to Nulls.
Simply using Int instead will alleviate both of those issues.

=Int([txtCompleted1])/(Int([txtCarriedIn1])+Int([txtReceived1]))*(100)

Bear in mind that rounding as you go can yield different results than just rounding the final result
i.e.
=Int([txtCompleted1]/([txtCarriedIn1]+[txtReceived1])*100

You might want to handle Nulls (though the nature of Null implies a lack of knowledge - so returning Null is often appropriate rather than a substitution) and of course division by zero - which I think was mentioned somewhere along the way...

e.g.
=Int([txtCompleted1]/IIF([txtCarriedIn1]+[txtReceived1] = 0, 1, [txtCarriedIn1]+[txtReceived1])*100

But again - you need to be aware of the substitutions so that they still make sense for your scenario.

Cheers.

 

[chris-uk-lad]

tried using just Int instead of CInt to see if that worked before considering the NULL factors yet still the same error occurs about overload. Maybe i could send a blank database with a table and the form for someone to look at?

 

[LPurvis]

Well blank is one thing - but what kind of values do you have in these fields in the calculations?

Expressions in Access will make assumptions about the datatype being returned - based on what datatypes are used - and what the minimum type returned could be.
You could coerce into a greater type such as Double - or for accuracy Currency.

Post the db anyway if you like.