Invalid use of Null in DlookUp

[olorin]

Hi,
I have some code that I just can't get over the line

Dim CompID As Integer

'look for component
CompID = DLookup("ID", "tblComps", "Pnum = '" & Forms!frmGoodsIn!tbScanComp & "'")

If CompID = False Then 'not found
    MsgBox "No component found, is this a new Component?", vbQuestion, "No Comp found"
Else 'found
    bunch of other stuff that works
end if

I'm trying to deal with a scenario where a comp is not found in the table, but I keep getting the invalid use of null error.
Any help would be greatly appreciated

 

[pbaldy]

An Integer can't hold Null. Either declare the variable as Variant or wrap the DLookup() in the Nz() function.

 

[MarkK]

Or possibly--if I could jump in--use DCount(), like...

If DCount("*", "tblComps", "Pnum = '" & Forms!frmGoodsIn!tbScanComp & "'") Then
[COLOR="Green"]    'bunch of other stuff that works    [/COLOR]
Else
    MsgBox "No component found, is this a new Component?", vbQuestion, "No Comp found"
End If

 

[olorin]

Thanks for both recommendations, both worked obviously.
Been out of this for a few years and it takes a while to get back into it.
Thanks again

 

[pbaldy]

No problem. I would actually use Mark's method myself, but was trying to educate.

 

[olorin]

I did go with Mark's in the end, but thanks for the pointer.
If anyone else is looking at this post for solving the dlookup null thing, the functioning code is here

Private Sub btnOK_Click()
On Error GoTo ErrHandler
'check quantity is entered
If txtQty = 0 Then
    MsgBox "Quantity cannot be zero.", vbCritical, "No Qty entered"
    txtQty.SetFocus
    Exit Sub
End If

'declare objects
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim CompID As Variant

'look for component ID based on text box on form
CompID = DLookup("ID", "tblComps", "Pnum = '" & Forms!frmGoodsIn!tbScanComp & "'")

'check component exists
If DCount("*", "tblComps", "Pnum = '" & Forms!frmGoodsIn!tbScanComp & "'") Then
    Set db = CurrentDb
        Set rst = db.OpenRecordset("tblMovements", dbOpenDynaset)
        With rst
        .AddNew
            ![fkcompID] = CompID
            ![fkLocationID] = cboLocation
            ![QtyIN] = txtQty
            ![QTyOUT] = 0
        .Update
        End With
        rst.Close
    
    txtQty = 0
    tbTypeComp = ""
    tbScanComp = ""
    tbScanComp.SetFocus
Else
    MsgBox "No component found, is this a new Component?", vbQuestion, "No Comp found"
End If

'Destroy DAO objects:
Set db = Nothing
Set rst = Nothing

Exit Sub

ErrHandler:
    MsgBox "Please take note of the error description on the next form"
    MsgBox Err.Description, vbOKOnly, "An Error has occurred, please inform S.Byrom"
    Set db = Nothing
    Set rst = Nothing
End Sub

 

[pbaldy]

A couple of thoughts. The variable and DLookup can be deleted since you use the DCount now. The more traditional error handling:

http://www.baldyweb.com/ErrorTrap.htm

would put the cleanup code in the exit handler so you only need to have it in one place.

 

[MarkK]

Oh, I see, looks like you need that value returned by the DLookup(), right? So, in that case I wouldn't use the DCount(). Use one or the other, and obviously if you need the looked-up value...

Private Sub btnOK_Click()
    Dim rst As DAO.Recordset
    Dim CompID As Variant

    If txtQty = 0 Then
        MsgBox "Quantity cannot be zero.", vbCritical, "No Qty entered"
        txtQty.SetFocus
    Else
[COLOR="Blue"]        CompID = DLookup("ID", "tblComps", "Pnum = '" & Forms!frmGoodsIn!tbScanComp & "'")
        If Not IsNull(CompID) Then[/COLOR]
            Set rst = CurrentDb.OpenRecordset("tblMovements", dbOpenDynaset)
            With rst
                .AddNew
                !fkcompID = CompID
                !fkLocationID = cboLocation
                !QtyIN = txtQty
                !QTyOUT = 0
                .Update
                .Close
            End With
            txtQty = 0
            tbTypeComp = ""
            tbScanComp = ""
            tbScanComp.SetFocus
        Else
            MsgBox "No component found, is this a new Component?", vbQuestion, "No Comp found"
        End If
    End If
End Sub

And I just removed the error handling for simplicity.

 

[pbaldy]

Arg, I didn't notice the variable was used later. :banghead:

 

[MarkK]

Understanding someone else's code--even understanding my own code a few months later--is not a trivial matter.