Help with ADO - if record not found...then

[systemx]

Hi all,

Should be a simple one. I have the following code that finds a record in a table, and deletes it (only if the 'Deleted' condition is true) -

If Deleted = True Then

strCriteria = "[ID] = " & EnquiryNumber

Set ADOrs = New ADODB.Recordset
ADOrs.ActiveConnection = CurrentProject.Connection
ADOrs.Open "tblMissingPayment", , adOpenKeyset, adLockOptimistic

With ADOrs
.Find strCriteria
.Delete
.Update
.Close
End With

Set ADOrs = Nothing
End If

In some instances, strCriteria may not exist in the table and I am not sure what code to use to handle this.

i.e.

If strCriteria exists in tblMissingPayment Then
.Delete
.Update
.Close
Else
.Close
End If

Could someone point me in the right direction please?

Thanks

Robert

EDIT: I have got around this by using a DLookup to check if the ID exists, if the Dlookup variable isNull - then do nothing, else perform the above function. If there is a better way though without DLookup I am keen to learn it! Thanks

 

[pbaldy]

Personally I wouldn't open the whole table; I would use an SQL statement that restricted the records returned to those that met the criteria. Then simply test for EOF. If true, there were none, if false there were. Since it appears that there would only be one record in this case:

If Not ADOrs.EOF Then
'do your thing here

 

[boblarson]

Or, an alternate suggestion:

Dim strSQL as String

If Deleted = True Then
     strSQL = "SELECT * FROM tblMissingPayment WHERE "[ID] = " & EnquiryNumber

         Set ADOrs = New ADODB.Recordset
         ADOrs.ActiveConnection = CurrentProject.Connection
         ADOrs.Open strSQL, , adOpenKeyset, adLockOptimistic

    With ADOrs
        If .BOF AND .EOF Then
            MsgBox "No Records Found to Delete", vbOKOnly, "No Records Found"
           .Close
        Else
           Do Until .EOF
              .Delete
              .Update
              .MoveNext
           Loop
             MsgBox "Records Deleted", vbOKOnly, "Selected Records Deleted"
             .Close
        End If
     End With

Set ADOrs = Nothing
End If

 

[systemx]

Thanks Bob!

This worked like a treat and I agree is a much more efficient way of achieving the result. I had to make a minor change to the strSQL statement -

strSQL = "SELECT * FROM tblMissingPayment WHERE ID = " & EnquiryNumber & ";"

Not sure if the semi-colon on the end is required - but I had used it somewhere else previously so left it in there.

Regards,

Robert