Help with login Code

[a2k4]

Hi all
I recently followed a tutioral to create a login form for my first database, which works a treat but I'd like it to perform an extra task. when a user logs on they have select their name from a drop down box then enter their password. What I would like it to do is when a user selects their name it copy's the name chosen and puts it into a table. I would then use this table to auto fill parts of various forms to save the user time.

At the moment I have one table called "tblEmployees" which consists of lngEmpID, strEmpName, strEmpPassword and strAccess.

here's the code which baffles me

Private Sub cmdLogin_Click()

'Check to see if data is entered into the UserName combo box

If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.cboEmployee.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If

'Check value of password in tblEmployees to see if this
'matches value chosen in combo box

If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", _
"[lngEmpID]=" & Me.cboEmployee.Value) Then

lngMyEmpID = Me.cboEmployee.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "frmSplash_Screen"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.txtPassword.SetFocus
End If

'If User Enters incorrect password 3 times database will shutdown

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database.Please contact admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If

End Sub

Can I place an extra line or two of code in here?

any help woul be much appreciated
Andy

 

[TylerTand]

You can add this code right before the last End Sub.
docmd.setwarnings false
docmd.runsql "INSERT INTO YourTable ( [strEmpName] ) SELECT [tblEmployees].[strEmpName] FROM [tblEmployees] WHERE ((([tblEmployees].[strEmpName])='" & Me![strEmpName] & "'));
docmd.setwarnings false

YourTable = The name of the Other Table where you wan to store the employees Name.

This is basically an append query that adds the Name in the combo box to a Table named YourTable. If the table doesn't exist, make sure you create it with a field named: strEmpName
Otherwise you will get an error because it won't be able to find the Table "YourTable".

Let me know if this helps.


For the future:
If you want to do either a make table query or an append query by pushing a button and it needs to reference a field on your form you can just make the query in the regular query builder and put the name of the field to reference in the criteria. Then go to the SQL view and copy the entire code. Go to your VBA on click of the button and put the code after docmd.run SQL "your code here" To reference the field on a form like with the employees name in the criteria just drop out of the string. TO do this you have to close off the double quotes and use & before and after the field name.

Ex. .....WHERE ((([tblEmployees].[strEmpName])='" & Me![strEmpName] & "'));

If the field is a text field you have to start with a single quote and then a double quote, then a space and then an ampersand, a space and Me![Field Name], then a space Ampersand Space Double and then single quotes. THe me! means that the field is on the current form. IF you need to reference a field on another form that is open you can do so by using: [forms]![Form Name Here]![Field Name Here]. Put this between the ampersands with spaces before and after and it will reference the other form and the corresponding field.

If it is a number field you only need double quotes and ampersands around the me![field name] or [forms]![Form Name Here]![Field Name Here].

Someone took the time to teach me and I am happy to pass on what I have learned. Let me know if I can help further

Tyler

 

[a2k4]

Thanks Tyler for the fast reply.
I created a Table called tblUser with a field strEmpName and pasted your code where you said. I'm now getting a run time error "The expression you entered refers to an object that is closed or doesn't exsist." If its any help here's where I got the tutorial http://www.databasedev.co.uk/login.html (Mods delete if links are not allowed)

thanks Andy

 

[TylerTand]

I guess there are two questions. 1.)Is the form that has the login name closed before when you run the sql code? If so then it can't reference the field if the form is closed. To solve this make sure that if you use a docmd.close command put it after you run the append query. I always try and make it a practice to open the new form before I close the old one. This way you never have to deal with seeing the database window if the new form has queries that take a minute or two. 2.) Are you saying that you created the table named tblUser before you ever ran the code? Make sure that you spelled the table name and field exactly the same or it won't be able to find it. Second, did you create the append query using the normal wizard first to make sure that the syntax was correct or did you try and just write the code in the VBA module? I suggest that untill you get some experience with exactly how the syntax is for the code that you should use the wizard first and then convert it to the VBA to reference the form field. If you ran the query and it worked properly using the wizard than you know that it will work in the VBA. Which line was highlighted when you got the runtime error? This will give me a better idea of which part of the code is giving you the problem. Let me know and I'll see what I can do,
Tyler

 

[rolaaus]

My recommendation would be when the user validates their log-in (username and password) and clicks the "log-in" button or whatever it is called, just hide the log-in form, and you'll be able to refer to that control without worrying about calling up a database reference or anything like that. It would be quick and simple to write Forms!LogInForm!UserID whenever you want their name.

Another way is to just declar a global variable and fill that during the log-in process.

 

[a2k4]

I got it to work, sort of. The form was looking directly at the table so I changed it to look at a query based on the table. I also moved your lines of code Tyler to the After Update event of the name field.
Now for the new problem when I select a user name from the drop down list, say Andy, and tab down to the password field. When checking the table tblUser the name Admin is always in the strEmpName, which is the first name in the table of users.
I've put this down to using a combo box, am I right?
Is there a code for copying the record selected to a new table? I've tried setting the Control Soure to my tblUser but it doesnt work
Also do you recommend any books on VBA as its something I'd like to learn or at least stop asking simple questions

Thanks
Andy