Deleting All Records

M

Mike

Is there a way I can assign a button to delete all record in a record source
for a form? The data placed in this form is only for temporary use.
 
J

John Vinson

Is there a way I can assign a button to delete all record in a record source
for a form? The data placed in this form is only for temporary use.
Click to expand...

Put code like this in the button's Click event (select the ... icon by
the event and choose Code Builder):

Private Sub cmdEraseData_Click()
DoCmd.RunSQL "DELETE * FROM tablename;"
End Sub

where tablename is the Table in which the temporary data is contained.
Note that this is very unforgiving - it will permanently and
irretrievably erase all the data from the table.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
M

Mike

BEAUTIFULLY UNFORGIVING!!!! Thank You

John Vinson said:
Put code like this in the button's Click event (select the ... icon by
the event and choose Code Builder):

Private Sub cmdEraseData_Click()
DoCmd.RunSQL "DELETE * FROM tablename;"
End Sub

where tablename is the Table in which the temporary data is contained.
Note that this is very unforgiving - it will permanently and
irretrievably erase all the data from the table.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
D

Douglas J. Steele

Mike said:
BEAUTIFULLY UNFORGIVING!!!! Thank You
Click to expand...

Far be it from me to contradict John, but if you are ever in a situation
where that code doesn't work, try the following instead:

Private Sub cmdEraseData_Click()
On Error GoTo Err_cmdEraseData_Click

CurrentDb.Execute "DELETE * FROM tablename", dbFailOnError

End_cmdEraseData_Click:
Exit Sub

Err_cmdEraseData_Click:
MsgBox Err.Description & " (" & Err.Number & ")"
Resume End_cmdEraseData_Click

End Sub

This should raise a message box containing a description of the error's that
occurring.

Note that if you're using Access 2000 or 2002, you may have to add a
reference to the Microsoft DAO 3.6 Object Library if you don't already have
one.
 
A

Alvin

Small Problem, what if "tablename" has a space like "Table Name"
Private Sub cmdEraseData_Click()
On Error GoTo Err_cmdEraseData_Click

CurrentDb.Execute "DELETE * FROM tablename", dbFailOnError

End_cmdEraseData_Click:
Exit Sub
 
Top