Question for Pique Tech

I'll be glad to help with this, but today will be a hectic day.  I should be able to respond tonight or tomorrow.

ok, thanks...

here is the code again:

Option Explicit

Public mstrNBID As String
__________________________________________________________________________________________
Private Sub Business_Unit_Executive_AfterUpdate()
Dim strNBID
Dim strVals As String

strVals = "('" & Me!Business_Unit_Executive & "', '" & strNBID & "', #" & Now() & "#, '" & Me!Auto_ID & "', 'Business_Unit_Executive', '" & Me!Business_Unit_Executive.OldValue & "')"
strNBID = Forms!ISelector.OpenArgs
CurrentDb.Execute "Insert into tbl_Audit (Business_Unit_Executive, Updated_By, Updated_Time, Auto_ID, Field_Changed, PriorValue) VALUES" & strVals

End Sub

OK, I can't stand working on an insane PowerPoint presentation one minute longer.  A break is needed.

Help me understand:  are you trying to capture an audit event for the whole form?  If so, I'd suggest that you do this at the form level, i.e., create a Public Sub in a module, that you pass the name of the form and work out the logic to control how your users actually save their work, i.e., if they've made any changes, don't let them navigate off the current record without prompting them to save their changes.  If they confirm then the record gets updated, but your audit tool gets called too.  If they decline, then nothing gets saved and of course the audit tool DOESN'T get called (because nothing changed).

If you're trying to do it one control at a time, you're going to have a lot of work to do to capture all that.  

Post back here how you want to proceed and we'll get started.

I would like it if I could use one piece of code that would capture any change that is made to any of the fields in my form, including adding records and deleting records.  That would be ideal.  Prompting users to save changes would be fine.

Just need to make sure I capture any changes within this form and can tie it back to who made the change.

thanks...

the name of  the form changes are made in is: f_master_table

Let's start small.  First, you'll need a table to store these audit records.  It can be as simple as:
FormName
ControlName
OldValue
NewValue
ChangedDate--default to Now()
ChangedBy

I'm still working out the logic, will follow with that soon.  How will your users leave the form or navigate among records?  Will you expose record selectors, a "Save Record" button, are you going to display the Close Button and the Control Box?  Ideally, you'd have no record navigators and a "Close but don't save", a "Close and save", and maybe a "Save and go to another record" button.

As for your second question above ("I need new records to be audited too"), this is not usually handled as an audit function.  Rather, you'd normally have a created date/time and created by user field in the record which could not be edited by the user but are populated when the record is created.  The reason I say it's not really an audit function is if it were, you'd end up with essentially all your data duplicated in the database (wherever it's actually stored, and in the audit table when it's created).  Does that make sense?  Let me know what you think of that and I'll respond soon with the actual logic of the audit record creation.

How will your users leave the form or navigate among records?  A: i created left and right arrows for them to navigate through the records.  

I am not sure what you mean by record selectors.

I have created a "save record" button, but it does not do anything yet.

I have a "close form" button

What do you mean by a control box?

I can impliment what you advise but why can't i keep my arrows buttons for users to navigate?  otherwise, how will them move from form to form?: "Ideally, you'd have no record navigators and a "Close but don't save", a "Close and save", and maybe a "Save and go to another record" button."


As for your second question above ("I need new records to be audited too"), this is not usually handled as an audit function.  A: Ok forget about auditing the addition of records...

basically, i am open to an easy solution.  i will follow your lead on this....i'll look at it as a learning experience...

Perfect!

Using YOUR buttons is fine.  It's better to do that when you want to carefully control navigation.  I actually misspoke--I didn't mean record selector, I actually meant Navigation Buttons--the little <- and -> that Access provides unless you tell it not to on the lower left of the form.

The control box is the little bitty form icon in the upper left of a form in form view.  You can use it to switch among various form views.  

Anysay, here's what I'd do:  

Create the following procedure in a new module:
Public Sub CreateAuditRecord(ChangedFormName As Form, ChangedByName As String)

    Dim frm As Form
    Set frm = ChangedFormName
    Dim ctl As Control
    Dim FormName As String
    Dim ControlName As String
    Dim OldValue As String
    Dim NewValue As String
    Dim ChangedDate As String
    Dim ChangedBy As String
    Dim db As DAO.DataBase
    Set db = CurrentDb()
    Dim strInsert As String
    Dim strValues As String

    strInsert = "INSERT INTO AuditTable " & _
                "(FormName, ControlName, OldValue, NewValue, ChangedDate, ChangedBy) VALUES "
   
    For Each ctl In frm.Controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
               
                If Nz(ctl.OldValue, "") <> Nz(ctl.Value, "") Then 'control was changed
                   
                    FormName = frm.Name
                    ControlName = ctl.Name
                    ChangedDate = Now()
                    ChangedBy = ChangedByName
                    If Nz(ctl.OldValue, "") = "" Then OldValue = "It was blank" Else OldValue = ctl.OldValue
                    If Nz(ctl.Value, "") = "" Then NewValue = "Changed to blank" Else NewValue = ctl.Value
               
                    strValues = "('" & FormName & "','" & ControlName & "','" & OldValue & "','" & NewValue & "',#" & ChangedDate & "#,'" & ChangedBy & "')"
               
                    db.Execute (strInsert & strValues)
           
                End If
        End Select
    Next
End Sub

Then any time anything anything happens to move record focus (i.e., clicking on your arrow buttons or on the save button), call the procedure with the following line:
If Me.Dirty Then CreateAuditRecord Screen.ActiveForm, "TheUserIDYouWantToRecord"

Save your module with some meaningful name.

The beauty of this is, you can use it from any form as long as it's bound to a table or query to create an audit history.  And the history events are available via simple queries.

This isn't quite a clear as I'd hoped, but hopefully it will get you started.  Post questions back here if you have any and I'll certainly try to help out.

Where does the following line go and what do i enter for "the useridyouwanttorecord"?  If Me.Dirty Then CreateAuditRecord Screen.ActiveForm, "TheUserIDYouWantToRecord"

Also, how will the user id be picked up here.  Basically, I have a list of users in a form and they are required to enter a personal id in order to get into the db when they open it.  How will this code know who is currently in the DB?

I borrowed security prompt from someone so i did not set up the logic and don't understand how it works.  However, I worked with someone else on trying to setup this audit table and they did develop a logic that captured the id.  Here is what I was using.  Problem with this script was that it did not show the old value or what field was being captured and it only handles one field at a time.  The id part worked though:

Private Sub Business_Unit_Executive_AfterUpdate()
Dim strNBID
strNBID = Forms!ISelector.OpenArgs
CurrentDb.Execute "Insert into tbl_Audit (Business_Unit_Executive, Updated_By, Updated_Time) values ('" & Me!Business_Unit_Executive & "', '" & strNBID & "', Now())"
End Sub

also, here is the link to that issue if it helps:  https://www.experts-exchange.com/questions/21199061/Creating-an-Audit-Trail.html

You should put this code ANYWHERE that the user can navigate off the current record, for example you said you had put command buttons on your form with arrows to move next/back in the records.  So on BOTH of those buttons, you'd use this code in the Click event:
If Me.Dirty Then CreateAuditRecord Screen.ActiveForm, "TheUserIDYouWantToRecord"

And if your other helpers have devised a way to capture the user ID, then the same logic applies here.  Replace
"TheUserIDYouWantToRecord" with Forms!ISelector.OpenArgs, like so:
If Me.Dirty Then CreateAuditRecord Screen.ActiveForm, Forms!ISelector.OpenArgs

(Based on a scan of your other posting, I'm assuming that the form called ISelector is the form where the user tells Access who they are, probably it opens when you open the Access db and stays open while they're using it?  As long as that's true, then what I recommended just above should work just fine for you.)

ok, i implimented the code but have a couple of questions.

1) For my close form button, there is already code in the on click event:


2) After entering the code, I get an error on this line: Dim db As DAO.DataBase
The error says: "Complie Error: User defined type not defined"

Thanks,
John

BTW, Yes to your questions: (Based on a scan of your other posting, I'm assuming that the form called ISelector is the form where the user tells Access who they are, probably it opens when you open the Access db and stays open while they're using it?  As long as that's true, then what I recommended just above should work just fine for you.)

1.  That's ok, you can add this one line of code above in addition to any other code you have in a particular control.  I'd suggest you add it BEFORE any other code.
2.  Hm.  Then take out
    Dim db As DAO.DataBase
    Set db = CurrentDb()
and change
     db.Execute (strInsert & strValues)
to
     CurrentDb.Execute (strInsert & strValues)
3.  Good, thanks for the confirmation.

An error did not pop up but no record was recorded in the audit db.

I put the on click statement in my right arrow (navigate to next record).

Here are my two code module's:

1)
Private Sub Command74_Click()
On Error GoTo Err_Command74_Click

If Me.Dirty Then CreateAuditRecord Screen.ActiveForm, "Forms!ISelector.OpenArgs"

    DoCmd.GoToRecord , , acNext

Exit_Command74_Click:
    Exit Sub

Err_Command74_Click:
    MsgBox Err.Description
    Resume Exit_Command74_Click
   
End Sub
___________________________________________________________________________________________
2)
Public Sub CreateAuditRecord(ChangedFormName As Form, ChangedByName As String)

    Dim frm As Form
    Set frm = ChangedFormName
    Dim ctl As Control
    Dim FormName As String
    Dim ControlName As String
    Dim OldValue As String
    Dim NewValue As String
    Dim ChangedDate As String
    Dim ChangedBy As String
    Dim strInsert As String
    Dim strValues As String

    strInsert = "INSERT INTO AuditTable " & _
                "(FormName, ControlName, OldValue, NewValue, ChangedDate, ChangedBy) VALUES "
   
    For Each ctl In frm.Controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
               
                If Nz(ctl.OldValue, "") <> Nz(ctl.Value, "") Then 'control was changed
                   
                    FormName = frm.Name
                    ControlName = ctl.Name
                    ChangedDate = Now()
                    ChangedBy = ChangedByName
                    If Nz(ctl.OldValue, "") = "" Then OldValue = "It was blank" Else OldValue = ctl.OldValue
                    If Nz(ctl.Value, "") = "" Then NewValue = "Changed to blank" Else NewValue = ctl.Value
               
                    strValues = "('" & FormName & "','" & ControlName & "','" & OldValue & "','" & NewValue & "',#" & ChangedDate & "#,'" & ChangedBy & "')"
               
                    CurrentDb.Execute (strInsert & strValues)
           
                End If
        End Select
    Next
End Sub

1.  No quotes in
If Me.Dirty Then CreateAuditRecord Screen.ActiveForm, "Forms!ISelector.OpenArgs"
should be
If Me.Dirty Then CreateAuditRecord Screen.ActiveForm, Forms!ISelector.OpenArgs

Actually, I think that would cause it to fail, and based on how your Access is set up, it might not have given you a warning or failure message.

made the change, but same thing.  no errors but also no entry in audit table....

'control was changed is in green font color.  does that mean anything or does vb allocate colors to certain tasks?

Yes, the vb editor makes comments (that's what the single quote mark does) green

First let's make sure that your Access installation will tell you about errors.  In the VB Editor (code window), go to Tools -> Options -> General, and make sure that Break on All Errors is selected (right of center, in the Error Trapping box).

Second, I assume you're making changes to an existing record?

Third, your form is "bound"--i.e., it is based on a particular table or query, right?

Finally, also in the VB Editor, there should be a grey bar immediately to the left of the white code window.  Click in that grey area just to the left of the If Me.Dirty... line of code.  A big brown dot appears:  this is a breakpoint and tells Access "Stop here so I can see what's happening."

Go back to the form and open it up.  Change something.  Click your right arrow.  Immediately, the VB window should open at the line of code with the brown dot.  You can hover over any variable name and the tool tip will show its value.  You can single-step through the code one line at a time using F8.  If Me.Dirty is true (and it should be because you changed something), then after you press F8 once or twice, the VB line tracker (yellow arrow to the left) will move into the code of the new sub I sent you.

If anything is wrong, then you should get an error immediately after the code executes.  Let me know what happens as you try all this out.

1) Break on all erros was not selected.  I selected it.

2) Yes, I am making changes to an existing record.

3) Yes, the form is based on a query.

4) Ok, did that.  It eventually moves into the long script you sent me.  I did not get an error.  In hovering over the variable of in the line I "browned out" the first part is true, the forms!ISelector.Openargs shows the correct user id.  However, what may be of note is when hovering over screen.activeform a statement that says the following comes up: screen.activeform = <You entered an expression that requires a form to be the...

I can't read the rest of it because it is to long I guess.  It ends with the ....

Thanks...

So far, so good.  Basically, the breakpoint hover tooltip trick only shows values of variables already set.  So you should be able to F8 and go past that line of code to the next one, and so on, until (hopefully) you get the error that's keeping your audit record from being created.   You'll also get to watch the code run line by line, which may provide a better sense of what it's doing (or at least in what order).

I'm sorry if this is frustrating...I thought it would be easier, and it will certainly more generally useful to you because you can re-use it once we get it working right, and for what it's worth, it's working for me now, so I know it *can* work...  ; )

I did not look at every value for each variable but I f8'ed through the whole thing and no errors came up.

Don't sweat it on the hard work.  I am actually enjoying this.  I have always wondered how to debug and now I am learning how.  Thanks...

Look no further.  I got it working.  It was an error on my part.  I added an Auto Id to the audit table that screwed things up.  I removed it and it works now.  Let me test it somemore before I close this questions just to make sure I have everything I need.  Thanks again for all your help.  It was good working with you once again.

John

by the way, i have another open question that you origionally helped me out on that is going no where.  if you want to jump in feel free.  this is a question that i reopened after I closed it on your answer the first time.  turned out, just like with this question, i need a script that will encompass all fields instead of just one.  here is the link if you are interested:

https://www.experts-exchange.com/questions/21211865/requery-in-access.html

one more question on this.  could i put this script in the after update and after insert fields for the form?  that way, i would not have to impliment the script for each button on the form...

The magic of this is that you shouldn't need to do it after EVERY control (textbox, combo box, list box, check box, etc).  Instead, you put it on any control that the user can click to navigate off the current record (your back and forward arrows, for example), and if any changes have been made on any control, then this code will detect that a change was made (that's the If Me.Dirty Then part) and capture those changes (that's what the sub does).

Does that make sense?

The one thing I think you may need to do to be able to use this code anywhere is:  if you haven't, create a new module in the Modules tab of the database window and put the code I provided there, rather than in the code module attached to your form.  That's where you put code that you want to be available throughout the database (rather than just from a particular form).

yes, that does make sense.  i just realized there is one thing we forgot to capture.  when a change is made, there is nothing in the audit table that shows which record was changed...how would i update the table and the code to capture the auto_id of each record changed?  sorry about that...

LOL!  Good catch.

I can help here, but have to leave the office now for some appointments and then I have evening plans too.  I will be able to complete that tomorrow.

no worries.  i will impliment you additions on monday morning.  thanks, have a good weekend..

I've been thinking about this a bit and I have a question:  if the form is based on a query, how do you want to capture which table a particular change field comes from?  I don't think there's a GREAT general answer to that.

My suggestion:  capture the form's RecordSource and the control's CountrolSource.  Then you'll at least be able to trace it back to a table (assuming you don't completely redesign all your queries).

This leaves the code I provided still completely independent of the form it's implemented against.  If you "hardcode" the name of the table or query and the name of the field, you'd have to change it everywhere you ever used this code, and that kinda defeats the purpose of the "general solution" approach, eh?

Try this and let me know if it gets you what you want.

Here's some detailed how-to:  

Add the following fields to the table where your audit changes are captured:
FormRecordSource, text, 255
ControlControlSource, text, 100

And make some slight modifications to the code I provided to capture those values.  It comes out looking like this:

Public Sub CreateAuditRecord(ChangedFormName As Form, ChangedByName As String)

    Dim frm As Form
    Set frm = ChangedFormName
    Dim ctl As Control
    Dim FormName As String
    Dim ControlName As String
    Dim OldValue As String
    Dim NewValue As String
    Dim ChangedDate As String
    Dim ChangedBy As String
    Dim FormRecordSource As String
    Dim ControlControlSource As String
    Dim db As DAO.DataBase
    Set db = CurrentDb()
    Dim strInsert As String
    Dim strValues As String

    strInsert = "INSERT INTO AuditTable " & _
                "(FormName, ControlName, OldValue, NewValue, ChangedDate, ChangedBy, " & _
                "FormRecordSource, ControlControlSource) VALUES "
   
    For Each ctl In frm.Controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
               
                If Nz(ctl.OldValue, "") <> Nz(ctl.Value, "") Then 'control was changed
                   
                    FormName = frm.Name
                    ControlName = ctl.Name
                    ChangedDate = Now()
                    ChangedBy = ChangedByName
                    If Nz(ctl.OldValue, "") = "" Then OldValue = "It was blank" Else OldValue = ctl.OldValue
                    If Nz(ctl.Value, "") = "" Then NewValue = "Changed to blank" Else NewValue = ctl.Value
                    FormRecordSource = Nz(frm.RecordSource, "Unbound Form")
                    ControlControlSource = Nz(ctl.ControlSource, "Unbound Control")
                    strValues = "('" & FormName & "','" & ControlName & "','" & _
                        OldValue & "','" & NewValue & "',#" & ChangedDate & "#,'" & _
                        ChangedBy & "','" & FormRecordSource & "','" & ControlControlSource & "')"
               
                    db.Execute (strInsert & strValues)
           
                End If
        End Select
    Next
End Sub

hello pique tech, i don't need to capture the table that was changed in my audit table, i need to capture the record that was changed.  there is a master table that contains all records.  i need a field in my audit table that will capture the auto id of the record i am changing (each record in my master table has an auto id).  so i just need to add a field to the script that captures the auto id of the record i change.  this field is also included on the form where i am making the changes...

I still like my idea, but will edit it out of my code if you wish.

To address your specific concern:  I've made the code so that in addition to changed values, it will record the value of any control that you add "AuditMe" to the control's Tag property (the last line on the Properties panel, Other tab).  Simply type AuditMe in that line, and whether or not the value changed, the control's value will be recorded in your audit table as a Forced Audit.

This is the code now:

Public Sub CreateAuditRecord(ChangedFormName As Form, ChangedByName As String)

    Dim frm As Form
    Set frm = ChangedFormName
    Dim ctl As Control
    Dim FormName As String
    Dim ControlName As String
    Dim OldValue As String
    Dim NewValue As String
    Dim ChangedDate As String
    Dim ChangedBy As String
    Dim FormRecordSource As String
    Dim ControlControlSource As String
    Dim db As DAO.DataBase
    Set db = CurrentDb()
    Dim strInsert As String
    Dim strValues As String

    strInsert = "INSERT INTO AuditTable " & _
                "(FormName, ControlName, OldValue, NewValue, ChangedDate, ChangedBy, " & _
                "FormRecordSource, ControlControlSource) VALUES "
   
    For Each ctl In frm.Controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
               
                If Nz(ctl.OldValue, "") <> Nz(ctl.Value, "") Or ctl.Tag = "AuditMe" Then
                        'either record was changed or is marked for a forced audit
                    FormName = frm.Name
                    ControlName = IIf(Nz(ctl.Tag, "") = "", ctl.Name, ctl.Name & " (forced audit)")
                    ChangedDate = Now()
                    ChangedBy = ChangedByName
                    If Nz(ctl.OldValue, "") = "" Then OldValue = "It was blank" Else OldValue = ctl.OldValue
                    If Nz(ctl.Value, "") = "" Then NewValue = "Changed to blank" Else NewValue = ctl.Value
                    FormRecordSource = Nz(frm.RecordSource, "Unbound Form")
                    ControlControlSource = Nz(ctl.ControlSource, "Unbound Control")
                    strValues = "('" & FormName & "','" & ControlName & "','" & _
                        OldValue & "','" & NewValue & "',#" & ChangedDate & "#,'" & _
                        ChangedBy & "','" & FormRecordSource & "','" & ControlControlSource & "')"
               
                    db.Execute (strInsert & strValues)
           
                End If
        End Select
    Next
End Sub

no, what you have is fine.  i just wanted to make sure we were clear on what the code needed.  i will work this out and let you know how it goes.  thanks...

ok, i got a compile error: user defined type not defined.  this line: Dim db As DAO.DataBase, is highlighted...

this is what i have:

Public Sub CreateAuditRecord(ChangedFormName As Form, ChangedByName As String)

Dim frm As Form
Set frm = ChangedFormName
Dim ctl As Control
Dim FormName As String
Dim ControlName As String
Dim OldValue As String
Dim NewValue As String
Dim ChangedDate As String
Dim ChangedBy As String
Dim FormRecordSource As String
Dim ControlControlSource As String
Dim db As DAO.DataBase
Set db = CurrentDb()
Dim strInsert As String
Dim strValues As String

strInsert = "INSERT INTO AuditTable " & _
"(FormName, ControlName, OldValue, NewValue, ChangedDate, ChangedBy, " & _
"FormRecordSource, ControlControlSource) VALUES "

For Each ctl In frm.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox

If Nz(ctl.OldValue, "") <> Nz(ctl.Value, "") Then 'control was changed

FormName = frm.Name
ControlName = ctl.Name
ChangedDate = Now()
ChangedBy = ChangedByName
If Nz(ctl.OldValue, "") = "" Then OldValue = "It was blank" Else OldValue = ctl.OldValue
If Nz(ctl.Value, "") = "" Then NewValue = "Changed to blank" Else NewValue = ctl.Value
FormRecordSource = Nz(frm.RecordSource, "Unbound Form")
ControlControlSource = Nz(ctl.ControlSource, "Unbound Control")
strValues = "('" & FormName & "','" & ControlName & "','" & _
OldValue & "','" & NewValue & "',#" & ChangedDate & "#,'" & _
ChangedBy & "','" & FormRecordSource & "','" & ControlControlSource & "')"

db.Execute (strInsert & strValues)

End If
End Select
Next
End Sub

Yikes, I forgot you had that error before.  Sorry about that oversight.  Here is the code with all lines relevant to that commented out.

Public Sub CreateAuditRecord(ChangedFormName As Form, ChangedByName As String)

    Dim frm As Form
    Set frm = ChangedFormName
    Dim ctl As Control
    Dim FormName As String
    Dim ControlName As String
    Dim OldValue As String
    Dim NewValue As String
    Dim ChangedDate As String
    Dim ChangedBy As String
    Dim FormRecordSource As String
    Dim ControlControlSource As String
    'Dim db As DAO.DataBase
    'Set db = CurrentDb()
    Dim strInsert As String
    Dim strValues As String

    strInsert = "INSERT INTO AuditTable " & _
                "(FormName, ControlName, OldValue, NewValue, ChangedDate, ChangedBy, " & _
                "FormRecordSource, ControlControlSource) VALUES "
   
    For Each ctl In frm.Controls
        Select Case ctl.ControlType
            Case acTextBox, acComboBox, acListBox, acOptionGroup, acCheckBox
               
                If Nz(ctl.OldValue, "") <> Nz(ctl.Value, "") Or ctl.Tag = "AuditMe" Then
                        'either record was changed or is marked for a forced audit
                    FormName = frm.Name
                    ControlName = IIf(Nz(ctl.Tag, "") = "", ctl.Name, ctl.Name & " (forced audit)")
                    ChangedDate = Now()
                    ChangedBy = ChangedByName
                    If Nz(ctl.OldValue, "") = "" Then OldValue = "It was blank" Else OldValue = ctl.OldValue
                    If Nz(ctl.Value, "") = "" Then NewValue = "Changed to blank" Else NewValue = ctl.Value
                    FormRecordSource = Nz(frm.RecordSource, "Unbound Form")
                    ControlControlSource = Nz(ctl.ControlSource, "Unbound Control")
                    strValues = "('" & FormName & "','" & ControlName & "','" & _
                        OldValue & "','" & NewValue & "',#" & ChangedDate & "#,'" & _
                        ChangedBy & "','" & FormRecordSource & "','" & ControlControlSource & "')"
               
                    CurrentDb.Execute (strInsert & strValues)
           
                End If
        End Select
    Next
End Sub

an entry was added to the table, but the id of the record was not picked up.  this is what was returned:

FormName      :  f_Master_Table                     (this is the name of my form)
FormRecordSource:  q_Master_Table         (this is the name of the query that provides the data for my form)
ControlControlSourceL:  ISO                      (this is the field i changed)
ControlName:  ISO                                     (this is the field i changed)
OldValue:  Joel Cain      
NewValue:  English
ChangedDate:  11/22/2004 12:53:49 PM
ChangedBy:  nba7777

      
      
      
      
      
      

You:
i need a field in my audit table that will capture the auto id of the record i am changing (each record in my master table has an auto id).  so i just need to add a field to the script that captures the auto id of the record i change.  this field is also included on the form where i am making the changes...

Me:
it will record the value of any control that you add "AuditMe" to the control's Tag property (the last line on the Properties panel, Other tab).  Simply type AuditMe in that line...

Do you have a textbox on your form with the record in the master table with the auto id?  The contol doesn't even have to be visible, but it does have to be on the form, and it does have to have AuditMe in the Tag property.

Also, this will create ONE EXTRA ENTRY in the audit table in addition to all the fields that changed.  It would look something like:

FormName     :  f_Master_Table                    
FormRecordSource:  q_Master_Table        
ControlControlSourceL:  TheAutoNumFieldName                    
ControlName:  TheAutoNumFieldName (forced audit)       <--------   the (forced audit) is the clue
OldValue:  TheValueOfTheAutoNum                                <-------    also note that the old and new vals
NewValue:  TheValueOfTheAutoNum                                              are the same
ChangedDate:  11/22/2004 12:53:49 PM
ChangedBy:  nba7777

Then you would use the ChangedDate, the form name, and the form record source to tie a set of records related to a particular change all together.

If that doesn't work for you (i.e., you don't like that approach), I can think of one other way to approach this which would require VERY minor changes to what we've done so far.  Just let me know.

got it.  that seems to be working.  i will test further and report back any issues.  if everything looks good, i'll close it out.  thanks again, john

How would this work? (You said "Then you would use the ChangedDate, the form name, and the form record source to tie a set of records related to a particular change all together.")

The two listings is not a problem for me.  I could also create a query to feed a form, if I wanted it all on one line...

I like it.  There is a problem with the code though.  I get an error that says: "Can't find the field "The name of the AutoIDControl" refered to in your expression.

You have to change that to be the name of the textbox where you have your AutoID.  From one of the original posts, I think it may be Auto_ID, so your line of code to call the function would be:
If Me.Dirty Then CreateAuditRecord Screen.ActiveForm, "TheUserMakingTheChange", Me!Auto_ID
and you have to change "TheUserMakingTheChange" to the string for whoever the user is (we discussed that pretty early too--you said you have a process for determining the user, you'd put that result here).

Looking Good.  Made the changes and it works fine.  Thanks again Pique Tech!

Hey Pique Tech,

I am having problems.  All of a sudden I am getting a Type Mismatch whenever i make a change and hit a button.  also, nothing records in the audit table.  i will set up another question for this.

here you go:

https://www.experts-exchange.com/questions/21218026/Question-For-Pique-Tech.html