Adding date and user who amended a record.

[Sue22]

Hi, have a database and currently I have in there the date when a new record was created and also have the Username of the user who created the record.

I want to add something so that when the user updates a courses record (or the related records contained in the sub form) that the User ID and the data are added to the Updated and updated by fields in my main database table.

Very simply there is a Courses table and a modules table (each course will have many modules) so if using the form the user changes anything in either the courses table and/ or a related record in the modules table I need the two fields to be populated.

Is this possible?

 

[plog]

Possible, yes. Easy, probably not.

Hi, have a database and currently I have in there the date when a new record was created and also have the Username of the user who created the record.

Define "in there". You have a seperate table set up to accomodate this data? If so, then doing what you want is simple. Your table to capture who made changes to data would look like this:

RecordEdits
Edit_ID, autonumber primary key
ID_User, number, foreign key to users table
Edit_Date, date/time, holds when edit was made
Edit_Table, text, table that was edited
Edit_Field, text, field that was edited
Edit_Type, text, tells if record was added, deleted, changed, etc.

Then, you would have to make all your add/edit forms unbound. That way, users have to click a button to submit changes. When that button is clicked you can capture all the data needed to add recrods to RecordEdits.

 

[Sue22]

Ooops, my design is not like that I have the fields in the Courses table and I really don't think it would be a good idea at this stage to alter all of the forms
Are there any other ways round this or am I stuck with just knowing who created but not who changed things or when?

 

[plog]

You definitely would need the table like I suggested to caputre all the edits of a table. You might be able to use a bound form and use an event--I'm not an expert with form events--maybe some one else can answer that.


But why? Why do you need to monitor your users like this? Is your data so sensitive that responsibility for typos need to be assigned and punishment meted out?

 

[Sue22]

But why? Why do you need to monitor your users like this? Is your data so sensitive that responsibility for typos need to be assigned and punishment meted out?

Yikes no!! Nothing like that. I think sometime user loose track of where they are up to with what they have updated and what they haven't if they come to me to say did I update these courses I can say these were updated by you on these dates so they can see what they have done and what they still need to do nothing sinister.


I will keep a note of how you suggested to do things as I think it will be useful for future projects.

Thanks again for your advice.