Code wont work

[SF]

Hi

I have the following code to check if a productID exist. I get runtime error
3021 when there is no match product ID. Could someone assist me?


Public Function CheckIfProductExist(ProductCode As Long) As Boolean
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim Stg As String


Stg = "SELECT tblProducts.ProductID "
Stg = Stg & "FROM tblProducts "
Stg = Stg & "WHERE (((tblProducts.ProductID)=" & ProductCode & "));"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(Stg, dbReadOnly)
rst.MoveLast
rst.MoveFirst
Debug.Print rst.RecordCount
If rst.RecordCount > 0 Then
CheckIfProductExist = True
Else
CheckIfProductExist = False
End If
Debug.Print CheckIfProductExist
End Function

SF

 

[Alex Dybenko]

Hi,
try this:

Set rst = dbs.OpenRecordset(Stg, dbOpenForwardOnly)
If rst.EOF Then
CheckIfProductExist = False
else
CheckIfProductExist = True
End If

rst.close

End Function

 

[Klatuu]

There is a much easier way and it is faster:

If IsNull(DLookup("[ProductID]", "tblProducts", "ProductID = '" &
Me.ProductCode & "'")) Then
'Product Doesn't exist
Else
'Product Exists
End If