Audit Update of a Field

[GPGeorge]

The ad

No, definitely not for users. But the developer might be inclined to do something like that. In addition, an access file can be found and copied. An Access backend is not completely isolated everywhere.


Working only via bound forms is a bit one-sided. Table entries can also be made via action queries, record sets and standard imports. Doesn't something like that happen to you?

I just enjoyed my cappucchino, but I'm about to get a new one.

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!

With regard to things like finding and copying an Access back end, I have to wonder if that is not subject to a whole different topic of discussion. The data changes in a copied version of the accdb would not be reflected in the original, production copy, but could be recorded via the data macro in the copied accdb. Perhaps that's a good thing because, if the owner ever did recover that copied accdb, the audit table in it could document who did the deed.

 

[ebs17]

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!

There was no question about that. Everything that has to do directly with records comes together at table level.

The question was how something determined by VBA could get to this table level.

 

[GPGeorge]

Ah, yes. Of course. Again, I was thinking of the context of a working production application with the audit function. Thanks for the reality check.

 

[Pat Hartman]

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!

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.

 

[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?

 

[Pat Hartman]

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?

No, in the discussion above, the Data Macro was created on the BE (you can't create on on the link, only on the physical table - so that means in the BE but the function it called was defined in the FE. If you want to use a function in the Data Macro, you MUST define the function in the BE. That way, the database engine can find the function regardless of what FE makes the update. In this statement, the BE is also an FE in concept if you open the BE directly and try to update tables.

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).

Unless you want to write code for every single control, doing this in the Control's BeforeUpdate event would cause an enormous amount of coding.
There are three logical places for placing this type of verification logic,
1. The on Dirty event. This event runs ONCE, when the user enters the first value in any control. If you are controlling the entire form, I would suggest this event since it is the most user friendly. The user doesn't get more than one character into the modification before you stop him and this works for existing or new records. You use Me.Undo and Cancel = 2 to reset the form and undo the change.
2. If control is on ONE field, it probably makes sense to put the code into the BeforeUpdate event of the control you are preventing the update of.
3. If you are controlling multiple but not all fields, you have to decide whether to be very user friendly and put the code on all fields you want to control OR you can put the code into the forms BeforeUpdate event and control it at the end in a single procedure.

BTW - for either 2 or 3, you probably want to create a sub or function that you call from each control if you are doing this control by control or run the code once for each specific control but in the form's BeforeUpdate event. You can abstract this further by using the control's tag property. In this case, you tag each control you want authorization to update and use a loop rather than one specific call for each control.

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.

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.

 

[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

 

[Pat Hartman]

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.

Here are three useful functions that you can modify to suit your situation. Two of them use the tag property to control the action. They aren't intended to discriminate by tag value so that is the change you would need to make.

To execute the code you need to pass in a reference to the form. "Me" is a reference to the active form. The code works in main forms or subforms. Just remember that if you use it in a main form, it only affects the records of the main form. You could add code to lock subform controls. I'm not sure what the type but you would probably have to set it's enabled property rather than its locked property. OR, you can pass a reference from the subform itself from the relative event.

The Lock code is intended to use the tag property to make a control "special" so you can make a field always Locked or always unlocked. untagged controls are toggled based on the second argument. So, when you want to lock the controls, you pass "True" and when you want to unlock all controls you pass "False". So, when you pass "True", all controls with NO tag get locked. The controls with Lock as the tag are always locked but the controls with "Unlock" are left unlocked. This makes it very easy to leave the search boxes on a form always unlocked even if the rest of the form controls are locked. If you use the AllowEdits property to lock the form as is frequently recommend, that also locks the unbound search controls which makes them useless. So, the LockControls() sub lets you lock all controls EXCEPT the search controls. Because the controls with blank tag properties are toggled based on the second argument, you don't have to remember to tag any new controls as you add them to the form and existing forms where no controls are taggd can be used without tagging anything. You only tag a control if you want it to be ALWAYS locked or unlocked regardless of the tag property. Again, this is a different concept than other code you will see that does a similar function. That other code will require that you tag every control.

Call LockControls(Me, True)

Public Sub LockControls(frm As Form, bLock As Boolean)
    Dim ctl As Control
    For Each ctl In frm.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acCheckBox
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Locked = False
                Case "Lock"
                    ctl.Locked = True
                Case Else
                    ctl.Locked = bLock         'toggle locks
            End Select
        Case acOptionGroup, acOptionButton, acCheckBox      ''not working ---- try adding acObjectFrame
             Select Case ctl.Tag
                Case "NoLock"
                    ctl.Locked = False
                Case "Lock"
                    ctl.Locked = True
                Case Else
                    ctl.Locked = bLock         'toggle locks
            End Select
        Case acCommandButton
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Enabled = True
                Case "Lock"
                    ctl.Enabled = False
                Case Else
                    ctl.Enabled = Not bLock         'toggle locks
            End Select
    End Select
Next ctl
Set ctl = Nothing
End Sub

Public Function EnsureNotEmpty(frm As Form) As Boolean
Dim ctl As Control

    For Each ctl In frm.Controls
       Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acCheckBox, acOptionGroup
                If ctl.Tag = "R" Then
                    If ctl & "" = "" Then
                        ctl.SetFocus
                        EnsureNotEmpty = False
                        MsgBox ctl.Name & " is required.", vbOKOnly
                        Exit Function
                    End If
                End If
            Case Else
        End Select
    Next ctl
   
    EnsureNotEmpty = True
End Function

Public Function EnsureNotEmptyAll(frm As Form) As Boolean   '''' not tested
Dim ctl As Control
Dim strMsg As String

    For Each ctl In frm.Controls
       Select Case ctl.ConitrolType
            Case acTextBox, acComboBox, acListBox, acCheckBox, acOptionGroup
                If ctl & "" = "" Then
                    ctl.BorderColor = RGB(186, 20, 25)
                    strMsg = strMsg & ctl.Name & " is required." & vbCrLf
                Else
                    ctl.BorderColor = RGB(192, 192, 192)
                End If
            Case Else
        End Select
    Next ctl
   
    If strMsg = "" Then
        EnsureNotEmptyAll = True
    Else
        EnsureNotEmptyAll = False
        MsgBox strMsg, vbOKOnly
    End If
End Function

 

[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!!!

 

[Pat Hartman]

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?

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.

 

[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!!

 

[Pat Hartman]

Great. Simple is always better.

 

[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?