Creating a Login page using MS Access/VBA

[webmaster]

Hi,

I am fairly new to Visual Basic and I am stuck on a little issue. I am
trying to create a login page in MS Access/VBA logs a user with
different privileges into a certain form.

I have a table set up with user names, passwords and user-access
numbers.

I created a form whose record source points to that table.

And I have a Textboxes for "Username" and "Password" plus the login
button.

It sounds fairly simple but I am having trouble getting the database to
find those fields in that table.

I started off trying it more simply by just using the Username. And it
always just pulls up the first record in that table. If I can get it
working with the User_name then I feel that I shouldn't have a problem
with adding the password and restriction rights in there.

I know that I should be using the Security feature with encryption from
within Access. But I find it too complicated to work with. And this
database may be passed on to someone else with less knowledge that
myself (which is a scary thought).

Any help would be thankful. =)

Here's what I've been trying:
My text boxes are called "User_name" and "Pass_word".
And my button is called "Login_Button"
The table is called "User_Access"
And the table fields are called "Username" and "Password".

'---------------------------------
Private Sub Login_Button_Click()

Dim var_login As Variant

var_login = DLookup("[User_Access.UserName]", "User_Access",
"[User_Access.UserName] = [User_name]")

If [UserName] = [User_name] Then

DoCmd.OpenForm "Assign_Request", acNormal

Else

MsgBox ("Incorrect Login")

End If

End Sub
'--------------------

If run it with a MsgBox([UserName]) in the place of the DoCmd, it will
only give me the value for the Username of the first record in the
table.

Should I be using the DLookup command or something else... I am lost
for ideas at this point

Thanks again,


Steven

 

[Rick B]

Why would you think ULS is complicated? It is much simpler than what you
are doing here. No need to build a table (the features are right there in
the Tools menu). It is also safer. What prevents any of your users from
simply opening your table and reading the passwords? Or, what keeps them
from bypassing your "logon form" all together and opening the file with full
access? Or, what keeps them from importing or linking to your tables?

I'd highly advise against re-inventing the wheel in this manner. Certainly
home-grown security can do as good a job as ULS in some cases, but you must
be much more diligent in plugging all the holes. Generally, growing your
own security will be much MORE complex than using the built-in tools, if you
do it properly.

 

[webmaster]

The username and password is really used for security/privacy reasons.
It is mainly used to direct the users in a starting direction.

I am working on a database that stores all of our work information in a
work log book. When we receive a request from a client one person will
open the database and enter the request information into the database.
Then another user will assign the request to one of their workers. This
has that user going into the database to that same record and adding
the Assigned information. Finally when the request has been completed.
That worker will go into the database to that record and fill in the
final information to close off the request.

The main purpose behind the login was to have 3 different entering
points for the users. A login for each user type. This way a worker
won't make the mistake of filling out info in a wrong area. Or deleting
something by mistake.

We don't really care if someone wants to hack into the database. It's
easy enough just to hold down the SHIFT key when opening the database.
There isn't anything work stealing in there. We just want to have it
safe from mistakes. The table with usernames and passwords already
existed from a previous database that I imported. So I figured that
there would have been a simple 20 lines of code that would look up the
user's login info and compare it with that record in the database and
then respond.

I am fairly new to MS Access and Visual Basic. And I find the Access
Security system fairly scary to work with. I somehow managed to lock
myself out of it the last time...lol.

This is why I found it a little to complicated for our needs.


Thanks,

Steven

 

[Douglas J Steele]

Your DLookup is incorrect in a couple of different ways.

You shouldn't have the table name in the field references, plus you need the
value of what's in the User_name textbox, not simply a reference to it:

var_login = DLookup("[UserName]", "User_Access",
"[UserName] = " & Chr$(34) & [User_name] & Chr$(34))

Now, var_login is going to contain the value of UserName if that user name
is in the table, or Null otherwise. That means your If statement is wrong:

If IsNull(var_login) = False Then

DoCmd.OpenForm "Assign_Request", acNormal

Else

MsgBox ("Incorrect Login")

End If

 

[webmaster]

Thank-you Doug.
I tried it, and it works perfectly.
Not I can tweak it a bit to work with the password and user login type
fields.

Thanks again. =)


Steven

 

[Douglas J. Steele]

Just a comment on passwords. By default, Access isn't case sensitive. Don't
know whether or not you care about that. If you do, you'll have to use the
StrComp function to compare what was typed in vs. what's stored.