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

How to add the stock quantity but that erase the total stock quantity any help!

100+
P: 115
hi i have a form that contain 2 text box (article and qty) and a next button .
when i click on the button a new form will open in datasheet view, contain 5 text box ( size, quantity, reference, article, qty_to_add).
i want when i change the qty_to_add to modify the stock quantity (quantity in the second form).
so i wrote this :
Expand|Select|Wrap|Line Numbers
  1.  Private Sub Add_AfterUpdate()
  2. Me.Qty.Value = Me.Qty.Value - Me.qty_to_Add.OldValue
  3. DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
  4. Me.Qty.Value = Me.Qty.Value + Me.qty_to_Add.Value
  5. End Sub
and it work fine but the problem is that sometime the quantity.value is null and when i modify the qty_to_add the stock quantity became null.
Any help please...
Aug 1 '10 #1

✓ answered by missinglinq

You can use the Nz() function to change a Null to Zero.

Me.Qty.Value = Nz(Me.Qty.Value, 0) + Me.qty_to_Add.Value

Welcome to Bytes!

Linq ;0)>

Share this Question
Share on Google+
4 Replies


beacon
100+
P: 579
Hi hannoudw,

Try adding an If...Then statement that checks to see if the quantity is null before you start your other calculations.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Add_AfterUpdate()
  2.  
  3.     If Not IsNull(Me.Qty.Value) Then
  4.         Me.Qty.Value = Me.Qty.Value - Me.qty_to_Add.OldValue
  5.         DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
  6.         Me.Qty.Value = Me.Qty.Value + Me.qty_to_Add.Value
  7.     Else
  8.         'Display a message to the user to enter a quantity
  9.         MsgBox "Please enter a value in the quantity field.", vbExclamation + VBOKOnly, "Null Quantity"
  10.         'Put the cursor in the Qty field so the user can enter a value
  11.         Me.Qty.SetFocus
  12.     End If
  13.  
  14. End Sub
  15.  
Aug 1 '10 #2

missinglinq
Expert 2.5K+
P: 3,532
You can use the Nz() function to change a Null to Zero.

Me.Qty.Value = Nz(Me.Qty.Value, 0) + Me.qty_to_Add.Value

Welcome to Bytes!

Linq ;0)>
Aug 1 '10 #3

beacon
100+
P: 579
@missinglinq
Completely forgot about Nz() Linq. I was addressing it from the perspective that null values weren't acceptable, so thanks for having my back.
Aug 1 '10 #4

100+
P: 115
Thanks @missinglinq
that solved the problem
now i'm trying to have a control over the total number that i added . therefore i added 2 text box on the header of the datasheet form. on of them have default value =[Forms]![Add Qty]![qtyTxt] (and that's the total quantity that i'm adding) and the other is equale of the sum of the qty_to_add (=sum[qty_to_add])
and it's not working...
olso i tried to add a button in the header and i wrote this code :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command22_Click()
  2. If Me.sum.Value <> Me.q.Value Then
  3. MsgBox ("check the adding numbers")
  4. End If
  5. End Sub
  6.  
and still not working ... any help please
Aug 2 '10 #5

Post your reply

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