Form validation (AfterUpdate) for Quantity, comparing with ComboBox column value

[LaughingDev]

I am working on a system which manages orders, purchases and the inventory for a small trading company, much like the northwind example.
The db currently has 6 tables: Products, Customers, Orders, OrderDetails, Purchases and PurchaseDetails.

I just realised that the CreateOrder form allows the input of a quantity value which is more than the StockOnHand value. The SOH value is displayed as the third (2) column in a ComboBox which queries the productcode, name and its SOH value.
To correct this I have attempted to add the following code in the AfterUpdate event of the Quantity field:

If Me!Quantity > Me!ProductCode.Column(2) Then

MsgBox "There is not enough Stock to satisfy this Quantity, you must make a PurchaseOrder or adjust the Quantity", vbExclamation, "Low Stock"
Me!Quantity = ""
​

End If

This does not give the correct behaviour, it still allows for values larger than the SOH to be entered. For some reason the condition is not being triggered.
Would appreciate any help on this. Thanks in advance.

 

[Mr. B]

Set a breakpoint in you code where you 'If' statement starts and then run the form and enter a value. Then when the code stops at your breakpoint, check to see that the 'Me!Quantity' and the 'Me!ProductCode.Column(2)' both have the values you are expecting.

Next, try stepping through the code one line at a time. This way you will at least be able to determine if your code is even executing the statements within your 'If' statement. If not, you should be able to determine what is causing the values you expect to not be as you expected them to be.

 

[LaughingDev]

Thanks for you advice, it pointed me in the right direction and I have managed to resolve the issue.
The problem was that the data types of the compared fields did not match, therefore even though the values were correct the conditional statement would not be triggered. I hadn't realised that the SoH value from the ComboBox column had a string/variant data type as opposed to an integer as entered into the Quantity field.

To solve this I declared a new variable as an integer and assigned the SoH value to it before executing the conditional statement where this variable is compared with the Quantity field.

Thanks again for the help.

 

[Mr. B]

You are quite welcome. Glad to help.