Adding Windows username in a new record? (1 Viewer)

C

contractor

Having Fun With Access
Local time
Today, 09:12
Joined
Apr 12, 2012
Messages
47
Hi Nate You probably did put a text after end sub, like "It's safer"

Remove the unnecessary text from the code....

Contractor
 
F

funwithaccess

Registered User.
Local time
Today, 12:12
Joined
Sep 5, 2013
Messages
80
I have eliminated the notes that you had included and had not added any. The debugger points to this in regard to comments:

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

contractor

Having Fun With Access
Local time
Today, 09:12
Joined
Apr 12, 2012
Messages
47
Hi,

You should put this code after Option explicit :

Code: 
Option Compare Database
Option Explicit
 Private Declare PtrSafe Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Contractor
 
F

funwithaccess

Registered User.
Local time
Today, 12:12
Joined
Sep 5, 2013
Messages
80
Contractor,

Should this be within the button click? Maybe that is what I am doing wrong. I believe that I have butchered the code. Haha

Here is the updated code:
Code: 
Private Sub cmdFind_Click()
On Error GoTo cmdFind_Click_Err
    
    
    Option Compare Database
    Option Explicit
    Private Declare PtrSafe Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserName" (ByVal lpBuffer As String, nSize As Long) As Long
    Dim lngLen As Long
    Dim lngX As Long
    Dim strUserName As String
    
    strUserName = String$(254, 0&)
    lngLen = 255&
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0&) Then
        strUserName = Left$(strUserName, lngLen - 1&)
    End If
    
    If strUserName <> vbNullString Then
        GetNetworkUserName = strUserName
    Else
        GetNetworkUserName = "{unknown}"
    End If
    
    Dim tmpDB As DAO.Database, qry As DAO.QueryDef
    Set tmpDB = CurrentDb
    
    Combo99.Value = ""
    Combo89.Value = ""
    Combo71.Value = ""
    Combo55.Value = ""
    Combo85.Value = ""
    Combo212.Value = ""
    
    For Each qry In CurrentDb.QueryDefs
        DoCmd.Close acQuery, qry.Name, acSaveYes
    
    Dim intCount As Integer
    intCount = 0
    
    If DCount("Location", "Phone numbers Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "", acViewNormal, acReadOnly
    End If
    
    If DCount("Alias", "SD Documentation Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "SD Documentation Query", acViewNormal, acReadOnly
    End If
    
    If DCount("Searchable Alias", "Info Gathering Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "SD Documentation Query", acViewNormal, acReadOnly
    End If

    If DCount("AssetNumber", "Xerox Assets Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "Xerox Assets Query", acViewNormal, acReadOnly
    End If

    If DCount("[IP Address]", "Xerox IP Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "Xerox IP Query", acViewNormal, acReadOnly
    End If

    If DCount("SerialNumber", "Xerox Serial Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "Xerox Serial Query", acViewNormal, acReadOnly
    End If

    If intCount = 0 Then MsgBox "No results found in ServiceBase." & vbCrLf & "Please provide a specific word, phrase, or alias.", vbExclamation + vbOKOnly, "ServiceBase Search Results"
        
    tmpDB.Execute "INSERT INTO [Usage Log]([NT ID], loginDate) VALUES(" & Chr(34) & GetNetworkUserName() & Chr(34) & ", " & CDbl(Now) & ")"

cmdFind_Click_Exit:
    tmpDB = Nothing
    Exit Sub

cmdFind_Click_Err:
    MsgBox Error$
    Resume cmdFind_Click_Exit
End Sub
 
C

contractor

Having Fun With Access
Local time
Today, 09:12
Joined
Apr 12, 2012
Messages
47
No it sould be like this :

Behind Module :

Code: 
Option Compare Database
Option Explicit
Private Declare PtrSafe Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserName" (ByVal lpBuffer As String, nSize As Long) As Long


Public Function GetNetworkUserName() As String
On Error GoTo ErrorHandler
    'Purpose:   Returns the network login name
    'Return:    The name, or "{Unknown}" on error.
    'Note:      Safer than testing Environ().
    Dim lngLen As Long
    Dim lngX As Long
    Dim strUserName As String
    
    strUserName = String$(254, 0&)
    lngLen = 255&
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0&) Then
        strUserName = Left$(strUserName, lngLen - 1&)
    End If
    
    If strUserName <> vbNullString Then
        GetNetworkUserName = strUserName
    Else
        GetNetworkUserName = "{unknown}"
    End If

Exit_ErrorHandler:
    Exit Function

ErrorHandler:
    MsgBox Err.Number & vbCrLf & Err.Description & vbCrLf & vbCrLf & "GetNetworkUserName()"
    Resume Exit_ErrorHandler
End Function

Code Behind Button :

Code: 
Private Sub cmdFind_Click()
On Error GoTo cmdFind_Click_Err
    
    Combo99.Value = ""
    Combo89.Value = ""
    Combo71.Value = ""
    Combo55.Value = ""
    Combo85.Value = ""
    Combo212.Value = ""
    
    For Each qry In CurrentDb.QueryDefs
        DoCmd.Close acQuery, qry.Name, acSaveYes
    
    Dim intCount As Integer
    intCount = 0
    
    If DCount("Location", "Phone numbers Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "", acViewNormal, acReadOnly
    End If
    
    If DCount("Alias", "SD Documentation Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "SD Documentation Query", acViewNormal, acReadOnly
    End If
    
    If DCount("Searchable Alias", "Info Gathering Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "SD Documentation Query", acViewNormal, acReadOnly
    End If

    If DCount("AssetNumber", "Xerox Assets Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "Xerox Assets Query", acViewNormal, acReadOnly
    End If

    If DCount("[IP Address]", "Xerox IP Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "Xerox IP Query", acViewNormal, acReadOnly
    End If

    If DCount("SerialNumber", "Xerox Serial Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "Xerox Serial Query", acViewNormal, acReadOnly
    End If

    If intCount = 0 Then MsgBox "No results found in ServiceBase." & vbCrLf & "Please provide a specific word, phrase, or alias.", vbExclamation + vbOKOnly, "ServiceBase Search Results"
        
    tmpDB.Execute "INSERT INTO [Usage Log]([NT ID], loginDate) VALUES(" & Chr(34) & GetNetworkUserName & Chr(34) & ", " & CDbl(Now) & ")"

cmdFind_Click_Exit:
    tmpDB = Nothing
    Exit Sub

cmdFind_Click_Err:
    MsgBox Error$
    Resume cmdFind_Click_Exit
End Sub

Enjoy !!

Contractor
 
F

funwithaccess

Registered User.
Local time
Today, 12:12
Joined
Sep 5, 2013
Messages
80
Contractor,

Now, how do I append the table to include the username via the append query? Same goes for date and time.
 
A

Angelflower

Have a nice day.
Local time
Today, 09:12
Joined
Nov 8, 2006
Messages
51
tmpDB.Execute "INSERT INTO [Usage Log]([NT ID], loginDate) VALUES(" & Chr(34) & GetNetworkUserName & Chr(34) & ", " & CDbl(Now) & ")"

I believe the statement above is what appends the data to the table named [Usage Log]. From there I would just use a normal append or update query to put it somewhere else.
 
F

funwithaccess

Registered User.
Local time
Today, 12:12
Joined
Sep 5, 2013
Messages
80
Angelflower said:
tmpDB.Execute "INSERT INTO [Usage Log]([NT ID], loginDate) VALUES(" & Chr(34) & GetNetworkUserName & Chr(34) & ", " & CDbl(Now) & ")"

I believe the statement above is what appends the data to the table named [Usage Log]. From there I would just use a normal append or update query to put it somewhere else.
Click to expand...

Hi Angelflower,

How would I write the code to do that? The append query that I created is called UL Query. I'm not sure how to write it to tell the query to append the table.

Thank you!
 
F

funwithaccess

Registered User.
Local time
Today, 12:12
Joined
Sep 5, 2013
Messages
80
Hi Contractor,

I created the module, added the code, and now I'm struggling to get the button click to work.

contractor said:
No it sould be like this :

Behind Module :

Code: 
Option Compare Database
Option Explicit
Private Declare PtrSafe Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserName" (ByVal lpBuffer As String, nSize As Long) As Long


Public Function GetNetworkUserName() As String
On Error GoTo ErrorHandler
    'Purpose:   Returns the network login name
    'Return:    The name, or "{Unknown}" on error.
    'Note:      Safer than testing Environ().
    Dim lngLen As Long
    Dim lngX As Long
    Dim strUserName As String
    
    strUserName = String$(254, 0&)
    lngLen = 255&
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0&) Then
        strUserName = Left$(strUserName, lngLen - 1&)
    End If
    
    If strUserName <> vbNullString Then
        GetNetworkUserName = strUserName
    Else
        GetNetworkUserName = "{unknown}"
    End If

Exit_ErrorHandler:
    Exit Function

ErrorHandler:
    MsgBox Err.Number & vbCrLf & Err.Description & vbCrLf & vbCrLf & "GetNetworkUserName()"
    Resume Exit_ErrorHandler
End Function

Code Behind Button :

Code: 
Private Sub cmdFind_Click()
On Error GoTo cmdFind_Click_Err
    
    Combo99.Value = ""
    Combo89.Value = ""
    Combo71.Value = ""
    Combo55.Value = ""
    Combo85.Value = ""
    Combo212.Value = ""
    
    For Each qry In CurrentDb.QueryDefs
        DoCmd.Close acQuery, qry.Name, acSaveYes
    
    Dim intCount As Integer
    intCount = 0
    
    If DCount("Location", "Phone numbers Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "", acViewNormal, acReadOnly
    End If
    
    If DCount("Alias", "SD Documentation Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "SD Documentation Query", acViewNormal, acReadOnly
    End If
    
    If DCount("Searchable Alias", "Info Gathering Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "SD Documentation Query", acViewNormal, acReadOnly
    End If

    If DCount("AssetNumber", "Xerox Assets Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "Xerox Assets Query", acViewNormal, acReadOnly
    End If

    If DCount("[IP Address]", "Xerox IP Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "Xerox IP Query", acViewNormal, acReadOnly
    End If

    If DCount("SerialNumber", "Xerox Serial Query") > 0 Then
        intCount = intCount + 1
        DoCmd.OpenQuery "Xerox Serial Query", acViewNormal, acReadOnly
    End If

    If intCount = 0 Then MsgBox "No results found in ServiceBase." & vbCrLf & "Please provide a specific word, phrase, or alias.", vbExclamation + vbOKOnly, "ServiceBase Search Results"
        
    tmpDB.Execute "INSERT INTO [Usage Log]([NT ID], loginDate) VALUES(" & Chr(34) & GetNetworkUserName & Chr(34) & ", " & CDbl(Now) & ")"

cmdFind_Click_Exit:
    tmpDB = Nothing
    Exit Sub

cmdFind_Click_Err:
    MsgBox Error$
    Resume cmdFind_Click_Exit
End Sub

Enjoy !!

Contractor
Click to expand...
 
C

contractor

Having Fun With Access
Local time
Today, 09:12
Joined
Apr 12, 2012
Messages
47
This code cmdFind_Click()

should be placed behind the button, and not in a module !

contractor
 
F

funwithaccess

Registered User.
Local time
Today, 12:12
Joined
Sep 5, 2013
Messages
80
That's how I have it. The cmdFind_Click() is placed in the Form code while the function GetNetworkUserName is located in a module.
 
A

Angelflower

Have a nice day.
Local time
Today, 09:12
Joined
Nov 8, 2006
Messages
51
funwithaccess said:
Hi Angelflower,

How would I write the code to do that? The append query that I created is called UL Query. I'm not sure how to write it to tell the query to append the table.

Thank you!
Click to expand...


' Append records from [Usage Log] table to SomeTableName
DoCmd.RunSQL "INSERT INTO SomeTableName (list out all the other fields for the SomeTableName, [NT ID], loginDate) " & _
"SELECT [UL Query].list out all the other fields for the UL Query, [UL Query].[NT ID], [UL Query].loginDate;"
 
C

contractor

Having Fun With Access
Local time
Today, 09:12
Joined
Apr 12, 2012
Messages
47
Check via form design view if the button code is on click event.

see attachment

contractor
 

Attachments

  • events1.gif
    events1.gif
    7.8 KB · Views: 78
F

funwithaccess

Registered User.
Local time
Today, 12:12
Joined
Sep 5, 2013
Messages
80
Angelflower said:
' Append records from [Usage Log] table to SomeTableName
DoCmd.RunSQL "INSERT INTO SomeTableName (list out all the other fields for the SomeTableName, [NT ID], loginDate) " & _
"SELECT [UL Query].list out all the other fields for the UL Query, [UL Query].[NT ID], [UL Query].loginDate;"
Click to expand...

This is definitely progress. After the button is clicked, the search for the queries are ran, and a box comes up asking to add a record and then a blank record is added to the Usage Log table. I get 3 Enter Parameter Value boxes that ask for UL Query.NTID, UL Query.loginDate, UL Query.loginTime. I need to set the parameters of the append UL query. What do I set the append query to in Design View to correct this issue?
 
A

Angelflower

Have a nice day.
Local time
Today, 09:12
Joined
Nov 8, 2006
Messages
51
You need to have an actual table in your database called [Usage Log] that has the fields named [NT ID] and LoginDate. The fields also much exists in the destination table that I am calling SomeTableName. The UL Query (a regular query with the Select statement, a from statement and a where statement if needed) will need a join to the [Usage Log] table so the fields named [NT ID] and LoginDate can be added to the query. Hope that makes sense. I forgot to add the from statement to the below code line. So sorry.

' Append records from [Usage Log] table to SomeTableName
DoCmd.RunSQL "INSERT INTO SomeTableName (list out all the other fields for the SomeTableName, [NT ID], loginDate) " & _
"SELECT [UL Query].list out all the other fields for the UL Query, [Usage Log].[NT ID], [Usage Log].loginDate FROM [UL Query];"

My example is not perfect because I don't know what you are using for [UL Query] but I just wanted to give you a general idea of what you need to be doing... Do some searches on the RunSQL command.
 
F

funwithaccess

Registered User.
Local time
Today, 12:12
Joined
Sep 5, 2013
Messages
80
Angelflower said:
You need to have an actual table in your database called [Usage Log] that has the fields named [NT ID] and LoginDate. The fields also much exists in the destination table that I am calling SomeTableName. The UL Query (a regular query with the Select statement, a from statement and a where statement if needed) will need a join to the [Usage Log] table so the fields named [NT ID] and LoginDate can be added to the query. Hope that makes sense. I forgot to add the from statement to the below code line. So sorry.

' Append records from [Usage Log] table to SomeTableName
DoCmd.RunSQL "INSERT INTO SomeTableName (list out all the other fields for the SomeTableName, [NT ID], loginDate) " & _
"SELECT [UL Query].list out all the other fields for the UL Query, [Usage Log].[NT ID], [Usage Log].loginDate FROM [UL Query];"

My example is not perfect because I don't know what you are using for [UL Query] but I just wanted to give you a general idea of what you need to be doing... Do some searches on the RunSQL command.
Click to expand...

I have the table Usage Log that includes NTID (I took the space out), loginDate, loginTime. The UL Query then references these fields. I have attached an image of the Design View of the the append query, UL Query. Do I need to change anything here?
 
A

Angelflower

Have a nice day.
Local time
Today, 09:12
Joined
Nov 8, 2006
Messages
51
Modify your statement to say:


' Append records from [Usage Log] table to SomeTableName
DoCmd.RunSQL "INSERT INTO SomeTableName ( NTID, loginDate, loginTime) " & _
"SELECT [Usage Log].NTID, [Usage Log].loginDate, [Usage Log].loginTime FROM [Usage Log];"
 
MSAccessRookie

MSAccessRookie

AWF VIP
Local time
Today, 12:12
Joined
May 2, 2008
Messages
3,428
Is the VALUES section of the INSERT Query correct? It looks like you are trying to store a DOUBLE in a Date Field and I am not sure what the result would be.


tmpDB.Execute "INSERT INTO [Usage Log]([NT ID], loginDate) VALUES(" & Chr(34) & getWinUser() & Chr(34) & ", " & CDbl(Now) & ")"
 
Cronk

Cronk

Registered User.
Local time
Tomorrow, 02:12
Joined
Jul 4, 2013
Messages
2,772
Gee, this has been a labored thread.

Contractor, be aware that the code you supplied last neither declared tmpDB, nor set it as a database object.

Rookie, dates are stored as double precision numbers but displayed in date format with the integer part representing the number of days since 30/12/1899 and the fraction being the part of 24 hours eg 0.25 is 6:00:00[FONT=&quot][/FONT]
 
You must log in or register to reply here.

Similar threads

T
Solved nagivation control. where clause = new record
2
Replies
25
Views
437
theDBguy
theDBguy
J
Followhyperlink alternate
Replies
8
Views
249
John Sh
J
R
Win32: hWnd for Continuous Forms Detail Section(s)?
Replies
9
Views
440
riktek
R
G
New vs old buttons on a form
Replies
2
Views
237
Pat Hartman
P
M
Hide default Add New Record Button ONLY - How?
2 3
Replies
47
Views
1,092
Marshall Brooks
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom