need data just deleted

[GingerR]

acc2k--keeping a log of changed, added and deleted data. all fine, except i can't figure out the deleted data:

i have a main form.
in the subform, is just a list of related 'areas'.
a user can choose one and hit DELETE key.

once they confirm delete, i want to log into my EDITS table the value they have deleted, i.e. AREA DELETED: 4335

but i cannot get the 4335. have tried BeforeDeleteConfirm, AfterDeleteConfirm and On Delete events. where can i capture this data?

thanks--

 

[spiralmind]

don't know if this is what you're looking for, but you could set an onkeypress that finds the value of the listbox and activates some Append SQL or a query to add that data to your table. if the data is not in the bound column you'll have to use the "column" property of the listbox, but same idea.

 

[GingerR]

thanks but it's not a list box. it's just a subform. any other ideas? i'll try keypress--but then won't that fire too if someone is merely typing in new data?

 

[spiralmind]

you can specify the which key(s) the event is supposed to respond to using the keypress argument (keyAscii as integer). look up the ascii character codes in the access vba help (split into segments for 0-127 and 128-255).

insert an "if" statement that makes your code run only if the keyAscii argument (the key the user pressed) = the ascii code for the "delete" key

 

[spiralmind]

correction:

the keypress event won't work for you -

the "delete" key has no ascii keycode, and doesn't even trigger the event (backspace does, though)

you say, though, that they must confirm the delete?

if so, why not attach your event to their confirmation, or to the subform event that's triggering the warning (some event must be triggering it, even if it's just the "are you sure you want to delete [some number of] records from [some table]" built into access. you might have better luck with that.

 

[GingerR]

thanks again, but as i said in my first post, i have tried all of that. once any of those events are triggered, the data that's been deleted is gone. i'll try reposting and see if anyone else can help.

 

[MichaelRed]

Have already seen/read the faq on audit trail for Ms. A. and tried attaching it to the subform Before/after update event?




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[GingerR]

just found it will try at work tomorrow thanks

 

[GingerR]

bummer--looks like that only documents if data in a field is deleted, not if a record is deleted. other ideas?

 

[PaulF]

use can capture the Delete Key using Key Down event and check for vbKeyDelete

PaulF

 

[spiralmind]

you could store all data in a secondary, temporary source (such as an array in your code that you update every time you go to a new record). by putting this data in an unbound array, and programmatically controlling how it is updated, you could precede this data update with an event triggered by the deletion of a record on your subform. use this event to "dump" the data in this temporary secondary repository into your "deletions" table before clearing it (as it will stay there until your code removes it, giving you the ability to read it before erasing it).

you could also, as i've done before, add a yes-no "flag" to the table serving as the source of the subform, dissalow deletions on your table, add a "delete" button that really just clears a textbox, and building the form recordsource to show only checked records. it will appear to the user that the record has been deleted, and will allow you later access to that data so you can view it and/or put it anywhere you would like to keep it. (using an append/delete query combination - adding an "unchecked" parameter to each to move only the "deleted" records out of your main table). remember, if you do this, to requery after each simulated deletion to ensure the record no longer appears in the subform's recordsource, and to put the user back in a place that makes sense (the first record, the next record...)

 

[spiralmind]

i tried the vbKeyDelete constant myself before writing a previous response to this thread. didn't work. the constant's value is the same as that of one of the ascii keys (34, i believe, in decimal form - which would be the same as the " character). because of this the event never triggered for the delete key, but it would for the ascii key represented by the same numeric value.

 

[PaulF]

spiralmind

I'm not quite sure why it doesn't work.. I've created a form, with a subform, added code (to the subform, not the main form) to check for vbKeyDelete on the Key Down Event (Key Press Set to Yes), and captured the key event, and even added code to Delete the record, or not based on the response to a messagebox.

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
If KeyCode = vbKeyDelete Then
If MsgBox("Do You Wish To Delete This Record", vbYesNo, "Delete Key Pressed&quot = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
Else
Exit Sub
End If
End If

End Sub

PaulF

 

[GingerR]

funny--doesn't work for me either. did as you said paul, it doesnt recognize me pushing the delete key.
spiral--thanks yes i've thought of those kinds of things but was putting off for now, hoping that some function or event in access already handled it...if not soon, i will be diving in to alternative methods........

 

[GingerR]

ok--got a little further. dumb--i should have read the help earlier. but you know how painful that can be in Acc2000. so in Acc97 help, i cancelled the DELETE event - this does not take the data out of the table yet - and wrote my own code:

Private Sub Form_Delete(Cancel As Integer)
    Cancel = True
    If MsgBox("Are you sure you wish to delete this PI Area?", vbYesNo, "Status") = vbYes Then
    
         Dim rs As Recordset
         Set rs = CurrentDb.OpenRecordset("Select * from BundleEdits;")
         
         rs.AddNew
         rs!Bundle = Me.Bundle
         rs!EditID = Environ("Username")
         rs!EditDate = Now()
         rs!EditDetails = ("PI AREA: Deleted PI " & Me.PIArea)
         rs.Update
         Set rs = Nothing
         
         'Delete the record
         
         Dim rsDelete As Recordset
         Set rsDelete = CurrentDb.OpenRecordset("Select * from BundlePI where PIArea = '" & Me.PIArea & "' and Bundle = '" & Me.Bundle & "';")
         
         rsDelete.Delete
         Set rsDelete = Nothing
         
    Else
        Exit Sub
    End If

End Sub

so it deletes the record, and it writes a log record correctly to the log table. Now i have to figure out how to requery the two subforms (the PIArea list and the edit log list). the DELETE event apparently does not support this function (error message). but since i cancel the DELETE event, the OnBeforeDelete and OnAfterDelete do not fire (tested both). OnCurrent gives an error message upon opening the main form. so i'm still trying to find some place to requery/refresh the display, otherwise all i get is #deleted in the PIArea subform, and the new record not displaying in the Edit Log subform. still searching.....by the way thanks for all the help i'm learning more and more....g

 

[MichaelintheUK]

Hello GingerR

Could you try another approach and mark them as deleted or expired and then filter these items out where marked as deleted/expired.

Thanks

Michael

 

[GingerR]

ok got it...Delete transaction supports REFRESH but not REQUERY, so i refreshed the main form. works great...thanks again for all the help..

 

[PaulF]

GingerR
I was stumped as to why it didn't work for you, until I highlighted a field or the entire record, and then it didn't perform as requested. Previously I had only placed the cursor in the field, not selected the record, or an entire field. I still recieved the MsgBox stating that I had pressed the Delete Key, as part of the Key Down Event, but I also received the Confirm Delete Message and the Delete process would occur unless I clicked NO. So, I added Key Code = 0 to cancel the event.. I still get the Access Delete Confirm Message, but only If I click OK to confirm the Delete, even with Warnings Set To False.


Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
DoCmd.SetWarnings True
If KeyCode = vbKeyDelete Then
KeyCode = 0
If MsgBox("Do You Wish To Delete This Record", vbYesNo, "Delete Key Pressed&quot = vbYes Then
DoCmd.RunCommand acCmdDeleteRecord
End If
End If
DoCmd.SetWarnings False
End Sub


PaulF

 

[MichaelRed]

" ... looks like that only documents if data in a field is deleted ... "]/i]. Wrong, at least if you are reading the faq. It should -as written- capture every change to ever (bound) field, Wheather it us an addiition, an edit, or a deletion.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[GingerR]

it gets the OldValue of the field who's data you've deleted
it logs a NEW record, any edits to bound fields (including if you delete the data in a bound field) but not a deleted RECORD. it documents if data in a bound field is deleted, but not if the entire RECORD is deleted. that is what i was saying.