How to delete all records found in a DAO recordset

[kcdoell]

Hello:

I am trying to write a code that will delete all records found in my DAO recordset Below is the code 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)
recordexists = rst.RecordCount
 
'If no records are found
    If recordexists = 0 Then
        MsgBox "There are no records to delete."
            Else
 
    rst.MoveLast        'Move to last record
    rst.MoveFirst        'Move to First record
 
        If MsgBox("The number of records you are about to delete is " & recordexists & "." & _
        " Click the ok button to proceed", vbOKCancel, vbDefaultButton2) = vbOK Then
 
'code will delete the records that the user has selected.
 
 
   End If
      End If
 
End Sub
 
 

Is there a quick code that would do this?

Keith.

[PianoMan64]

Hello:

I am trying to write a code that will delete all records found in my DAO recordset Below is the code 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)
recordexists = rst.RecordCount
 
'If no records are found
    If recordexists = 0 Then
        MsgBox "There are no records to delete."
            Else
 
    rst.MoveLast        'Move to last record
    rst.MoveFirst        'Move to First record
 
        If MsgBox("The number of records you are about to delete is " & recordexists & "." & _
        " Click the ok button to proceed", vbOKCancel, vbDefaultButton2) = vbOK Then
 
'code will delete the records that the user has selected.
 
 
   End If
      End If
 
End Sub
 
 

Is there a quick code that would do this?

Keith.

Are you simply wanting to delete all Records within the criteria of what you're deleting?

If so, once you established the connection, you can simply add a SQL Statement that will delete all the records that have been selected from you're conditional statement.

example

 
DoCmd.RunSQL ("DELETE * 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))
 

[ADezii]

Are you simply wanting to delete all Records within the criteria of what you're deleting?

If so, once you established the connection, you can simply add a SQL Statement that will delete all the records that have been selected from you're conditional statement.

example

 
DoCmd.RunSQL ("DELETE * 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))
 

Hello Pianoman, there is actually a much easier method:

DoCmd.SetWarnings False
  DoCmd.RunSQL Replace(LockSQL, "Select", "Delete")
DoCmd.SetWarnings True

[kcdoell]

I was away for a couple days and did not have access to the forum. I guess there is more than one way to skin a cat... Because I was in "Loop" mode, I came up with this solution:

'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"
 
'Close the recordset
     End If
        End If
            End If
End Sub
 

Thanks for the ideas and help. By the way, I like the cleaner look of the other ideas.....

Keith.

[kcdoell]

Hello Pianoman, there is actually a much easier method:

DoCmd.SetWarnings False
  DoCmd.RunSQL Replace(LockSQL, "Select", "Delete")
DoCmd.SetWarnings True

ADezii:

How does your method work. Is your code just simply swapping out the word "Select" for "Delete" in my SQL?

The reason I ask is that I am thinking of using it on something else I need to do. That is to say where the records have to append to a table; I could use the same method by swapping out the word "Select" for "Insert" in my SQL?

What do you think?

Keith.

[ADezii]

ADezii:

How does your method work. Is your code just simply swapping out the word "Select" for "Delete" in my SQL?

The reason I ask is that I am thinking of using it on something else I need to do. That is to say where the records have to append to a table; I could use the same method by swapping out the word "Select" for "Insert" in my SQL?

What do you think?

Keith.

How does your method work. Is your code just simply swapping out the word "Select" for "Delete" in my SQL?

You hit the nail on the head, since the SQL is exactly the same except for these 2 Keywords, Replace() simply substitutes Delete for Select in the Statement.