Log-In Form in Access 2007

[gabrielro]

hi there
First of all, I'm very glad that I found this big online comunity. I hope I will find the answer for my problem.
It might have been asked before so please excuse me if so.

So I have to create a logon form to connect to a database

the database has a table called "users" where the user_id, username and password for each user is stored.

I'm having trouble solving the authentication part where the user and password typed in form is compared with the values stored in table records. I didn't use a combox to select the user in the form, I used two textboxes, first for username and second from password. I've added one button for validating the authentication. As I recall, I have to put the solution at the "on click" event for this button.

So everyone who wants to log in must type both the user name and password into the fom and the press the button :). Please point to a solution for validating the authentication.
Other information I can provide
User_ID field's type is number
Username and Password fields' type is text


What's the easiest way to match the data typed in the form with the values in table records?

Please answer with the VBA code. Thanks in advance

[gabrielro]

nobody knows ? :( please help

[NeoPa]

Gabriel, we don't work here by providing ready made solutions. If you're looking for someone to do your work for you then I'm afraid you're in the wrong place. Also, posting bumps to a thread before at least 24 hours has elapsed is unlikely to win you any new friends here. Some people only get on during the working week. None of us feel it appropriate to have a questioner try to hurry us along with a bump so soon.

That said, if you are willing to learn, and want help in that direction, you will find many here willing and able to assist you.

-Administrator.

[NeoPa]

What you're asking for is essentially pretty simple. The code behind your Command Button simply searches through the [Users] table for a record that matches the [UserName] on your form. It then checks the [Password] value against the value entered on the form.

You can most easily use DLookup() to get the password value from the table.

I would suggest some sort of encryption routine to use for storing and comparing the passwords. It won't work any better, but if you're storing the passwords in a table it would be pretty easy to find the passwords from there.

[gabrielro]

You're right. I apologize for the reply posted in such short time after starting the topic. I have very little programming experience in VBA. In fact this project I'm working requires basic ms access knowledge. I just try to come up with something more thorough. It's the first time I use VBA actually.
Can I post VBA code I thought it should do the trick? To get maybe some new hints? I thought about Dlookup too but sadly I can't figure out the right criteria for my problem :( I'm a total newb maybe that's the reason.

[NeoPa]

It's perfectly acceptable to post your code (ensure you enclose it in the code tags), but it must have a valid matching question.

For DLookup(), you'd need something along the lines of :

Dim strCriteria As String
 
strCriteria = "(([UserName]='%U') AND ([Password]='%P'))"
strCriteria = Replace(strCriteria, "%U", Me.txtUserName)
strCriteria = Replace(strCriteria, "%P", Me.txtPassword)
If IsNull(DLookup("[User_ID]", _
                  "[Users]", _
                  strCriteria)) Then
  Call MsgBox("Your message here")
  'Some code to exit or retry
End If
'This is where the code runs that relies on a valid user

[NeoPa]

If you chose to use encryption of some form then line #5 would change to :

strCriteria = Replace(strCriteria, "%P", Scramble(Me.txtPassword, True))

I include a boilerplate encryption routine that has a security rating of just above sea-level. You are welcome to use it as-is, or even change it or put a totally new one in. Clearly, any passwords saved should be saved after they have been processed via the same routine as you use on your form. This one is two-way (allowing reversal of encryption) but the process would work equally well with one-way only encryption routines.

'If I told you what this did I'd have to kill you.
Public Function Scramble(strPW As String, _
                         Optional ByVal blnEncrypt = False) As String
    Dim intIdx As Integer, intChr As Integer
    Dim strChr As String, strHi As String, strLo As String
 
    If strPW = "" Then Exit Function
    If Not blnEncrypt Then
        If Left(strPW, 1) < "4" _
        Or Left(strPW, 1) > "7" _
        Or Len(strPW) < 4 _
        Or (Len(strPW) Mod 2) = 1 Then
            blnEncrypt = True
        Else
            For intIdx = 2 To Len(strPW)
                strChr = UCase(Mid(strPW, intIdx, 1))
                If (strChr < "0" Or strChr > "9") _
                And (strChr < "A" Or strChr > "F") Then
                    blnEncrypt = True
                    Exit For
                End If
            Next intIdx
        End If
    End If
    If blnEncrypt Then
        Scramble = Left(Hex(Asc(Environ("Path"))), 1)
        For intIdx = 1 To Len(strPW)
            Scramble = Scramble & _
                       Right(UCase(Hex(Not Asc(Mid(strPW, intIdx, 1)))), 2)
        Next intIdx
        Scramble = Scramble & Right(Hex(Asc(Environ("Path"))), 1)
    Else
        For intIdx = 2 To Len(strPW) - 2 Step 2
            strHi = UCase(Mid(strPW, intIdx, 1))
            strLo = UCase(Mid(strPW, intIdx + 1, 1))
            intChr = IIf(strHi > "9", Asc(strHi) - &H36, Val(strHi)) * &H10
            intChr = intChr + IIf(strLo > "9", Asc(strLo) - &H37, Val(strLo))
            Scramble = Scramble & Chr(&HFF And (Not intChr))
        Next intIdx
    End If
End Function

[gabrielro]

Thanks for your reply NeoPa. I asked an ex- class-mate who know more about VB than me and he told me I could use a global function for validating the authentication, that returns a boolean result.
It looks similar with this one:

Function CheckLoginPass(sUsername As String, sPassword As String) As Boolean
On Error GoTo ErrHandler
Dim sPass As String
sPass = DLookup("Password", "users", "User = '" & sUsername & "'")
If sPass = sPassword Then
 
    CheckLoginPass = True
Else
    CheckLoginPass = False
ErrHandler:
     CheckLoginPass = False
End If
End Function
 

And then, for validating in my case it should be something like this:

 If CheckLoginPass(Me.textboxuser, Me.textboxpassword) = True Then
 MsgBox "Success"    
 

It uses dlookup as well. Which method you guys think is more efficient, the one above or the one recommended by NeoPa?
By the way, many thanks goes to NeoPa for the encryption tip.

[NeoPa]

That very much depends on what you want from it. The function is simply an encapsulation of some very similar code to do the same job.

If you feel a function interface is required then use that approach. If the code is only ever used from one position then the workings are simple enough to appear in the code without complicating it unduly. Ultimately, it's a matter of your personal choice.

[NeoPa]

I've just noticed something else about the code provided by your ex-classmate. It does use a slightly different approach in that it returns the actual password into the code. Whether you use the function approach or not, I would not advise leaving that as it is if you are concerned about security. In principle, it is better that the unencrypted password is nevere used or seen except where absolutely unavoidable. Furthermore, if you had a situation where multiple passwords were allowed for the same account (used in some systems in place of multiple accounts) then this approach would have flawed logic. Not a big problem, but I'd certainly recommend the use of the technique of passing all known values to the DLookup() call to allow it to determine the validity of the data.