Having trouble visualizing how to create a login

[seth.murphine]

I have a table created for users, their passwords and a user level such as read/write, Modify and Administrator.



I know its possible just would like to learn how to implement this into a login or if someone could direct me to a good learning source for a basic login. I'm sure its going to come down to me doing tempvars to give me the ability to have the username follow into other forms so that when the user is adding information to a form they don't have to put in their name every time in order for their name to be on that record.

It would be even better if there was someone in the charlotte, nc area so we could jam on this together, but any help I can get will be great.

I have attached my db for anyone that's interested in pitching in a helping hand.database.zip

Thank you,
Seth

[June7]

What I do is populate a textbox on a form that always remains open (main menu) with the user ID. Then the ID is always available for reference by query parameter or VBA code.

[seth.murphine]

What I do is populate a textbox on a form that always remains open (main menu) with the user ID. Then the ID is always available for reference by query parameter or VBA code.

That would definitely relieve me from doing tempvars within the code, but I think my question revolves more around the actual coding whether in expression builder or VBA. I get how to do statements in my own language, for example:

"get sum of adEmailChk from yp_adv_data for today for currently logged in user"

Its the part of translating from my language to VBA or expression builder that I'm missing. Which I surmise shows my lack of education too

[edit] I appreciate you taking the time with me June [/edit]

[June7]

I don't bother with password security. User ID is only used to document user at stages of a process. Login required for each person only the first time they ever open the project. This login creates a record in table with user initials and username from the network. I presume that if user got through the network login then they are authorized users. After this initial login, whenever they open the db their username is retrieved from the network and matched to record in table and their initials are populated to a textbox on the Main Menu form. The db is split and users run copy of frontend from their computer. Network security controls which individuals have access to the backend folder. The Login form code:

Private Sub Form_Load()
Dim Shell
If Me.tbxVersion <> Me.lblVersion.Caption Then
    If VBA.Environ("UserName") <> "hjf" Then
        'Because administrator opens the master development copy, only run this for non-administrator users
        'Check for updates to the program on start up
        'If values don't match then there is a later version
        Set Shell = CreateObject("WScript.Shell")
        Shell.Run CurrentProject.Path & "\Update.vbs"
        Dim WAIT As Double
        WAIT = Timer
        While Timer < WAIT + 3
            DoEvents
        Wend
        Set Shell = Nothing
        Application.Quit
    End If
Else
    Me.tbxVersion.Visible = False
    UserLogin
End If
End Sub

Private Sub tbxUser_AfterUpdate()
If Me.tbxUser Like "[A-Z][A-Z][A-Z]" Or Me.tbxUser Like "[A-Z][A-Z]" Then
    CurrentDb.Execute "INSERT INTO Users(UserNetworkID, UserInitials) VALUES('" & VBA.Environ("UserName") & "', '" & UCase(Me.tbxUser) & "')"
    Call UserLogin
Else
    MsgBox "Not an appropriate entry.", vbApplicationModal, "EntryError"
End If
End Sub

Private Sub UserLogin()
Me.tbxUser = DLookup("UserInitials", "Users", "UserNetworkID='" & Environ("UserName") & "'")
If Not IsNull(Me.tbxUser) Then
    CurrentDb.Execute "UPDATE Users SET ComputerName='" & VBA.Environ("ComputerName") & "' WHERE UserInitials='" & Me.tbxUser & "'"
    DoCmd.OpenForm "Menu", acNormal, , , , acWindowNormal, Me.tbxUser
    DoCmd.Close acForm, Me.Name, acSaveNo
End If
End Sub

Then a textbox on main form can have ControlSource: =[OpenArgs]

Here is another reference http://www.mrexcel.com/forum/showthread.php?t=248191

[seth.murphine]

What about this? I know probably a lot of errors but I'm trying

Option Compare DatabasePrivate intLoginAttempts As Integer


Private Sub Form_Open(Cancel As Integer)
Me.LogInUser.SetFocus
End Sub


Private Sub LogInButton_Click()


    If IsNull(Me.LogInUser) Or Me.LogInUser = "" Then
            MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
            Me.LogInUser.SetFocus
        Exit Sub
    End If
    
    If IsNull(Me.LogInPassword) Or Me.LogInPassword = "" Then
            MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
            Me.LogInPassword.SetFocus
        Exit Sub
    End If
    
    If Me.LogInPassword.Value = DLookup("strPass", "yp_team", "[lngEmpID]=" & Me.LogInUser.Value) Then


        lngMyEmpId = Me.LogInUser.Value
        
    DoCmd.Close acForm, "yp_lgn_dia", acSaveNo
    
    If lngMyEmpId = DLookup("strEmpGrp", "yp_team" = crs) Then
    
    DoCmd.OpenForm "yp_crs"
    
    Else
    If lngMyEmpId = DLookup("strEmpGrp", "yp_team" = ipm) Then
    
    DoCmd.OpenForm "yp_ipm"
    
    Else
    If lngMyEmpId = DLookup("strEmpGrp", "yp_team" = teaml) Then
    
    DoCmd.OpenForm "yp_teaml"
    
    Else
    If lngMyEmpId = DLookup("strEmpGrp", "yp_team" = master) Then
    
    DoCmd.OpenForm "yp_master"
    
    Else
    MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
        Me.LogInPassword.SetFocus
    End If

[June7]

Trying is good. Did you try running? I expect it will error because of missing End If for each If Then. Use ElseIf.

    If Me.LogInPassword.Value = DLookup("strPass", "yp_team", "[lngEmpID]=" & Me.LogInUser.Value) Then
       lngMyEmpId = Me.LogInUser.Value
       DoCmd.OpenForm "yp_crs"  
    ElseIf lngMyEmpId = DLookup("strEmpGrp", "yp_team" = ipm) Then
       DoCmd.OpenForm "yp_ipm"
    ElseIf lngMyEmpId = DLookup("strEmpGrp", "yp_team" = teaml) Then
       DoCmd.OpenForm "yp_teaml"
    ElseIf lngMyEmpId = DLookup("strEmpGrp", "yp_team" = master) Then
       DoCmd.OpenForm "yp_master"
    Else
       MsgBox "Password Invalid.  Please Try Again", vbOKOnly, "Invalid Entry!"
       Me.LogInPassword.SetFocus
    End If

[seth.murphine]

June,

Thanks, ok I'm getting the following error:

"Run-time error '2471':

The expression you entered as a query parameter produced this error:
'[lngEmpID]'"

The following line is where debug takes me:

 If Me.LogInPassword.Value = DLookup("strPass", "yp_team", "[lngEmpID]=" & Me.LogInUser.Value) Then

[seth.murphine]

Haha figured that one out duh my ID was not labeled lngEmpID, but instead was ID... grr

Ok but now I'm getting a same run-time error but instead reads:

The expression you entered as a query parameter produced this error:
'seth'

Which is my username...

[June7]

The DLookup WHERE argument must use a field of table, not a variable. Text field criteria needs apostrophe delimiters.
DLookup("strPass", "yp_team", "username field='" & Me.LogInUser.Value & "'")

If lngEmpID is a field why would you set it to the LogInUser.Value? If it is a variable, how can you even refer to it before it is set? If lngEmpID is a number then cannot compare to string in LoginInUser textbox.

[seth.murphine]

Ok looks like it is grabbing the lngEmpID # instead of the what is in the strEmp column on the table... so when I drop down the drop down box it is only showing '1'.

[June7]

The combobox is set up as multi-column? Is the RowSource like: SELECT lngEmpID, strEMP FROM tablename;?

Set the BoundColumn property to the column with strEmp field - probably column 2. Set width of the lngEmpID column to 0, like 0";1.5".

I assume the combobox does not have ControlSource.

[seth.murphine]

June,

Thank you for being so patient with me, I know it must seem like I must be a child and when it comes to this game I am.

You are right I have "SELECT [yp_team].[lngEmpID], [yp_team].[strEmp] FROM yp_team;"

I have changed bound column from 1 to 2, but you lost me with where I would change the width for lngEmpID column.

And again you are correct with there not being a ControlSource for the combobox.

[seth.murphine]

on changing bound column from 1 to 2, it is back to giving me the error "The expression you entered as a query parameter produced this error: 'seth'"

[June7]

Combobox ColumnWidths property is on the Format tab.

Show the sql of the query that is failing. Or attach project for analysis.

[seth.murphine]

Ok so I changed the Row Source from "SELECT [yp_team].[lngEmpID], [yp_team].[strEmp] FROM yp_team;" to:

SELECT [yp_team].[strEmp], [yp_team].[lngEmpID] FROM yp_team;

Then I changed bound column to 2, works like a charm. Shows the names now and doesn't produce any errors.

Now I have to figure out how to get the ElseIf's working. I have taken them out because while they were in the window would close, but nothing else would happen.