Creating a maintanence History Table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good day - I am trying to ocreate a maintanence history table in Access and
am lost on the process. What I want to do is log every change on every day
for every field. My maint. Hist. table consist of the following fields;

1. Loan Number ???
2. Old Value ???
3. New Value ???
4. User ID = (CurrentUser())
5. Time = (Now())

Lines 1, 2, and 3 are proving to be quite difficult. Any suggestions??

Thank you in advance.
 
You only need to store the OldValue since the current record (or previous
one) will have the other value needed for comparison. Do an Insert (Append)
query and add the data to a history table in the form's BeforeUpdate event,
something like (aircode):

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Handler

Dim strSQL As String
Dim db As DAO.Database

Dim strLoanNumber As String
Dim strField1 As String
Dim lngField2 As Long

strLoanNumber = Me.txtLoanNumber.OldValue
strField1 = Me.txtField1.OldValue
lngField2 = Me.txtField2.OldValue

Set db = CurrentDb

strSQL = "INSERT INTO tblWhatever( LoanNumber, Field1, Field2)"
strSQL = strSQL & " VALUES ('" & strLoanNumber & "', '" & strField1 & "', "
& lngField2 & ");"

db.Execute strSQL

Exit_Here:
Set db = Nothing
Exit Sub

Err_Handler:
MsgBox Error$
Resume Exit_Here

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access Downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Back
Top