How to Loop Delete on a DAO Recordset

[kcdoell]

Hello I have a code where I want to delete the records that are found in my DAO recordset. I took a stab at this for the first time and got it to work but it is only delete one record at a time. If I execute the code again my record count will be minus one and then it will delete another single record etc etc until there are no records to delete. How could I create a loop statement so that I don't have to keep on executing the code??? Below is what I have so far:

[code=vb]
'Procdure to give the user the ability to delete all records
'for a predefined recordset from the tblStaticAllFor ecast table

LockSQL = "SELECT * FROM tblStaticAllFor ecast WHERE" & _
" DivisionIDFK = " & Val(Me.cboDivis ion.Value) & _
" And WrkRegIDFK = " & Val(Me.cboWrkRe g.Value) & _
" And CreditRegIDFK = " & Val(Me.cboCredi tReg.Value) & _
" And YearID = " & Val(Me.CboYear. Value) & _
" And MonthID = " & Val(Me.CboMonth .Value) & _
" And FWeek = " & Val(Me.cboWeek. Value)

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenR ecordset(LockSQ L)

'Check to see if there are any records

If rst.BOF And rst.EOF Then 'If none, then end process and send out MsgBox

MsgBox "There are no records to delete.", 64, "No Records Match"

Else

'Find the last and first record for the count

rst.MoveLast 'Move to last record
rst.MoveFirst 'Move to First record

'Count the records found in "LockSQL"

recordexists = rst.RecordCount

If MsgBox("The number of records you are about to delete is " & recordexists & "." & _
" Click the ok button to proceed", vbOKCancel, vbDefaultButton 2) = vbOK Then

'Delete the records that the user has selected.

rst.Delete

MsgBox "Records have been deleted.", vbInformation, "Message"

'Close the recordset

rst.Close

End If
End If
End If
End Sub
[/code]

Thanks,


Keith.

[kcdoell]

I figured this one out with a loop statement....

[code=vb]
'Delete the records that the user has selected.

With rst

.Delete

End With

'Check to make sure that at least one record exists in the recordsert

If (rst.RecordCoun t > 0) Then

rst.MoveFirst ' Start deletion from first record

'Delete one record at a time using a do while loop

Do While Not rst.EOF
rst.Delete
rst.MoveNext
Loop
End If

MsgBox "Records have been deleted.", vbInformation, "Message"

[/code]

Thanks

[davidelloyd]

I have a additonal question. I would like to delete on record but the first example seems a bit complicated do you have a code that will delete one record in a table that looks for certain words in the field names.