simple recordset question

  • Thread starter Thread starter NH
  • Start date Start date
N

NH

I want to write a simple fucntion which will delete a specified record from
a table. I have written this, but it halts at the rs.findfirst line saying
that the operation is not supported for this type of object.....I have tried
all sorts of syntax changes, but still get the same error. Can anyone point
out what I have done wrong?

===========================================
Public Function DeleteRecord(ID As Integer)
If IsMissing(ID) Then Exit Function

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("MyTable")

rs.FindFirst "[ID] = " & ID
If not rs.NoMatch Then
rs.Delete
rs.Update
End If
Set rs = Nothing

End Function
============================================

Thanks

Nick
 
Try to call something like this to replace the cursor at the begening of the
recordset:

rs.MoveFirst

regards,
//JF
 
NH said:
I want to write a simple fucntion which will delete a specified record from
a table. I have written this, but it halts at the rs.findfirst line saying
that the operation is not supported for this type of object.....I have tried
all sorts of syntax changes, but still get the same error. Can anyone point
out what I have done wrong?

===========================================
Public Function DeleteRecord(ID As Integer)
If IsMissing(ID) Then Exit Function

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("MyTable")

rs.FindFirst "[ID] = " & ID
If not rs.NoMatch Then
rs.Delete
rs.Update
End If
Set rs = Nothing

End Function
============================================
Click to expand...


FindFirst does not apply to Table type recordsets, it
operates on Dynaset and SnapShot type recordsets.

Try this instead:
Set rs = db.OpenRecordset("MyTable", dbDynaset)
 
You will find that the following function will work much
faster, especially on large tables.

Public Function DeleteRecord(ID As Integer)
CurrentDB.Execute "Delete from MyTable where ID=" &
ID
End Functioin

Also note, if ID is declared as an integer, it can not be
missing. There will have to be a value passed to it.


Chris Nebinger
 
I have noted Marshall's message for future reference, but have decided to
use Chris' function.

Thanks everyone.
 
NH said:
I have noted Marshall's message for future reference, but have decided to
use Chris' function.
Click to expand...


Wise decision ;-)

I only tried to explain why you got the error, Chris
provided an alternate approach that will perform the task
far more efficiently.
 
Back
Top