Solved Update the value of a field based on two other fields

[Safaa]

Hi
I am new to the forum and also to learning Access

I want while typing in a field called

result

If the value that I write is less than the value in the field named

low

Or greater than the value in the field named

High

Two things happen:

1 - Color of the value in a field

RESULT

It change in to red color

The field background change in to yellow

The second thing is a field -called

state

It takes the value

L

If it is a value of

RESULT

less than

low



------------------------

and It takes the value

H

If more than

high

Exactly like the picture

 

[bob fitz]

Suggest that you take a look at "Conditional formatting" of your control on the form

 

[Safaa]

Suggest that you take a look at "Conditional formatting" of your control on the form

I want these changes to occur while writing and not when leaving the field, exactly as in the picture

 

[MajP]

Private Sub Result_Change()
  Dim rslt As Double
  If IsNumeric(Me.Result.Text) Then
    rslt = Me.Result.Text
    If rslt < Me.low Then
      Me.State = "L"
    ElseIf rslt > Me.high Then
      Me.State = "H"
    Else
      Me.State = Null
    End If
  Else
    Me.State = Null
    MsgBox "Result must be numeric", vbInformation
  End If

End Sub

 

[MajP]

When you type into a textbox the Value of the textbox is not updated until the textbox after update event. That does not occur until you leave the textbox. So prior to leaving the textbox the Text property and Value property are not the same. Therefore you have to use the Text property if you want to check immediately after typing.

 

[Safaa]

Else
Me.State = Null
MsgBox "Result must be numeric", vbInformation

I NEED THIS textbox ACCEPT TEXT AND NUMBERS

 

[Safaa]

SO I DLEAT THIS FROM CODE

 

[Safaa]

When you type into a textbox the Value of the textbox is not updated until the textbox after update event. That does not occur until you leave the textbox. So prior to leaving the textbox the Text property and Value property are not the same. Therefore you have to use the Text property if you want to check immediately after typing.

If I delete the value that I wrote in the text box, I want to change the status value to NULL

 

[MajP]

ACCEPT TEXT AND NUMBERS

How are letters between two numbers? Makes no sense.
How do you plan to determine if a letter is between two values? Can you give an example?
is "Dog" supposed to be between 70 and 110?

You can use Val if the numbers come first, but not if they come after the letters
?val("123A")
123
?val("abc123")
0

Maybe this

Private Sub Result_Change()
  Dim rslt As Double
  If (Me.Result.Text & "") <> "" Then
    rslt = Val(Me.Result.Text)
    If rslt < Me.low Then
      Me.State = "L"
    ElseIf rslt > Me.high Then
      Me.State = "H"
    Else
      Me.State = Null
    End If
  Else
    Me.State = Null
  End If
End Sub

If I delete the value that I wrote in the text box, I want to change the status value to NULL

The original code already did that.

 

[Safaa]

How are letters between two numbers? Makes no sense.

for example result of HCV Ab ( Positive 0.98 ) i need when i write positive, condition of field applied but when i write Negative 0.18 do no change for textbox

 

[Safaa]

in the Conditional formatting i put this control

[state] Like "*L*" Or [state] Like "*H*" Or [state] Like "*low*" Or [state] Like "*High*" Or [Result] Like "*Equi*" Or [Result] Like "*Pos*" Or [Result] Like "*+*" Or [Result] Like "*>*" Or [Result] Like "*<*" Or [Result] Like "*Few*"