Hi Everyone:
I have a form with a delete command button that currently deletes employee records using SQL code. Below is the code: - Private Sub cmdDelete_Click()
-
On Error GoTo cmdDelete_Click_Err
-
Dim intResponse As Integer
-
-
If Me.NewRecord Or IsNull(Me.EmployeeID) Then
-
MsgBox "This record is null or new and cannot be deleted", vbOKOnly, "Delete Employee"
-
Exit Sub
-
End If
-
-
intResponse = MsgBox("Are you sure you want to delete this employee?", vbQuestion + vbYesNo, "Delete Employee")
-
If intResponse = vbYes Then
-
RunCommand acCmdDeleteRecord
-
End If
-
Exit Sub
-
-
cmdDelete_Click_Err:
-
MsgBox "Error is " & Err.Description
-
Exit Sub
-
-
End Sub
However, I want the record to go to a table called "EEDeleted"
How would I accomplish this?
Thanks you!
Other information would be : - 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.
- 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.
- 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.
- Private Sub cmdDelete_Click()
-
Dim strSQL As String
-
Dim cdb As DAO.Database
-
-
On Error GoTo cmdDelete_Click_Err
-
With Me
-
If .NewRecord Or IsNull(.EmployeeID) Then
-
Call MsgBox("This record is null or new and cannot be deleted", _
-
vbOKOnly, "Delete Employee")
-
Exit Sub
-
End If
-
-
If MsgBox("Are you sure you want to delete this employee?", _
-
vbQuestion Or vbYesNo, "Delete Employee") = vbYes Then
-
strSQL = "INSERT INTO [EEDeleted] " & _
-
"SELECT * " & _
-
"FROM [tblEmployee] " & _
-
"WHERE [EmployeeID]=" & .EmployeeID
-
Set cdb = CurrentDb
-
Call cdb.Execute(strSQL)
-
Call RunCommand(acCmdDeleteRecord)
-
End If
-
End With
-
Exit Sub
-
-
cmdDelete_Click_Err:
-
Call MsgBox("Error is " & Err.Description)
-
-
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
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).
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.
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
NeoPa 32,556
Expert Mod 16PB
Other information would be : - 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.
- 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.
- 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.
- Private Sub cmdDelete_Click()
-
Dim strSQL As String
-
Dim cdb As DAO.Database
-
-
On Error GoTo cmdDelete_Click_Err
-
With Me
-
If .NewRecord Or IsNull(.EmployeeID) Then
-
Call MsgBox("This record is null or new and cannot be deleted", _
-
vbOKOnly, "Delete Employee")
-
Exit Sub
-
End If
-
-
If MsgBox("Are you sure you want to delete this employee?", _
-
vbQuestion Or vbYesNo, "Delete Employee") = vbYes Then
-
strSQL = "INSERT INTO [EEDeleted] " & _
-
"SELECT * " & _
-
"FROM [tblEmployee] " & _
-
"WHERE [EmployeeID]=" & .EmployeeID
-
Set cdb = CurrentDb
-
Call cdb.Execute(strSQL)
-
Call RunCommand(acCmdDeleteRecord)
-
End If
-
End With
-
Exit Sub
-
-
cmdDelete_Click_Err:
-
Call MsgBox("Error is " & Err.Description)
-
-
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.
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. :)
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).
Sign in to post your reply or Sign up for a free account.
Similar topics
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,
......
|
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...
|
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...
|
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...
|
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...
|
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.
...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
| |