Audit Update of a Field

[GPGeorge]

I think the point of the critique of the method was that no OTHER FE of any kind, including Access would update the BE if the Data Macro used functions in the FE as well as opening the BE directly and trying to update the tables locally.

This is pretty much an intellectual exercise for me since my BE's are almost never ACE so I'm not going to even play with this. Creating the Data Macro with functions in the FE would never have occured to me since I would have instinctively known that it would not work from a different Access FE or any other type of FE. I am not at all surprised at the mention that you cannot update the tables in the BE directly and I would guess that if you made a new Access FE with links to the BE, the tables would not be updateable from that FE either for the same reason - the functions used by the Data Macros are simply not available.

So, this is a pro or a con depending on your point of view. It is a pro if you are sure that you will never, ever need to create a different FE that updates the BE tables. But a con if you might need to.

Yes, I tumbled to that. It was initially hard for me to envision a situation where users would normally be bypassing their own FE to do updates and simply didn't think through the many ways users can employ non-traditional means of getting their work done, or of cheating.

 

[Marshall Brooks]

I haven't played with data macros yet, but ...

You are saying that if I create a data macro for Table A in the Backend, I won't be able to directly update Table A from ANY frontend, correct? Would I still be able to update Table B if it doesn't have data macros associated with it?

Do not hard-code the list of people who can do the updates. Create a table and a form to manage the table. This form needs different authorization. You don't want to have to change code to change the users. The BeforeUpdate event is the correct event because you can cancel it if the user is not authorized. Validation ALWAYS needs to go into an event that provides a Cancel argument. Best is the form's BeforeUpdate event. Although, in this case, since it is only the single field that you are validating, you could use the control's BeforeUpdate event.

Follow-up question. I understand that for one text field, if I only want Group A users to be able to update it, I can use the field's BeforeUpdate event to check if the user is a member of Group A and cancel and undo the change if they are not (and display a message).

What if I want to restrict Group A members from updating the other 60 or so text fields on the form? I know I could put similar code in the BeforeUpdate event of each field, but is there a way to select all the fields and apply the restrictions at once?

 

[Marshall Brooks]

@Pat Hartman - Great Explanations!!!

I wasn't sure if the data macros went in the BE or the FE. I saw JDraw's example database and liked how it worked, but it (obviously) wasn't a split database.

For the last question, I would use the tag property and the Form's BeforeUpdate event. Tag with an A or a B or both AB and use a loop to process the validations.

I think you had mentioned this approach before, but I had never used it. I checked and I like that I can select multiple fields and apply the tag to them at one time. Also, if I understand correctly, I don't have to tag all of the fields - i.e. most of the fields currently have no tag now, so I can just tag with A the ones Group A can modify and then in the Forms BeforeUpdate loop through the controls and Cancel/Undo the change if the user is Group A and the field is not tagged A. Am I understanding correctly? (With the caveat that I think the Group A users could apparently change 50 fields on the form with no warning and then only 3 of the changes would be saved.)

Another method that might be simpler is to divide the fields into sections. The add a transparent box control over each section. Depending on whether it is Group A or Group B. put the transparent box to back or front.

This approach I really like. Simple and Ingenious. No warning message to the Group A users, but if they can't click in the field, they will figure it out. The box would be the rectangle control, correct? I'm not sure how to put the box to back or front? Would I just make it Box1.Visible = True?

I'll probably end up with a combination of approaches, but the box idea means I only have the 4 or 5 specific fields to worry about instead of 60 or so.

THANK YOU!!!

 

[moke123]

You can also lock or disable fields that you don't want people to edit. Divide users into groups A,B,C,D, etc.

On form load you can loop through the controls and check the tag property for the group Letter. I would use Instr() for checking the tag value so that more than 1 letter can be included in the tag property.

This approach I really like. Simple and Ingenious. No warning message to the Group A users, but if they can't click in the field, they will figure it out.

If you use Pats method of placing a control over the fields, use a command button and set the transparent property to true. Then you can set an on click event to fire a message box "You do not have access to this Field" or something

 

[Marshall Brooks]

@Pat Hartman - You are too kind with the code sample. Thanks again.

If you only tag a few fields. all the other fields can be changed unimpeded unless they are always locked. UNLESS you prevent it by cancelling the form's BeforeUpdate event, Access saves all changed controls.

I don't think I explained that well. Right now all users can edit all fields. Let's call my current users Group A. The new users who will only have access to a few fields would be group B. What I meant is I don't have to tag 55 fields with A and 5 fields with B. I can just tag the five fields and then in FormBeforeUpdate have "If Group B and ctl.tag <>"B" then ctl.Undo" <Air code, but what I mean is I don't have to tag 55 fields with A, I can leave the tag blank and still keep Group B from changing them, correct?

The box would be the rectangle control, correct? I'm not sure how to put the box to back or front? Would I just make it Box1.Visible = True?

I'm still trying to figure out the transparent box. I don't think a rectangle does anything. If I want to restrict the left third of the form, I think it would probably be a transparent label. Then if the user were Group B, I would make the label.visible = True and otherwise I would make the label.visible = False. Is this correct? And I'm thinking I would toggle visibility on the FormCurrent() event, correct?

Thanks again!!!

 

[Isaac]

Restrict the field update to only certain users - probably by user name. I know how to obtain the user name, and I can probably figure out how to implement this, not sure if I would use the beforeUpdate or AfterUpdate event - I'm thinking BeforeUpdate.

Honestly I have never done this, on the FE app. I'd rather restrict user access to a screen. Often I do this by using a tab control and only allowing a user access to certain tabs that contain certain update or edit functions.

One thing I'm big on emphasizing is trying to "clue" the user visually to what they can and can't do, rather than waiting for BeforeUpdate to catch it after, inevitably, the slowest typist in the world has spent 5 minutes keying something into a screen and tried to 'Save'

 

[Isaac]

The advantage of Data Macros in Access tables is that they fire regardless of how data is changed: via a form, an action query, record sets or standard imports, or even the actions of the developer!

You do make a nice point, and maybe I reconsider my harsh view of Data macros in access.
Mostly I just think of the so-much-better alternatives of data in the sql back end and trigger-related functionalities happening there (or in an ssis package or sql agent job) instead, but you do call out a nice feature of data macros - that they fire even with vba changes.

I personally like when threads take different routes of conversation. That's what makes them so rich - and makes the OP learn so much more than they would have otherwise! (Die, stack overflow!) (just kidding)

 

[Marshall Brooks]

You could make a version of the Lock code that locks all controls not marked with a B in the form's Current event and just not call the lock code for the A group so the form will allow them to update anything.

I like the transparent label (???) idea better. Two reasons:

• If the field is locked, but enable, users can still click in it. I think that would lead to me getting questions "I tried to change this field and nothing happened?" "Correct, good, everything is working like it was designed." I think if the users can't click in the field, they'll figure out that they can't change it. (Plus we will explain it to them.)
• I don't know, but it seems like it should be faster to toggle visibility on a couple of labels than to loop through all the controls and lock or unlock some 50 of them depending on the user access level.

 

[Marshall Brooks]

Did some testing and transparent label visible/invisible seems to do what I want!!

 

[Marshall Brooks]

DDL questions:

I'm going to make some changes to the back end, and I looked at the info for DDL and most of it is straight-forward, but I had some basic questions:

The changes will mainly come down to:

• Adding new tables.
• Deleting Existing Tables.
• Adding new Fields to existing tables.
• Adding data macros to the backend tables.

The command structure is fairly well documented in the links in Reply #71 here: https://www.access-programmers.co.u...ruption-assistance.328305/page-4#post-1884431, but I need to know:

• Where do I run the commands from? Obviously, I would want to start with a development copy of the backend and modify that and then later run all of the changes to the production BE, but do I run the statements from a FE linked to the BE, or do I open the BE directly, or something else?
• Where and how do I save the DDL commands once I know they are working?
• As I understand it, DDL only modifies structure, not data. I.e. I can add a field to the table, but if I want to populate the field in my test BE and copy that data to my production BE, I would need to use some type of SQL statement to do that. (Although the data can be populated while other users have the BE open).
• How do I build the statements? i.e. let's say I add NewField1 to Table1 of my database via DDL and it works in the test BE. Now I want to add NewField2 to the same table. If I just add a new line to the DDL code, I'm guessing it will crash since NewField1 already exists. So do I run each command individually and then go back and join them together, or do I need to start with a clean copy of the existing production BE each time (I guess either way would work).
• Is there a way to set the order of the table fields via DDL? (I know it really doesn't matter, but if I am adding NewField5A and I want it to appear after Field5, is there a way to do that via DDL. (Normally, I would just go into Table Design View and click on either Field 5 or Field 6 and say Insert, but…) (I suppose I could manually open the table in the backend in design mode (with exclusive access) and change the field order, but that is only slightly less involved than just adding the field without using DDL).

Thanks in advance!

 

[Marshall Brooks]

@jdraw - Data Macro Questions:

I've just started looking (in detail) at the idea of data macros. Can they be used/created with DDL? I think I can stumble through adding and getting Data Macros working in my test/copy BE file, but I don't really want to set aside exclusive access to add all of that info to the BE file and hope I do it correctly. I saw in JDraw's example that I can export the macro as text, but I'm not sure if there is an easy way to import the text into the new/production BE.

Other question - and I'm okay with "That's just the way it has to work". Why are there TWO macros required? i.e. looking at JDraws example, macAuditTStudent passes parameters to and runs data macro tblAuditLog.macWriteAuditRec which creates a new record in tblAuditLog. Why do I need macWriteAuditRec. Why couldn't I just set up tblAuditLog with "Create new record in tblAuditLog, Set Field1 = Parameter 1, etc."?

And another question. I see how jDraw added the UserName to a field in the tblAuditLog. I know how to do that, but I want a real person's name when I display the audit results. I can have a table of usernames to real names and do an Elookup to do the cross-reference. Would this require adding the Elookup function to the BE file, or could I simply create a query in the FE that did an ELookup of the username field and created the Edited By Field that I wanted to display?

 

[Marshall Brooks]

Also - once I get tblAuditLog set up, I would like to have a button that displays the audit results for the current record only. The display would be something like this thread: https://www.access-programmers.co.uk/forums/threads/simple-query-design.329618/page-2.

I'm not sure how to set that up. I'll have a unique field in the bound table that matches a unique field in the audit table, so I just need to filter my query so that those match, but I don't want to set up a query for every record in my table?

 

[jdraw]

Marshall,

Just saw your posts.
Background:
I was interested in "data macros" and could not find examples, articles etc. So I decided to build something to understand the concept, how it worked for me and then share. That was Spring 2017, and that's all I did with data macros.

Can they be used/created with DDL?
No. As best I know you work with the macro interface (which is klunky/unwieldy as others have said.)

I saw in JDraw's example that I can export the macro as text, but I'm not sure if there is an easy way to import the text into the new/production BE.
I did little with respect to saving and loading text. Mike Krailo indicated he had success with saving and loading data macros as text (see this link).

Why are there TWO macros required?
I decided to use macros to gather the CRUD (add/modify/delete) data and have a separate macro to write the data to the audit log. So I had macros for each table and a macro specific for writing the audit record.

Why do I need macWriteAuditRec. Why couldn't I just set up tblAuditLog with "Create new record in tblAuditLog, Set Field1 = Parameter 1, etc."?
You don't. You could design your data macro/audit logging without using a named macro. I did what I did as a learning exercise and chose to have a separate macro write the audit record.

I want a real person's name when I display the audit results.
You can use a user defined function with a table of usernames to real names or another mechanism that suits your requirement. You can get as much or as little data re the source/person who effected the audit record. You can add/remove fields from the audit table depending on your needs.

I would like to have a button that displays the audit results for the current record only.
You can access and query the audit table as you would any other table.

Good luck with your project. Let us know your progress.

 

[Marshall Brooks]

@jdraw - great info. Thank you.

I've used the macro interface before, I used to have a complicated AutoExec macro built from it. It's usable, but it is more for people that don't want to be bothered learning VBA and there are some VBA commands (I think) that aren't available using the interface. I ended up converting the federated macro to VBA and just calling the VBA from the interface, but I know how to work with the interface if that is the requirement - which it seems to be.

As far as copying - I'll experiment - probably create the macros in a test database, copy the macros to a copy of the production database, see how that works and then try it out on the live database. As I understand it, from the thread that you linked, there seem to be four options:

• Ctrl-a and Ctrl-c in the old database and then Ctrl-V in the new database as posted in reply 33 by zeroaccess. I think this will work (I think I remember doing it in the past when I rebuilt the database to a clean copy and transferred the federated macros.) It's probably the simplest solution.
• Export and Import from the Immediate Window using Mike Kraiko's method in Reply 31, which was your original link.
• It looks like @ebs17 in reply 38 has a method that would use VBA to do the reply 31 export and import in one step. That sounds promising and sounds like the fastest way to get the data macros transferred. I'm a bit hesitant about it, but it would probably be the fastest way to transfer the data.
• Of course, there is the manual method of creating all the data in the test BE and manually re-entering it in the production BE (offline and exclusively), but that risks typos and would have the production BE offline for an extended time period.

Based on your reply, I'll try to have one macro copy the data to the audit table and not have a macro to write to the table. Basically, our data has 7 independent tables, so the plan (although there are other approaches), will be to create 7 audit tables and have one DM per table write to the associated audit table. The single macro per table gives me 7 macros to transfer as opposed to 14!

This is "air code" but the current plan is something like this:

• The back end will have the data macros that write the events to a new tblAuditresults, which will probably contain a PK field, the source db PK field, an identifying field for the record that was changed in the source table, an event name, the old field value, the new field value and the username that created the change.
• I'll make a query in the FE from the table and can add a field to that for the real persons name.
• I'll have a button on the FE form that will open a pop-up form to display the query results for the currently selected record. I think I can use one of the events of the pop-up form to filter the results to the currently selected record.

I'll post progress reports. Unfortunately, this is something of a pie-in-the-sky project - meaning that there is a possibility that I will get it all working and management will decide it isn't acceptable and we need to continue with our current process. I and they are okay with that and I know I'll learn a lot either way.

@Pat Hartman - I'm not seeing an attachment in Reply #42 either. I'd be very interested in seeing this!

• Agreed - deleting tables can be problematic. There are tables that won't be needed after the current database is updated, but if I delete them before everyone updates to the new FE, the old FE will generate errors.
• I understood Reply item 6, but to clarify for others: You apparently can't adjust the new field order using DDL. You can add the new field using DDL and then manually open the back end and open the table in design view and select and drag the field to the correct position/order without losing data. It's an extra step, but it's still faster and less error-prone than inserting the new field manually.

Thanks to all!

 

[Marshall Brooks]

@jdraw (or others)

Out of curiosity, you use this function in the demo:

'---------------------------------------------------------------------------------------
' Procedure : fOSUserName
' Author    : Dev Ashish/MVPS.org
' Date      : 25-Jun-2017
' Purpose   :Returns the network login name of user
'Obtained from http://access.mvps.org/access/api/api0008.htm
'---------------------------------------------------------------------------------------
'
Function fOSUserName() As String
' Returns the network login name
' Code Courtesy of
' Dev Ashish

    Dim lngLen As Long, lngX As Long
    Dim strUserName As String
10  On Error GoTo fOSUserName_Error

20  strUserName = String$(254, 0)
30  lngLen = 255
40  lngX = apiGetUserName(strUserName, lngLen)
50  If (lngX > 0) Then
60      fOSUserName = Left$(strUserName, lngLen - 1)
70  Else
80      fOSUserName = vbNullString
90  End If

fOSUserName_Exit:
100 Exit Function

fOSUserName_Error:
110 MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure fOSUserName of Module basModule1"
120 Resume fOSUserName_Exit
End Function

What (if anything) is the advantage of this over simply using "Environ("Username")"? (or is the latter something that VBA recognizes, but the built-in macro code does not).

I see the advantage of using a function for the result and our usernames seem to vary in case, so I would want to LCase() the result so the lookup table entries can all be LCase(), but am I missing something obvious?

 

[jdraw]

Marshall,

I chose to use fOSUserName because I had seen it referenced as more reliable. And I followed Dev Ashish back in the old days. You can use what ever satisfies your requirement. Here's a link from Daniel Pineault re Get User name.

Access is case insensitive. So using LCase or UCase is aesthetic, but not required functionally.

 

[Marshall Brooks]

@jdraw - Thank you! Dev Ashish and Daniel Pineault are great resources and he explains it well. Essentially, the environ variable can be spoofed. I wouldn't know how and they would be in big trouble, but our company makes the usernames fairly accessible (to other employees).

It doesn't make sense to build all of this security/functionality into the database and then have someone potentially pretend they are me to get elevated rights. (Not saying they would, but it makes sense to prevent it.)

I also like Daniel's idea of calling it as a global function at startup - at least in theory. I can see errors if it fails - i.e. there is an unhandled error in the code and the public varUserName gets reset and now my code thinks the varUserName = Null.

Access is case insensitive. So using LCase or UCase is aesthetic, but not required functionally.

Access may be, but is Windows. Obviously, I'm obfuscating, but let's say my username is xy123456. On some systems I log in as xy123456 and in others I log in as XY123456.

Obviously:
If LCase(varUserName) = "xy123456" Then
will work fine, regardless of how I am logged in.

You are saying that
If varUserName = "xy123456" Then
will work even if I am logged in as XY123456?

 

[jdraw]

In Windows, passwords are case-sensitive, while usernames are not.

 

[Marshall Brooks]

I also like Daniel's idea of calling it as a global function at startup - at least in theory. I can see errors if it fails - i.e. there is an unhandled error in the code and the public varUserName gets reset and now my code thinks the varUserName = Null.

Is this a potential concern? I.e. an error occurs and the varUserName gets reset?

More plainly: Is it sufficient to have a public variable varUserName and populate that at startup, or would it be better to have a function that returns the username and then call the function any time the code needed to do something based on the username?

 

[Josef P.]

Obviously:
If LCase(varUserName) = "xy123456" Then
will work fine, regardless of how I am logged in.

You are saying that
If varUserName = "xy123456" Then
will work even if I am logged in as XY123456?

If you make the comparison in VBA, it depends on the setting in the module header.

Option Explicit
'Option Compare Database ' usually case insensitive
'Option Compare Text
Option Compare Binary ' <- Standard, if no compare option is specified

Private Sub Test()

    Dim x As String
    x = "ABC"
 
    If x = "abc" Then
        Debug.Print x & " = abc"
    Else
        Debug.Print x & " <> abc"
    End If

End Sub

With the StrComp function, you can use parameters to select the comparison method independently of the VBA compare option.