Deleting Records

[TeeSee]

Access 2003
I have a small table with (among other fields) two check boxes one, if
checked, allows that record to be shown on a form. Once that record
has been used that check box is set to 0 while the other is set to 1
"flagged for deletion".
The line of code --- Set rst = db.OpenRecordset("Select * from
qryItemsForDelete;") I thought became the group of data that the code
following would be affecting. So within the qryItemsForDelete there
are two records flagged for deletion. When I click my command button
to delete the two records I get to delete the first two records in the
underlying table rather than the two that are flagged. Here is the
rest of the code. Any suggestions appreciated.
Please ignore the commented out lines Under construction. Many thanks

Private Sub cmdDelete_Click()
Dim Counter As Integer
Dim rst As DAO.Recordset
Dim db As DAO.Database
Set db = CurrentDb()

Set rst = db.OpenRecordset("Select * from qryItemsForDelete;")
With rst
Counter = 0
' Very important to avoid errors if no records are returned
If Not (.EOF And .BOF) Then
'movefirst isn't strictly necessary - but just in case
'.MoveFirst
Do Until .EOF
'do something with record
Counter = Counter + 1
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
.MoveNext
Loop
End If
.Close
End With
Set rst = Nothing
Set db = Nothing
'MsgBox ("You are about to DELETE" & " " & Counter & " " & "records"),
vbYesNoCancel
End Sub

 

[Justin]

You probably need to do a Me.Refresh or Me.Dirty = False to get the updates
recorded to the table. Why go through all that to delete records? You can
run a simple query:

strSQL = "Delete * From MyTable WHERE CheckField = 1;"
DOCmd.RunSQL strSQL

to delete the records. You can do a DSUM() of the field if you want to tell
the user how many records are being deleted.

 

[TeeSee]

You probably need to do a Me.Refresh or Me.Dirty = False to get the updates
recorded to the table.  Why go through all that to delete records?  You can
run a simple query:

strSQL = "Delete * From MyTable WHERE CheckField = 1;"
DOCmd.RunSQL strSQL

to delete the records.  You can do a DSUM() of the field if you want to tell
the user how many records are being deleted.







- Show quoted text -

 

[TeeSee]

You probably need to do a Me.Refresh or Me.Dirty = False to get the updates
recorded to the table.  Why go through all that to delete records?  You can
run a simple query:

strSQL = "Delete * From MyTable WHERE CheckField = 1;"
DOCmd.RunSQL strSQL

to delete the records.  You can do a DSUM() of the field if you want to tell
the user how many records are being deleted.







- Show quoted text -

Justin .... Thanks for your reply. This is the second question I've
asked in the last two or three days where the answer was to run a
query. Where and how doe you run that query frm a command button?

Thanks

 

[TeeSee]

Justin .... Thanks for your reply. This is the second question I've
asked in the last two or three days where the answer was to run a
query. Where and how doe you run that query frm a command button?

Thanks- Hide quoted text -

- Show quoted text -

Sorry Justin ... Forget that last question ... The light just came on.
Thanks again for the response.