Format Number in field based on previous field

Good Morning:

I have to reflect positive and negative currency amounts from a table called tblDocument with an "Amount" field. What is the simplest way for it to automatically generate a negative figure based on the previous called "DebitCredit" with the options of "C" or "D?" Additionally, how would I change the color to green and bold text if it is a positive figure? The form I'll be inputting into is called "frmDocTransaction." Thanks in advance for your help.

Hi Tish,

If you format your calculated textbox field as Currency, then negative numbers will display with () around the value...Example ($5.00) would be the same as a negative $5.00

To make the text bold and the color green for positive values, either on the On Current of the form or in the after update event of the field that triggers the calculation

If NameofTextBox.Value > 0 Then
With NameofTextBox
.ForeColor = 32768
.FontWeight = 700
End With
Else
With NameofTextBox
.ForeColor = 0
.FontWeight = 400
End With
End If
Use the name of your field that holds the calculated value in place of what is bold and in italics above.

lwells

Is there any way of automatically generating negative figure in the "amount" field once you tab into the field if the based on the choice of "D" in the creditdebit field prior to it (vice manually entering "-")? Thanks!

Hi Tish,

Change your Amount field from the Format of Currency to a custom format as follows:

$#,##0.00;-$#,##0.00[Red];

Then use the following code

If Selection = "D" Then
Amount.Value = Amount.Value / -1
ElseIf Selection = "C" Then
Amount.Value = Amount.Value
End If

This will place a "-" in front for the values of negative numbers and will be displayed in Red color assuming that "D" is for a debit and is to be displayed as a negative number.

lwells

Thanks again for your help on this one!