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:

'Procdure to give the user the ability to delete all records
'for a predefined recordset from the tblStaticAllForecast table
 
        LockSQL = "SELECT * FROM tblStaticAllForecast WHERE" & _
                    " DivisionIDFK = " & Val(Me.cboDivision.Value) & _
                    " And WrkRegIDFK = " & Val(Me.cboWrkReg.Value) & _
                    " And CreditRegIDFK = " & Val(Me.cboCreditReg.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.OpenRecordset(LockSQL)
 
'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, vbDefaultButton2) = 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
 

Thanks,


Keith.

[kcdoell]

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

'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.RecordCount > 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"
 
 

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.