Audit Trail using Data Macros

There are different ways to do audit trails in Access. Up until Access 2010, these had to be implemented on the Form level. But Access 2010 has Data macros which operate on the Table level, so they will capture if someone bypasses your security (see my blog on Login Security using VBA) and makes changes in the table itself.
Setting up such an AuditTrail is a little more cumbersome but does provide a greater level of protection.
The first step is creating a table to hold the Audit Trail information. The following shows the design of this table:

The fields are pretty obvious. This table records the table and field that was changed, the RecordID (PK Value) of the changed record, the old and new value and who changed the record and when.
The next step is to open the table you want to audit in Design Mode. From the Table Design ribbon pull down the list of Data Macro events and select After Update event as shown below:

This will open the Macro Design environment where you can build the macro.
The screen shot below shows the macro commands to cover one field:

DataMacro

The first command is an IF command that is triggered if the field value changes. It uses the Updated function with the field name as the argument. The next step is the Create a Record command which uses the argument of tblAuditTrail. This will be the name of your Audit Trail table.

From there you add a SetField command for each field in your Audit Trail table (except for the autonumber PK, which is filled in automatically). The first field is the Tablename which is hardcoded. The next is the RecordID which is filled in by a reference to the fieldname. Then the ChangeBy field is populated with the ID of the user. In this example I am using the fOSUsername() function found at http://www.mvps.org/access (see Note). Next, I populate the FieldName, which is again hardcoded. Following are the Old and New values. I use the Old command with the fieldname as the property to capture the previous value. Then reference the tablename.fieldname. Finally, I set the ChangeDate to Now().
To place an Audit Trail on other fields, you can collapse the If and then copy and paste to add another set. Change the fieldname information as needed.
Save the macro and close the editor.
You can test by making changes to the fields you have place an audit trail on and then checking tblAuditTrail to make sure the changes were recorded.
You can also capture Deletes similarly using the After Delete event and writing all the fields to a deleted records table. Again using Create A Record and SetField for each field.
I have a sample file (see link below) that contains a table with Audit Trail and the Audit trail table as well as a form to edit the audit trail table and modules with the needed functions. Note: Both Front end and back end need to be in the same folder.

Note: If you use my Login Security techniques or some other way of capturing the current user, you may need to create your own function to replace fOSUsername(). Following is an example:

Public Function fUser()
fUser = Forms!frmLogin!cboUser
End Function

You can assign to fUser whatever you use to capture the current logged in user.

Limitation: This does not work with certain data types. An error 20342 is thrown with description The Updated function is not supported for memo, rich text, hyperlink, OLE Object, multi-value, or attachment fields. If you have one of those data types, then I would recommend using a VBA alternative (see Comments). In the case of multi-value or attachment data types, you can replace then with a child table.

Audit Trail Sample

162 Comments

  1. Hello,
    I greatly appreciate this walk through and descriptions with the pictures and examples but I have some questions as I am having troubling getting this to work correctly.

    Please let me know if you have a second and how I can contact you.

    Thank you,
    Eric

    • Scott,

      Thank you so much for looking carefully. I should have caught that, I was not paying close enough attention. I works!

      Thank you very much. I will be sure to pass this sit and the other site you mentioned along. I was searching for your exact post for a while and you made it extremely easy. I am sure I will be looking at your other stuff.

      Eric

  3. Hi Scott,

    Thanks for this tutorial! It works perfect.

    You wrote:
    You can also capture Deletes similarly using the After Delete event and writing all the fields to a deleted records table. Again using Create A Record and SetField for each field.

    I am trying to get the delete function working but it doesn’t.
    Could you please help me with the macro code by giving an example of what should be in there like you did with the update function?

    I made the backup database (should it all be text fields or exact the same as the original database?)

    Hope to hear from you.

    Greetings from the Netherlands,
    Stephan

  4. I am having trouble with the code, I am getting the following error: “Type mismatch on field “tblAudit.RecordID”.

    tblAudit.RecordID is a number – long integer
    the data I am trying to send into tblAudit.RecordID is an Autonumber – long integer.

    I am using this macro to track changes to one field in a table, the tblAudit.RecordID is filled with the Primary Key field of tblDocuments.

    Can this be solved? Thanks.

    • Hi Nina,

      Ok, so you set up the Audit trail table with a field named RecordID that is set for Number Long Integer. And you are trying to populate that field with an AutoNumber field from the record being modified? And you have a Setfield Action in your macro that assigns to tblAudit.RecordID the PK from the table being modified?

      That should work. Are you sure that you have the datatypes correct?

      Scott

  5. Thanks for the instructions.

    For the most part, it seems to work very well. However, I did run into a limitation it seems, I am receiving error 20342 with description (The Updated function is not supported for memo, rich text, hyperlink, OLE Object, multi-value, or attachment fields.) Is there anyway around it? I want to still keep track of memo fields so I can see all the changes in one table. Thanks!

    • First, I want to thank you for reporting that. I will revise the blog to mention that. But if its not supported I can see no way around it, using data macros. However, if your app can use VBA, you can use a VBA based Audit Trail. Here’s the code I use:

      Public Sub AuditTrail(strTable As String, strField As String, lngRecord As Long, varOld As Variant, varNew As Variant)

      Dim strSql As String, strUser As String

      strUser = DLookup(“[EmpID]”, “tblParameters”)

      strSql = “INSERT INTO tblAuditTrail (Tablename, Fieldname, RecordID, OldValue, NewValue, ChangeDate, ChangeBy) ” & _
      “VALUES(‘” & strTable & “‘, ‘” & strField & “‘, ” & lngRecord & “, ‘” & varOld & “‘, ‘” & varNew & “‘, #” & _
      Now() & “#, ‘” & strUser & “‘);”
      CurrentDb.Execute strSql
      End Sub

      This is called by the following line of code into the Before Update event of each control that you want to audit:
      Call AuditTrail(Screen.ActiveForm.RecordSource, Screen.ActiveControl.ControlSource, Me.PrimaryKey, Screen.ActiveControl.OldValue, Screen.ActiveControl)

      You only need to change Me.PrimaryKey to reflect the control bound to your Primary Key. That should work with all but multi-value fields and attachment datatypes. If there is a need for an audit trail on such types, then I wouldn’t use them but use a child table instead.

      Scott

      Scott

  6. Scott,

    I can get the fOSUserName function to work on a form with an unbound text box but cannot figure out how to make this work in a query to append into a table. Any chance you could help out? Many thanks.

    Cheers,
    Leah

    • I’m not sure it will work on an attachment field. I’ve never tried it. I don’t think it would work on any field that allows multiple values. As far as a memo field. you might have to modify your audit table to include memo fields and then modify the code to append the old and new values to those fields instead.

      Scott

      • i don’t have multiple value field. If someone add an attachment, will it do nothing or will access make an error message ?

        I have very little experience with macro/vba and i think an audit trail that doesn’t keep the old/new attachments will be better than no audit trail at all.

  8. Etienne,
    First, I just reviewed the blog and I forgot, I included a Limitations section at the end which specifies that it won’t work with certain fields including attachments or memo fields. And that it will throw an error message with those fields.

    The way this is designed is you can audit only the fields you want to audit.. So you would add an If block for each field you want to audit.

    Scott

  10. Scott, thanks for the tutorial but for some reason I’m not having any luck. For testing I’m just making changes to one field ScrapWeight and tracking the user and date. It never “visibly” logs the changes. My primary key in tblAudit is EditRecordID and if I manually add a record to the tblAudit the new ID will be 22 for example. I then open my ScrapDetail table in a datasheet and make a few changes to the ScrapWeight. They don’t appear in the tblAudit but now if I manually enter another record in tblAudit my next ID is 25. It acts like it is maybe logging the change but deleting it right after. I’m using the AfterUpdate event. Any suggestions?

  11. Scott, I found the problem. I built my first macro using the image you have on your blog. It shows the Create Record before the IF statement. When I looked at your sample database I noticed it. Also I was not putting my field name in quotes and the UsysApplicationLog show me that error but I would have thought Access would have raised an error when I tried to save the macro. Anyway all is working well and I appreciate the help.

  13. Pingback: Track who made last change

  14. Hi Scott,
    Your macro works great on the back end (_be) database with my table. But I can’t make it work for the front end. Are there any changes that I need to make to the modules that you have included in your test db?
    Thank you,
    Nora

  17. Scott,

    Great example and it works like a charm on the backend side. Thank you for this.

    I tried to import your same example with the web front end using Sharepoint 2010 (after taking out the fOSUserName from everything; after update and the module due to web compatiblity issues) where it ends up not working anymore. No error messages, nothing.

    Is there a method to relink web tables? I have been resyncing with Sharepoint, but I don’t think it works…

    Have you or anyone else tried this with Access Services?
    Any assistance would be greatly appreciated.
    Ling

  18. Hi Scott,

    First of all thanks for a great example on how to use macros. Unfortunatley i have run into a little bit of problem and maybe you can help.

    I need to keep record of location history for material my database handles.
    Every bit of material has a locationID set to it that is a auto generated database number. So when i run your example my OldValue is stored as the number values of my locations and not the names of my locations.

    Is there any way to connect my OldValue outputed as a locationID to the name of the location in my locationtable? I have tried to make a relashionship between the locationID in my locationtable and the OldValue field in a query but it still outputs only a number for the location and not the name so some link is missing if its even possible.

    Thanks in advance for any advice.

    Regards
    Per

    • I think the problem here is that the Datatype for OldValue is a Text datatype and your LocationID is Long Integer (autonumber). Sio what I would do is create a query like:

      SELECT RecordID, ChangeDate, ChangeBy, CLong(Oldvalue) as Previous, CLong(NewValue) as New
      FROM tblAuditTrail
      WHERE Fieldname = “Location”;

      This will give you a list of changes in Location. Then create another query using the above and add your Location table twice, joininf Previous to LocationID in one instance and New to LocationID in the other instance. Bring in all the fields you want and add the Location name from each instance and give them aliases of NewValue and PreviousValue. This second query should show the location names.

      However, if you need to show a history of locations, I would be more inclined to use a history table, rather than an audit trail.

      Scott

    • Sure and you are on the right track. You don’t need to change anything in the process I outlined. In the query you need to include both the Audit trail table and the location table. You need to add the Location table TWICE. Then create a join between the OldValue and the LocationID and the New Value and the LocationID. Then add the Location name from each instance of the Location table to the query grid. Finally, add aliases to each of those columns. Add OldLocation: in the front of the column linked to the Old Value and NewLocation: for the column linked to the New Value. When you run the query, you should see the location names labeled properly.

      Scott

  20. First, thank you for posting this excellent tutorial. I have it working, and it has been a great introduction to data macros. I do have one problem that I can’t seem to work through. The DB I have built uses a login form with a combo box for the username, that pulls from a User Table. One of the combo box columns is the user password. A small macro compares the user password entry to the combo box column. If the passwords match, we move on to a main nav form, while the login form stays open and gets hidden. I would like to be able to get the current user name from the hidden form, and get that into the audit record as the current user is entering data into various forms. I have tried various ways to get the data into a form, I even managed to create a temporary variable to store that bit of info, but haven’t been able to figure out how to the user name into the Audit Table record. Do you have any ideas on how I can do this?

  21. Scott, thank you for taking the time to reply. This I think is what I wasn’t able to figure out. I spent quite a bit of time searching about for how to handle variables in data macros, and didn’t find anything that answered my question. My DB is on my work machine, so I’ll have to wait until Monday morning to give it a try. As a follow up question, can you recommend any good resources on learning the ins and outs of data macros? Finally, after browsing around your site, I came across your post on login security using VBA, which is exactly how my login is set up, except I used the macro editor to build it. I don’t remember where I found the tutorial a couple years ago, but it looks like it came straight from yours. So I would like to thank you for that as well. I will let you know how your suggestion works out.

  22. Adding Tempvars(“UserName”) to the SetField doesn’t appear to work. In fact, when I add this SetField, it breaks the macro and the entire Create A Record section fails. Using the variable as the default value in a form text box, makes it work, but this kind of defeats the purpose of having the data macro tied directly to the table. Any thoughts?

    • Did you see my latest response to Dean? it appears that Tempvars don’t work in a Data macro. So you can create a public function.

      In a Global Module add a function like so:
      Public Function fUserName()
      fUsername = Forms!frmLogin!cboUser
      End Function

      or use

      Tempvars.Add “Username”, Forms!frmLogin!cboUser
      fUsername = Tempvars(“Username”)

      then substitute fUsername() for fOSUsername() in the Audit macro.

      I just tested this and either should work.

      Scott

  24. Hi Scott, I know I might be so annoying right now. Like I said, your code worked, but I’d have to always keep the form hidden or invisible.

    I set my login in form to close using this code:
    DoCmd.Close acForm, “f_Login”, acSaveNo

    Then I tried using TempVars, but Access tells me “Invalid Outside Procedure.” I used this as a global module:

    TempVars.Add “Username”, Forms!f_Login!cboUserName
    fUserName = TempVars(“Username”)

    I just changed my form name and cbo box name. What does that mean?

    Another thing, when I try to go back to my Data Macros in t_Department, Access tells my that the values: Now(), fUserName(), and even the Updated(“dDepartmentHead”) are invalid. But they used to work well.

    • Why are you closing the form? Much of the techniques are predicated on leaving the form open and hidden so you can reference the combobox and its values in other parts of the app.

      The reason the Tempvars didn’t work is because you closed the form. If you want to use Tempvars rather than leave the form open, you can, but you have to se the Tempvars BEFORE you close the form.

      The only reason Now() might cease to work if there is a missing reference. Open the VBE and check Tools>References to see.

      Scott

      • I see. So, either way, I’d have to keep the login form open, but invisible? Is that it? If so, then what’s the main difference between:

        Public Function fUserName()
        fUsername = Forms!frmLogin!cboUser
        End Function

        AND

        Tempvars.Add “Username”, Forms!frmLogin!cboUser
        fUsername = Tempvars(“Username”)?

        When do I use one over the other? And which one do you think is better?

      • If you are keeping the form open , there is little difference between the two. The advantage of using Tempvars is that they can be used in SQL statements as criteria. If you decide to use Tempvars, then once you set the value of the Tempvar, you can close the form since the Tempvar retains its value until reset.

        Scott

  26. Hi Scott,
    I just found your blog while doing a search for an audit trail using a data macro. Your examples above were extremely helpful! But, of course, I can’t get the fOSUsername() to work. I keep getting an application error that says Error Number -8988 “The function ‘fOSUsername’ is not valid for expressions used in data macros.
    Do you have any idea what I could have entered wrong?
    Thanks!
    Terri

  28. Hi Scott,
    first i’m from Germany and therefore sorry for my bad english but however I hope you understand me :-(. I found this post yesterday and it’s quite the thing I’m searching for. The integration in the DB works if i change a recordset directly in the table. But if i change a record over the form level there is no record inside the tblAudit. What do i have to do, that it is also an record in the tblAudit if i do a change over the form level?? I’m using Access 2013.

    regards from Germany
    Andreas

  29. Hi Scott,

    First off all thanks for the tutorial. Everything works perfectly on tables that are not linked. I have a difficulty to set it up correctly on linked tables. Is it possible to sort out this problem?

  30. Hi Scott, thanks for this very interesting and useful example. I use Access 2010 and I have been able to create a database of my own (Front end and back end) – I just put the macros in the backend database. When editing data in the database from the front-end using a form, it works perfectly. What confuses me a little is that given the macros are on the data table, I don’t understand why a record is not created in the audit table when I change the data table directly without using a form. This doesn’t work from either the front-end or from the back-end.

    You might wonder why I am even bothered about this! Well, that is my boss often goes into the data table he wants auditing without using a form.

    Any suggestions would me most appreciated.

    Thank you, Simon

    • The After Update event DOES monitor the whole table. Data macro event events are on the table level not the field level. However, if you want to track both the old and new values, you have to add a record for each field. If you just want to record the new values you could use a History table and just append the new record to it with a timestamp and user info.

      Scott

  32. Hi again Scott, I tested again and changing the table directly from the front end did indeed create a row in the audit trail table. So that’s great.

    Now I am trying to work out an implementation plan – since the back end database is in use about 12 hours a day then I need to do the work to prepare the database using a copy of the back end. So how does this plan sound:

    1) Make copy of back end to my computer.
    2) Add audit trail table to back end copy.
    3) Modify table that needs audit trail to add the macros.
    4) Test on back end copy for all columns in table to be audited.
    5) Carry out tests in step 4 using form and direct access to table.

    Out of hours steps – ensuring no-one is using the database!!

    1) Remove all data from table to be audited in back end copy.
    2) Remove all test data from audit trail table.
    3) Rename live table to become audited in live back end.
    4) Copy in new tables from back end copy to live back end.
    5) Load live data from renamed table (step 3) into new auditable table.
    6) Rename back end database in live environment.
    7) Copy back end copy database to live environment and rename to live name.
    8) Run tests again to ensure that implementation has been successful.
    9) In case of problems, revert live database to the renamed copy (step 6).

    Does this sound sensible?

    Just needed to write it down to bounce it off someone who knows about Access, since no-one else here does !!

    Thanks, Simon

    • It sounds workable. but I would do it differently. First test to see how long it takes you to add the audit trail macros to the table(s). Then I would just do it directly to the back end (making a safety copy first). Unless you feel it takes too long for the back end to be out of service. I would compare how long it takes to add the macros to how long it takes to copy all the data. I’m just not that comfortable with copying the data.

      Scott

  33. thanks for a great bit progamming it works wonders

    i have it set up with a little mod to tell me the change type in table “new ,chage or delete”

    what i swas swonderign is do you happen to know an easy way to restore a recored from the data that is “deleted ” whether this is via a macro or vba

    thansk
    in advance

    craig

  34. i have worked out how to recover the deleted table info from the audit table

    thank you
    my next question is about “memo fields” or long txt fields
    how do i do the same for those fields as i am doing for all the other fields in my tables?
    i have two particular fields in two tables that i am “concerned ” about
    thanks
    in advance

    craig

  35. The macro works with back-end tables after re-linking, as mentioned above. HOWEVER, when I save the application as a .accde (smaller executable file, to distribute to the users), I get the same error again (.accdb works fine; .accde doesn’t). I have tried re-linking from the .accde executable file – re-linking is successful, but now get the old error : The function ‘fOSUsername’ is not valid for expressions used in data macros. Thanks much, Scott, if you have any insights into this.

  41. I, too, am having a problem with the “fOSUserName” function and I don’t think there is anyway around it.

    From the Application Log File, I have the following error:
    “The function ‘fOSUserName’ is not valid for expressions used in data macros.” (#-8988)

    However, here’s the quirky part of it:
    This database in question is used by a commercial application that uses ODBC to communicate with the database (the application is flexible enough such that, as long as the necessary tables are in the database, it doesn’t matter whether its an Access, SQL Server, or Oracle database). If I’m working directly within the Access database, it work just fine; no errors and the data gets written to the audit table. However, when I’m editing the same table via the application, I get the above error.

    Therefore, I think that the function is only available when the Jet engine is available/active (ie: Access is open/running). If it’s not – which is the case when the commercial application is hitting against the database via ODBC – the function is not available and the “trigger” fails.

    Any other alternatives? (this is the same situation for “Environ”)

  42. Hi Scott,
    Every thing is working fine but can’t do Check or Uncheck fields
    how to get it worked with Yes/No check fields that are updated?
    eg: In table tblEmployees, if any Employee resigns and i uncheck the field “Active”. so how it will be audited in tblAuditTrail..
    Thanks

  47. Note that the OldValue and NewValue fields in tblAuditTrail are Text Datatypes. So when you write those values to the table they have to be converted to text. So no, you don’t need separate fields for different data types.

    Scott

  48. Scott,
    This has been a huge help, I’m pretty new to Access and came across this so thanks. But I’m using this on an Access Web App and it works like a charm, except the portion where the username is pulled. Any insight on this?

  49. Hi Scott, how do I create audit log for search only. Audit log will record date/time, username, search field (unique ID) and search results displayed in the form. It has no option to add, modify or delete in the database.

    Regards,
    Rick

      • Thanks for your prompt response. Not sure if I explained correctly. This is a read only database. User can only search using primary key and information displayed read only. All I need is to add search results into audit table along with date/time, username. I’m using your sample files so everything else if working in sample files except I need to add search result in the form to audit file.

        Thanks a lot!
        Rick

    • If user to enter a ref ID and search delivery details, I wish to record outcome of the search. For example user enter ref ID 1234 into the search field for database to look for delivery date, address of delivery (already in the database), I would like Audit log to record ref ID 1234 entered in the search field and delivery date, address displayed to the user at the time of search.

  52. I didn’t encounter a problem with the back end in a different folder (re:, your note above) after I copied the basGetUserID module – to get fOSUsername() – to the Front End, so a copy is now in both the Front and Back ends. Perhaps it doesn’t need to be in the back end if all activity is done in the front end…?

  53. How do I write old value to an audit file regardless of any changes made or not in the table. Currently it record old/new values when trigger modify to existing values but I like to record old values when even no modify taken place. Much appreciated if someone can give coding.

    Thanks

  56. Hello, thanks for your audit trail solution here. However, it doesn’t seem to like lookup fields as this triggers off an error in the Application error log. Any suggestions as how I can include these lookups fields in the audit trail?
    Thanks

  57. No, this is not a web app. Yes I’ve learnt that lookup fields are more trouble than they’re worth! It is coming out with an error saying ‘The identifier ‘[TblStatusTracking]’ could not be found.’ and ‘The updated function is not supported for memo, rich text, hyperlink, OLE object, multi-value or attachment fields.’
    Any suggestions?

    • There is the answer, you cannot use any of those types with a Data Macro. So its not really Lookup fields, but rather the MVF. I’m not a big fan of MVFs. You should be using a Junction Table to model the many to many relationship.

      Or you can use a VBA based Audit Trail. There are many examples of those.

      Scott

  59. Hey Scott. Nice article on creating audit trails. I’m having trouble though. I created the data macro like you instructed on the table to the audit table. But nothin seems to be writing to the audit table after I make a change. Any suggestions on how to alleviate this?

  60. Not strictly this thread, but I am trying to create a custom shortcut menu. I’ve tried using the Office library in VBA but unfortunately the msoControlButton object can only reference the menu item by means of its ID (the example I found uses ([Office.CommandBar].Controls.Add(msoControlButton, 15948) – ie, “Print”). I can’t find a full list of the IDs anywhere. Next I tried using macros but haven’t been able to get to grips with it. In addition, I can’t seem to find an option to edit any existing macros. I’d really prefer to go down the VBA route. Does anyone know where I can find the IDs of the command bar controls?

      • Thanks for the prompt reply, Scott.

        I am trying to create a shortcut menu to reference from File/Options/Current database in the section Ribbon and Toolbar Options via the drop-down box headed ‘Shortcut menu bar’. By a ‘shortcut menu’ I mean the little menus that pop up when you right-click on a form. At the moment I can’t find a way of customising them – you can completely disable them or they have to be the default. I have already customised the Ribbon and the main Toolbar. Research on the Microsoft support site suggests the only way to do it is via macros or a VBA function.

        Via the official Microsoft site, I have created a couple of shortcut menus in VBA using sample code written by Edwin Blancovitch (Advanced Developers.net). The menus work as I would expect, but don’t give me the commands I want to reference. There is no pointer to where I can find the control IDs and searching Tech support finds nothing. I hoped you might have come across this yourself?

        Kind regards

        Sue

      • Hi Scott

        This is the way to create a shortcut menu in Access 2016:

        First:

        1. Create a new macro (eg TestShortcutMenu) 2. On the Design section of the ribbon, make sure the Action catalogue is enabled. This will give you the items you need in a column on the right-hand side of the screen. 3. From the Action catalogue, drag ‘Group’ into the macro and name the group (the group name will not show in the finished menu) 4. For each item you need on the shortcut menu:

        a. Drag a submacro into the macro group b. Select the action RunMenuCommand c. Select the item you want to add to the menu from the drop-down list

        5. Save the group macro.

        Next:

        6. Create another new macro (eg CreateTestShortcutMenu) 7. From ‘User Interface Commands’ in the Action catalogue, drag Add Menu into the macro 8. From the list of macros, select the macro you created in (1) above. 9. To replace all other shortcut menus in the current database, type the name of the menu created in (6) above in ‘Shortcut Menu Bar’ for the current database (File/Options/Current database/Ribbon and Toolbar Options). 10. To replace the shortcut menu on specific forms/reports

        a. Design the form/report and display the properties b. On the Other tab, select ‘Yes’ for the item ‘Shortcut menu’ c. Type the name of the menu created in (6) above in ‘Menu Bar’.

        Hope you find this useful!

        Kind regards

        Sue

  61. I am getting an “Update Function must contain exactly one child element which is an identifier”

    Does this work in web apps? If not could you show give me an idea as to where I could find a web app data macro that will store changes?

  63. There is a link in the article where you can get fOSUsername(). But that is VBA code so won’t work in a WEB App.

    If you use this in a WEBApp you have to provide some way of capturing the user. Probably using a Tempvar. You can then reference the tempvar in the macro code.

  65. Would you have a good reference for AWA’s? Just can’t figure out a simple copy record function (which should just be a function!!) that can be done via click. I was not even able to add the sql query in the web app. Any help would be MUCH Appreciated!!

  66. I am having trouble getting this to work. Maybe I’m missing something. I set up my 2 tables and set up the different fields on the data macro. However, when I make a change to the referenced cell, no record is made on the audit table. Any trouble shooting questions you can give me would be appreciated.

  67. I figured out the issue. It’s the fOSUsername() function that I have:

    Function fOSUserName() As String
    ‘ Returns the network login name
    Dim lngLen As Long, lngX As Long
    Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
    fOSUserName = Left$(strUserName, lngLen – 1)
    Else
    fOSUserName = vbNullString
    End If
    End Function

    It highlighted ‘apiGetUserName’ and said sub or function not defined.

  69. Hi Scott

    Hope you can read this. I created two MS Access documents. One I created with several forms based on a main data sheet. Another one I created with several forms based on different main data sheets. May I ask how to indicate the form name the users amended by creating SetField in the Macro mode in MS Access. Thank you so much.

  71. I have made 2 different audit tables, one for my memo fields/long text fields and another audit table for all other fields. When I try to have the data macros create records in multiple tables it only creates a record in the first table that I put in the data macro.

  72. I’m not worrying about the Long text fields anymore, I cant get them to track the changes. Everything else is tracking correctly.

    I’m trying to get the username to track with the changes and I can’t get it to work. I tried Module

    Public Function fUser()
    fUser = Forms!frmLogin!cboUser
    End Function

    I even tried Make the Value for set field =Forms!frmLogin!cbouser and I couldn’t get it to work. I have my database split with front end and backend. How do I get the USer_ID to get passed to my backend database?

  74. Hi Scott,

    I was referring the process (found bit interesting) with regards to Data Macro to track changes made in a form (Access 2016).

    The question that I have is would this process/macro be helpful, if followed for a MS Access file which has linked tables from SQL Server.

    Thanks,
    Rakesh

  76. I’m very inexperienced with VBA. I have only one memo field (long text) in my database that I want to track thru a form and I don’t know how to convert it to VBA. I have a screenshot of my data macro but don’t see a way to attach it here.

    • When you say you want to track the memo field, I’m gathering you want to track edits to it, correct?

      Set up a table like the one mentioned in my blog article:
      tblTrackChange
      TrackChangeID (PK Autonumber)
      Tablename
      Fieldname
      EditDate
      OldValue (long text)
      NewValue (long text)

      In the After Update event of the control bound to the memo field use code like

      Dim strSQL As String

      strSQL = “INSERT INTO tblTrackChange (Tablename, Fieldname, EditDate, OldValue, NewValue) ” & _
      “VALUES(‘tablename’, ‘fieldname’, #” & Now() & “#, ‘” & Me.controlname.OldValue & “‘, ‘” & _
      Me.Controlname & “‘);”
      CurrentDB.Execute strSQL, dbFailOnError

      That should track changes to that field.

  78. Hi Scott,

    Many thanks for this blog post, it’s helped me quite a bit! I am however running into issues when a lookup field is edited. The record is set, but the result is simply the list value from the lookup table rather than the “New Value” or “Old Value”.

    I’m guessing the solution is to not use lookup fields, but is there some way to capture a part of the lookup table record other than the list value? I suppose ideally if the old record was 1|pancake|blueberries and the new 4|muffin|raspberries, I would want the old and new value to be “pancake” and “muffin” rather than “1” and “4”.

    Sorry for the poor terminology- I hope it makes at least some sense!

    • Well yes, using Lookup fields is NOT recommended. Lookups should be done on the table level using list controls, not on the table level. . However, that wouldn’t change the issue. The question is are your lookups based on a Value List or a table. If they are a Value List, that’s not a good idea. Value Lists are only good for short, static lists. Instead they should be based on a table. So, you can use a query to return Pancake or Muffin instead of 1 or 4. But 1 or 4 is what is stored in the table. And what will be noted as the New and Old values.

  79. So I think my lack of terminology has led to confusion- not surprise there! Sorry.

    My Lookup fields are linked to tables in a separate backend db. It sounds like you’re saying that I can’t return a more descriptive value than the record position within a table (1 or 4 in this example). Is there not some way to store a value from a different field of the same record?

    • Not while using a data macro. However, your terminology shows a lack of understanding. You refer to “the record position within a table”. That is NOT what is being stored. The ID field value is your Primary Key, not a record position. Every table should have a primary key field that uniquely identifies a record. Sometimes that PK is a specific value sometimes, its a autogenerated value. But the PK is unique and unchanging. So its not a Record position.

      As i said, you can use the PK value to join to the lookup table and pull the descriptive value.

Leave a reply to Ali Asghar Khan Cancel reply