automatically change other fields' properties after a calculated filed changes

[moyi]

Hello, I have a question about how to automatically change other field's properties when a filed changes without clicking.

on the form, I have a field called "DIADL". it is a sum of D1 to D7.
If the sum of D1 to D7 equals to 24, then fields D9 to D15's won't be seen (acPropertyVisible = 0) otherwise, they should be seen and be entered values.

My code is here:
If DIADL = 24 Then
Dim i As Integer
For i = 9 To 15
DoCmd.SetProperty "D" & i, acPropertyValue, 3
DoCmd.SetProperty "D" & i, acPropertyVisible, 0
Next
Else
Dim j As Integer
For j = 9 To 15
DoCmd.SetProperty "D" & j, acPropertyVisible, -1
Next
End If

The code works when I use DIADL onclick event. However, I don't want users to click this filed. The field would be invisible at the final form and whenver the user change any values of D1 to D7, D9 to D15's visible property should be automatically changed accordingly. I wonder which event I should use? I have tried afterupdate event and change event, none of them work.

Many thanks!

 

[Mark_]

Add your own sub to handle the display fields. I'd use the following, commented so you remember what its used for.

Private Sub TCO_DisplayFields

Dim afOn as Byte ' Turn fields On and Off
Dim aiLoop as Integer 'Counter for looping through controls.

afOn = True ' Assume we turn on controls.
If DIADL = 24 Then afOn = False 'but if our total is 24 turn them off.

For aiLoop = 9 To 15 ' Loop through controls 9 to 15
   if afOn = False 'If they need to be turned off, turn them off.
      DoCmd.SetProperty "D" & aiLoop, acPropertyValue, 3
      DoCmd.SetProperty "D" & aiLoop, acPropertyVisible, 0
   Else 'Otherwise turn them on.
      DoCmd.SetProperty "D" & aiLoop, acPropertyVisible, -1
   End If
Next

End Sub

In control's D1 to D7 in their After Accept, put in TCO_DisplayFields.

By having a sub deal with this you don't need to repeat the code.

 

[George Moore]

Hi

If you want the code posted by Mark_ to be actioned without the user having to click the DIADL field, you can use the forms timerInterval event

The status of DIADL would then be checked nn times every second

 

[moyi]

Thank you so much, Mark!

 

[moyi]

Thank you George, I tried Mark's code. It worked very well