Before/After Delete Confirm Events

[verdes]

I'm using Access 2007

I have a table that contains transaction records from a bound form.

When the user enters a certain type of transaction, I create a corresponding transaction in the after insert event.

Example
User record - tranId = 1, tranAmt = 10, tranType = DISB, tranAssocID = 0
My record - tranId = 2, tranAmt = 10, tranType = RECV, tranAssocID = 1

When the user deletes a record, I want to delete my corresponding record using the tranAssocID ( delete * from tran_tbl where tranAssocID = (the tranID of the user's record)

Problem:
1) I don't know how to save the tranID of the users record
2) I don't know how to reference the saved tranID in the delete statement.

I'm using the before delete confirm event to try to save the users tranID to no avail.

I'm using the after delete confirm to delete my record in a case statement - acDeleteOK

Any help you can give will be appreciated.

I tried to save the value of the ID in the form (Me.tid) to a public long variable, a hidden field on the form, and a temporary variable to no avail.

 

[Cronk]

Another way is to define the relationship between the tables using the Relationships feature and set Cascading deletes on.

If you do want to use VBA, post your code that is not working.

 

[verdes]

Both records are in the same table, trans_tbl.

In the before delete confirm event , I currently have:

Dim ltiddd As Long
ltiddd = Nz(Me.tidd, 0)
Me.hidetransid = ltiddd

Me.tidd is the name of the bound text box on the form for transID
Me.hidetransid is the name of an unbound text box on the form

The recordsource for the form is trans_tbl.

In the after delete confirm event, I currently have:

Dim sql As String
Dim theidd As Long
theidd = Nz(Me.hidetransid, 0)
Select Case Status
Case acDeleteOK
' delete the
sql = "Delete * from transaction_tbl where transTranID = " & theidd & ";"
DoCmd.SetWarnings False
DoCmd.RunSQL sql
DoCmd.SetWarnings True
MsgBox ("the delete happened")


Case acDeleteUserCancel
MsgBox ("user cancelled delete")
Case acDeleteCancel
MsgBox ("application cancelled delete")
End Select

It does not delete the record I added via the after insert event

 

[Cronk]

"Both records are in the same table, trans_tbl."

However your SQL has delete from transaction_tbl.

Does the code actually execute?

I'd put a break point in the code and check the actual SQL string being generated,
.

 

[verdes]

Yes. It executes. I just wrote it in shorthand for posting.

 

[Cronk]

And did you get the SQL string being generated and paste that into a query and run it manually?

 

[verdes]

the sql string runs if I hard code a numerical value ( = 32)

I don't know how to save the ID of the original transaction for use later in the after delete confirm

I don't know how to reference the saved ID in the delete transaction.

 

[Cronk]

You have it stored in the text box hidetransid

However, put a break point on the line after sql ="....." by putting the cursor on the line and press the F9 key.

Delete a record and when the execution of the VBA stops, in the Immediate window type
? sql

Then paste the string into a new query and try to run the query.

If you are going to write code, you need to learn debugging techniques.

 

[verdes]

Thanks for your help.

I needed to use the On Delete event to save the transaction ID.

Everything is working now.

Thank you again.