Results 1 to 7 of 7

New to Access, need help setting up an audit trail

  1. #1
    kwp6353 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    3

    New to Access, need help setting up an audit trail

    Hello, I apologize if I am not posting this in the correct place.

    I have recently created an Access database for work. Essentially we use it to reach out to customer's that are having account issues and work with them to correct them. We have 8 team members using the database. Start to end, we may take 2+ weeks to correct an issue and update the db after each customer interaction. *BTW did I mention i'm new to access* My boss is trying to track our productivity daily. Not so much on if we are successful, just how many customers/accounts we add or edit via interaction daily. From what I'm reading, I think an audit trail might be able to help me. Our db currently consists of 1 table containing all of the data, 2 forms (one for entry, the other for filtering purposes by date) a simple macro which just brings up a welcome message. There are no logins or passwords and users cannot access the actual table. We can easily track cases opened and closed by going into the table and filtering by date, but I may have 12 pending call backs that I work that day and I don't add any new cases, making it look like i did nothing all day. Im looking for an audit trail to add data to a new form that can capture new accounts added, and edits daily. We have multiple fields in our data entry form, but I'm not looking for something that says everything (or anything) that was changed, just like a ticker that says that it was modified/added. We have multiple drop down selectors and date selectors, one of our drop downs is for a rep name. Ideally, I'd like the audit trail to log DATE/TIME, "ACCOUNT" (which is our primary id), and if possible, record the "AGENT" (which is in the form) that is assigned to that case. I don't want it to record every time the account is populated just to view, only when of the fields are changes or modified and the rep clicks the save button. I have included a screenshot of the data entry form and a proposed table for the audit trail. I'd like to add a field that states new or edit, but it is not a must if it means reinventing the wheel. Im assuming this will need to go through a module (from what i've read) and part in the form properties before update. I have some existing code there as well that I will paste at the end. Can anyone help baby step me through what I need to do? BTW, data entry form is called: FORM, and the table is called: DATA.

    Private Sub Form_BeforeUpdate(Cancel As Integer)If Me![COMPLETE DATE] > Numm Then
    If IsNull(Me![Final Resolution]) Then
    MsgBox "A final resolution must be selected upon completion of a cusotmer.", vbOKOnly
    Cancel = True
    End If
    End If




    If Me![COMPLETE DATE] > Numm Then
    Me![FOLLOW UP DATE] = Null
    End If
    End Sub
    Attached Thumbnails Attached Thumbnails ENTRY FORM.PNG   PROPOSED TABLE.PNG  

  2. #2
    aytee111 is online now Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    1,672
    When the click the Save button, run a query to append a record to the table. You can do this in VBA by creating the SQL string with the person's name and account. For absolute accuracy you can add an extra field for the person who actually clicked the button, it is Environ("username") - that is the OS login value.

    You can add an extra step by checking the table first - if a previous entry existed for this account then it is "Edit", if no prior entry exist then "New".

  3. #3
    kwp6353 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    3
    Thank you so much for answering, i am very new to access. Do i need to create another table for this? And can you walk me through setting up the query? Thanks again!

  4. #4
    aytee111 is online now Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    1,672
    Table1 is the table, it looks like you have already created it.

    Create the query by going into query design and make an append query, with no incoming table. For each field use a value (1,2,a,b,etc).
    Once you have this query working, go to SQL view, copy and paste the SQL into the VBA Save button module. Then change the values (1,2,a,b) to the names on your form (Me!...)

    Post this SQL string when you are at this point.

  5. #5
    kwp6353 is offline Novice
    Windows 8 Access 2013 64bit
    Join Date
    Jan 2017
    Posts
    3
    Thanks so much for being patient with me. I believe I've done everything the way you explained except for the last part of renaming. Where and how do I rename? Sorry, thanks again. I uploaded a pic of my progress.Click image for larger version. 

Name:	UPDATE 1.png 
Views:	12 
Size:	19.0 KB 
ID:	26992

  6. #6
    ssanfu is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    6,024
    Welcome to the forum.

    PMFJI,

    Just a few comments....

    First, I think you will get excellent advise from aytee111 on your questions.

    Second, I know you are new, but there are some things you should be aware of.
    BTW, data entry form is called: FORM, and the table is called: DATA.
    - be aware of reserved words: (as in "Form" & "Data"). You should not use reserved words as object names. See http://allenbrowne.com/AppIssueBadWord.html
    - object names should only be letters and numbers. NO spaces, special characters (exception is the underscore) or punctuation. I see you have a field "Date/Time" and you probably have a field named "Phone#".


    - I'm not telling you you need to redesign your dB, but it does not appear to be normalized. Looking at your form, I can see at least 3 main tables (including the audit table) and maybe 3+ minor tables (look up tables instead of value lists). But as long as the dB is working for you, .....

    Just so you have a better understanding of audit tables, you might read this by Allen Browne. It is more involved that what you asked for, but it might help you follow what aytee111 says....



    I'll step out and leave you to aytee111......
    And Good luck with your project......
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  7. #7
    aytee111 is online now Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    1,672
    (Good interjection, Steve, as always. The difference between us is that I use as few words as possible and you as many! I have no doubt that your way is preferable so jump in any time.)

    KWP, good job for getting this far. Do what Steve says, however, including naming form controls instead of leaving it to Access to name them for you - Command50, I presume that is the save button?

    That is not an append query, that is a select query. Change it to an append query - under Design of the query. Then copy the SQL just as you have done - use numbers for number fields and letters for text fields, that way you will know when to use quotes and when not.

    The VBA code will look something like this:
    Code:
    Dim strSQL As String
    strSQL="......"
    DoCmd.RunSQL strSQL

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access 2010: Audit Trail not Working in Subform
    By besuchanko in forum Programming
    Replies: 19
    Last Post: 11-12-2015, 02:47 AM
  2. Replies: 1
    Last Post: 09-01-2015, 01:01 PM
  3. Audit Trail
    By zburns in forum Access
    Replies: 4
    Last Post: 07-27-2015, 07:49 AM
  4. Replies: 2
    Last Post: 02-09-2013, 11:39 AM
  5. Audit Trail
    By bigroo in forum Programming
    Replies: 4
    Last Post: 02-22-2012, 06:55 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums