Delete Record by Field Info

  • Thread starter Thread starter DavidW
  • Start date Start date
D

DavidW

I need to find a way to delete records by a fields content.
Another words,
If you had a table "usage", with a field "veh" and if you wanted to delete
all records that pertain to a certain vehicle like(T10 which is in the "veh"
field).
How would you through vba code, delete all records that match the "T10" in
the recordsource.
I will do my own prompts for verification, hopefully without prompts from
Access asking to confirm the deletion.
Thanks
David
 
Why not use a Sql command to delete a set of records. Code might look like:

strVehToDelete = "T10"
strSql = "Delete * from usage Where veh = '" & strVehToDelete & "';"
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True

Ron W
 
I have never done a sql commands
Do you use them in vba?
And are there any reference librarys needed for them?
Thanks for responding
 
Ron Weiner said:
Why not use a Sql command to delete a set of records. Code might look
Click to expand...
like:

Is this done from within a form with a recordsource of the table that has
the records in it , or do you specify what table?
 
I tried this in VBA and varibles need to be declared for
strVehToDelete and
strSql

Very New To ME-SQL Statements
 
Yup they most certainly do when you have Option Explicit declared (as you
should)

Try this:
Create a new form
Add a command button by default it will be called Command0
Add the following code

Private Sub Command0_Click()
Dim strVehToDelete As String, strSql As String

strVehToDelete = "T10"
strSql = "Delete * from usage Where veh = '" & strVehToDelete & "';"
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True

End Sub

Run the form, push the button, and all of the records in the table usage
where the data in the field veh = T10 will be deleted. It will not ask any
questions or give you a chance to undo what you have done.

In the real world you have to devise a way to get the value you want into
the variable strVehToDelete, display at minimum a "Are you sure?" message,
and add some error handling. But that is the gist of it.

Ron W
 
I always use Option Explicit
I have a delete sequence where its very hard to get to, its on a form that
opens with the "where" statement that defaults to the selected vehicle from
the primary form. From that point they have to enter an administrative
password to remove a vehicle from service.

Thanks for the response!
 
Back
Top