Create a Log In button using VBA

[Aya]

I've created a form named User Accounts which consists of a Log In that
restrict access to unauthorized users. The form has a Username field and a
Password field.

I wanted to create a command button with VBA coding which can help me
restrict the access to those unauthorized users to the data modification or
elimination. The User Accounts form opens and ask the user for their username
and password. The user enters the data and clicks on the Log In button. With
the Log In button, I want VBA to check the values entered in the fields by
the user are in the Users table.

If the values the user entered are founded in the Username and Password
fields, I want VBA to show a MsgBox saying "Welcome to the system", something
like that and opens the Modify form. If the values aren't founded in the
Username and Password fields, I want VBA to show a MsgBox saying "Username
and/or password invalid. We're sorry, you are not authorized to enter to the
system..." and closes the User Accounts form.

I just have the idea of what I want the form to do, but I've no idea of how
I'm going to do it in VBA. Can anyone help me with this. I'll appreciate it.

 

[John Nurick]

If you actually want to protect your data from unauthorised access by
moderately knowledgeable users, this will not work. They will be able to
use any of several techniques to get at the data without encountering
your "Log In" form - or to extract the user names and passwords from the
Users table so as to be able to log in as someone else.

To provide a significant level of security, you need to implement
Access's built-in User Level Security. You need to do this with great
care, because it is easy to make mistakes and quite possible to lock
yourself permanently out of your own database. Study the Security FAQ
thoroughly before you start, plan your steps carefully, and make certain
that you have a backup copy of the database.

The following links should take you to the necessary information:
Security FAQ - includes explanations and step by step instructions
http://support.microsoft.com/?id=207793
or
http://support.microsoft.com/support/access/content/secfaq.asp

Security Whitepaper (old, but explains the concepts)
http://support.microsoft.com/?id=148555

If all you want is a log in form with no real security, just use the VBA
DCount() function, something like this air code in the Log In button's
Click event procedure. It assumes that you have two textboxes on the
form, called txtUserName and txtPassword, and that there are UserName
and Password fields in the Users table.

If DCount("*", "Users", "UserName='" & Me.txtUserName _
& "' AND Password='" & Me.txtPassword & "'") = 0 Then
'Failure
MsgBox "Sorry, you are not authorised..."
Application.Quit
Else
'Success
MsgBox "Welcome"
DoCmd.OpenForm "Modify"
DoCmd.Close acForm, Me.Name
End If

 

[Guest]

I thought of using it with VBA before with a command button. The database is
going to be integrated as a web site using SQL and thought maybe I need a
command button in my log in form to continue to the modification part.

The part about editing and eliminating data, which is in the modification
part, is the part where i want to be secure by creating a log in form. The
viewing part is good since all is Read-Only data.

 

[RD]

I've created a form named User Accounts which consists of a Log In that
restrict access to unauthorized users. The form has a Username field and a
Password field.

I wanted to create a command button with VBA coding which can help me
restrict the access to those unauthorized users to the data modification or
elimination. The User Accounts form opens and ask the user for their username
and password. The user enters the data and clicks on the Log In button. With
the Log In button, I want VBA to check the values entered in the fields by
the user are in the Users table.

If the values the user entered are founded in the Username and Password
fields, I want VBA to show a MsgBox saying "Welcome to the system", something
like that and opens the Modify form. If the values aren't founded in the
Username and Password fields, I want VBA to show a MsgBox saying "Username
and/or password invalid. We're sorry, you are not authorized to enter to the
system..." and closes the User Accounts form.

I just have the idea of what I want the form to do, but I've no idea of how
I'm going to do it in VBA. Can anyone help me with this. I'll appreciate it.

Hi Aya,

One thing about "home grown" security is that it's only effective with users
that are not familiar with Access because it doesn't come into effect until
after your database opens. A better solution would be to use Access own
security. If you search Help for security and choose "Overview of Access
Security" you can find a bunch of info.

That being said I roll my own security all the time (I'm blessed with
non-technical users).

Below is some quick air code, assuming DAO referenced. Should give some ideas.
This goes in the On Click Event of your LogIn button and assumes that Username
and Password are your actual field names. Watch out for word wrap.

Sub LogIn_Click()
On Error Go To ErrorHandler
Dim db As DAO.Database, rs As DAO.Recordset
Dim strUserName As String, strPassword As String
Dim strSql As String

strUserName = Me.Username
strPassword = Me.Password

strSql = "SELECT UserTable.Username, UserTable.Password "
strSql = strSql & "FROM UserTable "
strSql = strSql & "WHERE Username = '" & strUsername & "'"

Set db = CurrentDb
Set rs = db.OpenRecordset(strSql, dbOpenSnapshot)
If Not rs.NoMatch Then
If rs.Fields("Password") = strPassword Then
MsgBox 'Welcome to the system " & strUsername & "!",,"Welcome"
Else
MsgBox "Incorrect password for " & strUsername & "!",,"Wrong
Password"
End If
Else
MsgBox "Sorry, you are not authorized to enter the system.", , "Good
bye"
Application.Quit
End If

ExitPoint:
Set rs = Nothing
Set db = Nothing
Exit Sub

ErrorHandler:
Debug.Print Err.Number & ": " & Err.Description
Resume ExitPoint

End Sub


Hope this helps,
RD