Security (Access 97/sr2)

Topic

New Reply

I have a database that is used to register students for class. As instructors, we can print rosters, certificates, waiting list, and other reports. Some of our classes have restrictions attached to them – basic excel must be completed before Inter excel. This program will also allow us to pull an educational profile by SS# to see whether a person has completed the course. There are times when we question who came to class. SO we check the roster to see if they signed in and check there profile. Lately, we have had some people say they attended the class however there is not signature on the roster.

Is there a way to tell who registered a person and the date and time of the entry? The program is secured using the security wizard. THe secretaries have permissions to register people as well as some supervisors.

Thanks in advance!!!
Deborah

Reply | Quote

Replies

User security is intended to prevent users to log in or to prevent them to see or modify some data or to use some objects.
If you want to watch who is doing what in the db, you have to add the appropriate fields and code to record the actions. There is no automatic way to do this.

Reply | Quote

I guess my post was not real clear. The people who are using the db have security access to add/delete/modify records to the table. I want to know if they is a way to capture who added/deleted/modifed the record in the table. They all have user names in security.

Can you provide some sample code or help to get this accomplised?

Deborah

Reply | Quote

In the table you want to monitor, create four additional fields:
CreatedBy, text field
CreatedDate, date field
ModifiedBy, text field
ModifiedDate, date field.

Then, in each form that use the table add the four fields to the form, make them eventually invisible and add the following code in the Before Update and Before Insert Event :
Private Sub Form_BeforeInsert(Cancel As Integer)
Me.CreateDate = Now()
Me.CreatedBy = CurrentUser
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.ModifiedDate = Now()
Me.ModifiedBy = CurrentUser
End Sub

This will give you for each record in the table the user that create the record and the last user who modify it.
This also suppose the users are unable to change the data directly in the tables or in the queries. There is no way in Access to record this.
If the user delete a record, you’ll also loose the info. If you want to get that info, then you should not allow the user to delete the record but add a field (Yes/No) to the table and when a user want to delete the record set this field to True. In the queries of the forms, you should the exclude the records with a True value in this field.

Reply | Quote

Thanks!

I searched the help files this morning and was testing the currentuser options. I was not very successful. I think I can follow your instructions and be able to assist the instructors with wrongful entries.

Thanks SO MUCH!!!!

Deborah

Reply | Quote

Thanks!

I searched the help files this morning and was testing the currentuser options. I was not very successful. I think I can follow your instructions and be able to assist the instructors with wrongful entries.

Thanks SO MUCH!!!!

Deborah

Reply | Quote

In the table you want to monitor, create four additional fields:
CreatedBy, text field
CreatedDate, date field
ModifiedBy, text field
ModifiedDate, date field.

Then, in each form that use the table add the four fields to the form, make them eventually invisible and add the following code in the Before Update and Before Insert Event :
Private Sub Form_BeforeInsert(Cancel As Integer)
Me.CreateDate = Now()
Me.CreatedBy = CurrentUser
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.ModifiedDate = Now()
Me.ModifiedBy = CurrentUser
End Sub

This will give you for each record in the table the user that create the record and the last user who modify it.
This also suppose the users are unable to change the data directly in the tables or in the queries. There is no way in Access to record this.
If the user delete a record, you’ll also loose the info. If you want to get that info, then you should not allow the user to delete the record but add a field (Yes/No) to the table and when a user want to delete the record set this field to True. In the queries of the forms, you should the exclude the records with a True value in this field.

Reply | Quote

I guess my post was not real clear. The people who are using the db have security access to add/delete/modify records to the table. I want to know if they is a way to capture who added/deleted/modifed the record in the table. They all have user names in security.

Can you provide some sample code or help to get this accomplised?

Deborah

Reply | Quote

User security is intended to prevent users to log in or to prevent them to see or modify some data or to use some objects.
If you want to watch who is doing what in the db, you have to add the appropriate fields and code to record the actions. There is no automatic way to do this.

Reply | Quote