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:

[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)
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, vbDefaultButton 2) = vbOK Then

'code will delete the records that the user has selected.


End If
End If

End Sub

[/code]

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:

[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)
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, vbDefaultButton 2) = vbOK Then

'code will delete the records that the user has selected.


End If
End If

End Sub

[/code]

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

[code=vb]

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

[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

[code=vb]

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

Hello Pianoman, there is actually a much easier method:
[CODE=vb]
DoCmd.SetWarnin gs False
DoCmd.RunSQL Replace(LockSQL , "Select", "Delete")
DoCmd.SetWarnin gs True[/CODE]

[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:

[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"

'Close the recordset
End If
End If
End If
End Sub
[/code]

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:
[CODE=vb]
DoCmd.SetWarnin gs False
DoCmd.RunSQL Replace(LockSQL , "Select", "Delete")
DoCmd.SetWarnin gs True[/CODE]

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.