Auto fill Based on other field value

  • Thread starter Thread starter Tom Scanlin
  • Start date Start date
T

Tom Scanlin

I'm trying to place a "Yes" value in a check box [CompleteTdy] if a
preceding form field [ActualCost] is > 0 (Don't ask me why I have to do
this, I realize I can create the value in a query but they want a check box
on the form...go figure)

Obviously, I must have missed something. Can you please lend a hand?

Private Sub ActualCost_Exit(Cancel As Integer)
Dim CompleteTdy As CheckBox
Dim ActualCost as Currency
CompleteTdy = IIf(ActualCost > 0, [CompleteTdy] = -1, CompleteTdy]= 0)
End Sub

Thanks
Tom
 
It depends on whether the checkbox is bound or unbound to a field.

If unbound, all you need to do is put:
= IIf(ActualCost > 0, -1, 0)

in the ControlSource of the checkbox.

If it is bound and you want to store the value in a field, change the code
as follows:
Private Sub ActualCost_AfterUpdate(Cancel As Integer)
If Me.ActualCost > 0 Then
Me.CompleteTdy = -1
Else
Me.CompleteTdy = 0
End If
End Sub

I do not recommend storing the value as this violates the 3rd Normal Form
and denormalizes your database causing the data integrity to be suspect.
 
Try

Sub ActualCost_AfterUpdate()

If [ActualCost] > 0 Then
[CompleteTdy] = -1
Else
[CompleteTdy] = 0
End If

End Sub

Hope this helps!

Howard

----- Tom Scanlin wrote: -----

I'm trying to place a "Yes" value in a check box [CompleteTdy] if a
preceding form field [ActualCost] is > 0 (Don't ask me why I have to do
this, I realize I can create the value in a query but they want a check box
on the form...go figure)

Obviously, I must have missed something. Can you please lend a hand?

Private Sub ActualCost_Exit(Cancel As Integer)
Dim CompleteTdy As CheckBox
Dim ActualCost as Currency
CompleteTdy = IIf(ActualCost > 0, [CompleteTdy] = -1, CompleteTdy]= 0)
End Sub

Thanks
Tom
 
Back
Top