By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

Creating Archive Update Process Using VBA coding in Forms

P: 9
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:

Expand|Select|Wrap|Line Numbers
  1. Dim LResponse As Integer
  3.     If Me.Dirty Then LResponse = MsgBox("Do you wish to SAVE your changes?", vbYesNo)
  5.     If LResponse = vbYes Then   'User chose Yes - Udpated
  6.     [txtDateRecordUpdated].Value = Now()
  7.     [RecUpdated].Value = True
  8.     [txtRecordUpdatedBy].Value = Forms!frmUtility!Full_Name
  9.     DoCmd.RunCommand acCmdSave
  10.     'The below query looks for a field "RecUpdated" = True then appends this record to the archive table.
  11. DoCmd.OpenQuery "qryAppendArchive_AllActiveArchive_AddUpdatedRec"
  12.     [RecUpdated].Value = False
  13.     Else    'User chose No - Not Updated
  14.     End If
Nov 1 '13 #1
Share this Question
Share on Google+
6 Replies

Expert 100+
P: 1,221
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
Expand|Select|Wrap|Line Numbers
  1. docmd.CancelEvent
if you've got a condition to test.

Nov 3 '13 #2

Expert Mod 2.5K+
P: 3,127

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

Expand|Select|Wrap|Line Numbers
  1.     Dim intView as Integer
  2.     intView = Forms!FormName.CurrentView
  3.     Select Case intView
  4.         Case 1 ' Form View
  5.             'Add some code
  6.         Case 2 'Datasheet View
  7.             'Add some code
  8.     End Select
This should enable you to fire specific code based on the CurrentView property of the form.

Hope this helps!

Grace and Peace,
Nov 4 '13 #3

Expert Mod 5K+
P: 5,287
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!
Nov 6 '13 #4

Expert Mod 2.5K+
P: 3,127

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!

Nov 6 '13 #5

P: 9
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  3. Dim LResponse As Integer
  5.     If Me.Dirty Then LResponse = MsgBox("Do you wish to SAVE your changes?", vbYesNo)
  7.     If LResponse = vbYes Then   'User chose Yes - Udpated
  8.         [txtDateRecordUpdated].Value = Now()
  9.         [RecUpdated].Value = True
  10.         [txtRecordUpdatedBy].Value = Forms!frmUtility!Full_Name
  11.         DoCmd.RunCommand acCmdSave
  12.         DoCmd.OpenQuery "qryAppendArchive_AllActiveArchive_AddUpdatedRec"          MsgBox "Record Updated"
  13.         DoCmd.RunCommand acCmdSave
  14.         [RecUpdated].Value = False
  16.     Else    'User chose No - Not Updated
  18.     End If
  20. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub NavMenu_AfterUpdate()
  2. On Error GoTo ErrorMessage_Click
  4.     'Main Menu
  5.     'Due to Expire Dashboard
  6.     'Rate Role Reviews
  7.     'Former Employees
  8.     'Ineligible Resources (NIL)
  9.     'Resource History (Pre 2013)
  10.     'Table Maintenance
  11.     'Admisistrator Functions
  12.     'Issues/Suggestions
  13.     'Exit Database
  15.     If NavMenu = "Main Menu" Then DoCmd.OpenForm "frmSwitchboard_Admin", acNormal
  16.     If NavMenu = "Due to Expire Dashboard" Then DoCmd.OpenForm "frmDueToExpireMetricsByCategory", acNormal
  17.     If NavMenu = "Rate Role Reviews" Then DoCmd.OpenForm "frmRateRoleReview", acNormal, , , acFormEdit
  18.     If NavMenu = "Former Employees" Then DoCmd.OpenForm "frmFormerEmployees", acNormal, , , acFormEdit
  19.     If NavMenu = "Ineligible Resources (NIL)" Then DoCmd.OpenForm "frmIneligibleResourcesNIL", acNormal, , , acFormEdit
  20.     If NavMenu = "Resource History (Pre-2012)" Then DoCmd.OpenForm "frmResourceHistory", acNormal
  21.     If NavMenu = "Table Maintenance" Then DoCmd.OpenForm "frmDBMaint", acNormal
  22.     If NavMenu = "Administrator Functions" Then DoCmd.OpenForm "frmDBAdmin", acNormal
  23.     If NavMenu = "Issues/Suggestions" Then DoCmd.OpenForm "frmIssuesAndSuggestions", acNormal
  24.     If NavMenu = "Exit Database" Then DoCmd.RunMacro "macro_exit"
  26.     DoCmd.Close acForm, "frmRenewalTracking"
  28.     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
  30.     DoCmd.Maximize
  32. Exit_NavMenu_AfterUpdate:
  33. Exit Sub
  35. ErrorMessage_Click:
  36.     Resume Exit_NavMenu_AfterUpdate
  38. 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.
Nov 12 '13 #6

Expert Mod 2.5K+
P: 3,127

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:

Expand|Select|Wrap|Line Numbers
  1. Me.RecUpdate = True
Then, upon navigation away from this record, you have this:

Expand|Select|Wrap|Line Numbers
  1. If Me.RecUpdate Then
  2.     DoCmd.OpenQuery "qappArchiveOldRecord"
  3.     DoCmd.OpenQuery "qupdUpdateNewRecord"
  4. 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,
Nov 12 '13 #7

Post your reply

Sign in to post your reply or Sign up for a free account.