Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Who changed the Data

Who changed the data? This question comes up when something found not in order with the information kept in the database and ended up with serious issues reaching out to too many. Here, I didn't give out much to make it clearer to you and wondering what this is all about. Let us look into a scenario to bring the story into clear view.

Assume that there is a database on Credit Customers of the Company, which includes high profile Credit Parties from Ministries or Ministers themselves. All Credit Parties are grouped into different Categories based on their status or other credibility criteria and assigned with different Category Codes. Periodically a Statement on their Payment Over Dues will print and forward to the parties as a regular follow up measure, to remind them to make payments or to check and reconfirm the statement is in order or not.

Since, high profile parties are kept under different Category Codes, their Statements are printed separately for internal record purposes only, but never forward it to the Parties, as per strict instructions from the top.

Several individuals in Accounts Department are involved in updating the database. Then one day someone edited one of the VIP Account Category Code by mistake and the Account landed in the common category and the monthly statement goes out to the VVIP party. As far as the common category is concerned it is a routine work to transport the statement to the concerned so that money can be recovered from them in time. But, the statements pertains to the VVIP categories, if delivered can put the Company into tight spots and can cause much embarrassment to the Management.

The investigating committee pointed their fingers on each one of the supposed to be responsible for the change, but nobody came forward hanging their head low. Finally EDP Department has been called up and the poor Application developer has to find the answers quickly or his head is likely to roll. After all, he can change any data in his Application. But he was already prepared for such eventualities and had ready answers with him to save his skin. He caught the culprit red handed having left his finger print on the record crystal clear, whether the change was intentional or not, is a different issue altogether.

If you are from Accounts Department, I know you have so many questions to ask and you may even argue such a thing can never happen in your department. I already mentioned it is a story (assumption), but you cannot rule out the possibility altogether.

Data Entry and Editing are two major functions that involve maintaining information up-to-date. Field level validation checks are performed to maintain reasonable accuracy of the information fed into the System. Present day inventions like Scanners, HHTs (Hand Held Terminals) and other devices are also used. In these devices also the User IDs, Date and Time etc. can be recorded as part of the information fed into computers.

When several Users are involved in using and updating information in MS-Access Databases, they should be installed in a Network, implementing strict MS-Access Security under Workgroups. Users must be organized into different Workgroups based on their activity with the database and allow them to Log-in with their own User ID and password limiting the activity within their own privileges.

We were discussing about the finger printing (I coined this phrase here all by myself) of the data editing event on the record. When a new record is entered into a table or during the editing session we can record the date, time and User ID into that record itself, which is very useful to sort the records or find the information entered or edited on a particular day or within a time-period. Inadvertent change to the data can happen and the user may not remember on which record she has made the wrong change, even if she knows something went wrong somewhere. With the aide of approximate time and date the user herself can find the record involved and correct them, if she is given the facility to do that.

While designing the Table add the following three fields at the end of the field list to record the Data Entry Date-Time, User ID and record Edited Date-time:

Field Name Data Type Size
DEDate Date/Time  
EditedBy Text 20
EditDate Date/Time  

Click on the DEDate Field. Enter the expression =Now() in the Default Value property of the field at the bottom. The EditDate and EditedBy fields we will update every time a new record is added or when changes made to it on the Form.

While designing the Data Entry/Edit Forms place the above fields also on the Form at a convenient location. Display the Property Sheet of the fields and set the Locked property of the above fields to Yes so that the user will not change the values manually. Set the Tab Stop property to No so that during normal data entry or editing time the cursor will not move into these fields.

Even if you set the Locked property to Yes the user can click on this field and sort the data and find the records she has edited recently and correct the mistakes, if she knows about it, but don't know how to find it.

If you don't want the user to use this field any way but let her see the contents then set the Enabled Property of the field to No and reset the Locked Property to No. If you plan to keep these fields hidden from the user then set the Visible property of the fields to No.

Now, with setting up a simple Before Update Event Procedure we can start recording these values on the table. While the Form is still in design view, display the VB Module of the Form (View - - > Code), copy and paste the following code into the module and save the Form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
     Me!EditedBy = CurrentUser
     Me!EditDate = Now()
End Sub

The DEDate field will record the current date automatically when a new record is created and the above procedure will record the Current User ID and Current Date and Time as the final step of the edit action of each record.

Even this method is not 100% fault free. For example, if someone else edited that record changing some other field, after the error has happened, her name will be recorded overwriting the actual person made the mistake. Then we will be catching an innocent person for somebody else wrong doing.

Then we have to consider keeping a history of changes made to the records and each edited record must be copied to a history file with Date, Time and User ID.


Share:

2 comments:

  1. I dealt this issue by sacrificing the ID Key (unique key) field & added these fields: 'status', 'date_time', 'user'. Status = 1 (active) and 0 (no longer active). This way, I know who did what & when; plus I know how the data look like at certain time period. This is very important for auditing & troubleshooting. But as I said, I have to sacrifice uniqueness (and not to forget, the database size also increase).

    ReplyDelete
  2. [...] am sorry about the Error, please find the correct link below: Who changed the Data __________________ http://www.msaccesstips.com (Learn MS-Access Tips & [...]

    ReplyDelete

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Queries External Links msaccess reports msaccess tips Accesstips Menus and Toolbars MsaccessLinks Process Controls Art Work Downloads msaccess How Tos Graph Charts msaccessQuery List Boxes Array Command Buttons Emails and Alerts Query Combo Boxes Custom Wizards DOS Commands Data ms-access functions msaccess functions msaccess graphs msaccess reporttricks msaccessprocess security advanced Access Security Calculation Custom Functions Data Type Macros Menus Property Report Top Values VBA Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Command Button Copy Field Type Fields Form Join Microsoft Numbering System Records Security Split SubForm Table Utility Workgroup database msaccess wizards Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Controls Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export Expression External Filter Form Instances Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Methods Monitoring Object Reference Objects Octal Numbers Operating System Paste Primary-Key Product Rank Reading Recordset Rich Text Sequence SetFocus Summary Tab-Page Tables Time Difference Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar tutorial updating upload vba code

Featured Post

User-Defined Data Type-3

Last week we have learned how to define a User -Defined Data Type and it’s usage in programs.  If you have landed on this page, you may go t...

Labels

Blog Archive

Recent Posts