Deleting Multiple Records From A Form

  • Thread starter Thread starter Jeff Garrison
  • Start date Start date
J

Jeff Garrison

I have a form that contains multiple records...simple enough. What I want
to do is to be able to delete arbitrary records from the form. I have a
check box for each record (a "selected" box). The logic I have is to select
the record(s) and then click on a Delete button, that will delete ONLY the
selected records.

Is my approach to this correct? If not, point me in the right direction.
If so, what code would I need to use. I'm sure it's either a Do...While or
Do Until or something along those lines.

Thanks

Jeff G
 
Hi Jeff

Assuming your "Selected" box is bound to a yes/no field in your table named
"Selected", you can do it with a SQL "delete" statement:

Dim db as DAO.Database
Me.Dirty = False ' save the current record if necessary
Set db = CurrentDb
db.Execute "Delete from [your table name] where Selected<>0", _
dbFailOnError
MsgBox db.RecordsAffected & " record(s) have been deleted"
Me.Requery ' requery the form
 
Thanks for the reply.

When I click the button to execute, I immediately get a Compile Error;
User-defined type not defined and the first line is highlighted.

Any clue on what's wrong?

Thanks.
JG

Graham Mandeno said:
Hi Jeff

Assuming your "Selected" box is bound to a yes/no field in your table
named "Selected", you can do it with a SQL "delete" statement:

Dim db as DAO.Database
Me.Dirty = False ' save the current record if necessary
Set db = CurrentDb
db.Execute "Delete from [your table name] where Selected<>0", _
dbFailOnError
MsgBox db.RecordsAffected & " record(s) have been deleted"
Me.Requery ' requery the form

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jeff Garrison said:
I have a form that contains multiple records...simple enough. What I want
to do is to be able to delete arbitrary records from the form. I have a
check box for each record (a "selected" box). The logic I have is to
select the record(s) and then click on a Delete button, that will delete
ONLY the selected records.

Is my approach to this correct? If not, point me in the right direction.
If so, what code would I need to use. I'm sure it's either a Do...While
or Do Until or something along those lines.

Thanks

Jeff G
Click to expand...
Click to expand...
 
Hi Jeff

In Access 2000, when ActiveX Data Objects (ADO) was all new and shiny,
someone made the crazy decision to make ADO the default data access library
for new Access databases. (They have reversed this change in later
versions, thank goodness!) My guess is that your database has this setting.

From the code window, go to Tools->References. Find and check:
Microsoft DAO 3.x Object Library
(where "3.x" is the highest version you can see - probably 3.6)

If you are NOT using any ADO code anywhere in your database, you should also
uncheck the reference to:
Microsoft ActiveX Data Objects 2.x Library

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand


Jeff Garrison said:
Thanks for the reply.

When I click the button to execute, I immediately get a Compile Error;
User-defined type not defined and the first line is highlighted.

Any clue on what's wrong?

Thanks.
JG

Graham Mandeno said:
Hi Jeff

Assuming your "Selected" box is bound to a yes/no field in your table
named "Selected", you can do it with a SQL "delete" statement:

Dim db as DAO.Database
Me.Dirty = False ' save the current record if necessary
Set db = CurrentDb
db.Execute "Delete from [your table name] where Selected<>0", _
dbFailOnError
MsgBox db.RecordsAffected & " record(s) have been deleted"
Me.Requery ' requery the form

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

Jeff Garrison said:
I have a form that contains multiple records...simple enough. What I
want to do is to be able to delete arbitrary records from the form. I
have a check box for each record (a "selected" box). The logic I have is
to select the record(s) and then click on a Delete button, that will
delete ONLY the selected records.

Is my approach to this correct? If not, point me in the right
direction. If so, what code would I need to use. I'm sure it's either a
Do...While or Do Until or something along those lines.

Thanks

Jeff G
Click to expand...
Click to expand...
Click to expand...
 
Back
Top