How to clear a back end table that is used as a form control source

Posted on 2012-08-30
Medium Priority
Last Modified: 2012-08-31
I have a MS Access form that uses a table as the control source.  I would like to create a button so when the user click it ~ it will delete the contents of the table getting it ready for new entry.  I don't need to save the table contents.
Question by:deefel
LVL 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 240 total points
ID: 38353758
1. Add your button to the form.

2. n the proeprty sheet, find the OnClick event and enter "[Event procedure]" and click the builder button (...) off to the right.  A code window will open up.

3. In the code window, enter:

  CurrentDB().Execute "DELETE * FROM <table name>"

  in the above replace <table name> with the actual table name.

Close the code windwo and give it a try.

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 38355612
<No Points wanted, just some extra info>

<I don't need to save the table contents. >
OK, but a user can always click the "Delete" button by mistake....
...Thus clearing the table before it should be cleared.
(note that in Access, once a record is deleted, it cannot simply be recovered with an UNDO...)

I always throw code like this around any "destructive" code

If msgbox("Clear all records?",vbyesno+vbquestion)=ybNo then
    exit sub
    'Your Delete code here
end if



Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question