Get UserName and Insert that in a Table

J

jeanhurtado

I want to get the username and computer name when the user open a form
then want to add the username, computer name and, the record that the
user modified (primary number) to a table that is called users. This
table has 3 fields:
USERNAME, MOFIED RECORD AND COMPUTER NAME

I have the code to get the Computer Name and User Name but don't know
how to get that data(ComputerName, Username and Current Record) and
enter to the Users table. Need this to get a log of the users and what
record the user modified or enter a new record.

The Code to get Username
------------------------------------------------------------------------------
Private Sub form_Open(Cancel As Integer)


On Error GoTo ErrHandler


DoCmd.GoToRecord acDataForm, Me.Form, acNewRec


Me!UserName.Text = fOSUserName()
Me!ComputerName.Value = fOSMachineName()
Me!BeginTime.Value = Now()
RunCommand acCmdSaveRecord


Exit Sub


ErrHandler:


MsgBox "Error in Form_Open( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear


End Sub
-----------------------------------------------------------------------------------------------------

The code to get Computer Name
------------------------------------------------------------------------------------------------------
Private Declare Function apiGetComputerName Lib "kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As
Long

Function fOSMachineName() As String
'Returns the computername
Dim lngLen As Long, lngX As Long
Dim strCompName As String
lngLen = 16
strCompName = String$(lngLen, 0)
lngX = apiGetComputerName(strCompName, lngLen)
If lngX <> 0 Then
fOSMachineName = Left$(strCompName, lngLen)
Else
fOSMachineName = ""
End If
End Function
 
J

jeanhurtado

Thanks Mr. Ken for asnwer me, I have inserted the code in the " before
updated" event in the form but there is an error and it says " Compile
error: expected: end of statement" The compiler say that the error is
located in the following part of the code:

DateTimeModified) VALUES


is between the " values" and parenthesis. I'm trying to find out how
this come. Can you help me?


Ken said:
You can execute an SQL statement to insert a row into the table. If you do
this in the AfterUpdate event procedure of the form this will insert a row
when a user updates an existing record or enters a new one. Remember that
updating a record does not necessarily mean changing its data, however.

I notice that you don't mention a field in the Users table for storing the
date/time when the record was updated. I'll assume it has a field of
date/time data type called DateTimeModified, and that the primary key of the
form's table is called MyID and is a number data type, in which case the code
would be:

Dim cmd As ADODB.Command
Dim strSQL As String

strSQL = "INSERT INTO Users(" & _
"UserName,ComputerName,[Modified Record]," & _
DateTimeModified) VALUES(" & _
""" & fOSUserName() & "",""" & _
fOSMachineName() & ""," & _
Me.MyID & ",#" & _
Format(Now(),"mm/dd/yyyy hh:nn:ss") & "#)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = strSQL
cmd.Execute

Set rst = Nothing
Set cmd = Nothing

Ken Sheridan
Stafford, England

I want to get the username and computer name when the user open a form
then want to add the username, computer name and, the record that the
user modified (primary number) to a table that is called users. This
table has 3 fields:
USERNAME, MOFIED RECORD AND COMPUTER NAME

I have the code to get the Computer Name and User Name but don't know
how to get that data(ComputerName, Username and Current Record) and
enter to the Users table. Need this to get a log of the users and what
record the user modified or enter a new record.

The Code to get Username
------------------------------------------------------------------------------
Private Sub form_Open(Cancel As Integer)


On Error GoTo ErrHandler


DoCmd.GoToRecord acDataForm, Me.Form, acNewRec


Me!UserName.Text = fOSUserName()
Me!ComputerName.Value = fOSMachineName()
Me!BeginTime.Value = Now()
RunCommand acCmdSaveRecord


Exit Sub


ErrHandler:


MsgBox "Error in Form_Open( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear


End Sub
-----------------------------------------------------------------------------------------------------

The code to get Computer Name
------------------------------------------------------------------------------------------------------
Private Declare Function apiGetComputerName Lib "kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As
Long

Function fOSMachineName() As String
'Returns the computername
Dim lngLen As Long, lngX As Long
Dim strCompName As String
lngLen = 16
strCompName = String$(lngLen, 0)
lngX = apiGetComputerName(strCompName, lngLen)
If lngX <> 0 Then
fOSMachineName = Left$(strCompName, lngLen)
Else
fOSMachineName = ""
End If
End Function
 
J

jeanhurtado

Thanks Mr. Ken for asnwer me, I have inserted the code in the "
AFTERUPDATED" event in the form but there is an error and it says "
Compile
error: expected: end of statement" The compiler say that the error is
located in the following part of the code:


DateTimeModified) VALUES


is between the " values" and parenthesis. I'm trying to find out how
this come. Can you help me?



Ken said:
You can execute an SQL statement to insert a row into the table. If you do
this in the AfterUpdate event procedure of the form this will insert a row
when a user updates an existing record or enters a new one. Remember that
updating a record does not necessarily mean changing its data, however.

I notice that you don't mention a field in the Users table for storing the
date/time when the record was updated. I'll assume it has a field of
date/time data type called DateTimeModified, and that the primary key of the
form's table is called MyID and is a number data type, in which case the code
would be:

Dim cmd As ADODB.Command
Dim strSQL As String

strSQL = "INSERT INTO Users(" & _
"UserName,ComputerName,[Modified Record]," & _
DateTimeModified) VALUES(" & _
""" & fOSUserName() & "",""" & _
fOSMachineName() & ""," & _
Me.MyID & ",#" & _
Format(Now(),"mm/dd/yyyy hh:nn:ss") & "#)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = strSQL
cmd.Execute

Set rst = Nothing
Set cmd = Nothing

Ken Sheridan
Stafford, England

I want to get the username and computer name when the user open a form
then want to add the username, computer name and, the record that the
user modified (primary number) to a table that is called users. This
table has 3 fields:
USERNAME, MOFIED RECORD AND COMPUTER NAME

I have the code to get the Computer Name and User Name but don't know
how to get that data(ComputerName, Username and Current Record) and
enter to the Users table. Need this to get a log of the users and what
record the user modified or enter a new record.

The Code to get Username
------------------------------------------------------------------------------
Private Sub form_Open(Cancel As Integer)


On Error GoTo ErrHandler


DoCmd.GoToRecord acDataForm, Me.Form, acNewRec


Me!UserName.Text = fOSUserName()
Me!ComputerName.Value = fOSMachineName()
Me!BeginTime.Value = Now()
RunCommand acCmdSaveRecord


Exit Sub


ErrHandler:


MsgBox "Error in Form_Open( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear


End Sub
-----------------------------------------------------------------------------------------------------

The code to get Computer Name
------------------------------------------------------------------------------------------------------
Private Declare Function apiGetComputerName Lib "kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As
Long

Function fOSMachineName() As String
'Returns the computername
Dim lngLen As Long, lngX As Long
Dim strCompName As String
lngLen = 16
strCompName = String$(lngLen, 0)
lngX = apiGetComputerName(strCompName, lngLen)
If lngX <> 0 Then
fOSMachineName = Left$(strCompName, lngLen)
Else
fOSMachineName = ""
End If
End Function
 
J

jeanhurtado

Thanks Mr. Ken I have use the code and works very well but I got an
error that it say"s:

----------------------------------------------------------------------------------
Run-time error '-2147217904 (800040e10)':

No value given for one or more required parameters.

----------------------------------------------------------------------------------

THIS IS THE CODE:

Private Sub Form_AfterUpdate()

Dim cmd As ADODB.Command
Dim strSQL As String


strSQL = "INSERT INTO Users(" & _
"USERNAME,COMPUTER_NAME,[MODIFIED RECORD]," & _
"DATE_TIME_MODIFIED) VALUES(" & _
"""" & fOSUserName() & """,""" & _
fOSMachineName() & """," & _
Me.ASSET_NUMBER & ",#" & _
Format(Now(), "mm/dd/yyyy hh:nn:ss") & "#)"



Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = strSQL
cmd.Execute


Set rst = Nothing
Set cmd = Nothing

End Sub


WHEN I DEBUG TO SEE THE ERROR IT SHOWS AND SELECT THE ERROR IS IN THE
FOLLOWING LINE OF THE CODE:

CMD.EXECUTE

CAN YOU HELP ME?tHANKS SO MUCH FOR YOUR EFFORT I'M VERY THANKSFUL TO
YOU.




Ken said:
Mea culpa; I missed a few quotes characters from:

strSQL = "INSERT INTO Users(" & _
"UserName,ComputerName,[Modified Record]," & _
"DateTimeModified) VALUES(" & _
"""" & fOSUserName() & """,""" & _
fOSMachineName() & """," & _
Me.MyID & ",#" & _
Format(Now(), "mm/dd/yyyy hh:nn:ss") & "#)"

Ken Sheridan
Stafford, England

Thanks Mr. Ken for asnwer me, I have inserted the code in the "
AFTERUPDATED" event in the form but there is an error and it says "
Compile
error: expected: end of statement" The compiler say that the error is
located in the following part of the code:


DateTimeModified) VALUES


is between the " values" and parenthesis. I'm trying to find out how
this come. Can you help me?



Ken said:
You can execute an SQL statement to insert a row into the table. If you do
this in the AfterUpdate event procedure of the form this will insert a row
when a user updates an existing record or enters a new one. Remember that
updating a record does not necessarily mean changing its data, however.

I notice that you don't mention a field in the Users table for storing the
date/time when the record was updated. I'll assume it has a field of
date/time data type called DateTimeModified, and that the primary key of the
form's table is called MyID and is a number data type, in which case the code
would be:

Dim cmd As ADODB.Command
Dim strSQL As String

strSQL = "INSERT INTO Users(" & _
"UserName,ComputerName,[Modified Record]," & _
DateTimeModified) VALUES(" & _
""" & fOSUserName() & "",""" & _
fOSMachineName() & ""," & _
Me.MyID & ",#" & _
Format(Now(),"mm/dd/yyyy hh:nn:ss") & "#)"

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = strSQL
cmd.Execute

Set rst = Nothing
Set cmd = Nothing

Ken Sheridan
Stafford, England

:

I want to get the username and computer name when the user open a form
then want to add the username, computer name and, the record that the
user modified (primary number) to a table that is called users. This
table has 3 fields:
USERNAME, MOFIED RECORD AND COMPUTER NAME

I have the code to get the Computer Name and User Name but don't know
how to get that data(ComputerName, Username and Current Record) and
enter to the Users table. Need this to get a log of the users and what
record the user modified or enter a new record.

The Code to get Username
------------------------------------------------------------------------------
Private Sub form_Open(Cancel As Integer)


On Error GoTo ErrHandler


DoCmd.GoToRecord acDataForm, Me.Form, acNewRec


Me!UserName.Text = fOSUserName()
Me!ComputerName.Value = fOSMachineName()
Me!BeginTime.Value = Now()
RunCommand acCmdSaveRecord


Exit Sub


ErrHandler:


MsgBox "Error in Form_Open( ) in" & vbCrLf & _
Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf & Err.Description
Err.Clear


End Sub
-----------------------------------------------------------------------------------------------------

The code to get Computer Name
------------------------------------------------------------------------------------------------------
Private Declare Function apiGetComputerName Lib "kernel32" Alias _
"GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As
Long

Function fOSMachineName() As String
'Returns the computername
Dim lngLen As Long, lngX As Long
Dim strCompName As String
lngLen = 16
strCompName = String$(lngLen, 0)
lngX = apiGetComputerName(strCompName, lngLen)
If lngX <> 0 Then
fOSMachineName = Left$(strCompName, lngLen)
Else
fOSMachineName = ""
End If
End Function
 
G

Guest

If Access comes across something in the query which it doesn't recognize as
an object name it treats it as a parameter, so check the SQL statement
carefully to make sure that the table, field names etc exactly match those in
the table. Look out particularly for where spaces and underscore characters
are used in the names. And remember that if an object name includes a space
it must be enclosed in square brackets, as with [MODIFIED RECORD].

Ken Sheridan
Stafford, England
 
J

jeanhurtado

Thanks Ken for answer me. I check all (Tables and Forms) including
spaces, field names,ect. I discover when the [modified record] contains
letters the error comes but when the [modified record] is a number runs
well. The problem is that the primary key contains numbers, letters and
combination of both. I have change the Asset Number field (primary key)
in the table (Asset Master) to text and also in the Users table.
There's something in the code that it will need a change so can accept
numbers,letters and combination in the modified record?Thanks for all
your efforts to help me. Merry Christmas and happy New Year.




Ken said:
If Access comes across something in the query which it doesn't recognize as
an object name it treats it as a parameter, so check the SQL statement
carefully to make sure that the table, field names etc exactly match those in
the table. Look out particularly for where spaces and underscore characters
are used in the names. And remember that if an object name includes a space
it must be enclosed in square brackets, as with [MODIFIED RECORD].

Ken Sheridan
Stafford, England

Thanks Mr. Ken I have use the code and works very well but I got an
error that it say"s:

----------------------------------------------------------------------------------
Run-time error '-2147217904 (800040e10)':

No value given for one or more required parameters.

----------------------------------------------------------------------------------

THIS IS THE CODE:

Private Sub Form_AfterUpdate()

Dim cmd As ADODB.Command
Dim strSQL As String


strSQL = "INSERT INTO Users(" & _
"USERNAME,COMPUTER_NAME,[MODIFIED RECORD]," & _
"DATE_TIME_MODIFIED) VALUES(" & _
"""" & fOSUserName() & """,""" & _
fOSMachineName() & """," & _
Me.ASSET_NUMBER & ",#" & _
Format(Now(), "mm/dd/yyyy hh:nn:ss") & "#)"



Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText
cmd.CommandText = strSQL
cmd.Execute


Set rst = Nothing
Set cmd = Nothing

End Sub


WHEN I DEBUG TO SEE THE ERROR IT SHOWS AND SELECT THE ERROR IS IN THE
FOLLOWING LINE OF THE CODE:

CMD.EXECUTE

CAN YOU HELP ME?tHANKS SO MUCH FOR YOUR EFFORT I'M VERY THANKSFUL TO
YOU.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top