Run Code on Delete

[GulfImages]

Hello,

I have some code that runs on a payment form that goes into my Order Table and marks paid to true or false based on the payment amount subtracted from the order amount, here is the code:
--------
Dim PdI As Database, PdOrd As Recordset
Set PdI = DBEngine.Workspaces(0).Databases(0)
Set PdOrd = PdI.OpenRecordset("tblInvoices", DB_OPEN_TABLE)

PdOrd.Index = "OrderID"
PdOrd.Seek "=", OrdID

PdOrd.Edit

If IsPaid(OrdID) Then PdOrd!InvPaid = -1
If IsPaid(OrdID) = 0 Then PdOrd!InvPaid = 0

PdOrd.Update

PdOrd.Close

Set PdI = Nothing
Set PdOrd = Nothing
------------

This works perfectly on the afterinsert and afterupdate events of the payments form but when I delete a record it will give me the following error:

Run Time Error '3021'
No Current Record

It's happening on the PdOrd.Update portion of the code.

I've tried it on the OnDelete and AfterDelConfirm events of my payment form, but it is obviously happening because the record is already deleted and OrderID is no longer a valid variable. The payment is deleted but the Invoice is still marked as paid.

Any Ideas?
Thanks,
Bobby Cunningham

 

[TheAceMan1]

How are ya GulfImages . . . . .

Unless I'm missing something, it appears your [blue]payment forms[/blue] [purple]Record Source[/purple] is based on the same table [blue]tblInvoices[/blue]. If so, [purple]your trying to update a record your deleting[/purple] and what would be the point in that?

See Ya! . . . . . .

 

[willir]

Regardless, some bullet proofing...

Dim PdI As Database, PdOrd As Recordset
Set PdI = DBEngine.Workspaces(0).Databases(0)
Set PdOrd = PdI.OpenRecordset("tblInvoices", DB_OPEN_TABLE)

If Nz(OrdID, 0) > 0 Then

     PdOrd.Index = "OrderID"
     PdOrd.Seek "=", OrdID

     PdOrd.Edit

     If IsPaid(OrdID) Then PdOrd!InvPaid = -1
     If IsPaid(OrdID) = 0 Then PdOrd!InvPaid = 0

     PdOrd.Update

End If

I am assuming OrdID is numeric. If not, then use

If Len(Nz(OrdID,"")) > 0 Then...

Richard

 

[RoyVidar]

Some more bullet proofing, in case TheAceMan1 is correct - I tend to always test whether a search/find operation has found anything before trying to do something on the recordset. 3021, means exactly that, there is not current record (deleted, not found...) based on the given criteria.

[tt]PdOrd.Index = "OrderID"
PdOrd.Seek "=", OrdID
if not PdOrd.Nomatch then
PdOrd.Edit
' other update code
else
msgbox "record not found/deleted..."
endif[/tt]

But the challenge here seems to be that you run your delete routine before this code. Could you reverse the order, or do both operations together? Or are you perhaps allowing the user to delete thru the ordinary user interface? In the latter case, you could try dumping the primary key of the control of the current record (Me!OrdId?) to a form public variable in the on current event of the form (i e as you enter each record), and use that in your delete code.

I'm not entirely sure of the "on delete" event's, but I think the records are either deleted, or in a "delete buffer" when entering the beforedelconfirm and afterdelconfirm, so referencing the current record(s) would probably not be available, but I'd thought the on delete should still be able to reference the current record (and offer a possibility to cancel).

Roy-Vidar

 

[GulfImages]

Thanks Guys,

The Payments form is NOT based on the same table as the order table. The payments table has OderID as one of it's fields. After the change or delete in this case, my code above goes into the Orders table and marks the order paid or unpaid. It works fine for new payment records or changed payments records, but when the user deletes a payment, my code will not run as the OrderID from the payments table has been deleted. I think I may have just thought of something as I'm typing this, I'll post if it works.

Thanks again!

 

[TheAceMan1]

GulfImages . . . . .

[blue]If IsPaid(OrdID) Then PdOrd!InvPaid = -1
If IsPaid(OrdID) = 0 Then PdOrd!InvPaid = 0[/blue]

I am missing something. Even if The Payments form is based on another table, your still deleting the [purple]OrdID![/purple]

See Ya! . . . . . .

 

[GulfImages]

Yes, OrdID is being deleted from the Payments table. You mentioned that Payments form and Orders form seemed to be in the same table, that's why I mentioned thye are not.
I wrote the MarkPaid code to update the Invoice table after a payment has been entered, modified or deleted.

When the payment is deleted, the field within that payment record (OrderID) is being deleted along with it, so I am trying to figure out how to catch that OrderID number to use in my code that updates the Invoice table and marks it paid before it is removed from the payments table. Am I making sense?

 

[GulfImages]

Well, I figured it out. I had to make a Public variable in the payments form code then on the OnDelete event, I set the variable to the OrderID, then in the afterconfirmdelete event I run the code to mark the invoice in the Invoice table. For some reason, I could not set the variable, then run the code all from the OnDelete event, but it works now.

Thanks for the help!