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.
