How to grab the username from UI interface and put it in one Database Table (1 Viewer)

andreylungu

New member
Local time
Today, 13:46
Joined
Jul 15, 2015
Messages
4
Hello,

I created an Access Web App for tasks management in which i also have a Tasks Audit Table. When a task from the Tasks table is modified, i created an ON UPTADE macro which creates a new row in the Tasks Audit table with the "Task ID", "Old Status" and "New Status", "Old Deadline" and "New Deadline". (usind the .old function)

Because all users can modify the tasks, it is necessary to have also a column with "Modified by" where to put the username who made the modification.

I tried 3 days to find a solution for this issue but i cannot find a way how to grab the username from UI (where I have it with the userdisplayname() function) and to put it in the Tasks Audit Table. It is very frustrating, because i have it there, but only can't write it :(

I attached a printscreen with the On Update macro from Tasks table.

Does anyone have a solution for this ? I also searched on google but didn't find someting to solve it..

Thanks a lot !
 

Attachments

  • tasks on update macro.jpg
    tasks on update macro.jpg
    46.5 KB · Views: 165

Ranman256

Well-known member
Local time
Today, 08:46
Joined
Apr 9, 2015
Messages
4,337
you should be using queries to add records.
try : Environ("Username")

vUser = Environ("Username")
sSql = "insert into tLog ([action],[userID] values('saved','" & vUser & "')
 

andreylungu

New member
Local time
Today, 13:46
Joined
Jul 15, 2015
Messages
4
Hello and thanks a lot for your answer !

I know the Environ function from Excel and it is a great function, but it seems that in Access 2013 Web Appplications it doesn't exist :(

Could you please explain more detailed how to apply your ideea into the Accesss Web App if possible ?

If i will have no other solution, maybe i will try to find someting like the "On Update" Event in SQL Server and create there the query where will insert the value into the table with the log. But don't know how to do this either right now (to set the query run every time a modification is done in the Tasks table)

Thanks !
 
Last edited:

Ranman256

Well-known member
Local time
Today, 08:46
Joined
Apr 9, 2015
Messages
4,337
here is code to pull userID via API. Try this to see if it works with web apps.
paste into a module, usage: vUser = getUserID()

Code:
Option Compare Database

Public Const C_STARTCOUNT As Integer = 1001
Public Const C_MIN_LIST_HEIGHT As Integer = 3675
Public Const C_MAX_LIST_HEIGHT As Integer = 4210

Public Const MAX_PATH = 260
Public Const CSIDL_FLAG_MASK = &HFF00                    'mask for all possible flag values
Public Const SHGFP_TYPE_CURRENT = &H0                    'current value for user, verify it exists
Public Const SHGFP_TYPE_DEFAULT = &H1
Public Const S_OK = 0
Public Const S_FALSE = 1
Public Const E_INVALIDARG = &H80070057                   ' Invalid CSIDL Value

'Use the GetUserName API function.
Public Declare Function SHGetFolderPath Lib "shfolder" Alias "SHGetFolderPathA" (ByVal hwndOwner As Long, ByVal nFolder As Long, ByVal hToken As Long, ByVal dwFlags As Long, ByVal pszPath As String) As Long
Public Declare Function WNetGetUserA Lib "mpr.dll" (ByVal lpName As String, ByVal lpUserName As String, lpnLength As Long) As Long
Private Declare Function apiGetComputerName Lib "kernel32" Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function getUserName()
 getUserName = CurrentUser()
End Function
  


' Return the user's name.
'-------------
Public Function getUserID() As String
'-------------
Dim sBuffer As String * 255
Dim sName As String

'Environ("Username")
sBuffer = Space(255)
Call WNetGetUserA(vbNullString, sBuffer, 255&)

sName = Left$(sBuffer, InStr(sBuffer, vbNullChar) - 1)
If Len(sName) Then
    getUserID = LCase$(sName)
Else
    getUserID = "<Unknown>"
End If
End Function


Public Function GetUserLastFirst()
Dim vName, vUserName, vUserDomain
Dim i As Integer

    Set WSHnet = CreateObject("WScript.Network")
    vUserName = WSHnet.UserName
    vUserDomain = WSHnet.UserDomain
    Set objUser = GetObject("WinNT://" & vUserDomain & "/" & vUserName & ",user")
    
    i = InStr(objUser.FullName, "(")
    If i = 0 Then
       vName = objUser.FullName
    Else
       vName = Left(objUser.FullName, i - 1)
    End If
    
    GetUserLastFirst = vName
End Function

Public Function WindowsLogin(ByVal strUserName As String, ByVal strpassword As String, ByVal strDomain As String) As Boolean
        'Authenticates user and password entered with Active Directory.

        On Error GoTo IncorrectPassword
        
        Dim oADsObject, oADsNamespace As Object
        Dim strADsPath As String
        
        strADsPath = "WinNT://" & strDomain
        Set oADsObject = GetObject(strADsPath)
        Set oADsNamespace = GetObject("WinNT:")
        Set oADsObject = oADsNamespace.OpenDSObject(strADsPath, strDomain & "\" & strUserName, strpassword, 0)
        
        WindowsLogin = True    'ACCESS GRANTED
        
ExitSub:
        Exit Function
        
IncorrectPassword:
        WindowsLogin = False   'ACCESS DENIED
        Resume ExitSub
End Function



'use for auto login of users
Public Function AddNewUser()
Dim sSql As String
Dim vUserID, vName

vUserID = getUserID()
vName = DLookup("[Name]", "tUsers", "[UserID]='" & vUserID & "'")
If IsNull(vName) Then vName = InputBox("Enter Your Name", "New User Login")

If vName <> "" Then
  sSql = "INSERT INTO tUsers ( UserID, Name ) values ('" & vUserID & "','" & vName & "')"
  Run1Qry sSql, True
End If
End Function


Function getPCName() 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
        getPCName = Left$(strCompName, lngLen)
    Else
        getPCName = ""
    End If
End Function
 

andreylungu

New member
Local time
Today, 13:46
Joined
Jul 15, 2015
Messages
4
Hi again !

In Access Web Apps i don't think it's possible to use VBA.

The only option i found and i think it wil work is Shiva's solution with an UPDATE Trigger in SQL Server from the following link

The problem is that it needs to be done in SQL Server and i would prefer to do this in Access Web Apps. It's really not possible?

Thanks !
 

JulianKirkness

Registered User.
Local time
Today, 12:46
Joined
Apr 15, 2015
Messages
14
Hi

As you know, you can't use either VBA or write SQL code in a Web App - so...

The way I do this kind of thing is to write a value (UserDisplayName()) into a hidden field on the View (form for desktop users). This can be done with a macro or you could simply use the default value property (=UserDisplayName()). You can store this in the main Tasks table and then write it from Tasks to Audit in the On Update Macro / Trigger.

This should work - I actually use a Users table and hold a CurrentUserID variable in my app so I can have a drop down with user names etc - but the principle is the same storing UserEmailAddress() directly. I use this for internal app security and so on.

Hope this helps.

Julian
 

Users who are viewing this thread

Top Bottom