Dlookup problem - Invalid use of Null

[chuki2]

Hello, in my form i have 2 List box, one call "listPilih" and one more call "listSofProduct". Then i have click button called "addBtn", and code for this button is :

Private Sub addBtn_Click()Dim strSql As String
Dim checkValue As Integer

'Error on below
checkValue = DLookup("idProduct", "tblSofProduct", "[idProduct]=" & Me.listPilih)


If Me.listPilih.ListIndex < 0 Then


MsgBox " Please select invoice, then choice which one product to add on S.O.F", vbCritical, "ERROR"


Else
    If Me.listPilih = checkValue Then


    MsgBox "Sorry,Product already in the list", vbCritical, "ERROR"


    Else


'SQL string to tblSofProduct


    strSql = " INSERT INTO tblSofProduct " _
            & "(idSof, idProduct) VALUES " _
            & "(" & Forms!formListSof!idSof & ", " & Me.listPilih & ");"




    CurrentDb.Execute strSql, dbFailOnError


    Me.listSofProduct.Requery


End If
End If
End Sub

So when user click addBtn, product from "listPilih' will copy and insert to "listSofProduct". But user cannot insert same product to "listSofProduct".

When code excute, have error : Invalid use of Null...

How to solved this problem? i need help... Thank you

p/s: Sorry for bad english... hehehe

[chuki2]

Hello, i have resolved this problem... i just change from Dlookup to Dcount... Because Dlookup does return Null value

This is my code, hope can help who have a same problem...

Private Sub addBtn_Click()
Dim strSql As String
Dim checkValue As Integer


'Check is user select from list or not


If Me.listPilih.ListIndex < 0 Then


MsgBox " Please select invoice, then choice which one product to add on S.O.F", vbCritical, "ERROR"


Else


    ' Run dcount here
    
    checkValue = DCount("idProduct", "tblSofProduct", "[idProduct]=" & Me.listPilih)
    If checkValue = 0 Then


    'SQL string is here


        strSql = " INSERT INTO tblSofProduct " _
            & "(idSof, idProduct) VALUES " _
            & "(" & Forms!formListSof!idSof & ", " & Me.listPilih & ");"


        
        'Run SQL
        CurrentDb.Execute strSql, dbFailOnError
        
        'Requery list box
        
        Me.listSofProduct.Requery


    
    'If Dcount return 1
    
    ElseIf checkValue = 1 Then


        MsgBox "Sorry,Product already in the list", vbCritical, "ERROR"
        
    Exit Sub
    
    End If


End If
End Sub