Date/Time Stamp

[Guest]

Hi

I have a database form for each Incident Report raised, I want to add a
approval box, which Team Leads tick to confirm they approve the action. Is
it possible to have a Date/time stamp to show when it was approved, but also
automatically put who approved it.

 

[Bill Mosca, MS Access MVP]

Using the control's Default as =CurrentUser() will return the user name if
you are using User-Level Security. The function =Environ("USERNAME") will
return the user if you are not using ULS.

As to the date/time stamp, make Now() the default value for the text box and
hide it from the user.

You can automatically update an existing record by writing some code to set
those same values in the BeforeUpdate event.

 

[Guest]

Add a Boolean (Yes/No) Approved field, a date/time ApprovedDateTime field and
a text ApprovedBy field to the underlying table.

Add a check box to the form with the Approved field as its ControlSource and
in the check box's AfterUpdate event procedure put the following code:

If Approved Then
Me. ApprovedDateTime = Now()
Me.ApprovedBy = GetUser()
Else
Me. ApprovedDateTime = Null
Me.ApprovedBy = Null
End If

If the user checks the check box the current date and time will be assigned
to the ApprovedDateTime field and the user name to the ApprovedBy field. If
the user unchecks the check box, i.e. 'unapproves' the action then both
fields will be cleared to Null. The GetUser function calls the Windows API
to return the user name of the currently logged in user, for which you need
to add the following module to the database:

''''module starts''''
Option Compare Database
Option Explicit

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal _
lpBuffer As String, nSize As Long) As Long


Public Function GetUser() As String

Dim strBuffer As String
Dim lngSize As Long, lngRetVal As Long

lngSize = 199
strBuffer = String$(200, 0)

lngRetVal = GetUserName(strBuffer, lngSize)

GetUser = Left$(strBuffer, lngSize - 1)

End Function
''''module ends''''

If you have implemented Access user and group level security then you can
alternatively get the name with which the current user is logged into Access
with:

Me.ApprovedBy = CurrentUser()

and would not need the module to call the Windows API.

Ken Sheridan
Stafford, England

 

[Douglas J. Steele]

I always cringe when I see it suggested to use the UserName environment
variable, given how trivial it is to reset.

I'd recommend using the API call, as illustrated in
http://www.mvps.org/access/api/api0008.htm at "The Access Web", if
User-Level Security hasn't been applied.

 

[Bill Mosca, MS Access MVP]

Doug

I thought Environ("USERNAME") retrieved the network log-in user name. Are
you saying it is returning a local setting?

 

[Douglas J. Steele]

Try this.

Create a batch file that does two things:
1) sets UserName
2) opens the database

The batch file will be something like:

SET UserName = Bob
"C:\Program Files\Microsoft Office\OFFICE11\msaccess.exe" "C:\My
Folder\MyApp.mdb"

Open a command prompt and run that batch file, and see what gets reported as
UserName.

Now, UserName will only be Bob in that particular DOS session, but so what?

 

[Bill Mosca, MS Access MVP]

I did that on a win XP Pro OS and still got my network user name, not "Bob"
when using Environ().

 

[Douglas J. Steele]

I just retested (WinXP Pro SP2), and it definitely showed Bob to me.

I'm using Access 97, but that shouldn't matter, since we're talking about an
environment variable, which comes from the OS.

Even if I double click on the bat file to run it, as opposed to running it
at a DOS prompt, I get Bob.

 

[Bill Mosca, MS Access MVP]

Hmm.
1. I created a bat file exactly as you showed, substituting on of my dbs
2. I double-clicked said file and the database opened. I opened the debug
window and typed:
?environ("USERNAME")
and pressed Enter.
3. return was my network user name, not Bob.

Doug - Are you testing this on a network workstation PC or a independent
machine? Mine is a workstation.

Just in case, I'm switching all my code using environ to using the API call.

 

[Douglas J. Steele]

Strange.

I'm on a network, connected to a domain. (It's actually a notebook, but that
shouldn't matter either)

 

[Bill Mosca, MS Access MVP]

Well, I believe you that it can happen so I won't be taking any chances from
now on. Thanks for the info, guy.