vba saving integer for later use

[vegascaptain]

Hi,

I have a Quantity field in an invoice that is of "Number" type. I have that number change my inventory when entered, or when I exit that invoice record. But, if I go back to that field and change the number, the inventory new number entered is subtracted again

I want to save the number in that field , so when I update the field, it does not subtract the new number from my inventory. I want it to add or subtract the difference of the old number and the new number in the Quantity field so the inventory remains correct.

So, I can get the number in the Quantity field with the OnClick function in VBA. But, I am trying to store the number, so when I click out of the record the AfterUpdate function will use the old number and add or subtract accordingly, so the inventory will reflect only the change in the value, not just the new value.

I am trying to use the Application.TempVars to store the value. But, I am not sure how to write the statement. Does that sound correct?

Sorry for the redundancy, just want to explain myself clearly.

 

[pbaldy]

I'm not really clear on what you're doing. Most of us wouldn't try to store quantity on hand, we just calculate it from transactions. There's a discussion on the topic here:

http://allenbrowne.com/AppInventory.html

If you want to continue, I'd probably use the before update event of the form. In that event, you can compare the Value property of the control and the OldValue property to get the change.

 

[Orthodox Dave]

I agree with pbaldy / Allen Browne.

You are trying to add to / subtract from your inventory list on the fly - for each transaction you make at the time you make it. This is very prone to error. Even if the coding is right, what happens when the connection is interrupted or the computer shuts down mid flow?

The only way to an accurate inventory is to recalculate it from scratch. If your database is enormous, this can take a long time, so would only be done from time to time.

So to get the up-to-date picture you could have fields in the inventory for "Sales since last update" (subtract) and "Deliveries since last update" (add). These could then be calculated on the fly by calculating from only the invoices and deliveries dated after the last update, which would be a smaller calculation. Then at the next full update, these would be set back to zero. You'd need a date/time field for the time of the update of course.

 

[arnelgp]

what is your code for the AfterUpdate Event,
on the Form or Control?

i don't think there is need to saving the
previous Quantity on a Variable or Tempvars.
you use the the .Value and .OldValue of
Quantity textbox.

your code should look like this on the
AfterUpdate Event of the Form"

Private Sub Form_AfterUpdate()
Dim db As DAO.Database
set db=Currentdb
db.Execute "Update [yourInventoryTable] " & _
"SET [balanceField] = [balanceField] + " & _
Nz(Me.[Quantity].Value, 0) & " - " & Nz(Me.[Quantity].OldValue, 0) & _
" Where [yourProductCode] = '" & Me.yourProductTextBox & "'"

 

[Solo712]

what is your code for the AfterUpdate Event,
on the Form or Control?

i don't think there is need to saving the
previous Quantity on a Variable or Tempvars.
you use the the .Value and .OldValue of
Quantity textbox.

your code should look like this on the
AfterUpdate Event of the Form"

Private Sub Form_AfterUpdate()
Dim db As DAO.Database
set db=Currentdb
db.Execute "Update [yourInventoryTable] " & _
"SET [balanceField] = [balanceField] + " & _
Nz(Me.[Quantity].Value, 0) & " - " & Nz(Me.[Quantity].OldValue, 0) & _
" Where [yourProductCode] = '" & Me.yourProductTextBox & "'"

Something tells me it should be the other way round:

"SET [balanceField] = [balanceField] + " & _
Nz(Me.[Quantity].[COLOR="Red"]OldValue[/COLOR], 0) & " - " & Nz(Me.[Quantity].[COLOR="red"]Value[/COLOR], 0) & _
" Where [yourProductCode] = '" & Me.yourProductTextBox & "'"

Best,
Jiri

 

[arnelgp]

you're right, good eyes.

 

[pbaldy]

your code should look like this on the
AfterUpdate Event of the Form"

As I said, it would need to be the before update event. In my experience, the values will be the same in the after update event.

 

[Pat Hartman]

I also do not recommend keeping a running value and I have the scars to prove it is a poor idea. I would never have designed an app this way but I acquired custody of one that I was not authorized to modify to the extent necessary to change to a transaction method. I can't even begin to tell you how tough it is to manage this process. It's like trying to hold jello in your hands. Every time you think you've plugged the last hole, another one appears.

In any event, if you are wedded to the concept, you can't implement it with separate queries that run in the form's AfterUpdate event, you have to do it in the control and form's BeoreUpdate events. You can use the .OldValue property to get the original value of the form field. So if the .Value property is 5 and the .OldValue property was 2, the difference is -3 and that is the adjustment you need to make to the quantity on hand.

 

[arnelgp]

ok, on before update event of the form but you cannot do it on same table as with your form.

 

[Pat Hartman]

The OrderDetail table is joined to the Inventory table so that all the inventory data is available in the subform. That way, in the Form's BeforeUpdate event, you take the value you are saving as the OrderDetail, you also adjust the Inventory quantity. This encapsulates the two updates into a single transaction so that they are either both committed or neither is committed.

If you separate these actions and do not use a transaction, you run the risk of colliding with a concurrent update made by another user. You may think you have inventory and so you Order 5 pieces and -5 pieces from inventory but meanwhile some other user got in ahead of you and sold off 4 and your -5 now makes the quantity on hand = -4.

 

[vegascaptain]

Worked through and found solution. Here is my new code. I guess it could be cleaned up a little, but it works as is:

Option Compare Database
Option Explicit

Dim xQty As Integer

Public Sub Qty_Click()
If IsNull(Qty) Then
Exit Sub
Else
xQty = Qty 'Saves current value of Qty
End If
End Sub

Private Sub Qty_AfterUpdate()
If xQty < Qty Then
xQty = xQty - Qty
CurrentProdLevel = CurrentProdLevel + xQty
ElseIf xQty > Qty Then
xQty = Qty - xQty
CurrentProdLevel = CurrentProdLevel - xQty
End If
End Sub

 

[Solo712]

Worked through and found solution. Here is my new code. I guess it could be cleaned up a little, but it works as is:

Option Compare Database
Option Explicit

Dim xQty As Integer

Public Sub Qty_Click()
If IsNull(Qty) Then
Exit Sub
Else
xQty = Qty 'Saves current value of Qty
End If
End Sub

Private Sub Qty_AfterUpdate()
If xQty < Qty Then
xQty = xQty - Qty
CurrentProdLevel = CurrentProdLevel + xQty
ElseIf xQty > Qty Then
xQty = Qty - xQty
CurrentProdLevel = CurrentProdLevel - xQty
End If
End Sub

vegascaptain, frankly, this is waste of everyone's time. You screwed this up royally even though you received a bunch of pointers. So, this leads me to believe that like few others I have seen here you don't come to AWF to learn something, but to convince yourself that you are smarter than the bunch of us.

Best,
Jiri

 

[pbaldy]

As I mentioned I don't agree with trying to store the balance, but to paraphrase media outlets "the opinions of the previous poster do not necessarily reflect the views of AWF or other contributors on this thread". My apologies, and glad to found a solution that works for you.