By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,460 Members | 2,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,460 IT Pros & Developers. It's quick & easy.

Access AfterUpdate or OnChange for field

P: 5
I have an invoiceDetail form that has a Quantity field in the record. When I add an item to the invoice and then add a value to the Quantity, I want the invoice to update the productOnHand field in ProductInventory table.

In my vba, I have tried the following. This code "CurrentProductLevel = CurrentProductLevel - y
" works without the If statement, but fails to work with it (actually, nothing happens at all with the If statement.) I have also tried this with Me.Quantity in place of Quantity. My big problem is that, without the if statement it subtracts that value from the "CurrentProdLevel" every time I change the value in the Quantity field in the record, hence the If statement.

So, the question is, should I be using the AfterUpdate or the OnChange vba code. And, what is causing my If statement to fail.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Quantity_AfterUpdate()
  2.     Dim x As Integer, y As Integer
  3.     x = Quantity
  4.         If x <> 0 Then
  5.             y = Quantity - x
  6.             CurrentProductLevel = CurrentProductLevel - y
  7.         End If
  8. End Sub

Any Suggestions?
Thank you in advance!
Attached Images
File Type: jpg AccessForm.jpg (21.8 KB, 89 views)
Nov 21 '17 #1
Share this Question
Share on Google+
8 Replies

Expert 100+
P: 1,430
Do the maths.
X = quantity
y = quantity (x) - x which will always be 0
or the other way of looking at it is
y = Quantity - Quantity = 0

Nov 21 '17 #2

P: 5
Thanks Phil, that makes sense. But, what I am trying to do is save the last value put into Quantity, so if I change the value in Quantity on the invoice, it won't update the CurrentProdLevel again. I am looking for a way to only update CurrentProdLevel if the new value is different from the original value. Maybe, I should be using a different class, such as BeforeUpdate, or OnChange. What do you think?
Nov 21 '17 #3

Expert 100+
P: 1,430
I think the best way to do it it to have a value called "HoldCurrentProductLevel" at the top of your form module (Just underneath the Option Explicut & Option Compare databse.

This value will be "visible" to all procedures within the module.

So on the before update of the Quantity, save the value
Expand|Select|Wrap|Line Numbers
  1. HoldCurrentProductLevel = CurrentProductLevel
and on After update you need
Expand|Select|Wrap|Line Numbers
  1. CurrentProductLevel = HoldCurrentProductLevel + Nz(Quantity)
The problem with this could be that if quantity is entered as 4 the CurrentProductLevel will be reduced by 4. Fine. If subsequently the operator realises the quantity should have been 3, and enters that, the CurrentProductLevel will be reduced by a further 3 (7 in all).

I Can't see a way round this. If you have a button to confirm the Quantity is correct, you might just as well have entered it correctly in the first place.

There is a possibility of setting a flag in the record to say that a quantity has been entered, and if that flag is set to true and a further Quantity entered, throw a wobbly.

Nov 21 '17 #4

P: 5
Yes Phil, that is my problem. Each time I correct an invoice Quantity, it subtracts from the CurrentProdLevel. Your HoldCurrentProdLevel is kind of what I'm looking for. Maybe saving the current Quantity as a global variable, then comparing it to the new value to see if there is a change would work. Or maybe use DLookup to save the value for comparison.
What do you think?

Nov 21 '17 #5

Expert 100+
P: 1,430
Well, you've got to decide the point at which where no more fiddling with the quantity is allowed. Perhaps when you decide this, I can give you other options.

Nov 21 '17 #6

P: 5
Hey Phil,

Not to beat a dead horse. But I may have found a solution. But I am not sure how to write code.

Yes, I have figured out a way to submit an error and ask for new value in my field, But...

If I use the OnClick sub in the Quantity field, I can save the value in that field. But, I am trying to save it globally so I can then use it in the AfterUpdate function to add or subtract the difference. I believe I can do this with the Application.TempVars function, but I am not sure how to store the value in that function.

Does that sound reasonable?
Nov 24 '17 #7

Expert 100+
P: 1,430
To declare a tempvar, depending on whether you want it globally available or just available to the module in question, for the former use
Expand|Select|Wrap|Line Numbers
  1. Public HoldLevel as Tempvar
at the top of a general Module

For the latter, use

Expand|Select|Wrap|Line Numbers
  1. Private HoldLevel as Tempvar
in the form module.

To refer to it use
Expand|Select|Wrap|Line Numbers
  1. Tempvars!HoldLevel = .....
  2. or 
  3. ..... = Tempvars!HoldLevel
You still have ducked the earlier question about the cutoff point, and I don't think the Tempvars is going to solve that!!

Nov 25 '17 #8

P: 5
Hey Phil,

Figured it out! Had to use the OnClick, then AfterUpdate.
Here it is:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  4. Dim xQty As Integer
  6. Public Sub Qty_Click()
  7.     If IsNull(Qty) Then
  8.         Exit Sub
  9.     Else
  10.         xQty = Qty 'Saves current value of Qty
  11.     End If
  12. End Sub
  14. Private Sub Qty_AfterUpdate()
  15.     If xQty < Qty Then
  16.         xQty = xQty - Qty
  17.         CurrentProdLevel = CurrentProdLevel + xQty
  18.     ElseIf xQty > Qty Then
  19.         xQty = Qty - xQty
  20.         CurrentProdLevel = CurrentProdLevel - xQty
  21.     End If
Thank for the input. Everytime I hear someone else's idea, it makes me think more about other scenarios.

Thanks Again!!!
Nov 25 '17 #9

Post your reply

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