Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272

    How to make access database capture username of user against any entry made

    I have a database with two tables

    First table which is the login table with table name tbl_login with fields:

    FirstName, LastName, UserName, Password, access_level



    the 2nd table which records my data with table name INCOME_TABLE with fields
    DATE, TYPE OF INCOME/EXPENSE, REVENUE


    i will like to create a new field in INCOME_TABLE with the name USERNAME which will capture the username of the logged inn user against any entry made in INCOME_TABLE.

    Thee are the codes i used for the login



    #
    Dim FIRST_NAME As Variant, access_level As Variant

    If Trim(Me.txt_username.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Username should not be left blank.", buttons:=vbExclamation, title:="SOFTWARE"
    Me.txt_username.SetFocus
    Exit Sub
    End If

    If Trim(Me.txt_password.Value & vbNullString) = vbNullString Then
    MsgBox prompt:="Password should not be left blank.", buttons:=vbExclamation, title:="SOFTWARE"
    Me.txt_password.SetFocus
    Exit Sub
    End If

    ' RETREIVE FROM SAVED QUERY
    ' ASSUMES EVERY USER GIVEN A NON-NULL ACCESS LEVEL
    FIRST_NAME = DLookup("FirstName", "access1")
    access_level = DLookup("access_level", "access1")

    If IsNull(FIRST_NAME) = True Then
    MsgBox prompt:="Incorrect username/password. Try again.", buttons:=vbCritical, title:="SOFTWARE"
    Me.txt_username.SetFocus
    Else
    MsgBox prompt:="Welcome, " & FIRST_NAME & ".", buttons:=vbOKOnly, title:="SOFTWARE"

    ' CONDITIONALLY OPEN FORMS
    Select Case access_level
    Case "Administrator"
    DoCmd.OpenForm "SYSTEM"
    DoCmd.Close acForm, "frm_login", acSaveYes

    Case "Accounts"
    DoCmd.OpenForm "Accounts"
    DoCmd.Close acForm, "frm_login", acSaveYes

    Case "Secretary"
    DoCmd.OpenForm "Secretary"
    DoCmd.Close acForm, "frm_login", acSaveYes
    End Select
    End If




    End Sub
    #




    Any help with this will be greatly appreciated

  2. #2
    Hpool2018 is offline Novice
    Windows 7 64bit Access 2013 32bit
    Join Date
    Apr 2019
    Posts
    10

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    'for userID
    txtBox = Environ("Username")

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Do you just want to retrieve the user name from the login form.
    If so, save it to a variable strUserName then retrieve it using a user defined function GetUserName.
    See my example app Password Login - Mendip Data Systems

    Otherwise, there are three approaches for getting the user name from Windows login: Environ, WScript and using Windows APIs.
    See Get User Name - Mendip Data Systems for details of each method.
    My preference is using WScript
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    Quote Originally Posted by isladogs View Post
    Do you just want to retrieve the user name from the login form.
    If so, save it to a variable strUserName then retrieve it using a user defined function GetUserName.
    See my example app Password Login - Mendip Data Systems

    Otherwise, there are three approaches for getting the user name from Windows login: Environ, WScript and using Windows APIs.
    See Get User Name - Mendip Data Systems for details of each method.
    My preference is using WScript

    Can you demonstrate it properly using my data here for easy understanding because I don’t seem to get this one

  6. #6
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    Quote Originally Posted by isladogs View Post
    Do you just want to retrieve the user name from the login form.
    If so, save it to a variable strUserName then retrieve it using a user defined function GetUserName.
    See my example app Password Login - Mendip Data Systems

    Otherwise, there are three approaches for getting the user name from Windows login: Environ, WScript and using Windows APIs.
    See Get User Name - Mendip Data Systems for details of each method.
    My preference is using WScript

    I had some help from you from one of my old posts where I wanted the username to display on the form.


    https://www.accessforums.net/showthr...t=83595&page=3




    Is there a way for this same username to appear against each record?

    Thus, once I save the record, logged in username should automatically be attached.

    The form will not be the same form that the username displays on but is on a different form

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I put mine in a TempVar and used that for my DBs
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    How can that be done ?

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Emmanuel View Post
    I had some help from you from one of my old posts where I wanted the username to display on the form
    https://www.accessforums.net/showthr...t=83595&page=3

    Is there a way for this same username to appear against each record?
    Thus, once I save the record, logged in username should automatically be attached.

    The form will not be the same form that the username displays on but is on a different form
    Once the user name is saved as a global variable or tempvar, it remains available throughout the database.
    So use the same approach I demonstrated before, but this time insert the same username whenever that user adds or edits a record on another form.
    You will need to populate a username field in the table(s) used as recordsource for your form(s)
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    Quote Originally Posted by isladogs View Post
    Once the user name is saved as a global variable or tempvar, it remains available throughout the database.
    So use the same approach I demonstrated before, but this time insert the same username whenever that user adds or edits a record on another form.
    You will need to populate a username field in the table(s) used as recordsource for your form(s)
    Can you please demonstrate
    Sorry but am not so good with this.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Need code in some event behind the form, possibly BeforeUpdate. Simply set value of field.

    Me!User = variable name
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I think the O/P wants it the other way around?

    TempVars("UserName") = Me.txt_username

    then when you create/save a record

    Me.CreatedBy = TempVars("UserName")
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That's what I meant. I just didn't pick a good field name.

    Me!whateverField = whateverVariable
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Emmanuel is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2020
    Posts
    272
    Quote Originally Posted by Welshgasman View Post
    I think the O/P wants it the other way around?

    TempVars("UserName") = Me.txt_username

    then when you create/save a record

    Me.CreatedBy = TempVars("UserName")
    And how will this code save the username in the table?
    How will I do that?

  15. #15
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by Emmanuel View Post
    And how will this code save the username in the table?
    How will I do that?
    Well first you need an appropriate field to store the user name or ID. I would use ID, up to you.
    Then in in your form, you update the relevant field.

    As mentioned you could just use the Environ as I did below
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
        Me.CreatedDate = Now()
        Me.CreatedBy = Environ("username")
    Else
        Me.AmendedDate = Now()
        Me.AmendedBy = Environ("username")
    End If
    End Sub
    You use whatever you prefer.
    Where I was using this code, no one would even know or want to spoof the username, so adequate for our purposes.

    Regardless, the basics are you store the value you want and just set a field in the table/query.?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Made a database single user?
    By markcrobinson in forum Access
    Replies: 3
    Last Post: 06-18-2019, 07:03 PM
  2. Creating a database for end user data entry Access 2003
    By ethan.geerdes in forum Database Design
    Replies: 2
    Last Post: 02-19-2016, 08:23 AM
  3. Replies: 6
    Last Post: 12-08-2014, 08:05 PM
  4. how to Capture Individual User who made entries in form
    By bronson_mech in forum Programming
    Replies: 2
    Last Post: 08-19-2013, 10:07 AM
  5. Replies: 3
    Last Post: 07-23-2012, 11:46 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums