Append query/Delete Query/Refresh Form Issue

[T. McConnell]

So I have run into yet another fun issue with my database.
I have 3 different forms that I utilize the Audit Trail with, this part is working fine finally. I now am running into an issue where on my forms I want to move the records to a Deleted Table to have a better viewing of what was on the record when it was deleted. What I have done is I have the below code that is to log the Audit file with a DELETE action, (Works) then I have a function in place to send an email as a notification a record was deleted, (Works) Next I turn off the warnings and run the a query to Append the record to a deleted jobs table, then a another query to delete the record. I then turn the warnings back on, and what I want to happen is for the form to go to a new record. Everything works up to the New Record part, I have tried removing the New Record command and just refresh the form, but I get a 3164 runtime error saying field cannot be updated, when looking at the debug it takes me to any line I have trying to either refresh, requery, or go to a new record. Any help would be greatly appreciated. I have also tried to just place the code on the Delete button, but I do have a msgbox on that which verifies submission or deletion of the record. Do I need to break this code up to different events?

Private Sub Form_AfterDelConfirm(Status As Integer)
    Call AuditChanges("JobID", "DELETE")    'updates the audit log
        Call send_email1            'email function to send email for notification
        DoCmd.SetWarnings False            'turn off warnings
        DoCmd.OpenQuery "QryDeletedJobsAppend"    'Appends the record to the DeletedJobs Table
        DoCmd.OpenQuery "DeleteJobQry"        'Deletes the record
        DoCmd.SetWarnings True            'turns warnings on
        DoCmd.RunCommand (acCmdRecordsGoToNew)    'go to new record
End Sub

Thank you to anyone who can help with this.

 

[vba_php]

i'm no expert in this type of process, but if you delete records from a table that serves as the recordsource for your form, and your form is actually open at the time the records are delete, what you should see if your form, I believe if you're focus is on one of the deleted records or the *current* record is one of the deleted records, is "#deleted". it's very possible that you can't issue a document command statement (docmd) is the form of record navigation when the form is in that state. I know for a fact that the member here called "The_Doc_Man" has answered questions about this issue before, so hopefully he will see this and say something. you could also try these options:

=> issue a .requery command on the form before trying to navigate to a new record
=> close the form using .close and re-open it using docmd.openform

=> change your entire process via a data restructure so you don't even need a "deleted records" table but rather a single field
in the original table called "status" indicating whether or not the record is deleted or not

=> dropping the "deleted records" table completely and storing multiple copies of each record in the original table,
including with them a few new fields called "date updated", "currently used record?" and "deleted?"

you could also check this out: https://www.google.com/search?q=ms+access+form+3164+runtime+error

where did you get your code to do your audit trail with? I wrong an article on that concept many years ago on an England-based website for a consulting fee. I've attached a file that is on my github page as well. you might possibly be able to take something from it.

 

[isladogs]

Several things.
1. The AfterDelConfirm event occurs after records are deleted or after a deletion event is cancelled.
See Form.AfterDelConfirm event
So I believe you shouldn't be running a delete query as part of this event

2. I would avoid deleting records or moving them to a separate table. You are making unnecessary work for yourself and it may cause issues in the future. Instead add a Boolean field called Active and set true by default. Set false for records to be archived.

3. if you really must run those action statements, I would instead execute as SQL statements using code like this

CurrentDb.Execute "DELETE .....", dbFailOnError

Doing so will be more efficient and allow you to see any errors arising

4. There are many ways of moving to a new record. No brackets are needed for the RunCommand method

5. I'm not in front of my computer at the moment so can't check. What is the exact error 3164 message you got and which field / datatype is referenced?

 

[T. McConnell]

Thank you both for the info, I guess I never really thought of doing it where it doesn't necessarily delete and move the record, but just flags it with a field called deleted or whatnot. The person I'm creating this for was something he wanted to do because on the forms it had a monetary value that he reported off of, but in the report I can have it ignore the ones flagged "deleted" that way it still acts similar to what we have now.
So if I'm not asking a dumber question if I go this route and add a field to my Jobs table called deleted or something like that, on the main form for jobs would I just have a checkbox (yes/no) field that just simply is to "delete" a record, then have my submit order button like normal and remove my delete order button basically altogether? Then on my report or anything I want to exclude the "deleted" records just put in my query for such report? Is that how I'm understanding this? Sorry to sound ignorant, but I am still learning lol and have learned quite a bit from this forum.

Vba_php,
To answer your question about the audit trail, I've used a version by arnel (sorry for the life of me, can't remember the full name right now, but can look it up later) and have tweaked it a little bit. I'll look at your example a little later when I have some more time.

Isladogs,
The error I'm getting, my idiot self doesn't have the best error trapping in place so all I have to go off of is it flags the refresh or new record command in the vba code screen. I don't know the specific field, but I was thinking (and I could be wrong) but like Vba_php had said, I'm wondering if because the form remains open, it can't refresh it or load a new record because it doesn't have something to go by maybe.

Again thank you both for your help! It is much appreciated.

 

[vba_php]

T.Mc,

I'm currently tied up with some things, but it sounds like you're on the right track by the reading of your post. Hopefully isladogs or the others can offer a little more than this as a follow up to you.

 

[isladogs]

You can call your boolean field anything you like but I would recommend not using Deleted as it will confuse you. I normally use Active (with default true) or another alternative is Archived (with default false.
You can show that checkbox on your form to allow the record status to be modified as required. However I suggest you ask users to confirm that change before implementing it (and/or only allow certain users to alter the checkbox status)

The boolean field would be in place of your current Delete button.
You would need to restore any records already deleted with the boolean field value checked as appropriate.
The separate table & those queries should then be scrapped.

As you suggested, you just need to filter the records to only show those still active on your report

I can't really advise on that error 3164 (Field cannot be updated) without having more details.
Hopefully changing your approach will eliminate the error.

Why not add suitable error trapping now then come back if the error still occurs & if you still need help managing it.
I would be surprised if you need to close & reopen the form though there have been circumstances where I've needed to do that.

BTW - arnel's full user name is arnelgp. He does still post here occasionally but is much less active than in the past