Dlookup - new record

R

Rpettis31

I am trying to lookup if there is a record based on an item on a different
table.
Basically there is a list box and if the item on the list has a record an
item record form opens in edit mode and if not then a new item record is
created.
I am trying to use a dlookup, to do this in a test mode below but I am
apparently doing something wrong.

Private Sub List8_DblClick(Cancel As Integer)

lstItem = Me.List8.Column(1)

recID = DLookup("Item", "tblItemRecords", "lstItem = " & Nz(lstItem, 0))

If recID = 0 Then _
ms = MsgBox("HELLO", vbCritical)
 
D

Dirk Goldgar

Rpettis31 said:
I am trying to lookup if there is a record based on an item on a different
table.
Basically there is a list box and if the item on the list has a record an
item record form opens in edit mode and if not then a new item record is
created.
I am trying to use a dlookup, to do this in a test mode below but I am
apparently doing something wrong.

Private Sub List8_DblClick(Cancel As Integer)

lstItem = Me.List8.Column(1)

recID = DLookup("Item", "tblItemRecords", "lstItem = " & Nz(lstItem, 0))

If recID = 0 Then _
ms = MsgBox("HELLO", vbCritical)


Probably you need this:

recID = DLookup("Item", "tblItemRecords", "Item = " & Nz(lstItem, 0))

But it's not clear what your plan is, and it seems likely to me that you
also need to apply the Nz function to the (possibly Null) value returned
from DLookup:

recID = Nz(DLookup("Item", "tblItemRecords", "Item = " & Nz(lstItem,
0)), 0)
 
J

John W. Vinson

I am trying to lookup if there is a record based on an item on a different
table.
Basically there is a list box and if the item on the list has a record an
item record form opens in edit mode and if not then a new item record is
created.
I am trying to use a dlookup, to do this in a test mode below but I am
apparently doing something wrong.

Private Sub List8_DblClick(Cancel As Integer)

lstItem = Me.List8.Column(1)

recID = DLookup("Item", "tblItemRecords", "lstItem = " & Nz(lstItem, 0))

If recID = 0 Then _
ms = MsgBox("HELLO", vbCritical)

If there is no record, then Item will not be equal to 0, for one thing!

Is lstItem a Listbox on your form? The DLookUp expression suggests that you're
searching tblItemRecords for a record where the *TABLE FIELD* named lstItem is
equal to the value of lstItem on the form, which doesn't make much sense to
me. DLookUp doesn't look things up on forms or in listboxes - it looks in
Tables!

Please explain where the lstItem is, and what you're expecting to find.
 
C

Chegu Tom

Try Dcount instead. then if there is no record the value will be 0
Dlookup will not do that for you. It will probably return a null
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top