473,320 Members | 1,955 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Delete and archive to a table!

Hi Everyone:

I have a form with a delete command button that currently deletes employee records using SQL code. Below is the code:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDelete_Click()
  2.  On Error GoTo cmdDelete_Click_Err
  3. Dim intResponse As Integer
  4.  
  5. If Me.NewRecord Or IsNull(Me.EmployeeID) Then
  6.  MsgBox "This record is null or new and cannot be deleted", vbOKOnly, "Delete Employee"
  7.  Exit Sub
  8.  End If
  9.  
  10.  intResponse = MsgBox("Are you sure you want to delete this employee?", vbQuestion + vbYesNo, "Delete Employee")
  11.  If intResponse = vbYes Then
  12.  RunCommand acCmdDeleteRecord
  13.  End If
  14.  Exit Sub
  15.  
  16. cmdDelete_Click_Err:
  17.  MsgBox "Error is " & Err.Description
  18.  Exit Sub
  19.  
  20. End Sub
However, I want the record to go to a table called "EEDeleted"

How would I accomplish this?

Thanks you!
Jul 14 '11 #1

✓ answered by NeoPa

Other information would be :
  1. The names of both the field (in the table) and the control (on the form). You provide a name but don't make it clear which it is (If it's both then, as a novice, you are in a good position to avoid that habit in future. Generally prefixing the fieldname with "txt" for TextBox, or other TLA for other controls, is recommended). I will assume for now that both field and control are named [EmployeeID]. You can change to suit your own situation if necessary.
  2. Your preferences about which approach you prefer to handle small record updates. Some prefer recordset processing entirely within the VBA (Either DAO or ADODB), while others prefer to get the work done using a SQL command. As a novice you may not even know of these, let alone have a preference yet ;-) I will assume for now that a SQL approach will suit.
  3. The name of the table where the deleted record is from. This may be the Record Source of your form, but it needn't be necessarily. I will use [tblEmployee] for the name of your table. You can change to suit your own situation.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDelete_Click()
  2.     Dim strSQL As String
  3.     Dim cdb As DAO.Database
  4.  
  5.     On Error GoTo cmdDelete_Click_Err
  6.     With Me
  7.         If .NewRecord Or IsNull(.EmployeeID) Then
  8.             Call MsgBox("This record is null or new and cannot be deleted", _
  9.                         vbOKOnly, "Delete Employee")
  10.             Exit Sub
  11.         End If
  12.  
  13.         If MsgBox("Are you sure you want to delete this employee?", _
  14.                   vbQuestion Or vbYesNo, "Delete Employee") = vbYes Then
  15.             strSQL = "INSERT INTO [EEDeleted] " & _
  16.                      "SELECT      * " & _
  17.                      "FROM        [tblEmployee] " & _
  18.                      "WHERE       [EmployeeID]=" & .EmployeeID
  19.             Set cdb = CurrentDb
  20.             Call cdb.Execute(strSQL)
  21.             Call RunCommand(acCmdDeleteRecord)
  22.         End If
  23.     End With
  24.     Exit Sub
  25.  
  26. cmdDelete_Click_Err:
  27.     Call MsgBox("Error is " & Err.Description)
  28.  
  29. End Sub
Notice the indentation of the code. This is never accidental, but each indented line gives a clue to the reader to various things about the structure. It's never a good idea to indent randomly as this can make difficult to read code even more of a struggle.

6 2435
gnawoncents
214 100+
There are a few ways to do this, and mine probably isn't the most efficient, but you could create an append and a delete query. Add a column to your table that gets updated when your delete code runs (say with a -1), then call your append query to copy all recors with the -1 value to the EEDeleted table, then call the delete query to delete all with the -1 value (or continue with acCmdDeleteRecord).
Jul 14 '11 #2
NeoPa
32,556 Expert Mod 16PB
Karen, I'm sorry to contradict you, but there is no SQL code in your question. The code is VBA and the line that actually does the deleting (#12) deletes the record currently selected on your form.

To ensure an archive (or Log) is kept of deleted records in another table you would need to include extra code before line #12 (and after line #11) that copied your data to a new table. There are so many ways this can be done and so many different circumstances to handle that depend on your preferences and other information we don't have (such as whether the record contains a PK and whether that is available to the form) that I won't proceed from this point at this time.

If you want to add the relevant information so that we have a question we can work on in more detail then be my guest.
Jul 14 '11 #3
Thanks to both of you, gnaw and neo. As you can see, I am a novice.

I do have a PK on the form. It is "EmployeeID" (Autonumber data type). Is there any other information you need?

Karen
Jul 15 '11 #4
NeoPa
32,556 Expert Mod 16PB
Other information would be :
  1. The names of both the field (in the table) and the control (on the form). You provide a name but don't make it clear which it is (If it's both then, as a novice, you are in a good position to avoid that habit in future. Generally prefixing the fieldname with "txt" for TextBox, or other TLA for other controls, is recommended). I will assume for now that both field and control are named [EmployeeID]. You can change to suit your own situation if necessary.
  2. Your preferences about which approach you prefer to handle small record updates. Some prefer recordset processing entirely within the VBA (Either DAO or ADODB), while others prefer to get the work done using a SQL command. As a novice you may not even know of these, let alone have a preference yet ;-) I will assume for now that a SQL approach will suit.
  3. The name of the table where the deleted record is from. This may be the Record Source of your form, but it needn't be necessarily. I will use [tblEmployee] for the name of your table. You can change to suit your own situation.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDelete_Click()
  2.     Dim strSQL As String
  3.     Dim cdb As DAO.Database
  4.  
  5.     On Error GoTo cmdDelete_Click_Err
  6.     With Me
  7.         If .NewRecord Or IsNull(.EmployeeID) Then
  8.             Call MsgBox("This record is null or new and cannot be deleted", _
  9.                         vbOKOnly, "Delete Employee")
  10.             Exit Sub
  11.         End If
  12.  
  13.         If MsgBox("Are you sure you want to delete this employee?", _
  14.                   vbQuestion Or vbYesNo, "Delete Employee") = vbYes Then
  15.             strSQL = "INSERT INTO [EEDeleted] " & _
  16.                      "SELECT      * " & _
  17.                      "FROM        [tblEmployee] " & _
  18.                      "WHERE       [EmployeeID]=" & .EmployeeID
  19.             Set cdb = CurrentDb
  20.             Call cdb.Execute(strSQL)
  21.             Call RunCommand(acCmdDeleteRecord)
  22.         End If
  23.     End With
  24.     Exit Sub
  25.  
  26. cmdDelete_Click_Err:
  27.     Call MsgBox("Error is " & Err.Description)
  28.  
  29. End Sub
Notice the indentation of the code. This is never accidental, but each indented line gives a clue to the reader to various things about the structure. It's never a good idea to indent randomly as this can make difficult to read code even more of a struggle.
Jul 15 '11 #5
Thank you so much for your help. It works beautifully. I should have posted to the forum earlier and saved myself a lot of headaches. :)
Jul 18 '11 #6
NeoPa
32,556 Expert Mod 16PB
Don't post too early. Always know we're here for you if needed, but part of the learning and expanding process is to go through some of the head-aches ;-)

That said, you're very welcome and I'm pleased that helped you.

May I just suggest though, that you reset the current Best Answer from post #3 and set it to post #5. I'm sure any other searchers would find the latter more helpful. To start you off I'll reset the current one for you (but I can't set #5. You'll need to do that).
Jul 18 '11 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

8
by: jim | last post by:
I have two tables that are related by keys. For instance, Table employee { last_name char(40) not null, first_name char(40) not null, department_name char(40) not null, age int not null, ......
5
by: Jucius | last post by:
Using Delphi for developing, I get and delete records from an Access Database 2000. Sometimes, It happened 3 times (but not reproductible), the application did not succeed in connecting the...
5
by: Tony | last post by:
hi NG I have got a database, and from this database I want to delete a table in another database, before Iexport a new table to that other database. My question is how can I delete a table in...
2
by: indigo | last post by:
hi.. can someone suggest the best way to move data from one table to another. i'm trying to do this mainly due to my own limitations with regard to access. i have a form which has various cells...
4
by: Ian | last post by:
Hi, I have a problem with delete using where in clause. This is a query: delete from tab1 where id not in (select id from tab2) I calculated costs using select instead of delete: select...
10
by: nickvans | last post by:
Hello everyone, I'm fairly new to VBA and MS Access (I'm using 2003) but my issue seems like a pretty straight forward one. I would like to delete all records found in one table from another one. ...
2
vanc
by: vanc | last post by:
I'm trying to delete a table with just around 2000 rows. What I got is timeout error. I can't use Truncate Table, which is very quick, because I have Trigger with Delete command. Is there any way to...
5
by: alex250374 | last post by:
I have a db is in archive mod (LOGRETAIN = RECOVERY) and logprimary is set to 3. Pat the log file is / arch_log. If I go in that path i have: S0000000.LOG S0000001.LOG S0000002.LOG S0000003.LOG...
3
topher23
by: topher23 | last post by:
I have a query that isn't working as expected. The query links to one table used for data entry and has outer joins on two tables used to display information based on choices entered. I can...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.