No records found in Select statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello all,

How can I make it if this statement below does not find any records to
continue on and not get an error and stop running the code?

vSql1 = "Select sku from pm_f where sku_type = ""NORM""
Set db1 = CurrentDb()
Set rs1 = db.OpenRecordset(vSql1)
 
First, there are mismatched quotes in the first line. However, I'm guessing
this is a typo in the message because the editor should flag this error.

vSql1 = "Select sku from pm_f where sku_type = ""NORM"""
or
vSql1 = "Select sku from pm_f where sku_type = 'NORM'"

Next, the Set rs1 statement shouldn't produce an error just because no
records are returned by the SQL. However, once you've opened the recordset,
you can test for no records prior to proceeding further by using an If
statement. If there are no records then the recordset will be a EOF and BOF
simultaneously.

If Not (rs1.BOF And rs1.EOF) Then
'work with the recordset here
End If

What type of recordset was rs1 declared as? If you only stated "Dim rs1 As
Recordset" it may be getting defined as an ADO recordset. Try "Dim rs1 As
DAO.Recordset" to force it to a DAO type of recordset.
 
Back
Top