Deleting Selected records from a table via Visual Basic

[Spanky]

Hi,

Having some issues deleting records from a table based on a text field in
that table.
I want to delete all fields in the table 'tblTags' inserted by an auto
process, creatively designated by the text "auto" in a field 'DataEntry'. The
code is triggered by a button on a form that is used to manage this data.

The current code is:
-------------------
Private Sub btnUpdateTags_Click()
On Error GoTo Err_btnUpdateTags_Click
Dim rsttest As Recordset
Dim strSQL As String

If MsgBox("Delete all Auto records from tblTags ?", vbYesNo) = vbYes Then
Set rsttest = CurrentDb.OpenRecordset("SELECT * FROM tblTags")
rsttest.MoveFirst
While Not rsttest.EOF
strSQL = "DELETE FROM [tblTags] WHERE [tblTags.DataEntry] = """ & auto &
""""
CurrentDb.Execute strSQL, dbFailOnError
rsttest.MoveNext
Wend
End If

Exit_btnUpdateTags_Click:
Exit Sub
Err_btnUpdateTags_Click:
MsgBox Err.Description
Resume Exit_btnUpdateTags_Click
End Sub
-------------------------
Summary is code doesn't delete anything. If I replace
""" & auto &"""" with """ & 1 & """" and put some corresponding 1's in
the table, it works fine, so I assume I've stuffed up the syntax for dealing
with text values somehow.

Any helpmuch appreciated.

ps. also tried
strSQL = "DELETE FROM [tblTags] WHERE [tblTags.DataEntry] = '" & auto & "'",
same result.

 

[Spanky]

Also - the table in question could have up to 8 thousand records - is this
the most efficient ways to sort through these and delete the specified
records (which will comprise >95% or records)?

Thanks

 

[Albert D. Kallal]

I don't think you need to build a recordset here?

Both the table your deleting from, and the
reocrdset according to your code is the same single table?

The code to delete is;

Currentdb.Execute "delete * from tblTags where DataEntry = 'auto'"

That is ONE line of code!!!...

Get rid of all your looping and recordset code...you don't need it.

You might throw in a me.Refresh at the start of your code to force out any
pending disk writes, but there is not need to declare a reocrdset and
loop...

-------------------
Private Sub btnUpdateTags_Click()

If MsgBox("Delete all Auto records from tblTags ?", vbYesNo) = vbYes
Then
Currrentdb.Execute "delete * from tblTags where DataEntry = 'auto' "
end if

End Sub

Perhaps the "auto" value is supposed to be taken from the current
record/form?
With what you explained, the above seems all you need. Perhaps I miss
understood your question, but if the text is 'auto', then above should
work..

 

[Spanky]

Ahh, thankyou. I knew I would feel stupid when I found out the problem - that
was a holdover from the last bit of code I used that on. Works perfectly now.

As an extension, the code then runs an append query to repopulate the table
with the latest info. After the delete and append operations, the form
obviously has a lot of #Deleted's throughout. A simple 'Refresh All' will fix
this, but I'm trying to include this in the vb script. Me.Refresh and
Form.Refresh don't appear to to the trick.

Note that the form has some embedded subforms, but I'm only reaslly
interested in refreshing the main form.

 

[John Spencer]

I wonder why you don't filter the records in the append query so you don't
need to delete the records at all.

I'm probably missing some information and you have a good reason.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[Spanky]

Sorry, probably didn't describe this well enough.

The append query creates the 'auto' entries and adds them to the table. The
other 'manual' entries I'm trying NOT to delete are entered manually by the
user at some point. Hence, to refresh the list, I needed to delete all the
old 'auto' entries, and add the new 'auto' entries, leaving the 'manual'
entries in place. Hopefully that makes more sense (or less?).

Albert's solution allows this pefectly, but I'm now just trying to perform
the same function as the 'refresh all' button via vb script to clear up all
the broken records left over on the form after the table records are
regenerated.

 

[Spanky]

Okay, clearly my brain has departed and moved to a retirement village
somewhere - a simple Me.Requery fixed the problem.

Thanks for the help.