I need help -- Creating a history table for changes

  • Thread starter Thread starter Sandspirit
  • Start date Start date
S

Sandspirit

I do not want to save the entire record as there are 20+ fields per
record that can be changed. I just want to save the data from the
fields that are changed. Can someone please help me????

Thank You,

Sandspirit
 
Sandspirit said:
I do not want to save the entire record as there are 20+ fields per
record that can be changed. I just want to save the data from the
fields that are changed. Can someone please help me????

Thank You,

Sandspirit
Click to expand...

This is an extract from a function I call from my form's Before Update event
that you might be able to adapt.
Keith.
www.keithwilby.com

Public Function libHistory(frmForm As Form, lngID As Long)

'Author: Keith Wilby
'Date: 05 July 2005
'Purpose: Record data transactions in tblHistory
'Called from: Form_BeforeUpdate & Delete events

Dim ctl As Control
Dim db As Database, rs As Recordset, strSQL As String, strUser As String
Set db = CurrentDb
strSQL = "Select * From qryHistory;"
Set rs = db.OpenRecordset(strSQL)
strUser = libUserName()
For Each ctl In frmForm
'Ignore controls such as labels
If ctl.Name Like "cmd*" Then GoTo Skip
If ctl.Name Like "btn*" Then GoTo Skip
If ctl.SpecialEffect = 0 Then GoTo Skip
If ctl.Name Like "txt*" Or ctl.Name Like "cbo*" Or ctl.Name Like
"ogr*" Or ctl.Name Like "chk*" Then
'Record null to value, value to null, and value changes
If ((IsNull(ctl.OldValue) And Not IsNull(ctl.Value)) Or
(IsNull(ctl.Value) And Not IsNull(ctl.OldValue)) _
Or ctl.OldValue <> ctl.Value) Then
With rs
.AddNew
![DataSource] = frmForm.Name
![ID] = lngID
![FieldName] = ctl.ControlSource
![UpdatedBy] = CurrentUser
![UpdatedWhen] = Now()
.Update
End With
End If
End If
Skip:
Next

rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End Function
 
Thank you. How do I call it?

I am new to this.

Is this correct??

Call libHistory

Thanks!

-Sandspirit
 
Sandspirit said:
Thank you. How do I call it?

I am new to this.

Is this correct??

Call libHistory

Thanks!

-Sandspirit
Click to expand...
Almost. You need to pass the parameters to the function:

Call libHistory(Me, Me.txtIssueNo)

"Me" is the form object and "Me.txtIssueNo" is the text box on my form that
contains the unique ID of the current record.

HTH - Keith.
www.keithwilby.com
 
I am working on it now. What is INGid? I am very new to this. I pasted
the code exactly as it appears but I am receiving errors.
 
This is the way I called the function:

Private Sub Form_beforeupdate()

Call libHistory(Me, Me.ID)

End Sub

This is the code with my fields:


Option Compare Database


Public Function libHistory(frmForm As Form, ID As Long)


'Author: Keith Wilby
'Date: 05 July 2005
'Purpose: Record data transactions in tblHistory
'Called from: Form_BeforeUpdate & Delete events


Dim Ctl As Control
Dim db As Database, rs As Recordset, strSQL As String, strUser As
String
Set db = CurrentDb
strSQL = "Select * From qryBusiness_Fields;"
Set rs = db.OpenRecordset(strSQL)
strUser = libUserName()
For Each Ctl In frmForm
'Ignore controls such as labels
If Ctl.Name Like "cmd*" Then GoTo Skip
If Ctl.Name Like "btn*" Then GoTo Skip
If Ctl.SpecialEffect = 0 Then GoTo Skip
If Ctl.Name Like "txt*" Or Ctl.Name Like "cbo*" Or Ctl.Name
Like "ogr*" Or Ctl.Name Like "chk*" Then
'Record null to value, value to null, and value changes
If ((IsNull(Ctl.OldValue) And Not IsNull(Ctl.Value)) Or _
(IsNull(Ctl.Value) And Not IsNull(Ctl.OldValue)) _
Or Ctl.OldValue <> Ctl.Value) Then
With rs
.AddNew
![DataSource] = frmFrm.Name
![ID] = ID
![FieldName] = Ctl.ControlSource
![UpdatedBy] = CurrentUser
![UpdatedWhen] = Now()
![Officer] = Officer
![OldVal] = Ctl.OldValue
![NewVal] = Ctl.Value
.Update
End With
End If
End If
Skip:
Next


rs.Close
Set rs = Nothing
db.Close
Set db = Nothing


End Function




Please help. Thank you sooo much. My deadline is today. I need to
correct this code so that we can start data entry in the database.

-Sandspirit
 
Sandspirit said:
This is the way I called the function:

Private Sub Form_beforeupdate()

Call libHistory(Me, Me.ID)

End Sub
Click to expand...

To avoid confusion (and assuming that Me.ID is a text box) I would rename
the ID control to txtID and then use Call libHistory(Me, Me.txtID). Make
sure it's the control's *name* you change and not it's data source.
This is the code with my fields:


Option Compare Database
Click to expand...

Add "Option Explicit" here then compile your code and check for errors.
Public Function libHistory(frmForm As Form, ID As Long)
Click to expand...

For clarity I would change your ID variable name to lngID.
'Author: Keith Wilby
'Date: 05 July 2005
'Purpose: Record data transactions in tblHistory
'Called from: Form_BeforeUpdate & Delete events


Dim Ctl As Control
Dim db As Database, rs As Recordset, strSQL As String, strUser As
String
Set db = CurrentDb
strSQL = "Select * From qryBusiness_Fields;"
Set rs = db.OpenRecordset(strSQL)
Click to expand...
strUser = libUserName()
Click to expand...

Your app won't recognise the line above because it's another of my functions
which you won't have. Change libUserName() to CurrentUser.
For Each Ctl In frmForm
'Ignore controls such as labels
If Ctl.Name Like "cmd*" Then GoTo Skip
If Ctl.Name Like "btn*" Then GoTo Skip
If Ctl.SpecialEffect = 0 Then GoTo Skip
If Ctl.Name Like "txt*" Or Ctl.Name Like "cbo*" Or Ctl.Name
Like "ogr*" Or Ctl.Name Like "chk*" Then
'Record null to value, value to null, and value changes
If ((IsNull(Ctl.OldValue) And Not IsNull(Ctl.Value)) Or _
(IsNull(Ctl.Value) And Not IsNull(Ctl.OldValue)) _
Or Ctl.OldValue <> Ctl.Value) Then
With rs
.AddNew
![DataSource] = frmFrm.Name
![ID] = ID
![FieldName] = Ctl.ControlSource
![UpdatedBy] = CurrentUser
![UpdatedWhen] = Now()
![Officer] = Officer
![OldVal] = Ctl.OldValue
![NewVal] = Ctl.Value
.Update
End With
End If
End If
Skip:
Next


rs.Close
Set rs = Nothing
db.Close
Set db = Nothing


End Function




Please help. Thank you sooo much. My deadline is today. I need to
correct this code so that we can start data entry in the database.

-Sandspirit
Click to expand...
The rest of it looks OK. Post back if you still can't get it to work.

Keith.
www.keithwilby.com
 
I changed it to Option Explicit but I am still receiving an error
message that states: Object not defined.

--Sandspirit
 
About what object is it complaining?

Looking at Keith's code, he's using DAO. If you're using Access 2000 or
2002, you might not have a reference set to the DAO library. With any code
module open, select Tools | References from the menu bar, scroll through the
list of available references until you find the one for Microsoft DAO 3.6
Object Library, and select it.

Also, change the line of code

Dim db As Database, rs As Recordset, strSQL As String, strUser As String

to

Dim db As Database, rs As DAO.Recordset, strSQL As String, strUser As String

The Recordset object exists in both the DAO and ADO models, so it's
necessary to "disambiguate" it like that.
 
Douglas J. Steele said:
About what object is it complaining?

Looking at Keith's code, he's using DAO. If you're using Access 2000 or
2002, you might not have a reference set to the DAO library. With any code
module open, select Tools | References from the menu bar, scroll through
the list of available references until you find the one for Microsoft DAO
3.6 Object Library, and select it.

Also, change the line of code

Dim db As Database, rs As Recordset, strSQL As String, strUser As String

to

Dim db As Database, rs As DAO.Recordset, strSQL As String, strUser As
String

The Recordset object exists in both the DAO and ADO models, so it's
necessary to "disambiguate" it like that.
Click to expand...

Thanks for jumping in there Doug (I've been away for the weekend :-))

Regards,
Keith.
 
From your original post Keith, how does the code know where to insert
the fields? I do not make out where the table the records are pasted to
is located in the code.

Thanks,

Sandspirit
 
Sandspirit said:
the fields? I do not make out where the table the records are pasted to
is located in the code.

Thanks,

Sandspirit
Click to expand...

In my app, qryHistory is based on table tblHistory with fields DataSource,
ID, FieldName, UpdatedBy and UpdatedWhen.

HTH - Keith.
www.keithwilby.com
 
Thank you Keith. I am new to this and I was trying to add the data
directly to the History table. I made a qry with the fields and all is
well. You were a great help. I hope we can converse in the future.
Thanks!

-Sandspirit
 
Back
Top