Update an Access field on change of another field - Access 2016 Windows platform

[Kime1266]

I am working with a Form and have a currency field ([Amt_Tendered]) and a check box field ([Treasurer]) from a table source. I would like the check box field to change to False (0) if the value in the currency field should change. I tried the OnChange, AfterUpdate, OnExit properties of the Amt_Tendered field with =Iif([Treasurer]=-1,0,0) but it does not change the True value to False or zero as I would like it to. Any suggestions?

[twinnyfo]

Kime1266,

It would be helpful to see your entire procedure, so we could troubleshoot.

However, based upon what you've described, you only wan tthe Treasurer check box to change if the Amt_Tendered changes? What I mean by this is, if someone updates that field with the same, unchanged ammount, then the check should not change.

One way to do this is add a hidden text box to the form called txtAmt_TenderedCheck. In the form's OnCurrent Event:

Me.txtAmt_TenderedCheck = Me.Amt_Tendered

In the AfterUpdate Event of the Amt_Tendered text Box (I hope you have it named something else, like txtAmt_TenderedCheck--if not, you should!):

Me.chkTreasurer = (Me.txtAmt_Tendered = Me.txtAmt_TenderedCheck)

Again, this assumes a proper naming convention for the Treasurer check box.

Hope this hepps!

[zmbd]

Hopefully you've renamed your controls from their default names.

Open the form in design view
Show the properties for your text field
in the after_update event

Private Sub Ctrl_Txt_Amt_Tendered]_AfterUpdate()
  Me![Treasurer]  = False
  Me.Refresh
End Sub

This is the most simplistic version.

[zmbd]

Looks like twinnyfo and cross-posted - :-)

However, based upon what you've described, you only wan the Treasurer check box to change if the Amt_Tendered changes? What I mean by this is, if someone updates that field with the same, unchanged amount, then the check should not change.

no need for a hidden text box :-)

Try this instead:

Private Sub Ctrl_Txt_Amt_Tendered_AfterUpdate()
  'Only refreshing the snapshot of the record. No need to do a full requery for same record changes.
  Me.Refresh
End Sub
 
Private Sub Ctrl_Txt_Amt_Tendered_BeforeUpdate(Cancel As Integer)
  If Not Me.Ctrl_Txt_Amt_Tendered.OldValue Like Me.Ctrl_Txt_Amt_Tendered.Value Then
    Me![Treasurer] = False
  End If
End Sub

Kime1266,
Pay very close attention here - I am referring to controls in one instance and to the form's record set in the second

Me![Treasurer] is referencing the form's data source
Me.Ctrl_Txt_Amnt is referencing the control
This is important - and is why many of us strongly advise renaming the controls from the horrible MS-Default names.

[Kime1266]

Thank you for your response. I guess I am not following the most current standard naming conventions.

[Kime1266]

This worked like a charm. It did exactly what I was trying to do. I guess I was trying to bypass writing an event procedure by trying to use an expression or macro which obviously was not the correct solution. Thank you so much for expertise and for muddling through my poor explanation.

[twinnyfo]

Hey Z,

I’m gonna have to look into the .OldValue property. I keep forgetting that it exists, but I think I could use it in some of my applications.

Thanks for the reminder.

[zmbd]

NB: the .OldValue property only works with bound controls
-
For unbound controls I'll often use the .Tag property storing it from the On_Enter event
-