By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,471 Members | 2,264 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,471 IT Pros & Developers. It's quick & easy.

Storing computer username in field of a table

P: 20
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
Sep 18 '07 #1
Share this Question
Share on Google+
12 Replies


P: 20
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!)
Sep 18 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Sep 19 '07 #3

P: 20
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):
Expand|Select|Wrap|Line Numbers
  1. Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
  2.     "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
  3.  
  4. Function fOSUserName() As String
  5. ' Returns the network login name
  6. Dim lngLen As Long, lngX As Long
  7. Dim strUserName As String
  8.     strUserName = String$(254, 0)
  9.     lngLen = 255
  10.     lngX = apiGetUserName(strUserName, lngLen)
  11.     If (lngX > 0) Then
  12.         fOSUserName = Left$(strUserName, lngLen - 1)
  13.         UserID = Left$(strUserName, lngLen - 1)
  14.          Else
  15.         fOSUserName = vbNullString
  16.     End If
  17.  
  18. End Function
Sep 19 '07 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
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.

Expand|Select|Wrap|Line Numbers
  1. Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
  2.     "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
  3.  
  4. Function fOSUserName()
  5. ' Returns the network login name
  6. Dim lngLen As Long, lngX As Long
  7. Dim strUserName As String
  8.  
  9.     strUserName = String$(254, 0)
  10.     lngLen = 255
  11.     lngX = apiGetUserName(strUserName, lngLen)
  12.     If (lngX > 0) Then
  13.         UserID = Left$(strUserName, lngLen - 1)
  14.     Else
  15.         UserID = vbNullString
  16.     End If
  17.  
  18. End Function
  19.  
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
Sep 19 '07 #5

poucedeleon
P: 4
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
Sep 19 '07 #6

P: 20
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.

Expand|Select|Wrap|Line Numbers
  1. Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
  2.     "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
  3.  
  4. Function fOSUserName()
  5. ' Returns the network login name
  6. Dim lngLen As Long, lngX As Long
  7. Dim strUserName As String
  8.  
  9.     strUserName = String$(254, 0)
  10.     lngLen = 255
  11.     lngX = apiGetUserName(strUserName, lngLen)
  12.     If (lngX > 0) Then
  13.         UserID = Left$(strUserName, lngLen - 1)
  14.     Else
  15.         UserID = vbNullString
  16.     End If
  17.  
  18. End Function
  19.  
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..


Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Public UserID As String
  3.  
  4. Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
  5.     "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
  6.  
  7. Function fOSUserName()
  8. ' Returns the network login name
  9. Dim lngLen As Long, lngX As Long
  10. Dim strUserName As String
  11.     strUserName = String$(254, 0)
  12.     lngLen = 255
  13.     lngX = apiGetUserName(strUserName, lngLen)
  14.     If (lngX > 0) Then
  15.             UserID = Left$(strUserName, lngLen - 1)
  16.          Else
  17.             UserID = vbNullString
  18.     End If
  19.  
  20. End Function
Sep 19 '07 #7

NeoPa
Expert Mod 15k+
P: 31,186
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.
Sep 19 '07 #8

NeoPa
Expert Mod 15k+
P: 31,186
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 :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_Open(Cancel As Integer)
  5.   With Me
  6.     .UserField.DefaultValue = Nz(UserID, "")
  7.     ...
  8.   End With
  9. 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.
Sep 19 '07 #9

P: 20
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 :
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub Form_Open(Cancel As Integer)
  5.   With Me
  6.     .UserField.DefaultValue = Nz(UserID, "")
  7.     ...
  8.   End With
  9. 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":
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  Option Explicit
  3.         Private Sub Form_Open(Cancel As Integer)
  4.         With Me.UserField.DefaultValue = Nz(UserID, "")
  5.         End With
  6.       End Sub]
  7.  
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:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2.  
  3. Public UserID As String
  4.  
  5. Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
  6.     "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
  7.  
  8. Function fOSUserName()
  9. ' Returns the network login name
  10. Dim lngLen As Long, lngX As Long
  11. Dim strUserName As String
  12.     strUserName = String$(254, 0)
  13.     lngLen = 255
  14.     lngX = apiGetUserName(strUserName, lngLen)
  15.     If (lngX > 0) Then
  16.             UserID = Left$(strUserName, lngLen - 1)
  17.          Else
  18.             UserID = vbNullString
  19.     End If
  20.  
  21. End Function
Sep 20 '07 #10

NeoPa
Expert Mod 15k+
P: 31,186
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).
Sep 20 '07 #11

P: 20
Great Thanks! Works like a charm!
MikeSA
Sep 30 '07 #12

NeoPa
Expert Mod 15k+
P: 31,186
Excellent Mike.
Always good to hear of success :)
Oct 3 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.