Search code examples
vbaruntime-error

IsError() in Access VBA

In my code I run a SQL statement in VBA like this:

  Dim rs As DAO.Recordset
  Dim db As DAO.Database
  Set db = CurrentDb
  Set rs = CurrentDb.OpenRecordset( SQL-Statement)

This SQL-Statement either returns one value or none.

I then want to check wether a value is returned or not.

  If IsError(rs.Fields("Name").Value) = True Then   
     MsgBox(Error)
  Else   
     Variable = rs.Fields("Name").Value
  End If

However: If there is an error the method IsError doesn't return true but the script says runtime-error 3021 and breaks. Why does the method fail on this?

Unfortunately I have to write this on my mobile since I'm not allowed to log in on platforms from my work PC.

Ty all in Advance

Solution

  • You need to check the EOF property of the recordset.

    Try this:

    If Not rs.EOF Then Variable = rs.Fields("Name").Value

    which is the same as

    If Not rs.EOF Then Variable = rs![Name]