Ilir,
This code is written using VB6 for access 2000.
The first part of the code gets the current connection for the project,
so that you can use the transaction model built into access 2000.
The second and third part of the code sets up the two commands that you
will use to run the queries in code.
Then, we begin the transaction... if either of the queries fails for any
strange reason, we want both of them to be canceled, which is why we
send execution to the rollback clause. For more transaction details, I'd
google "VB6 transaction" or something like that.
The Call cmd.Execute lines actually run the queries. The second
parameter of execute is an array with all of the Parameters for the
query, in order.
You can't add this code directly to a click event, because it doesn't
have the right signature for a click event.
Just add a regular OnClick event to the button, then, inside the on
click code, get the record ID of the record you want to delete (the
current one), and call this Sub from there. Something like:
Private Sub MyDeleteButton_Click()
Call Delete(Me![recordID])
End Sub
would be for a button named "MyDeleteButton" and a Key named "recordID".
You will have to replace recordID and MyDeleteButton with the correct
names for your situation. Also, in the queries, make sure that
everything is named according to your situation.
If you still get error messages you need to tell me exactly what the
error is, and what version of Access you are using.
Luke
ILIR said:
I had no problems with queries but i am stucked with the code. I tried to to
ad that code on a button (on click event) but I keep receiving errorr
messages. Can you tell me something more about this code
If you really want to move it, you'd set up two queries and run them in
a transaction.
TBL1: table ( recordID autonumber PRIMARY KEY, a text, b text, ... )
TBL2: table_del ( recordID long PRIMARY KEY, a text, b text, ... )
QRY1:
PARAMETERS recordIDIn Long;
INSERT INTO table_del ( recordID, a, b )
SELECT table.recordID, table.a, table.b
FROM
WHERE (((table.recordID)=[recordIDIn]));
QRY2:
PARAMETERS recordIDIn Long;
DELETE table.recordID
FROM
WHERE (((table.recordID)=[recordIDIn]));
Then, in code:
Public Sub Delete(recordID As Long)
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
Dim copy As New ADODB.Command
copy.ActiveConnection = conn
copy.CommandType = adCmdStoredProc
copy.CommandText = "QRY1"
Dim del As New ADODB.Command
del.ActiveConnection = conn
del.CommandType = adCmdStoredProc
del.CommandText = "QRY2"
On Error GoTo Rollback_Sub
conn.BeginTrans
Call copy.Execute(, Array(recordID))
Call del.Execute(, Array(recordID))
conn.CommitTrans
Exit_Sub:
Exit Sub
Rollback_Sub:
conn.RollbackTrans
MsgBox Err.Description
GoTo Exit_Sub
End Sub
You could make the copy query more complicated, ie add calculated fields
like the date or User id of who deleted it, etc.
Keep in mind that there are probably easier ways to do this, I just like
it because it separates the queries from the Delete code, and the
transaction means that one won't happen without the other.
All in all I'd stick with Rick B's suggestion of a flag field, unless
there are compelling reasons not to.
Good luck,
Luke
Rick said:
Typically, you would not move the record, you would simply insert the
Deleted time and date in a "deleted" field. Then, you can modify your
forms, queries, and reports to not pull anything with an entry in that
field.
Another common way is to add an "inactive" checkbox and exclude records
where the box is checked.
