Time Stamp on each textbox

[BittenApple]

Hello team,
Is it possible to insert a date or time stamp on each textbox on a record instead of inserting of inserting time and date stamp on each form?
Regard,
BBBBB

[HansV]

Do you really need to check each change to each field?
And what about deleting records?

You can keep track of changes to all fields in events of the form - see for example Add an Audit Trail to your Access Database.

[BittenApple]

Hello Hans,
I am going to following this instruction; copy and paste the exact code. If the code requires changes, I will fail. Is not there a simpler way?
Regards,
BBBBB

[HansV]

You can download a sample database from that page to see the code in action; you can study the code and you should try to understand it. You won't learn if you blindly copy the code.

[BittenApple]

I will,
Regards,
BBBBB

[BittenApple]

Hello Hans,
I read it several times. I think I understood what is going on.
It worked on the subform, I passed the Primary key of the subform when I called it code in before update event of the form; It didn't work on a main form. The main form contains a combo box, that I can select one from it and then 3 subforms are getting populated. I thought the reason is that the combo box Primary key is hidden in the combo box... I tried to change the combo box and I ran into many issues...! the forms are not getting populated although I fill out master link and child link.
Regards,
biiiitttttv

[HansV]

I assume that Master and child link don't link a subform a main form continues this question.

[BittenApple]

Hello Hans,
My inquiry is related to Post=201895.
I applied the code on the subform and it worked well, it doesn't work on Main form that contains a combo box.
These lines are from the author:
The Visual Basic Editor opens showing the form's code module with and empty BeforeUpdate event procedure ready for you to add the necessary code. You have two options here. If you want to audit all changes, including the addition of new records, enter the first code statement shown below (Listing 2). You need add only the second of the three lines shown here, don't duplicate the Private Sub and End Sub statements. IMPORTANT: The example shows "EmployeeID" as the parameter value. Change this to the name of the field that identifies the current record, usually the Primary Key field although you can use any field that uniquely identifies the record.
Listing 2: A procedure to call the AuditChanges routine (including new records)
Private Sub Form_BeforeUpdate(Cancel As Integer)
Call AuditChanges("EmployeeID")
End Sub

This is what seems to me: since my main form contains a combo box (that is I need to be a combo box, I don't need a text), I have to capture the values of the combo box and pass it to Call AuditChanges, I don't know how to capture the value of the current record of combo box on the main form.

When I entered Call AuditChanges("hosID"), it didn't work.

combo box has two fields hosId and hosName. and it only shows hosName.

I tried:
Dim comboBoxText As string
ComboxText = Me.Mycomboboxname.column(0)

This doesn't work. I should be able to capture the current Record hosID (hosID of combo box) and pass it to the AuditChanges.

any tips?

Regards,
abpl

My

[HansV]

Since your main form should be unbound, you should not track changes on the main form, that makes no sense.
You should only track changes in the subform.

[BittenApple]

Hello HansV,
Thanks and thanks,
code works on subform when the subform is tested by itself and it is stand alone, however when I open main form which contains the subform, the changes on the subform is not recorded. what is the problem.
Regards,
enap

[HansV]

That is impossible to say without seeing the database.

[BittenApple]

Hello Hans,
The code is only working for when the form is stand alone, as soon as it is embedded inside another form, it doesn't work. Thanks for the code though, I had something to start with. I am so lucky.
I was able to get help and he was able to change the code to work on the second form, now the code is working for subform when it is embedded inside another form. you can see the difference on the code in both modules.
I need the code is also applied to first form as well. I mean I need the changes to the combo box of main form to be also recorded.
Regards,
ttie,

[HansV]

To make the code work for a subform, pass the subform as a form to the AuditChanges procedure:

Code: Select all

Sub AuditChanges(frm As Form, IDField As String)
    On Error GoTo AuditChanges_Err
    Dim cnn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim ctl As Control
    Dim datTimeCheck As Date
    Dim strUserID As String
    Set cnn = CurrentProject.Connection
    Set rst = New ADODB.Recordset
    rst.Open "tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
    datTimeCheck = Now()
    strUserID = Environ("USERNAME")
    For Each ctl In frm.Controls
        If ctl.Tag = "Audit" Then
            If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                With rst
                    .AddNew
                    ![DateTime] = datTimeCheck
                    ![UserName] = strUserID
                    ![FormName] = frm.Name
                    ![RecordID] = frm.Controls(IDField).Value
                    ![FieldName] = ctl.ControlSource
                    ![OldValue] = ctl.OldValue
                    ![NewValue] = ctl.Value
                    .Update
                End With
            End If
        End If
    Next ctl
AuditChanges_Exit:
    On Error Resume Next
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Exit Sub
AuditChanges_Err:
    MsgBox Err.Description, vbCritical, "ERROR!"
    Resume AuditChanges_Exit
End Sub

Change the Before Update event procedure of the subform like this:

Code: Select all

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Call AuditChanges(Me, "MetId")
End Sub

2forms.zip

[BittenApple]

Hello Hans,
Thank you so very much for the code.
you said in post:
"Since your main form should be unbound, you should not track changes on the main form, that makes no sense.
You should only track changes in the subform." Post=202034
My Main form is bound to a table through a combo box, so it is not unbound.
Regards,
enap

[HansV]

Having a combo box bound to a table does not make the form bound to that table. The Record Source of your main form should be blank.