Login Form Error

[poohbear2012]

I have designed a user login form in Access 2010 which enables users to enter a password to login into the database. I wish the form to close and go to the default Home page form.

I have written the following VBA code under the button to undertake the "OnClick" function of the Login button task but it still is not working. The Home page loads but the login form does not close. Am not sure what is wrong as have used the DoCmd.Close and open function.

Private Sub cmdLogin_Click()

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

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

If Me.Password.Value = DLookup("Password", "Employees", _
"[ID]=" & Me.cboEmployee.Value) Then

ID = Me.cboEmployee.Value

DoCmd.Close acForm, "Logon Dialog", acSaveNo
DoCmd.OpenForm "Home", acNormal

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

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

 

[carl6885]

I have tried to recreate the error with no success as my forms are closing using the same code.

I thought it might be the space in the name of the form but it isn't. Might seem simple but have you spelt it correctly?

Could you attach a copy of your DB for us to test it on our machines to help?

Thanks

Carl

 

[poohbear2012]

Dear Carl

Thanks for your reply, I did end up identifying an error in the spelling of the form name. Changed the form name and it worked perfectly.

But now I have encountered some other issues on the Home form I wish a box to contain the name of the current logged in user, I currently have this set up unsuccessfully with a SetTempVar macro but think VBA may be a better option but am unsure of the best way to achieve it.


Regards
Trisha

 

[carl6885]

Hi Trisha

A good way, or at least the way i return the logged in user in a corporate envirnoment is a function:

Public Function ReturnUser()

ReturnUser = Environ("Username")

End Function

Then call the function:

me.lblUserName.Caption = ReturnUser

If this has the undersirable effect you could, on a successful login pass the value of your cboEmployeeValue through to a global variable on Main Form or via the OpenArgs property of the open form command.

Does that make sense? I could provide an example if needed.

Thanks

Carl

 

[poohbear2012]

Hi Carl

I am only just beginning to undertake VBA coding so am still not 100% sure of all of the terminology. Are you able to provide an example.

Many thanks
Trisha

 

[carl6885]

No problem.

What version of access are you using?

Thanks

Carl.

 

[poohbear2012]

Access 2010

 

[carl6885]

Even easier.

I have attached an example of how I would do it.

This is by no means exhaustive as in practice you would want to add password masking, deny access after failed attempts etc but that is beyond what you was asking form.

You can try the following logins:

Username: Carl Law Password: Tuesday123
Username: Trisha Password: Monday123

If you need further assistance on user login let me know I will be happy to help.

P.S little bit of advice based on the above, ensure you follow naming conventions when programming to make it easier to refer to objects but also if others take over the development for example:

frmForm1, qryQuery1, txtTextbox1, cboCombobox1.

Apologies if you knew this I am basing on the above named forms.

Thanks

Carl

 

[poohbear2012]

Hi Carl

Thank you for this example it has been most useful.

But I have encountered an error (copy attached) with the relevant line.

Regards
Trisha

 

[carl6885]

Hi

No problem.

That errors is normally to do with the sql statement. Is it typed correctly?

 

[poohbear2012]

Hi

I have corrected the sql error, but when I go to login into the database it keeps telling me that the password is incorrect despite being correct. Not sure where to go next.

 

[carl6885]

Hi

If you attach a copy of the db I'll have a look for you.

 

[poohbear2012]

Hi Carl

Copy of the affected forms etc attached.

Cheers
Trish

 

[carl6885]

Hi Trisha

The issue is in your SQL and the Combo box.

The combo box is returning the primary key as the value (you can change this using .text) yet you are asking the sql statement to look for a LoginName that equal 1 or 2.

You can change the sql where clause to WHERE ID = combo box.....

Does that make sense?

 

[carl6885]

PS dont forget those naming convention I talked about specifically on those controls in the forms.... your combo box is named txtUsername when it would be better calling it cboUsername