Data Macro Limitations

[jjsande1492]

You and I had slightly different setups. You called the named macro and passed two parameters to it where it then checked if the field was updated. I checked if the field was updated, then called the named macro with parameters. It would seem that in my setup that once the named macro finishes running, it does not revert to the After Update event to continue running the code there. I need to finish a test version for user feedback by next week. I'll see if I can put up some sample data after that. Thanks again.

[CJ_London]

Have you considered simply not allowing updates, only inserts? i.e. insert to a history file. Assuming you have a timestamp field you can find the latest record, or if preferred, copy or update the newly inserted record to a 'current' table.

Then, when you need to check the history, use the history file to track a particular component.

[Minty]

Left field solution: Move the data into SQL Server Express and turn on change data logging ?
https://www.mssqltips.com/sqlservert...l-server-2008/

[orange]

jjsande1492,
I'm still not sure that the underlying design (122 fields) is correct. I'm not saying it is incorrect, but just want to see more details of the requirement.
If logging is critical, then moving to SQL Server Express as Minty suggests would seem a better move(Logging is built in).

Can you tell us more about the application and the table that is central to your project? We're just looking for facts to help us offer more focused responses/suggestions.
Good luck.

[jjsande1492]

Hello again and thanks for the responses.

The business is not interested in migrating this project into an SQL environment, so I'm stuck with Access. Orange, the table has 122 fields because each of these fields is a point of data for each part the business decides to sell. Those 122 fields are on a standard upload form that is imported into a mainframe environment. The argument can certainly be made that I should not be designing to suit one particular output format and I have been looking at ways to better handle the situation. However, this is the current situation. Again, novice here. I knew almost nothing about Access a month or two ago. I've already redone several components of this database as I find better ways to do them.

My data macros are now working quite well tracking the change events except for my long text fields. Apparently data macros will not work on long text/memo fields, so I have been trying to find a way in VBA to do it. It's easy for a singular change, but if a user copies/pastes across multiple records, I get the error:

Here is my After Update event:

Private Sub Product_Blurb_AfterUpdate()

Call LogChange("PARTS", "MODIFIED", Me.ID.Value, Me.Part_Number.Value, Me.ActiveControl.Name, Me.ActiveControl.OldValue, Me.ActiveControl.Value)


End Sub

Here is my function:

Function LogChange(TableChanged As String, ChangeType As String, ChangedID As String, ChangedName As String, FieldChanged As String, ChangedFrom As Variant, ChangedTo As Variant)

Dim User As String                                                                                          'User that made the change
Dim strSQL As String                                                                                        'SQL statement
Dim rs As Recordset                                                                                         'Recordset
On Error GoTo ErrHandler                                                                                    'On errors, go to the error handler


GetWindowsUserID                                                                                            'Call the function to get the user's windows username
Set rs = CurrentDb.OpenRecordset("tbl_Users", dbOpenDynaset)                                                'Store the contents of tblUsers
rs.FindFirst "[Windows Username]='" & UserName & "'"                                                        'Look for a matching user name in the record set
User = rs![Full Name]                                                                                       'Get the user's full name


strSQL = "INSERT INTO tbl_ChangeLog " & " ([Table] ,[Action], [Change Date], [User], [Changed ID], [Changed Name], [Field Changed], [Changed From], [Changed To]) " & "VALUES (""" & TableChanged & """,""" & ChangeType & """,#" & Now() & "#,""" & User & """," & ChangedID & ",""" & ChangedName & """,""" & FieldChanged & """,""" & ChangedFrom & """,""" & ChangedTo & """);"


DoCmd.SetWarnings False                                                                                    'Turn off the pop-up confirmation messages
DoCmd.RunSQL strSQL                                                                                        'Run the SQL statement
DoCmd.SetWarnings True                                                                                     'Turn the warnings back on
RunCommand acCmdSaveRecord                                                                                 'Save the record into the table


Exit Function                                                                                               'Exit the function


'Skip to here when an error has occurred
ErrHandler:
  MsgBox Err.Description & vbNewLine & Err.Number, vbOKOnly, "Error"


End Function

This issue as I understand it is that Access has moved away from the subform as the active window. I haven't figured out how to handle that. I tried setfocus on the subform again with no luck. If anyone has any ideas how to solve this, it will be the end of all of my auditing requirements and I would be very grateful.

[orange]

@jjsande1492

A couple of other things:

-data macros don't handle long text fields, attachment fields, nor MVFs

-you can replace this code

DoCmd.SetWarnings False                                                                                    'Turn off the pop-up confirmation messages
DoCmd.RunSQL strSQL                                                                                        'Run the SQL statement
DoCmd.SetWarnings True

with a newer style

Currentdb.execute strSQL, dbFailOnError

It removes the constant prompts with action queries, but will prompt if there is some error condition. Less frustration.

- I reported an issue with Martin Green's Audit Logging routine when dealing with subforms. It was a poster on a different forum who had issues with the code/approach. I had issues with ActiveForm/ActiveControl etc and modified the code to include a different set up. The thread is located here. But the thread is quite long so the dialog is important.
A sample database is included in post 43.
You may get some ideas and insight from the thread.

I'm still not convinced that you need this degree of audit/logging. And I'm still questioning the 122 fields in your table.

The suggestion of a history table may be a simpler solution.

Good luck.

[ssanfu]

Those 122 fields are on a standard upload form that is imported into a mainframe environment.

Well, if the table were normalized, you could still use a query to "put the data back together" to make it *look* like it came from one table.
Maybe there is no logical way to normalize the table, but it sure sounds wrong..

About your code.... I modified it a little to fix things I saw that were a little off. (read, not the way I do it)

Not sure why you are using a function instead of a sub, but .........

Function LogChange(TableChanged As String, ChangeType As String, ChangedID As String, ChangedName As String, FieldChanged As String, ChangedFrom As Variant, ChangedTo As Variant)

    Dim User As String                                                                                   'User that made the change
    Dim strSQL As String                                                                                 'SQL statement
    Dim rs As Recordset                                                                                  'Recordset
    On Error GoTo ErrHandler                                                                             'On errors, go to the error handler


    GetWindowsUserID                                                                                     'Call the function to get the user's windows username
    Set rs = CurrentDb.OpenRecordset("tbl_Users", dbOpenDynaset)                                         'Store the contents of tblUsers
    rs.FindFirst "[Windows Username]='" & UserName & "'"                                                 'Look for a matching user name in the record set
    User = rs![Full Name]                                                                                'Get the user's full name


    strSQL = "INSERT INTO tbl_ChangeLog " & " ([Table] ,[Action], [Change Date], [User], [Changed ID], [Changed Name], [Field Changed], [Changed From], [Changed To])"
    strSQL = strSQL & " VALUES (""" & TableChanged & """, """ & ChangeType & """, #" & Now() & "#, """ & User & """, " & ChangedID & ",""" & ChangedName & """,""" & FieldChanged & """,""" & ChangedFrom & """,""" & ChangedTo & """);"
    '   Debug.Print strSQL                                                                               ' to check the SQL string

    '  DoCmd.SetWarnings False                                                                            'Turn off the pop-up confirmation messages
    '  DoCmd.RunSQL strSQL                                                                                'Run the SQL statement
    '  DoCmd.SetWarnings True                                                                             'Turn the warnings back on
    '  RunCommand acCmdSaveRecord                                                                         'Save the record into the table

    CurrentDb.Execute strSQL, dbFailOnError
    Me.Dirty = False                                                                                      'saves data in the form


Err_Exit:
    rs.Close                                                                                              'close the record set
    Set rs = Nothing                                                                                      'destroy object
    Exit Function                                                                                         'Exit the function


    'Skip to here when an error has occurred
ErrHandler:
    MsgBox Err.Description & vbNewLine & Err.Number, vbOKOnly, "Error"
    Resume Err_Exit

End Function

NOTE: the two words in RED in the SQL string are reserved words and shouldn't be used as object names.
You should also delete all spaces in object names........

[jjsande1492]

Thanks for the tip on slimming down my code. I'm all about slim code. Good point on reserved words, ssanfu. I'll change those. Thank you.

Orange, I know how much my 122 fields hurts your brain. Be glad that I found a way to avoid the full 731 field upload form in favor of the 122 field form. As I've said, I'm still learning and I'm not shy about re-doing things as I discover better ways to do them. The number of fields may well fall into this area.

The audit trail of all fields is 100% necessary for accountability of missing / incorrect data flowing through to a customer-facing point. If something is wrong or missing, the user needs to be coached or removed from their position upon repeated errors. I understand the history table solution, but prefer the current method. It is working very well aside from my two long text fields. Thanks for the link to the other thread. I'll have a read of it today and see what I can draw from it.

As always, I appreciate the help.

[orange]

jjsand1492,
This is just info for consideration.
Regarding the memo/long text fields, you may be able to use ColumnHistory. I stumbled on it from other posts and have no direct experience with it.
Don't know your application details but here are links that you can view and determine if it is relevant to your situation.
--http://www.fmsinc.com/microsoftacces...tory/index.asp
--https://docs.microsoft.com/en-us/pre...9(v=office.12)