Need advice on how to solve user log-in to open tables that's filtered by username

[aflores41]

Hello,

I need some advice/help into solving an issue I have with user log-in. Currently, I have a working log-on screen with password that's linked to a table. Once password is entered correctly, the form opens with a text box naming the username.

Here are my issues:

1. The issue is, once the main form closes, the link between the two gets severed and I get an error (username is not populated in the textbox anymore. Here's the code I use

   [Forms]![frm_tools]![txt_user1].Value = [Forms]![frm_logon]![cmb_user].Value

2. I was going to use this link to filter the data from table based on user log-on. (e.g. log in as A, then the form opens to a datasheet with only data filtered to A.)
   
   The goal is having this database as multiuser so 5 or so user can log-on then opens the same form with prefiltered data based on log-in information.

Thank you in advance!

[Rabbit]

Store the logged in user in a global variable and use a function to return the logged in user to your queries.

On a side note, I would be remiss if I didn't warn you about the security of Access, there's practically none. This storing of users and passwords is something any of your users can easily bypass. I hope that at the very least, you are storing the passwords as a hash and not in plain text. If it's in plain text, you have now potentially leaked everyone's passwords to everyone else. And since most people use the same password for everything, other people may now know the password of your user's sensitive accounts, such as bank accounts. Even if you stored it as a hash, those are easy to break if you don't use a salt.

[aflores41]

Thank you. I had a feeling access doesn't have very good security so I was thinking of just adding dummy passwords. The goal is really to use that user name into filtering the tables opened so that I don't have to make a multiple queries per user. Could you provide examples?

Thanks.

[jforbes]

If all you need is the currently logged in Windows User, you might want to use this code that can be found easily enough on the Internet:

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
 
Public Function GetWindowsUser() As String
On Error GoTo ErrorOut
    Dim sTemp As String
    Dim lCnt As Long
    Dim lDL As Long
    Dim sPos As Single
    Dim sUserName As String
    Dim txtUsername As String
 
    'Get the Windows User Name
    lCnt = 199
    sTemp = String(200, 0)
    lDL = GetUserName(sTemp, lCnt)
    sUserName = Left(sTemp, lCnt) & lCnt
    sPos = InStr(1, sUserName, Chr(0))
    If sPos > 0 Then
       txtUsername = Left(sUserName, sPos - 1)
       GetWindowsUser = txtUsername
    Else
       GetWindowsUser = ""
    End If
ExitOut:
    Exit Function
ErrorOut:
    Msgbox err.Description
    Resume ExitOut
End Function

That way you can skip the login screen.

[aflores41]

Well not use the windows log-in information. I want to use the log-in information from table which is a selection in a combo box.

For example, user logs in selecting username: A123. He then enters password as: pass. Then as he logs in, a table opens filtered to only show data for A123 username.

I'm trying to research global variable and it looks like it's gonna work. I just don't know how to declare or use the tl_users table as the reference for user.

Would tempvar be better than global variable?

Thank you.

[aflores41]

Nevermind. I used tempvars and it works!

Function Log_in()
On Error GoTo Log_in_Err
 
    With CodeContextObject
        If (Not IsNull(.cmb_user)) Then
            TempVars.Add "UserName", .cmb_user
            DoCmd.Close , ""
            DoCmd.OpenForm "frm_tools", acNormal, "", "", , acNormal
        End If
    End With
 
 
Log_in_Exit:
    Exit Function
 
Log_in_Err:
    MsgBox Error$
    Resume Log_in_Exit
 
End Function

Then use this on the following form. Inser the code in the default value of textbox or objects you would like to use.

=[TempVars]![username]