subform #error

[JaredNJames]

Hi, i have a form which shows stock levels. It works by the user entering a value and pressing search, then one query returns the data on the product from the stock table (so that it can be edited), and another query returns a "total of how many have been sold" in the form of [sumofunitquantity]. by subtracting the number sold on the one subform from the number bought in on another, i get the number in stock.

my problem is, because of the way this system works, if the item has been bought in but none sold, it returns an #error in the [in stock] field because the return on the [sumofunitquantity] is null.

when the query is run in this situation, the product information is shown along with the number bought in, but the field for the number sold remains invisible on the form, so the calculation cannot be completed.

i have tried using a code such as this:
Private Sub Combo63_Change()
If IsNull([frm_stocksubsub].Form![SumOfUnitQuantity]) = "" Then
[frm_stocksub].Form![Text23] = 0
Else
[frm_stocksub].Form![Text23] = ([frm_stocksub].Form![StockBoughtIn] - [frm_stocksubsub].Form![SumOfUnitQuantity])
End If
End Sub

but with no success. basically i want it so that if there is a null value in no. sold, the stock value is 0.

any ideas?

thanks
jjames

 

[pbaldy]

You should not post the same question twice:

http://www.access-programmers.co.uk/forums/showthread.php?p=683750#post683750