+ Reply to Thread
Results 1 to 6 of 6

Audit Trail

  1. #1
    Registered User
    Join Date
    11-05-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    43

    Audit Trail

    Hi,

    I've created a database to be able to add in new aircraft parts, edit parts and search and duplicate parts into a raw data set. There is currently around 12,000 rows of data in the raw data sheet with 10 column headings (e.g. Part Number, Section, Reference, Quantity). When a user comes to edit information in a part's row (most probably being the quantity) and makes the changes, i'd like there to be the function that when the row is double clicked (or if easier a cell at the end of the row named history), a form box appears with a list of the edit history and what has been done to the fields.

    For example, a user changes the quantity of a 'Bolt' from 36 to 30 as they have removed 6 bolts. Then, when the row or end cell is double clicked, a from appears saying " 'Quantity' changed from 36 to 30 on 27/04/2016" and this history also shows changes to all other fields.

    Is this possible? If so it would be great if someone could explain it to me, or link me to a video explanation.

    I have very basic knowledge of VBA and how it works, so i'm not sure if i'm going to be throwing myself into the deep end here!

    Cheers

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,949

    Re: Audit Trail

    A few questions for clarification:

    1) Does each part have a unique identifier? Part number for example.
    2) How far back do you need the audit trail to go? Last 2, 5, 10, 20 changes? ALL historic changes?
    3) Are changes to items currently made directly into the worksheet? i.e. there's a cell for quantity and the user would manually change this from 36 to 30 if they took 6 bolts?
    4) Are you open to a slightly different approach to managing this?

    BSB

  3. #3
    Registered User
    Join Date
    11-05-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    43

    Re: Audit Trail

    Hi BSB,

    Answers to your questions:

    1) Pretty much all part numbers are unique in their own right. However, i have assigned each part with a unique ID (one of the column headings) to be able to search to a pinpoint.
    2) Ideally to show all historic changes, but if this is too difficult then a max of 5 would be acceptable.
    3)Yes the changes would be made in the worksheet 'Quantity' cell. (The user uses an advanced filter search facility above the raw data set that filters the list in place, to then edit the desired parts that are shown.)
    4) Yes i am completely open to any new approaches !!

    Cheers

  4. #4
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,949

    Re: Audit Trail

    In that case, could we see a small sample of the data?
    What would be helpful would be to see the headers you currently have and a full list of items including name, numbers etc.

    BSB

  5. #5
    Registered User
    Join Date
    11-05-2015
    Location
    England
    MS-Off Ver
    2010
    Posts
    43

    Re: Audit Trail

    Hi BSB,

    Yes no problem.

    (You will see that the part numbers all vary in different formats so just ignore that!)

    I did have the idea of creating another heading column named 'booked out'. The user would enter how many they would like to remove, this would then remove x amount from 'Quantity' through an if formula. Another heading would also be 'Booked out date' that captures the data of the change.
    However, in my view that idea would only capture the one last change history for 'Quantity', and also wouldn't capture any changes to other fields such as 'Primary Storage' etc.

    Cheers,

    Moggzzz
    Attached Files Attached Files

  6. #6
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,239

    Re: Audit Trail

    You should be using a database for this, not Excel. There's an old saying that accountants never use erasers, to create an audit trail this should be transaction based, so each transaction has an in or out qty. The sum of transaction quantities give you the stock balance, to adjust the balance when you stock check etc, it's just another transaction to write on/write off the items.

    I'd strongly advise you to look at an off the shelf stock system. This is a solved problem and excel just is not the answer - I guarantee that you will spend more time & money trying to hammer excel into this than if you simply buy one off the shelf. These things always start simple and always introduce scope creep, I've seen this more times than I can count.

    Investing in the right product now will save a tremendous amount of time and money in the future.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA code for audit trail issue
    By TheGrinch1 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-11-2015, 08:06 AM
  2. Pseudo-audit trail for unlocking a doc?
    By Gunther Maplethorpe in forum Excel General
    Replies: 1
    Last Post: 01-15-2014, 02:42 PM
  3. Audit Trail
    By jenziepie in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-01-2013, 01:56 PM
  4. Doing an audit trail on multiple sheets
    By Nett in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-05-2013, 08:18 PM
  5. Excel Audit Trail?
    By jvest0648 in forum Excel General
    Replies: 1
    Last Post: 12-17-2011, 02:57 PM
  6. Track Changes - Audit Trail
    By ChemistB in forum Excel General
    Replies: 2
    Last Post: 10-27-2006, 02:16 PM
  7. [SOLVED] Audit Trail
    By Pendelfin in forum Excel General
    Replies: 1
    Last Post: 01-23-2006, 11:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1