423,473 Members | 2,511 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,473 IT Pros & Developers. It's quick & easy.

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

P: 10
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?
Aug 3 '18 #1

✓ answered by 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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Ctrl_Txt_Amt_Tendered]_AfterUpdate()
  2.   Me![Treasurer]  = False
  3.   Me.Refresh
  4. End Sub
This is the most simplistic version.

Share this Question
Share on Google+
7 Replies


twinnyfo
Expert Mod 2.5K+
P: 2,605
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:

Expand|Select|Wrap|Line Numbers
  1. 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!):

Expand|Select|Wrap|Line Numbers
  1. Me.chkTreasurer = (Me.txtAmt_Tendered = Me.txtAmt_TenderedCheck)
Again, this assumes a proper naming convention for the Treasurer check box.

Hope this hepps!
Aug 3 '18 #2

zmbd
Expert Mod 5K+
P: 5,283
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Ctrl_Txt_Amt_Tendered]_AfterUpdate()
  2.   Me![Treasurer]  = False
  3.   Me.Refresh
  4. End Sub
This is the most simplistic version.
Aug 3 '18 #3

zmbd
Expert Mod 5K+
P: 5,283
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Ctrl_Txt_Amt_Tendered_AfterUpdate()
  2.   'Only refreshing the snapshot of the record. No need to do a full requery for same record changes.
  3.   Me.Refresh
  4. End Sub
  5.  
  6. Private Sub Ctrl_Txt_Amt_Tendered_BeforeUpdate(Cancel As Integer)
  7.   If Not Me.Ctrl_Txt_Amt_Tendered.OldValue Like Me.Ctrl_Txt_Amt_Tendered.Value Then
  8.     Me![Treasurer] = False
  9.   End If
  10. 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.
Aug 3 '18 #4

P: 10
Thank you for your response. I guess I am not following the most current standard naming conventions.
Aug 4 '18 #5

P: 10
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.
Aug 4 '18 #6

twinnyfo
Expert Mod 2.5K+
P: 2,605
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.
Aug 4 '18 #7

zmbd
Expert Mod 5K+
P: 5,283
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
-
Aug 4 '18 #8

Post your reply

Sign in to post your reply or Sign up for a free account.