I am developing db company projects informations in MS Access 2007.
Different users are entering the data records by
different forms.

How can find / keep records that which users enter this informations /
records ?

what properties will use to keep / records users name or user id
that this records entery by that user ?

Please help to provide support / steps to keep users data entry
records ?

Thanks and regards

Mateen

This is a fairly common solution.
On your tables that you want to keep this information, add four fields:

DateCreated - Date
WhoCreated - text
DateModified - Date
WhoModified - text

This will keep a record of who created the record and when, and who last modified the record. If you want to keep a record of every transaction, then we can do that as well, but that is another post.

On the DateCreated field, add this as the Default Value: Now()

On the forms where data entry will happen for your tables, add all four new fields to the form, and set their Visible property to No. Then on the Before Insert event for the form, add this code:

Me.WhoCreated = (Environ$("Username"))

This will take the user's name and add it to the record as it is created.

On the same form's Before Update event, add this code:

Me.DateModified = Now()
Me.WhoModified = (Environ$("Username"))

This is the simplest solution but has limitations. If you want a transaction record for each transaction, then I would suggest sending the records to another database somewhere on the network, and then using the current and old values for each field.

Did this help?


mmcdonal

Look it up at: http://wrox.books24x7.com

Thanks for your response.

I following the steps as describe above.

DateCreated
DateModified

It keeping records in DateCreated and DateModified fields but

WhoCreated
WhoModified

it is not keeping /recording the records WhoCreated and WhoModified ?

I use your code in before insert and before update event like this

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.WhoCreated = (Environ$("Username"))
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.DateModified = Now()
Me.WhoModified = (Environ$("Username"))
End Sub

how can keep records / recording of whocerate and whomodified the records
every transaction ?

what property will need to change / update ?

Thanks and regards
Mateen

Try this:

Dim sWho As String
sWho = (Environ$("Username"))
MsgBox sWho

Put this on a button or something and see if it is taking the environment variable to the string. If that works, then do this:

Dim sWho As String

sWho = (Environ$("Username"))

Me.WhoCreated = sWho

Did that work?


mmcdonal

Look it up at: http://wrox.books24x7.com

Thanks for your response.
I put above code on button click event

Dim sWho As String
sWho = (Environ$("Username"))
MsgBox sWho

but when I click no message display, no response.

how can get / store the username who is inserting the data ?

Thanks and regards
Mateen

Try this then:

Dim objNet as Variant
Dim sUser As String

Set objNet = CreateObject("WScript.Network")
sUser = objNet.UserName

Me.WhoCreated = sUser

or

Me.WhoModified = sUser

Of course I should have asked if your users are using Windows or some other OS.


mmcdonal

Look it up at: http://wrox.books24x7.com

Thanks for your response
I use above code in before insert and before update event.

Dim objNet as Variant
Dim sUser As String

Set objNet = CreateObject("WScript.Network")
sUser = objNet.UserName

Me.WhoCreated = sUser

both option but it not recording whocreated and whomodified records in respective fields.
but it recording the DateCreated , DateModified.

I am using windows vista MS office 2007 access.

regards
Mateen

Sorry to hear that about using Vista and Access 2007, both iffy products. I can't even get the events to fire at all in Access 2007 in XP. I will look around for security settings. I am not sure what is going on but I think it has to do with sandbox problems.


mmcdonal

Look it up at: http://wrox.books24x7.com

Okay, I found out how to turn off the macro seucrity in Chapter 2 - The Basics of Writing and Testing VBA Code, Beginning Access 2007 VBA
by Denise Gosnell (Wrox Press © 2007), and now I am thinking that you will need PowerShell commands since it is Vista.

I got this code to work as last posted by me in Access 2007 on Windows XP. Let me look up some Vista stuff.




mmcdonal

Look it up at: http://wrox.books24x7.com

Mateen,
I don't know if this will help...I have not used Vista or MS Office 2007...but I guess anything is worth a try when working on newer software and operating systems.

(I obtained this module code from "Real World Microsoft Access Database Protection and Security" by Garry Robinson.)
Create a module and put this in it:
Then on the BeforeInsert & BeforeUpdate events you can list the code as:
Regards,
Laura

FYI...My user id has changed. My old id was lbreitenbach

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!