Capture Success or Failure For Query Run From Code

[mbx1275]

Hi,

I am building an application in Access 2003. I would like to capture the error for the following query, like "No Record Found". Right now if the record does not exist Access pops the End/Debug error box up.

Dim dbThisDatabase As DAO.Database
Dim rstGetRecord As DAO.recordset
Dim qdf As DAO.QueryDef

Set dbThisDatabase = CurrentDb
Set qdf = dbThisDatabase.QueryDefs("qryExLoginSelect")
qdf.Parameters("qryParcelID") = Me.[parcel ID]
Set rstGetRecord = qdf.OpenRecordset


The other thing I would like to do is capture the number of records added for the following code.

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryExemptAddRecord"
DoCmd.SetWarnings True

Thanks for any help anyone can provide.

Greg

[Lysander]

Hi,

I am building an application in Access 2003. I would like to capture the error for the following query, like "No Record Found". Right now if the record does not exist Access pops the End/Debug error box up.

Dim dbThisDatabase As DAO.Database
Dim rstGetRecord As DAO.recordset
Dim qdf As DAO.QueryDef

Set dbThisDatabase = CurrentDb
Set qdf = dbThisDatabase.QueryDefs("qryExLoginSelect")
qdf.Parameters("qryParcelID") = Me.[parcel ID]
Set rstGetRecord = qdf.OpenRecordset


The other thing I would like to do is capture the number of records added for the following code.

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryExemptAddRecord"
DoCmd.SetWarnings True

Thanks for any help anyone can provide.

Greg

Hi Greg,

For your first question, after you have opened the recordset you can do the following

    Set rstGetRecord = qdf.OpenRecordset
    If rsGetRecord.eof then
       'you have no records so do something here
       'Dont forget to close the empty open recordset
   else
       'You have records so do something with them
   end if
 

For your second question, you can use the recordcount property

rs.RecordCount

but beware, it only counts the records accessed, i.e. it does not hold the correct count until you have ran through the whole recordset.

Another way of doing it is to use DCount. Say qryExemptAddRecord is adding records from tblSomething WHERE xzy conditions apply, you can say
intRecsAdded=dcount("somekey","tblSomething",xyz conditions