Audit Update of a Field

You can delete UsysApplicationlog once you have finished testing. It does give info on errors that can be "helpful".
If you look at my sample database in BestUse of DataMacros, you'll see that I used parameters in data macros and used the Named data macro to write the audit record. You have instructed the AfterUpdate data macro on tblSource to run your named data macro.
I'll be out most of the day and we're expecting lots of snow later today and overnight.
 
Understood. I was wanting to create a different named data macro to call from the AfterDelete and AfterInsert DM's, but it didn't work, but I'll do more testing.

Stay safe with the poor weather!
 
Got it working. For Deleted records, I had to transfer the [Old].value to the tblAuditLog, or it created blank entries.

Thank you again for showing me how to create these.
 
Okay, I'm back to things I don't know how to do ...

First, I have the DM populating the tblAuditLog.

I want to display real names in the Audit Table so I created a query in the FE based on the tblAuditLog. I have another table which I'll call tblUsers with a column I'll call RealName and a column I'll call UserName.

MsgBox Nz(ELookup("[RealName]", "[tblUsers]", "[UserName] = '" & GetUserName & "'"), GetUserName)

will display the real name if it exists or the username if it does not exist.

I want to add this as a column in my Query using the result in column UserName and adding a New Column for RealName.

Then, I want a button on the form to display the auditTable Query. (I know how to do this, I want to filter it to only results that match the current record). I think I know how to do that, I'll post back if I have problems with it.

Then, we want to save the displayed info to a PDF File. I'm not sure if I can add a button on the form to print the form to a PDF, or if I have to set up a report structure to do this, and I'm not sure how to do either one.

Thanks again!
 
Don't store redundant information. Rather than using a lookup, you can link the UserName in your Audit table to your users table in the query to display the result, don't store both bits of data. Use a left join in case there isn't a UserName for some reason.

Use a very similar syntax to create the calculated field in the query:

DisplayName : Nz([RealName],[Username])

The report should be simple once you have the core data.
 
@Minty - Sounds good, but I'm not following you! Do I need to add the tblUsers as a reference to the qryAuditTable? How do I add the additional column?

Stuck again ...

I have the pop-up form displaying the query results. I want to only display results that match the currently selected record in one of my bound forms.

I tried adding the following lines in my Form_Load() Event:
Me.Filter = "Orig_PK = """ & Forms![FormName].[PrimaryKey] & """"
Me.FilterOn = True

I'm getting an error "Microsoft Access cannot find the referenced form "FormName", even though I use the same expression in module code ...
 
I can probably figure out the rest of the issues with a co-worker who is familiar with SQL.
 
A few things to consider:
-you seemed adamant that you only needed basic info for after update--now this has changed??

-it is time to get clarity on what exactly is the purpose of/for your audit table. This should be related to business needs/requirement. What is required is not determined by the co-worker familiar with SQL. The SQL person may help with HOW the WHAT gets implemented.

-if you have an authoritative table that relates user login info to actual names, then you can that in combination with your GetUserName type of function to get the value (whatever you need--research the requirement) to record in the tblAuditLog.

-have you tried/tested using an update query to modify a record(s) in your tblSource. You'll find that such a change is recorded in the tblAuditLog.
 
@jdraw - Basically all of your points have been considered ... (And fortunately, we are early on in the process ...)

jdraw said:
-you seemed adamant that you only needed basic info for after update--now this has changed??
Click to expand...
No, it hasn't changed. As I said initially, the need/requirement is for audit info when a field changes. The want/desire would be nice to have a record in the table when a record is added or deleted - with or without a field change. (Partly b/c we have had previous issues with records disappearing and/or being copied over. I have both working now with your help.)

jdraw said:
-it is time to get clarity on what exactly is the purpose of/for your audit table.
Click to expand...
Essentially, we have date fields in the database for completion steps in our process. We don't have information on Who entered the data for the completion steps or when the data was entered. We have PDF files that are signed and retained separately from the database to also track the completion steps. The idea is get rid of the PDF files and use the database to track/record/audit the completion steps.
jdraw said:
What is required is not determined by the co-worker familiar with SQL. The SQL person may help with HOW the WHAT gets implemented.
Click to expand...
I never said anything otherwise. The co-worker speaks SQL. I speak VBA. The main difference is with the co-worker, I can say "Here's my database - how would I do this?" And as @Minty said, he basically said "Well, you could go with a left Join in your WHERE statement in your SQL query", which went somewhat over my head. The advantage of the co-worker over asking on here, is I don't have to say "I have a table, but it's really called something else, and it has these fields, but they aren't the real names, and I want to do this, but I don't know how, so when you reply back, I'll convert the created names for the real names and hopefully it will work."
jdraw said:
-if you have an authoritative table that relates user login info to actual names, then you can that in combination with your GetUserName type of function to get the value (whatever you need--research the requirement) to record in the tblAuditLog.
Click to expand...
Yes, I found a glitch in the matrix this morning and two possible workarounds. I have an authoritative table that relates CURRENT login info to actual names. I usually remove people from the table when they leave the company/department. So let's say Joe Smith is ab12345. He leaves the company. If the table pulls from my lookup table, the tblAudit says Joe Smith made the change 5 years ago. Okay, he was here then, makes sense. If the record is gone from my lookup table, the tblAudit says ab12345 made the change and nobody knows who that is, and if he left the company, he won't show up in any of our global searches either. HR should have a record that they could cross-reference, but I wouldn't want to tell an auditor "I think ab12345 is Joe Smith, but he's not working here anymore, I'll have to verify with HR and get back to you on that."

Workaround 1 is to record the real name in the tblauditresults - which probably means copying Elookup to the BE, but avoids all the SQL Join query issues.

Workaround 2 is to never delete entries from the table, but add an Active Employee Yes/No field and uncheck it when someone leaves.

jdraw said:
-have you tried/tested using an update query to modify a record(s) in your tblSource. You'll find that such a change is recorded in the tblAuditLog.
Click to expand...
We rarely do that, but one of the main reasons I wanted to go with DM for this is that often the fields might technically be updated via VBA rather than direct input by the user.

Thank you again for all of your assistance!
 
Good stuff.(y)
I would put an inactive flag on the record of the user who has left the company. That keeps the data in the database rather than tracking down an employeenumber and matching to a user name via HR or a pdf file.

I agree with your comment re the SQL guy. My concern with the What to record in the tblAuditLog was to get management or whoever has to use the information to identify the requirement. Someone(s) reference the data to make some decision or correction --who are they and what do they need. If you can identify or mock up how the data is used, it will help with designing your tblAuditlog and the related macros to capture the data. Eventually you can use the tblAuditlog to create queries or reports to satisfy specific requests. But it is the user of that data that can describe what needs to be captured.
 
Correct - as I said initially, I might get this working perfectly and management may say "That is not acceptable, you can't do that." Management (local) is aware of that risk and has authorized me to try it anyway. (And none of this gets RELEASED until management okays it. Worst case, we keep (and modify) the current process.
 
Marshall Brooks said:
I usually remove people from the table when they leave the company/department.
Click to expand...

Don't remove them - simply add a DateLeft field and fill it out.
Then your records will always match up for ever.
You can filter out those who have left from new data entry by excluding anyone with a DateLeft that isn't null.

"I have a table, but it's really called something else, and it has these fields, but they aren't the real names, and I want to do this, but I don't know how, so when you reply back, I'll convert the created names for the real names and hopefully it will work
Click to expand...

Why do this - unless your table and field names are called something daft like MyCompanyName.FredSmith they are just field names, not confidential material? They might mean something but surely it can't be that sensitive?
 
Minty said:
Why do this - unless your table and field names are called something daft like MyCompanyName.FredSmith they are just field names, not confidential material? They might mean something but surely it can't be that sensitive?
Click to expand...
Self-protection. It isn't that the information is that sensitive in itself. It's that if I ever get questioned, I can say "This is what I asked and it is all generic information."

If the right people get involved, it would come down to: Did your management know and approve of you asking these questions? (Yes, local management, although they don't know everything I post...) Who above local management was aware of and approved this exchange? (Now my immediate manager is in trouble) Did you have a non-disclosure agreement and a third-party proprietary authorization agreement with everyone you posted this information to and everyone who viewed it?

You can see where I am going ...
 
@jdraw and others:

I'm back with an odd issue ...

I decided that I would prefer to have real names in the Audit Table and have them stored that way initially.

Previously, I was using a function GetUserName to store the user ID. I think it worked with my bound forms also.

I added ELookup to the backend and created a new Function GetRealUserName() similar to Reply #78 to lookup the actual name and store it in tblAudit, or store the userid if it can't resolve the name.

It works fine when I manually change tblSource in the BE.
It works fine when I manually change tblSource in the linked table in the FE.
When I change the field via the bound form, I get an Error "3892 The function 'GetRealUserName' is not valid for expressions used in data macros."

But as I said, it works when I manually update the table, so I'm not sure that is really what the error is ???

Thoughts or suggestions?

It MIGHT be related to having Elookup() in both the FE and the BE, but I don't think so, since GetUserName is also in both the FE and BE and that was working properly before.
 
In the test database you posted there was no ELookup.
Can you post the code?

Do you have an authoritative UserTable? Can you post the structure?
How about a revised test database to review?
 
Code: 
Public Function ELookup(Expr As String, Domain As String, Optional Criteria As Variant, _
    Optional OrderClause As Variant) As Variant
On Error GoTo Err_ELookup
' https://www.everythingaccess.com/tutorials.asp?ID=Extended/Replacement-ELookup%28%29
    'Purpose:   Faster and more flexible replacement for dLookup()
    'Arguments: Same as dLookup, with additional Order By option.
    'Return:    Value of the Expr if found, else Null.
    '           Delimited list for multi-value field.
    'Author:    Allen Browne. allenbrowne.com
    'Updated:   December 2006, to handle multi-value fields (Access 2007 and later.)
    'Examples:
    '           1. To find the last value, include DESC in the OrderClause, e.g.:
    '               ELookup("[Surname] & [FirstName]", "tblClient", , "ClientID DESC")
    '           2. To find the lowest non-null value of a field, use the Criteria, e.g.:
    '               ELookup("ClientID", "tblClient", "Surname Is Not Null" , "Surname")
    'Note:      Requires a reference to the DAO library.
    Dim db As DAO.Database          'This database.
    Dim rs As DAO.Recordset         'To retrieve the value to find.
    Dim rsMVF As DAO.Recordset      'Child recordset to use for multi-value fields.
    Dim varResult As Variant        'Return value for function.
    Dim strSQL As String            'SQL statement.
    Dim strOut As String            'Output string to build up (multi-value field.)
    Dim lngLen As Long              'Length of string.
    Const strcSep = ","             'Separator between items in multi-value list.

    'Initialize to null.
    varResult = Null

    'Build the SQL string.
    strSQL = "SELECT TOP 1 " & Expr & " FROM " & Domain
    If Not IsMissing(Criteria) Then
        strSQL = strSQL & " WHERE " & Criteria
    End If
    If Not IsMissing(OrderClause) Then
        strSQL = strSQL & " ORDER BY " & OrderClause
    End If
    strSQL = strSQL & ";"

    'Lookup the value.
    Set db = DBEngine(0)(0)
    Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)
    If rs.RecordCount > 0 Then
        'Will be an object if multi-value field.
        If VarType(rs(0)) = vbObject Then
            Set rsMVF = rs(0).value
            Do While Not rsMVF.EOF
                If rs(0).Type = 101 Then        'dbAttachment
                    strOut = strOut & rsMVF!FileName & strcSep
                Else
                    strOut = strOut & rsMVF![value].value & strcSep
                End If
                rsMVF.MoveNext
            Loop
            'Remove trailing separator.
            lngLen = Len(strOut) - Len(strcSep)
            If lngLen > 0& Then
                varResult = left$(strOut, lngLen)
            End If
            Set rsMVF = Nothing
        Else
            'Not a multi-value field: just return the value.
            varResult = rs(0)
        End If
    End If
    rs.Close

    'Assign the return value.
    ELookup = varResult

Exit_ELookup:
    Set rs = Nothing
    Set db = Nothing
    Exit Function

Err_ELookup:
    MsgBox Err.DESCRIPTION, vbExclamation, "ELookup Error " & Err.Number
    Resume Exit_ELookup
End Function

This was from Allen Browne's site, but I'm not sure it is still there.
 
I didn't exactly follow all of that, but I created GetRealUserName() in the FE and all is good now.

Much appreciated.
 
You must log in or register to reply here.

Similar threads

S
End of the Month
Replies
4
Views
552
Sebsboutique
S
M
working with unbound field in a form
Replies
8
Views
864
CJ_London
C
P
Extract Year From Control On Form
Replies
7
Views
293
arnelgp
arnelgp
S
Need help turning a list into something form readable - training certifications
Replies
11
Views
881
MajP
MajP
J
Date Calculations/Review Trigger
Replies
3
Views
772
LarryE
LarryE

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom