Dlookup - new record

  • Thread starter Thread starter Rpettis31
  • Start date Start date
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)
 
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)
Click to expand...


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)
 
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)
Click to expand...

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.
 
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
 
Back
Top