Using the example of a student's form, we would need
> An additional table - tblStudentChanges.
> Two extra fields (Usercode and DateofChange) in both tblStudent and tblStudentChanges
> Use a password driven system or turn on Access built-in security to ascertain who the current user is.
Store all changes made
When both the tables have identical field names, this process is very simple. Use the AfterUpdate event of the form, to insert a new record in the tblStudentChanges table, whenever a change is made.
Private Sub Form_AfterUpdate()
Dim db As Database
Set db = CurrentDb
db.Execute "INSERT INTO [tblStudentChanges] " _
& " SELECT * FROM [tblStudent] WHERE " _
& " [tblStudent].[StudentID]=" & Me![StudentID] & ";"
Set db = Nothing
Ascertain who made each change and when
In the sample database, the student form receives the usercode of the current user through the OpenArgs property. Use the FormOpen event to set it up.
Private Sub Form_Open(Cancel As Integer)
'if you are using Access passwords to identify
' current user, use CurrentUser() Access function
[txtCurrentUser] = Me.OpenArgs
In the BeforeUpdate event of the form, check to see if any real changes have been made. You can use the OldValue property of controls to test for changes. If no changes have been made, simply cancel the update. If changes have been made, update the Usercode and DateofChange fields in the record.
Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error Resume Next
' some controls may not have the Tag property ,
' hence the resume next
Dim blnCheckDiff As Boolean
Dim ctl As Control
blnCheckDiff = False
For Each ctl In Me.Controls
If ctl.Tag = "Check" And ctl.Value <> ctl.OldValue Then
blnCheckDiff = True
If blnCheckDiff Then
[txtTime] = Now()
[txtuser] = [txtCurrentUser]
Cancel = True
Create a separate form based on the tblStudentChanges table. The form in the sample db displays the records in reverse chronological order, but you can change that, if needed. Make sure the form is a read-only and pop-up as well. Provide a command button to close the form and display the original Student's form again.
In the Student's form, create a command button that will display the Student Changes form for the current student. Make sure that all current changes have been saved before you open the Student Changes form.
Private Sub cmdChange_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmStudentChanges"
' the forced save of the current record
' will ensure that the current changes
' are reflected in the new form to be opened.
stLinkCriteria = "[StudentID]=" & Me![StudentID]
Me.Visible = False
DoCmd.OpenForm stDocName, , , stLinkCriteria
Hope this will help you.