setting a recordset 1

[tziviak]

I have a recordset that extracts some records from a table-but the problem is with my code-is that it only extracts one record-instead of a few-so the only way to delete the whole recordset (that's what i want to do ) is to keep on looping and setting new recordset-and I get one record every time-until there's no more records left.
How can I get all the records at once and delete them all at once?
here's my code: (the msgbox-was for debugging and only outputted 1 or 0 -never more -even though I have more records)

Set rec = dbs.OpenRecordset("Select Quarterlies.IFSPID, Quarterlies.DateDue, Quarterlies.Type FROM Quarterlies WHERE (Quarterlies.IFSPID =" & IFSPCounter & &quot&quot

MsgBox "record Count " & rec.RecordCount

Do While rec.RecordCount > 0
rec.Delete
Set rec = dbs.OpenRecordset("Select Quarterlies.IFSPID, Quarterlies.DateDue, Quarterlies.Type FROM Quarterlies WHERE (Quarterlies.IFSPID =" & IFSPCounter & &quot&quot

MsgBox "record Count " & rec.RecordCount
Loop


thank you

 

[GComyn]

Ok.. recordcount will show how many records you HAVE BEEN THROUGH so far... the only way to get the correct recordcount is to .movelast, then get the record count.

What you should test for is rec.EOF, like so:

Do While rec.EOF
    rec.Delete
    Set rec = dbs.OpenRecordset("Select Quarterlies.IFSPID, Quarterlies.DateDue, Quarterlies.Type FROM Quarterlies WHERE (Quarterlies.IFSPID =" & IFSPCounter & ")")
Loop

Also... you can use a delete query, and use Docmd.RunSQL instead of creating a recordset.

    Docmd.RunSQL "Delete * FROM Quarterlies WHERE (Quarterlies.IFSPID =" & IFSPCounter & ")"

GComyn

 

[tziviak]

thank you for the info- I used move first, deleted it, and movenext

is it quicker-and more efficient to use the Docmd.RunSQL? because i'm dealing with a large table

 

[GComyn]

Generally, it should be. If you parse through a recordset one at a time, it has to recreate the recordset, which takes up time and resources. using the Delete query doesn't recreate the recordset, just runs an sql.

give it a try, and see if it is faster.

all you have to do is create a new function, keeping the old one, so if it doesn't work, you still have the one you know works.

GComyn

 

[tziviak]

thank you for your help-you earned a star