Update/Insert Record

[jjcyeshua]

Hi guys

I need some ideas/help on something I'm working on . here's the basic structure. I have a base table and Transaction Table.. Basically Transac Table is just a history table to keep track of alll the changes made in fileds in Base table. If I use my base table as datasource for the forms, how can i make it work that whenever i make changes to the base table it would also insert these new values to the transact table.

Thank you in advance

 

[pbaldy]

I'm not sure, but it sounds like you want audit functionality. Search on that and see if it's what you want.

 

[thydzik]

Hi guys

I need some ideas/help on something I'm working on . here's the basic structure. I have a base table and Transaction Table.. Basically Transac Table is just a history table to keep track of alll the changes made in fileds in Base table. If I use my base table as datasource for the forms, how can i make it work that whenever i make changes to the base table it would also insert these new values to the transact table.

Thank you in advance

why not just use the base table to track changes as well?
each new record (row) will be timestamped and allow you to view previous changes.

 

[mearle]

If you set the recordset property of the form at run time to an ADO recordset object, declared WithEvents, you can use one of the associated ADO events of this recordset, such as RecordChangeComplete as a central place to pick up the changes, and record them.
eg:
Public WithEvents rs As ADODB.Recordset

Private Sub Form_Load()
Set rs = New ADODB.Recordset
Set rs.ActiveConnection = CurrentProject.Connection
rs.Open "SELECT * FROM Contacts", , adOpenDynamic
Set Me.Recordset = rs
End Sub

Private Sub rs_RecordChangeComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal cRecords As Long, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
' store transcation here
End Sub