Full Version: Login Form
UtterAccess Forums > Microsoft® Access > Access Forms
MrFormula
I am creating a login form for my db I have the form created "frmLogin". I want it to take the number that is entered in the login text box and do a dlookup from users table. Find the coresponding values in the ID field and the UserID field an put them in the Cur_User table. I will of course add the code to the "Enter" button I have on the frmLogin.
guess my vb skills just aren't there yet.
Tables.
Users
ID AutoNumber
Userid Text
Password Text
Cur_User table only has one record in it. The Key Value is set to 1 and will never change.
Cur_User
Key Number
User Text
RepID Number
Thanks for the help... again
Alan_G
Hi
See if this Code Archive demo is of any use to you
CyberCow
There's a demo I favor in the Code Archive called db Login that may help give you some VBA tips.
Hope this helps
MrFormula
I have downloaded both of these. I will look and see if I can find something I can adapt. My main problem is that I need to insert that data into the other table.
Thanks for the suggestions.
Alan_G
Hi
nce you have the User and RepID data (from the log in form), a simple UPDATE query will update the one record in your table
CODE
Currentdb.Execute "UPDATE Cur_User SET User = '" & Replace(UserName,"'","''") & "', Repid = " & RepIDData, dbFailOnError
CODE
RepID = DLookup("id", "users", "password = '" & Me.Login & "'")
UserID = DLookup("userid", "users", "password = '" & Me.Login & "'")

The same would apply to the UPDATE query SQL
CODE
Currentdb.Execute "UPDATE Cur_User SET User = '" & UserID & "', RepID = '" & RepID & "'", dbFailOnError

If you're using a combobox to select the user name though, there should be no need for any DLookup() - you could include the password and RepID as hidden columns in the combo and get the values using the .Column property <
MrFormula
Wow I am just trying to figure out what the world would do with out Alan. Everyone here has helped me so much. Thanks for taking the time to help. Your code worked perfectly (Of course)
Thanks again.
Alan_G
Hi
ou're very welcome and glad you got it working. That's what UA's here for EMO_DIR#>/thumbup.gif" style="vertical-align:middle" emoid=":thumbup:" border="0" alt="thumbup.gif" />
MrFormula
Do you have any suggestions to speed it up. When I put in my number and click the enter button where the code is attached it takes forever (3-5 sec) to close the form.
!--c1-->
CODE
On Error GoTo Err_NoUser_Click
    Dim stDocName As String
    stDocName = "Main Page"
txtLogin = CStr(Login)
NumLogin = Val(Login)
RepID = DLookup("id", "users", "password = '" & Me.Login & "'")
UserID = DLookup("userid", "users", "password = '" & Me.Login & "'")
CurrentDb.Execute "UPDATE Cur_User SET User = '" & UserID & "', RepID = '" & RepID & "'", dbFailOnError
If Me.Dirty = True Then Me.Dirty = False
'Forms!stDocName.Requery
Msg = "No user activity detected in the last "
DoCmd.Close
Exit_Err_NoUser_Click:
    Exit Sub
Err_NoUser_Click:
    MsgBox "User Not Found Please Try again"
    Resume Exit_Err_NoUser_Click
MrFormula
I am still working on my login form.
need to find a way to speed it up. It takes 8 seconds to log in after I click the enter key. We need to find a faster way to write the code to Cur_User.
If this helps There is only one record in the Cur_User Table. I just keep track of who is currently logged in. There is no history or anything like that.
I would appreciate any help.
Thanks in advance
CODE
Private Sub Enter_Click()
On Error GoTo Err_NoUser_Click
    Dim stDocName As String
    stDocName = "Main Page"
RepID = DLookup("id", "users", "password = '" & Me.Login & "'")
UserID = DLookup("userid", "users", "password = '" & Me.Login & "'")
'I think this is the problem with the time delay
CurrentDb.Execute "UPDATE Cur_User SET User = '" & UserID & "', RepID = '" & RepID & "'", dbFailOnError
If Me.Dirty = True Then Me.Dirty = False
'Forms!stDocName.Requery
Msg = "No user activity detected in the last "
DoCmd.Close
Exit_Err_NoUser_Click:
    Exit Sub
Err_NoUser_Click:
    MsgBox "User Not Found Please Try again"
    Resume Exit_Err_NoUser_Click
End Sub
Kenny Holmes
Have you stepped through your code to see which section takes the time?
I have used currentdb.execute to update hundreds of records at a time and it works in a flash.
Alan_G
Hi
Is Kenny says above, I'd be very surprised if that update was taking any length of time - well, obviously it does, but you know what I mean <
I'd definitely go along with Kenny's suggestion of stepping through the code a line at a time to see what's happening, plus there's a couple of things in the code you posted that seem to be superfluous to requirements - eg the declared variables and the Me.Dirty (don't really know why you have that there, as nothing happens after it and below it you're closing the form which will save any data anyway if it's a bound form).
You could 'tidy up' the code a bit to (assuming RepID and UserID are controls on your form) -
CODE
Private Sub Enter_Click()
On Error GoTo Err_NoUser_Click
Me.RepID = DLookup("id", "users", "password = '" & Me.Login & "'")
Me.UserID = DLookup("userid", "users", "password = '" & Me.Login & "'")
CurrentDb.Execute "UPDATE Cur_User SET User = '" & Me.UserID & "', RepID = '" & Me.RepID & "'", dbFailOnError
DoCmd.Close acForm, Me.Name
Exit_Err_NoUser_Click:
    Exit Sub
Err_NoUser_Click:
    MsgBox "User Not Found Please Try again"
    Resume Exit_Err_NoUser_Click
End Sub

Another thing you can check is to make sure that Name Auto Correct is turned off (which is a high on the list suspect for slow closing objects)
Having said all that, you could do away with the DLookup()'s and update code altogether by making the form bound to the Cur_User table, setting the Cycle Records property to Current Record and using a combobox to list all available UserNames. A hidden column in the combo could be used to check they've entered the correct password.
MrFormula
thanks for the responses. I will do some programing later this weekend and See what I can do to implement your responses.
Thanks again.
CyberCow
I'd wager that if you did timing routines against those "DLookup"s, you'd find they may be the slowest component(s) of the code.
'd also wager that using recordset look ups would be faster; more code, but faster. DLookup is just plain slower than a recordset lookup.
hope this helps
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.