Dsum returns null

[Sammie]

In a field's afterupdate event, I have the following
command: Me!UnitPrice = DLookup("UnitPrice",
"ProductsInvoiced", strFilter)
If the UnitPrice is null, I get an error saying "The object
doesn't contain the automation object "Invoice Status
subform". Any clues what this means? I do have such a
subform on my form, but it is not referenced in this
procedure at all.

I would like to add another instruction, if the unitprice
is null, to go to the ProductsInvoiced table (via the form
called "ProdInvoiceDblClk") and update the unit price.
What code do I need and where do I put it in relation to my
Dsum statement?
Thanks
Sammie

 

[tina]

not sure why you're talking about a DSum() function, when your posted code
is using a DLookup() function. also not sure why you're getting the error
message as posted. but, to fix your posted code, try

Me!UnitPrice = Nz(DLookup("UnitPrice", "ProductsInvoiced", strFilter), 0)

I would like to add another instruction, if the unitprice
is null, to go to the ProductsInvoiced table (via the form
called "ProdInvoiceDblClk") and update the unit price.

to do the above task, and assuming that a *valid* unit price is never zero
(0) try something along the lines of

Dim dblUnit As Double

dblUnit = Nz(DLookup("UnitPrice", "ProductsInvoiced", strFilter), 0)

If dblUnit = 0 Then
Msgbox "Update the unit price, please"
'opening the form as Dialog will suspend this procedure until the
'form is closed.
DoCmd.OpenForm "ProdInvoiceDblClk", , , , , acDialog
'the next 2 lines of code should all be on one line
Me!UnitPrice = Nz(DLookup("UnitPrice", "ProductsInvoiced",
strFilter), 0)
Else
Me!UnitPrice = dblUnit
End If

hth