Code wont work

  • Thread starter Thread starter SF
  • Start date Start date
S

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
 
Hi,
try this:

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

rst.close

End Function
 
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
 
Back
Top