Storing computer username in field of a table

[MikeSA]

Hi
The idea is to record and store the computer user's ID in a record, so that I can track which user created or altered the record. Eg. A certain user creates a quotation in a table called 'Orders' and 'Order Detail', I want to know which computer user created the record.

Currently I use a query to calculate the TotalPrice from the Qty and Price and to retrieve the computer's username through VBA. But every time I run the query, it calls the current user's ID and does not store or keep the previous user ID values.

This must be simple, I just cannot get it?
MikeSA

[MikeSA]

Hi
The idea is to record and store the computer user's ID in a record, so that I can track which user created or altered the record. Eg. A certain user creates a quotation in a table called 'Orders' and 'Order Detail', I want to know which computer user created the record.

Currently I use a query to calculate the TotalPrice from the Qty and Price and to retrieve the computer's username through VBA. But every time I run the query, it calls the current user's ID and does not store or keep the previous user ID values.

This must be simple, I just cannot get it?
MikeSA

Another example is if I have a price list change, I want to keep the previous records reflecting the old pricing for example, and the new records to use the new pricing. In both examples, I need to store calculated values if I use a query as mentioned in the first example.(This I know is taboo!)

[MMcCarthy]

Hi
The idea is to record and store the computer user's ID in a record, so that I can track which user created or altered the record. Eg. A certain user creates a quotation in a table called 'Orders' and 'Order Detail', I want to know which computer user created the record.

Currently I use a query to calculate the TotalPrice from the Qty and Price and to retrieve the computer's username through VBA. But every time I run the query, it calls the current user's ID and does not store or keep the previous user ID values.

This must be simple, I just cannot get it?
MikeSA

Mike

You need to create a global variable to hold the userID. To do this you need to declare the variable as Public in a module (not a form module)

Public user As String

Now run the function to get the userID when the database opens and the variable will hold the value throughout the session.

[MikeSA]

Mike

You need to create a global variable to hold the userID. To do this you need to declare the variable as Public in a module (not a form module)

Public user As String

Now run the function to get the userID when the database opens and the variable will hold the value throughout the session.

Thanks Mac, now how do I insert the variable's value into a table field? My code so far is as follows (public variable you mentioned not declared as yet in this version, edit below):

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

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
        UserID = Left$(strUserName, lngLen - 1)
         Else
        fOSUserName = vbNullString
    End If
 
End Function

[MMcCarthy]

assuming your public variable is called "UserID" then just assign the value at the end of the function instead of returning it as a string.

[CODE=vb]
Private Declare Function apiGetUserName Lib "advapi32.d ll" Alias _
"GetUserNam eA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName()
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName( strUserName, lngLen)
If (lngX > 0) Then
UserID = Left$(strUserNa me, lngLen - 1)
Else
UserID = vbNullString
End If

End Function
[/CODE]

Now any time you add a record to a table then UserID will hold the value you need to assign to the user field in the table. There are a number of ways you can do this. The easiest is probably to put the field on the form in a hidden textbox (set visible property to No). Then when adding a new record set the textbox to this variable.

Me!UserField = UserID

[poucedeleon]

Hi
The idea is to record and store the computer user's ID in a record, so that I can track which user created or altered the record. Eg. A certain user creates a quotation in a table called 'Orders' and 'Order Detail', I want to know which computer user created the record.

Currently I use a query to calculate the TotalPrice from the Qty and Price and to retrieve the computer's username through VBA. But every time I run the query, it calls the current user's ID and does not store or keep the previous user ID values.

This must be simple, I just cannot get it?
MikeSA

Mike,
Go to AllenBrowne.com he have code for a Audit Trail It tracks Insert Deletes Changes (Before and After) The entries have to have been done through A form. Hopefully this will help

[MikeSA]

assuming your public variable is called "UserID" then just assign the value at the end of the function instead of returning it as a string.

[CODE=vb]
Private Declare Function apiGetUserName Lib "advapi32.d ll" Alias _
"GetUserNam eA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName()
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName( strUserName, lngLen)
If (lngX > 0) Then
UserID = Left$(strUserNa me, lngLen - 1)
Else
UserID = vbNullString
End If

End Function
[/CODE]

Now any time you add a record to a table then UserID will hold the value you need to assign to the user field in the table. There are a number of ways you can do this. The easiest is probably to put the field on the form in a hidden textbox (set visible property to No). Then when adding a new record set the textbox to this variable.

Me!UserField = UserID

Hi again,
How do I set the text box to this variable?
Me!UserField = UserID
Have I declared the variable in the right way? (See code below)

Please explain more explicitly in elaborate terms, still learning..


[CODE=vb]Option Compare Database
Public UserID As String

Private Declare Function apiGetUserName Lib "advapi32.d ll" Alias _
"GetUserNam eA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName()
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName( strUserName, lngLen)
If (lngX > 0) Then
UserID = Left$(strUserNa me, lngLen - 1)
Else
UserID = vbNullString
End If

End Function[/CODE]

[NeoPa]

Definition right on the button.

I would probably set the default value rather than code in the value on an add record. I asked about this very situation recently myself (Add Matching Record on Filtered Form). If it's all a bit complicated don't worry - I'll try to put some example code together anyway for you.

[NeoPa]

Again, assuming that you have a control on your form which matches the field in your table and is called [UserField].
Assuming also that the previously posted code to assign the UserID field is in place and runs when the database opens.
In your form's module include something like :

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
  With Me
    .UserField.DefaultValue = Nz(UserID, "")
    ...
  End With
End Sub

If you use this you need to change the names to protect the innocent ;)
Let us know how you get on with this.

[MikeSA]

Again, assuming that you have a control on your form which matches the field in your table and is called [UserField].
Assuming also that the previously posted code to assign the UserID field is in place and runs when the database opens.
In your form's module include something like :

Option Compare Database
Option Explicit

Private Sub Form_Open(Cancel As Integer)
  With Me
    .UserField.DefaultValue = Nz(UserID, "")
    ...
  End With
End Sub

If you use this you need to change the names to protect the innocent ;)
Let us know how you get on with this.

Thanks for the help, still a bit puzzling (Suppose I need to walk before I can run, still battling with some fundamentals..)

I have inserted the code below into the 'on open' field on the properties menu of the form named "Order Details":

Option Compare Database
 Option Explicit
        Private Sub Form_Open(Cancel As Integer)
        With Me.UserField.DefaultValue = Nz(UserID, "")
        End With
      End Sub]

I understand that this will assign variable 'UserID' to the default value of the text box named 'Userfield', GREAT!

But I still have a problem (your assumption above) on where to put my original code that retrieves the userid from the system and assigns it to the variable UserID? I suppose I need to get this module to run when I open the database? How do I do this? (I used it previously in a query, but have scrapped that since talking to you..)

See original module code below in repetition of previous:

Option Compare Database

Public UserID As String

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

Function fOSUserName()
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
            UserID = Left$(strUserName, lngLen - 1)
         Else
            UserID = vbNullString
    End If
 
End Function

[NeoPa]

Running code automatically upon opening a database can be handled in one of two ways :

1. {Not recommended} An AutoExec macro.
2. {Recommended} A form with a Form_OnOpen() procedure.

The form (which can be invisible or shown depending on your preference) must be set to open automatically on opening of the database (Tools / Startup... / Display form/page).

[MikeSA]

Great Thanks! Works like a charm!
MikeSA

[NeoPa]

Excellent Mike.
Always good to hear of success :)