TransferText export from Form

[mudstuffin]

I want to log changes that a user makes to a form by using the transfertext method. I see from the help on this that it refers to exporting from tables, but can I do this from the data in the text boxes on the form? I was thinking of using it with the beforeupdate and afterupdate events to log the old value and the changed to value.

Is this possible or is there a better way. I wondered if I could use a 'for each...' sequence to loop through the text boxes.

Many Thanks,


Greg.

 

[PeasNCarrots]

You can create a log table

ex:

Create a table with the following Fields

User, FieldChanged, OldValue, NewValue, DateChanged

I would initially lock the fields so the user cannot accidently change the values. Then add a command button to unlock the fields. Once this is clicked you can capture the OldValue and newValue using variables and then append that info into the logtable once the user clicks save.

 

[PJStephenson]

This is some code I wrote for a similar problem:

Private Sub Form_BeforeUpdate(Cancel As Integer)
DoCmd.SetWarnings False
If Me.Dirty = True And Me.NewRecord = False Then
    If MsgBox("Do you want to save changes?", vbYesNo) = vbNo Then
       Me.Undo
      ' DoCmd.CancelEvent
    Else
        i = Me.Controls.Count
        For j = 0 To i - 1
            If Me.Controls(j).ControlType <> 100 And Me.Controls(j).ControlType <> 104 Then
                If Me.Controls(j).Value <> Me.Controls(j).OldValue Then
                    FillChangeControl Environ("USERNAME") & " at " & Format(Now(), "dd/mm/yyyy HH:nn:ss"), Me.Name, Me!BrokerID.Value, Me.Controls(j).Name & ", was: " & Me.Controls(j).OldValue & " ,is now: " & Me.Controls(j).Value
                End If
            End If
        Next
    End If
End If
DoCmd.SetWarnings True
End Sub

and in a module I wrote a function to record changes in a table.

Public Sub FillChangeControl(strChangedBy As Variant, strChangedTable As Variant, strChangedRecord As Variant, strChangeDetail As Variant)
Dim rstChangeControl As Object

Set rstChangeControl = CurrentDb.OpenRecordset("tblChangeControl")

With rstChangeControl
    .AddNew
    !ChangedBy = strChangedBy
    !ChangedTable = strChangedTable
    !ChangedRecord = strChangedRecord
    !ChangeDetail = strChangeDetail
    .Update
    .Close
End With

End Sub

Hope this helps you
Peter