Full Version: Store "GetUserName" in table
UtterAccess Forums > MicrosoftŪ Access > Access Forms
tbpowers
When my form is opened I have it showing the Logon Name of the computer accessing it. When the save button is hit, I would like to store the logon name to a table. How do I do this?
theDBguy
Some possibilities are:
. Create a bound control on your form to store the Logon name and assign the value in the open event of the form. For example:
Me.LogonName = GetUserName()
2. Use an Append query to insert the record into the table. For example:
DoCmd.Execute "INSERT INTO YourTable (LogonName) VALUES '" & GetUserName() & "'", dbFailOnError
(untested)
Hope that helps...
Grafixx01
All I've done with that is put it in the underlying table and make the control source for it something like =GetUserName() , depending on how you have it set up to either get the name of the computer, name of the user logged into the computer, or get the user logged into the database.
tbpowers
I created a text box and set the control source to "GetLogonName". That pulls the name in each time. When the save command button is hit it performs the spell check code below. How do I roll the "Insert into table" code into it? I would also like it to track the time in and time out.
My table name = Login
LoginID
TimeIn
TimeOut
Private Sub Save_Click()
Dim ctl As Control
DoCmd.SetWarnings False
For Each ctl In Me.Controls
If ctl.Tag = "SpellCheck" Then
DoCmd.GoToControl ctl.Name
DoCmd.RunCommand acCmdSpelling
End If
Next ctl
DoCmd.SetWarnings True
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.Refresh
Exit_Save_Click:
Exit Sub
Err_Save_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Save_Click
End Sub
theDBguy
I need to understand the function of the form. What is the user doing on it? When do you want to capture the TimeIn, as soon as the form opens? When do you want to capture the TimeOut, as soon as the Save button is clicked? More info, please...
tbpowers
Sorry,
The users will be inputing scrap information into the form. As soon as the form is opened I want to log the time in and their computer login name. As soon as they click the close button I want to log the time out.
It wasn't until I read your questions that I figured out what I really wanted. Thanks.
theDBguy
Okay, so this really has nothing to do with the Save button you were talking about earlier then?
. To save the login name and time when the form opens, you can try something in the form's Open event like:
Dim strSQL As String
strSQL = "INSERT INTO Login (LoginName, TimeIn) VALUES ('" & GetUserName & "', #" & Now() & "#)"
CurrentDb.Execute strSQL, dbFailOnError
2. To save the time after they close the form, try something like this in the form's Close event:
Dim strSQL As String
strSQL = "UPDATE Login SET TimeOut = #" & Now() & "# WHERE LoginName = '" & GetUserName & "' AND TimeOut IsNull"
CurrentDb.Execute strSQL, dbFailOnError
(untested)
Hope that helps...
tbpowers
Currently I have a macro set to GoToRecord = New and GoToControl = NMR_No when the form is opened. I also have a macro set to CloseDatabase when the close button is clicked. How do I convert the macros to code and integrate them into the code you posted?
tbpowers
I found the convert macro to code button in the db tools menu. Now how do I integrate the SQL scripts in with my other code?
tbpowers
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_Err
DoCmd.GoToRecord acForm, "NMR", acNewRec
DoCmd.GoToControl "NMR_No"
Form_Open_Exit:
Exit Sub
Form_Open_Err:
MsgBox Error$
Resume Form_Open_Exit
End Sub
+
Dim strSQL As String
strSQL = "INSERT INTO Login (LoginName, TimeIn) VALUES ('" & GetUserName & "', #" & Now() & "#)"
CurrentDb.Execute strSQL, dbFailOnError
?????????
theDBguy
Hi,

For that one, you can insert it like so:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_Err

Dim strSQL As String
strSQL = "INSERT INTO Login (LoginName, TimeIn) VALUES ('" & GetUserName & "', #" & Now() & "#)"
CurrentDb.Execute strSQL, dbFailOnError

DoCmd.GoToRecord acForm, "NMR", acNewRec
DoCmd.GoToControl "NMR_No"

Form_Open_Exit:
Exit Sub

Form_Open_Err:
MsgBox Error$
Resume Form_Open_Exit

End Sub

Just make sure you use the proper names for you table and fields and your function.

Hope that helps...
tbpowers
I tried that. I get "Compile Error: Type Mismatch" and the first & symbol is highlighted.
tbpowers
I figured out the error. How do I go about the code, timeout?
My the way thanks for your patience and help.
tbpowers
Ok. The more I think about this the more I realize that I probably don't want to log info each time a user accesses the form. There will be several people throughout the shop who just open the form to look at information. With that being said, I would combine these two pieces of code to execute when the save button is clicked?
.
Dim strSQL As String
strSQL = "INSERT INTO Login (LoginName, DateTime) VALUES ('" & GetUserName & "', #" & Now()& "#)"
CurrentDb.Execute strSQL, dbFailOnError
2.
Dim ctl As Control
DoCmd.SetWarnings False
For Each ctl In Me.Controls
If ctl.Tag = "SpellCheck" Then
DoCmd.GoToControl ctl.Name
DoCmd.RunCommand acCmdSpelling
End If
Next ctl
DoCmd.SetWarnings True
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.Refresh
Exit_Save_Click:
Exit Sub
Err_Save_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Save_Click
theDBguy
You could insert it like so...

Dim ctl As Control
DoCmd.SetWarnings False
For Each ctl In Me.Controls
If ctl.Tag = "SpellCheck" Then
DoCmd.GoToControl ctl.Name
DoCmd.RunCommand acCmdSpelling
End If
Next ctl
DoCmd.SetWarnings True
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Me.Refresh
Dim strSQL As String
strSQL = "INSERT INTO Login (LoginName, DateTime) VALUES ('" & GetUserName & "', #" & Now()& "#)"
CurrentDb.Execute strSQL, dbFailOnError

Exit_Save_Click:
Exit Sub
Err_Save_Click:
MsgBox Err.DESCRIPTION
Resume Exit_Save_Click

Hope that helps...
tbpowers
I get a runtime error "3134" and "CurrentDb.Execute strSQL, dbFailOnError" is highlighted when I debug.
theDBguy
While in the code window, go to Tools > References and see if you have a reference to DAO...
FOr give me a list of what references has the check marks.
tbpowers
"References" is greyed out in the tool menu.
tbpowers
Sorry, I had to go back in when it wasn't debugging to view the references. I'm learning. Here they are:
isual Basic for Applications
Microsoft Access 12.0 Objects Library
OLE Automation
Microsoft Office 12.0 Access database engine Object Library
theDBguy
No problem. Scroll down and look for "Microsoft DAO 3.6 Object Library" and put a checkmark next to it.
Hope that helps...
tbpowers
Get an error "Name conflicts with an existing module, etc...." However, I think I worked it out. I had one of my table columns named "DateTime", which the query builder picked it up as incorrect syntax. Once I changed that every thing worked fine.
appreciate all your time and help greatly!
theDBguy
You're welcome. Glad to hear you got it sorted out. Good luck with your project.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.