i'm using access 2007,
i have in my form a "CarPN" field (PN for product number), a "CarPrice" field (of type 'currency'), and a button - "CarPriceRestore".
in the same principle, i have many other products types, prices and corresponded Restore buttons in my form.
the concept behind the restore button is that when it is pressed, the VBA code will get the product's price from the "Products" table and put it in the product's price field. if there's no match, it will put 0 rather than null, so to enable later aggregate functions.
for example, the user choose "CarPN": BMW2009, presses the "CarPriceRestore" button, and "CarPrice" gets 0 (zero), because there's no such PN in the "Products" table.
to do so i wrote the attached code. i get "invalid use of null" when running it while there's no match. if there's a match it works perfect.
what am i doing wrong?
Private Sub CarPriceRestore_Click()
Me.CarPrice = GetPrice(Me.CarPN)
Function GetPrice(Pn As String)
If IsNull(DLookup("Price", "Products", "Pn = '" & Pn & "'")) Then
GetPrice = 0
Else: GetPrice = DLookup("Price", "Products", "Pn = '" & Pn & "'")