Logging username and date of record edits

[Introfield]

I need to compare (ona daily basis) how many records are updated by my users. I've found some code that will insert a new record in a separate table whenever changes are made. However, I cant get it to go past the line:
Dim db As Database

The process looks like this...

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
End Sub

 

[RoyVidar]

Hi!

Assuming you are using access 2000+ versions and this is DAO code (default data access objects in access 97), then you'll have to do the following, check the Microsoft DAO 3.# Library (in any module, Tools | References), then also explicit declaration of the database object:

[tt]dim db as dao.database[/tt]

HTH Roy-Vidar

 

[JeremyNYC]

Also, unless this is stored in the original table, you're not adding the username and date to the record. Assuming you're using user-level security, this should do the trick (once you change the field names to match yours):

Sub WriteHistory()
Dim db As DAO.Database

Set db = CurrentDb
db.Execute "INSERT INTO tblStudentChanges (ModifierName, DateModified)" _
& " SELECT tblStudent.*, '" & CurrentUser & "', #" & Now() & "# " _
& " FROM tblStudent " _
& " WHERE (((tblStudent.StudentID)=4));"

db.Close
Set db = Nothing
End Sub

If not, search around the site for how to get the windows user name using environment variables (I always use security, so I've never had to learn that stuff).

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.