Results 1 to 7 of 7
  1. #1
    aqutan is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    3

    Question Access checkboxes & auditing

    Hi i was wondering if the below is possible


    1. Combine the current and deleted users into one table (obviously yes)
    2. Have a checkbox (column) which is ticked for current and unticked for deleted
    3. Whenever the checkbox is selected (ie tick box added or removed) a dialogue box pops up asking to state reason
    4. The reason is kept in an audit table with timestamp, userid, added/removed
    5. Examples of the reason are Switching with another user, New License

    Licenses for users are being changed at a daily rate and i need to keep an audit trail of all changes.
    Thanks in advance or if you could suggest a better way of doing this.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    All yes. Will require a form and code behind form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    aqutan is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    3

    Smile

    Thanks June could you point me in the right direction websites etc or if you have any good examples i can work off that would be great

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You want to do a lot and all depends on your specific database design.

    Do you have step 1 and 2 handled?

    From 3 on will need to have form with checkbox control bound to the field in table. Then code in checkbox Change event will accomplish tasks. You want to first have 'popup' prompt the user for input. This popup can be the VBA MsgBox or a custom form. Then user response will be included in an SQL UPDATE action to save record to the audit table. Check out this article http://allenbrowne.com/appaudit.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    aqutan is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    3
    Thanks Jun ill start working on this.
    By the way would you suggest another approach to this?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I've never designed an audit trail procedure so really don't have any alternative to suggest. You seem to be on right track.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Are you trying to make an audit trail for software a user has had, or users software has had? or both?

    Just to chip in on June's suggestions you will likely want a table for your users (do you intend people to log into your database with your own built in login system, or do you intend to inherit the username of the current person logged in?). A table of your software, and a junction table because your relationship (it appears) is many to many, a piece of software may have many different owners (if the license is transferred) and obviously a user can have a license for many pieces of software.

    Finally you'll need your 'audit' table which will just be a list of the userID, the field(s) they changed (if it's just the yes/no field that's pretty simple) the reason and the date of the change which can be a default of now() which returns the current date/time.

    Just a word of advice since you say this data changes daily, if you are going to have more than 3 -5 concurrent users that are changing data you will probably want to switch your database to a front end/back end configuration so that each user has their own copy of the front end and, possibly, make all of your forms unbound so that the likelyhood of more than one person touching the same record at the same time is minimized.

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

Similar Threads

  1. Reports and Checkboxes
    By seah0rse in forum Access
    Replies: 1
    Last Post: 06-07-2011, 03:37 AM
  2. Auditing in Access Database
    By hannankhanji in forum Access
    Replies: 0
    Last Post: 03-01-2011, 05:21 AM
  3. select multiple checkboxes in access 2003?
    By GoneFusion in forum Forms
    Replies: 2
    Last Post: 09-20-2010, 01:44 PM
  4. Clear all checkboxes?
    By thekruser in forum Forms
    Replies: 2
    Last Post: 09-16-2010, 09:50 AM
  5. checkboxes to display records
    By jamie123 in forum Forms
    Replies: 1
    Last Post: 05-13-2009, 10:43 AM

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
  •  
Other Forums: Microsoft Office Forums