VBA IF THEN ELSE statement stopped working... (1 Viewer)

[ChrisC]

Hi everyone, I hope you are all keeping safe out there!

For an unknown (to me) reason, the following VBA code has stopped working as it should.

Private Sub txtNewQty_AfterUpdate()

If Me.txtNewQty < Me.txtLocatStock Then
   Me.txtTransactionInfo.Value = "You are deleting " & Me.txtLocatStock - Me.txtNewQty & " from this location"
   Me.txtStockAdjustmentType.Value = "Stock Loss"
   Me.txtTransactionQty.Value = 0 - (Me.txtLocatStock - Me.txtNewQty)
    
Else
   Me.txtTransactionInfo.Value = "You are adding " & Me.txtNewQty - Me.txtLocatStock & " to this location"
   Me.txtStockAdjustmentType.Value = "Stock Gain"
   Me.txtTransactionQty.Value = Me.txtNewQty - Me.txtLocatStock

End If

End Sub

In a nutshell: the value of txtLocatStock is prepopulated. If the figure entered manually in to txtNewQty is greater than txtLocatStock, it should say "You are adding *the difference* to this location".
Alternatively, if the entered amount is Less than txtLocatStock, then it should say that you are "deleting" from this location.

The "less than" option works fine, however the "more than" option simply doesn't work and the message the user gets is: "You are deleting -"x" from this location"; where X is the difference value.

As it happens, deducting a negative does provide the correct result, but I need the wording to be correct for the user as it is understandably causing confusion.

This has worked fine before and I just cant work out why it has stopped now!

Many thanks for all your help!

Chris

 

[theDBguy]

Hi Chris. Have you tried stepping through the code?

 

[ChrisC]

Hi DB guy,

yes, even went through everything else line by line on the form that is completely unrelated to this part) and i cant see anything untoward.

 

[theDBguy]

Hi DB guy,

yes, even went through everything else line by line on the form that is completely unrelated to this part) and i cant see anything untoward.

Can you post a sample copy of your db?

 

[ChrisC]

I will try but sadly its a bit of a beast with lots of information already in it (we have been using it in the business I work for a while now, which is odd as to why it should stop working now). I will see what I can do though!

Many thanks
Chris

 

[MajP]

VBA never stops working unless there is corruption of the db or a missing reference. The condition is not being met as you thing. Also check the datatype on these fields to ensure not string. My guess is this is being called before the field is updated.

debug.print "new qty: " nz(Me.txtNewQty,0) & " Stock" " & nz(Me.txtLocatStock,0)


If nz(Me.txtNewQty,0) < nz(Me.txtLocatStock,0) Then
   Me.txtTransactionInfo.Value = "You are deleting " & Me.txtLocatStock - Me.txtNewQty & " from this location"
   Me.txtStockAdjustmentType.Value = "Stock Loss"
   Me.txtTransactionQty.Value = 0 - (Me.txtLocatStock - Me.txtNewQty)
   
Else
   Me.txtTransactionInfo.Value = "You are adding " & Me.txtNewQty - Me.txtLocatStock & " to this location"
   Me.txtStockAdjustmentType.Value = "Stock Gain"
   Me.txtTransactionQty.Value = Me.txtNewQty - Me.txtLocatStock


End If

what the debug say?

 

[ChrisC]

Hi MajP, thank you.

Forgive my ignorance - how do I get that debug to run? simply add that line of code in after Private Sub txtNewQty_AfterUpdate() I guess?

 

[ChrisC]

Hi MajP,

I ran things with the following tests:
txtLocatStock = 30
txtNewQty = 38 (which would add 8)

and then I tried txtNewQty = 28 (which would deduct 2).

then results of the debug are:

 

[theDBguy]

Hi MajP,

I ran things with the following tests:
txtLocatStock = 30
txtNewQty = 38 (which would add 8)

and then I tried txtNewQty = 28 (which would deduct 2).

then results of the debug are:
View attachment 87569

Hi. Can you please show us exactly how you implemented @MajP's code? I think you may have to modify this:

debug.print "new qty: " nz(Me.txtNewQty,0) & " Stock" " & nz(Me.txtLocatStock,0)

into this:

debug.print "new qty: " & nz(Me.txtNewQty,0) & " Stock: "  & nz(Me.txtLocatStock,0)

 

[Pat Hartman]

Please fix the debug command.

debug.print "new qty: " & nz(Me.txtNewQty,0) & " Stock: " & nz(Me.txtLocatStock,0)

 

[ChrisC]

Hello everyone, sorry for only just being able to get back to you now.

Private Sub txtNewQty_AfterUpdate()

Debug.Print "new qty: " & Nz(Me.txtNewQty, 0) & " Stock: " & Nz(Me.txtLocatStock, 0)

If Me.txtNewQty < Me.txtLocatStock Then
   Me.txtTransactionInfo.Value = "You are deleting " & Me.txtLocatStock - Me.txtNewQty & " from this location"
   Me.txtStockAdjustmentType.Value = "Stock Loss"
   Me.txtTransactionQty.Value = 0 - (Me.txtLocatStock - Me.txtNewQty)
    
Else
   Me.txtTransactionInfo.Value = "You are adding " & Me.txtNewQty - Me.txtLocatStock & " to this location"
   Me.txtStockAdjustmentType.Value = "Stock Gain"
   Me.txtTransactionQty.Value = Me.txtNewQty - Me.txtLocatStock

End If

End Sub

Here is the result when entering the txtNewQty value as 38 then 28.

thank you!
Chris

 

[Pat Hartman]

and what happens when you step through the code?