Creating Archive Update Process Using VBA coding in Forms

[chip0105]

I created an archive process that records every update made to a record. These updates are written to an archive table.

There is a Navigation Drop-Down Menu located on each form. When the Nav Menu changes, and if the form is Dirty, the updated record is copied to the archive table, then moved to the Nav Menu selection. Simple enough.

However, some forms are able to be viewed in a DS format and here is where the problem comes in. If they close the form while in DS view things work OK. However, if they close from Form view I do not want the "Form Before Update" function to run as some updateable fields will no longer be available...the form has already closed.

Is there a way around this?? I want an archive record created when the form is closed OR when the the Nav Menu is used but not both to happen at the same time.

I am currently using the following formatted code:

Dim LResponse As Integer
 
    If Me.Dirty Then LResponse = MsgBox("Do you wish to SAVE your changes?", vbYesNo)
 
    If LResponse = vbYes Then   'User chose Yes - Udpated
    [txtDateRecordUpdated].Value = Now()
    [RecUpdated].Value = True
    [txtRecordUpdatedBy].Value = Forms!frmUtility!Full_Name
    DoCmd.RunCommand acCmdSave
    'The below query looks for a field "RecUpdated" = True then appends this record to the archive table.
DoCmd.OpenQuery "qryAppendArchive_AllActiveArchive_AddUpdatedRec"
    [RecUpdated].Value = False
    Else    'User chose No - Not Updated
    End If

[jimatqsi]

I don't understand what the problem is. Microsoft says this about order of events:
Exit (control) Arrow LostFocus (control) Arrow Unload (form) Arrow Deactivate (form) Arrow Close (form)

If you have changed data in a control, the BeforeUpdate and AfterUpdate events for both the control and the form occur before the Exit event for the control.

BeforeUpdate runs before the form is closed; AfterUpdate also. In any event, you could add a

docmd.CancelEvent

if you've got a condition to test.

Jim

[twinnyfo]

chip0105,

My initial thought is that for these forms that could switch between views, you should check the current view using this:

    Dim intView as Integer
    intView = Forms!FormName.CurrentView
    Select Case intView
        Case 1 ' Form View
            'Add some code
        Case 2 'Datasheet View
            'Add some code
    End Select
 

This should enable you to fire specific code based on the CurrentView property of the form.

Hope this helps!

Grace and Peace,
Twinnyfo

[zmbd]

chip0105:
Please do NOT double post nor bump your questions.
Because our experts and other members have answered in both threads, this time I have merged them...

- Hello Twinnyfo! You beat me to just about the same code I was going to post (^-^) in the other thread!

[twinnyfo]

Z,

So you're sayin' I actually got one right this time? ;-)

Glad to know I'm doing something right!

Good to hear from you again!

Peace!

[chip0105]

I apologize for posting my question twice. After over 140 individuals took the time to look at it and three days later, I decided it was not worth a look by anyone so I posted it again hoping to get a responce; which I obciously did. Again, sorry for posting twice.

With that said, I still am running into an issue with when updating a record.

I have the previously posted code showing that when the NAVMenu_AfterUpdate is changed, it looks for a dirty record then runs a process to append the record that was changed to an archive table. This part works fine.

However, if a record is changed and the user goes to a different record on the same form, the record does not get updated. So, I changed my NAVMenu_After update process.

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
Dim LResponse As Integer
 
    If Me.Dirty Then LResponse = MsgBox("Do you wish to SAVE your changes?", vbYesNo)
 
    If LResponse = vbYes Then   'User chose Yes - Udpated
        [txtDateRecordUpdated].Value = Now()
        [RecUpdated].Value = True
        [txtRecordUpdatedBy].Value = Forms!frmUtility!Full_Name
        DoCmd.RunCommand acCmdSave
        DoCmd.OpenQuery "qryAppendArchive_AllActiveArchive_AddUpdatedRec"          MsgBox "Record Updated"
        DoCmd.RunCommand acCmdSave
        [RecUpdated].Value = False
 
    Else    'User chose No - Not Updated
 
    End If
 
End Sub
 

When I update a record, the RecUpdated field is set to True for that record. I then have an append query that I run to look for these records and add them to an Archive table. The process updates the RecUpdated field as well as a couple additional fields for User Name, and date/timestamp of Now(). However, the append query does NOT append this record before reseting the RecUpdated field to False.

Q1: Am I performing these actions in the wrong order?
Q2: How can I perform these updates when they use a navigation drop-down to change to another form or exit the database as shown below in the NavMenui_AfterUpdate process:

Private Sub NavMenu_AfterUpdate()
On Error GoTo ErrorMessage_Click
 
    'Main Menu
    'Due to Expire Dashboard
    'Rate Role Reviews
    'Former Employees
    'Ineligible Resources (NIL)
    'Resource History (Pre 2013)
    'Table Maintenance
    'Admisistrator Functions
    'Issues/Suggestions
    'Exit Database
 
    If NavMenu = "Main Menu" Then DoCmd.OpenForm "frmSwitchboard_Admin", acNormal
    If NavMenu = "Due to Expire Dashboard" Then DoCmd.OpenForm "frmDueToExpireMetricsByCategory", acNormal
    If NavMenu = "Rate Role Reviews" Then DoCmd.OpenForm "frmRateRoleReview", acNormal, , , acFormEdit
    If NavMenu = "Former Employees" Then DoCmd.OpenForm "frmFormerEmployees", acNormal, , , acFormEdit
    If NavMenu = "Ineligible Resources (NIL)" Then DoCmd.OpenForm "frmIneligibleResourcesNIL", acNormal, , , acFormEdit
    If NavMenu = "Resource History (Pre-2012)" Then DoCmd.OpenForm "frmResourceHistory", acNormal
    If NavMenu = "Table Maintenance" Then DoCmd.OpenForm "frmDBMaint", acNormal
    If NavMenu = "Administrator Functions" Then DoCmd.OpenForm "frmDBAdmin", acNormal
    If NavMenu = "Issues/Suggestions" Then DoCmd.OpenForm "frmIssuesAndSuggestions", acNormal
    If NavMenu = "Exit Database" Then DoCmd.RunMacro "macro_exit"
 
    DoCmd.Close acForm, "frmRenewalTracking"
 
    If IsOpen("frmRenewalTracking") Or IsOpen("frmDueToExpireMetricsByCategory") Or IsOpen("frmRateRoleReview") Or IsOpen("frmFormerEmployees") Or IsOpen("frmIneligibleResourcesNIL") Or IsOpen("frmResourceHistory") Or IsOpen("frmDBMaint") Or IsOpen("frmDBAdmin") Or IsOpen("frmIssuesAndSuggestions") Then DoCmd.OpenForm "frmSwitchboard_Admin", , , , , acHidden
 
    DoCmd.Maximize
 
Exit_NavMenu_AfterUpdate:
Exit Sub
 
ErrorMessage_Click:
    Resume Exit_NavMenu_AfterUpdate
 
End Sub
 

I cannot seem to get an archive process to work when the record gets an update and the form changes. I can only get one process to work at a time. Any suggestions? This has been really aggravating.

[twinnyfo]

chip0105,

I hope I explain this properly....

Perhaps I am a bit confused by your code, but I think I understand that you want to archive any records that have been changed, while keeping the changes. Is this correct?

My thought on this would be to have your form use data from a recordset to populate the values in your text boxes (i.e. the data on your Form is NOT the data in your Table). Then, whenever any textbox is updated, you can change your RecUpdate field to true. It is very important to keep track of the Record ID (primary key).

Then, anytime the user navigates away from this record (either by going to another record or navigating to another Form, BEFORE that action is executed, you copy the record from your Table to your Archive Table (you have the Primary Key, so this is an easy Append action), and then you take the current data on the Form and update the record in the Table (again, using the Primary Key, an easy Update).

One key for this to work properly is that you would have to restrict the user to being able to view only one record at a time and you would have to control the navigation (lots of ways to do this).

So, in general, all your text boxes would have the following code in the AfterUpdate Proerty:

Me.RecUpdate = True

Then, upon navigation away from this record, you have this:

If Me.RecUpdate Then
    DoCmd.OpenQuery "qappArchiveOldRecord"
    DoCmd.OpenQuery "qupdUpdateNewRecord"
End If

I'm not sure if I am completely clear.

I am not sure I am certain how you want your Form to Archive data, either. From your code, it seems like it is "archiving" the new record, reather than the old.... That could be what you want to do.

HTH, provide more info if I am way off base. I really want to assist.

Grace and Peace,
twinnyfo