Creating a login form in Microsoft Access using macros, without VBA.

Question

I've looked around for ages and I haven't found an answer to my problem so I was hoping someone here could help me.

I am creating a system using Microsoft Access where I have a members table containing a username and password and various other fields such as date of birth, etc.

I want to create a form where users can enter a username and password. By clicking a button on this form, these details will then be checked against the usernames and passwords in the members table. If the details match, a message will be displayed saying they have logged in. If the details are not found in the table, a message saying the details are incorrect will show.

How can I do this without using VBA?

I have started by creating a form called loginform with two text boxes loginusername and loginpassword.

Where should I go from here?

Solution 1

The VBA solution shouldn't be that complicated. A quick and dirty solution:

Dim Result as Variant

Result=Dlookup("Password","tblMembers","UserName='" & nz(loginusername.value,"") & "'")
If nz(Result,"")<>nz([login password].value,"") Then
  MsgBox "Invalid password"
Else
  MsgBox "Password correct"
End If

Solution 2

'set the variables
Dim UN As String
Dim PW As String
Dim user, pass As Boolean
'make sure none of the fields are null, or blank
UN = Text
PW = Text
If IsNull(Username) Then
MsgBox "You must enter a username."
Username.SetFocus
Else
'assign true to user
user = True
End If
If IsNull(Password) Then
MsgBox "You must enter a password."
Password.SetFocus
Else
pass = True
End If
If user = True And pass = True Then
UN = DLookup("[Username]", "LoginTable", "[Username]= '" & Me.Username & "'")
PW = DLookup("[Password]", "LoginTable", "[Password] = '" & Me.Password & "'")
End If
If Me.DummyUser = Me.Username And Me.DummyPass = Me.Password Then
MsgBox "Access granted."
Else
MsgBox "Access denied."
End If

Comments

[Doc Brown]

Why not with VBA? Would be the most easiest way.

[robin sirleaf]

I'm preparing for an exam where I have to create my system in a timed environment without any outside help except for before I go in. All vba I've seen is long and complicated and I just wouldn't be able to remember it off by heart. I can try though if there is no other way..

[HardCode]

If you think VBA is long and complicated, you're going to be in trouble thinking you can do this in Access without VBA.

[robin sirleaf]

Well I've worked out without VBA how to check the username and that wasn't too difficult, it's just also getting it to check against the password I can't seem to solve.

[robin sirleaf]

Perhaps it would be easier to use VBA?

[David-W-Fenton]

I shudder to think how hard this would be using nothing but macros. VBA would take a few lines of code. It's a terrible exam question, anyway, as it doesn't apply to Jet user-level security, since you're already logged in when you open the database, and it can't apply to the ACE's new ACCDB format, which has no security at all (other than the security theater of database passwords). For any other security, it's a vulnerability waiting to be exploited. In short, based on this question, I'd say your teacher is not someone I'd trust for instruction.

[robin sirleaf]

Thank you so much! Works perfectly. Now to try and remember this off by heart.. Thanks again!