Subform error issue

[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]

Sounds like this may help:

http://www.mvps.org/access/forms/frm0022.htm

 

[JaredNJames]

hi, thanks for that, yes it is the answer, but i dont understand that much access coding so have no idea how to use it. but thats the basis of what i need to do.

any help appreciated.

jared james

 

[pbaldy]

Paste that code into a standard module, and then you can call it as described in the link.

 

[JaredNJames]

i dont know how to call it

 

[pbaldy]

From the link:

This function returns zero instead of #ERROR when used like this:nnz([Subf field]) on the main form

So for you:

nnz([frm_stocksub].Form![StockBoughtIn])

 

[JaredNJames]

hi, thanks now im getting it, but where exactly would i put that?

in a particular event command or in the part which says where the field gets the data?

i really dont know where to put it.

jared james

 

[pbaldy]

This type of thing:

[frm_stocksub].Form![Text23] = (nnz([frm_stocksub].Form![StockBoughtIn]) - nnz([frm_stocksubsub].Form![SumOfUnitQuantity]))