Finding records in a recordset

[AndySuk]

Hi

I'm aware that this has been asked before but I'm obviously doing something wrong as I can't seem to follow the examples.

I'm using Access 2003 and have only just started using recordsets. I managed to add a new record by creating a recordset and then transferring values from fields in a form to it.

Now I need to be able to delete records as well.

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_pet_Personnel Transactions")
delrec = pet_Id
rs.FindFirst "pet_Id =" & delrec
rs.Delete
rs.Update

This opens the recordset based on a table and should find the value held in the pet_Id. pet_Id is a personnel ID; a numeric primary key. pet_Id is showing in a textbox on my form.

Access tells me that "the operation is not supported for this type of object".

Would anyone be able to give me an idea of what I'm doing wrong or, alternatively, a better way of doing it please?

Many thanks

Andy

 

[KeithG]

I personally would use a delete query to delet the record

Sql="Delete t1.* From tbl_pet_Personnel Transactions Where PetID=" & delRec

docmd.runsql(sql)

 

[AndySuk]

Thanks Keith

With a little bit of manipulation that worked fine. I didn't realise you could use sql direct in the code - that's majorly useful.

Thanks again.

A

 

[KeithG]

Glad I could help