Capture Users that Create and Edit the records

[mikejaytlabustro]

What is the best way to capture the users that modify the records in the database?

Let us start from User Log-in Form
After log-in, Main Form will open
The main form has various menu (example Add Customer)
Once I open the "Add Customer Form", I want to record the username in the Createdby/Editedby Field automatically.

 

[CJ_London]

There are a number of ways this can be done. The easiest is to have a module with the following (have assumed this is numeric, but could be text:

Public pubUserID as Long
 
Public Function GetUserID() as Long
    GetUserID=pubUserID
End Function

Then in your login form, assign pubUserID with the ID of your user

In your beforeupdate event on the form use the following:

Me.UserID=pubUserID

or if you are using an insert or update query

Update myTable SET myTable.UserID=GetUserID()

Note you cannot access a public variable directly in SQL, hence the use of the function

 

[mikejaytlabustro]

Me.UserID=pubUserID

I get zero(0) using this code.
But it works when I reverse the code.

pubUserID=Me.UserID

Is it correct?

 

[CazB]

sorry for jumping in, but I just thought I'd say: I just use

me.LastEditedBy=currentuser()

on the beforeupdate of my forms... and that seems to work ok, without using a separate function... don't know if there are any problems with this approach?

 

[CJ_London]

pubUserID=Me.UserID

I would expect to see this in your login form assuming me.userid is a valid field relating to the user logging in.

Me.UserID=pubUserID

This appears in the form you are using to edit data

 

[CJ_London]

CazB

The problem with currentuser is if you haven't established a security-enabled workgroup, the CurrentUser method returns the name of the default user account, Admin. The Admin user account gives the user full permissions to all database objects. - See this link http://msdn.microsoft.com/en-us/library/office/ff845778.aspx

You can only establish a security enabled workgroup in Access 2003 and earlier