problems with Login Form with VBA that validates username and password?

[Bryan]

Hey everyone,

I know this question has been asked many times, but I can't seem to get a handle on it.

In a nutshell, I just want a form that validates the username and password someone puts in that's in a database. I know the code will the use the DLookup function, but that's where I get a little mixed up. I know what it's used for, but I'm not sure how to make use of it.

I currently have a table called "klanten" wich contain the rows "login" and "wachtwoord" (with various other rows). What i have at this moment is the code to check is somthing is filled in in the form.

Private Sub cmmndLogIn_Click()

'Check to see if data is entered into login

If IsNull(login) Or login = "" Then
MsgBox gelieve een login in te vullen.", vbOKOnly, "Required Data"
.login.SetFocus
Exit Sub
End If
''Coded in by Bryan


If anyone could help explain to me how I would get this to work, that would be great!

Thanks in advance!

[twinnyfo]

Bryan,

First, please use Code Tags when posing code to aid us in reviewing your code. Also, make sure you are doing a direct copy and paste from your project (as it does not appear you have done that with yours).

Based on your code, I am surprised you don't get a group of errors--which is why it is difficult to troubleshoot from the start.

Next, I'm not quite sure what your exact question is. You talk about looking up Login and Password credentials form a table, using DLookup, but your code refers to data validation.

Both are valid questions, but I'm not sure what your true question is. You don't state whether your current code works or not. So, this is the place to start.

We'll be glad to assist once we get a little more clarification.

By they way, I think your are on the right track with this, so don't be discouraged!

:-)

[Bryan]

Thanks already for your reply! What i want to achieve is first look up the data, so i can later use the data achieved from the lookup to validate the data that was put in the form.

The code i have at the moment is the following:

If IsNull(Me.Username) Or Me.Username = "" Then
        MsgBox "gelieve een login in te voeren.", vbOKOnly, "Required Data"
            Me.Username.SetFocus
        Exit Sub
    End If
 
'Check to see if data is entered into Password
 
    If IsNull(Me.Password) Or Me.Password = "" Then
        MsgBox "Gelieve een paswoord in te voeren.", vbOKOnly, "Required Data"
            Me.Password.SetFocus
        Exit Sub
    End If
 'Coded in by Bryan
 
     If Username.Value <> DLookup("[login]", "Klanten", "[Username]='" & Username & "'") Then
MsgBox "Invalid Username. Please try again.", vbOKOnly, "Invalid Entry!"
 
End If
    If Password.Value <> DLookup("[wachtwoord]", "Klanten", "[Username]='" & Username & "'") Then
MsgBox "Invalid Password. Please try again.", vbOKOnly, "Invalid Entry!"
End If
End Sub
 
  If Username.Value <> DLookup("[login]", "Klanten", "[Username]='" & Username & "'") Then
MsgBox "Invalid Username. Please try again.", vbOKOnly, "Invalid Entry!"
End If
 
  If Password.Value <> DLookup("[wachtwoord]", "Klanten", "[Username]='" & Username & "'") Then
MsgBox "Invalid Password. Please try again.", vbOKOnly, "Invalid Entry!"
End If
 
THis code seemed to be working so far. But i seems to only work correct with the first ID or login/password combo from the table "klanten". If i for example take the login and password combo from the third row of the table and use that to try on log into the form i get the message that the used login password is incorrect while it is correct!
 
Furthermore i want to open a new form is the combination of both the password value and the usernam value is correct. I tried with the code underneath but it doesn't seem to work.
 
If Password.Value = DLookup("[wachtwoord]", "Klanten", "[Username]='" & Username & "'") And Username.Value <> DLookup("[login]", "Klanten", "[Username]='" & Username & "'") Then DoCmd.OpenForm "Mainmenu"
end if 
end sub

Im sorry if i'm being unclear i'm still fairly new to all these things!

[jforbes]

Hello Bryan,

I'm not seeing anything glaring wrong with your code. You may have to step through it in debug mode and see what values you are getting. You also might want to type the DLookups into the Immediate Window (Ctrl-G while in the VB Editor) to see what they are returning:

?DLookup("[wachtwoord]", "Klanten", "[Username]='" & Username & "'") 

That being said, when you get it to work, you may want to consider going about your security slightly different. Creating a way to login to you database is usually only half the security model people tend to use. The other half is permission to Forms and Data. So when you get your Login to work, it looks like you will need to then look up the permissions in the Klanten table as needed or grab all the permission on login.

What I do is to look up the Windows User Name on Open of the Database, then set global variables for permissions based on their record in the Employees (Klanten) table.

This code gets the Windows User login and is located in a Module. The first line is not to be inside a code block, it just supposed to hang out:

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
 
Global gImpersonateUser As String
Public Function GetWindowsUser() As String
 On Error GoTo GotoError
 
    Dim st As String
    Dim slCnt As Long
    Dim slDL As Long
    Dim slPos As Single
    Dim slUserName As String
    Dim txtUsername As String
 
    If Len(gImpersonateUser) > 0 Then
        GetWindowsUser = gImpersonateUser
    Else
        'Get the Windows User Name
        slCnt = 199
        st = String(200, 0)
        slDL = GetUserName(st, slCnt)
        slUserName = Left(st, slCnt) & slCnt
        slPos = InStr(1, slUserName, Chr(0))
        If slPos > 0 Then
           txtUsername = Left(slUserName, slPos - 1)
           GetWindowsUser = txtUsername
        Else
           GetWindowsUser = ""
        End If
    End If
 
GotoExit:
    Exit Function
GotoError:
    MsgBox Err.Description
    Resume GotoExit
End Function

The variable gImpersonateUser is to allow you as the developer to login as someone else, you can rip that out if you don't need it.

Now that the function to get the Currently logged in Windows User is available, I use the following on Open of the Database to lookup Permission and System Wide variables:

Global gQuoteDirectory As String
Global gGlobalAttachmentDirectory As String
Global gLongAppName As String
Global gShortAppName As String
 
Global gAdmin As Boolean
Global gEngr As Boolean
Global gManagement As Boolean
 
Public Sub LoadPreferences()
On Error GoTo GotoError
 
    Dim rst As DAO.Recordset
    Dim sSQL As String
 
    ' Load System Values
    sSQL = ""
    sSQL = sSQL & "Select TOP 1 SystemDefaults.* FROM SystemDefaults "
    Set rst = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbForwardOnly + dbSeeChanges)
        If rst.RecordCount > 0 Then
            gQuoteDirectory = Nz(rst!QuoteDirectory, "")
            gGlobalAttachmentDirectory = Nz(rst!GlobalAttachmentDirectory, "")
            gLongAppName = Nz(rst!LongAppName, "")
            gShortAppName = Nz(rst!ShortAppName, "")
        End If
    rst.Close
 
    ' Load Preferences/Security
    sSQL = ""
    sSQL = sSQL & "SELECT TOP 1 * FROM Employees "
    sSQL = sSQL & "WHERE  UserID='" & GetWindowsUser() & "'"
    Set rst = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset, dbForwardOnly + dbSeeChanges)
        If rst.RecordCount > 0 Then
            gAdmin = Nz(rst![Admin], False)
            gEngr = Nz(rst![Engr], False)
            gManagement = Nz(rst![Managment], False)
        Else
            gAdmin = False
            gEngr = False
            gManagement = False
            msgBoxError("You do not currently have permissions to " & gLongAppName & ".  Please see your System Administrator to gain access.")
        End If
    rst.Close
GotoExit:
    Exit Function
GotoError:
msgBoxError ("There was an error in LoadPreferences.  The most likely cause is that you do not currently have permissions to this Database.  Please see your System Administrator to gain access.")
Resume GotoExit    
End Sub
Public Function msgBoxError(ByVal Message As String) As Boolean
    msgBoxError = False
    If MsgBox(Message, vbCritical + vbOKOnly, gLongAppName) = vbOK Then
        msgBoxError = True
    End If
End Function

After this runs, the variables gAdmin, gEngr, gManagement contain the permissions for the current user.

This is just an option for you. I also use a SQL Server back end using Trusted Connections (Windows User) so if the user doesn't have permissions in the SQL Database, they cannot even see the data.

[twinnyfo]

Also, just looking briefly at your code, it is unclear as to whether "Password" and "Username" are declared variables or controls on your form. I have a tendency to always be completely explicit. If I am trying to refer to a text box control, I always name the control something that differentiates it from the underlying field name. For example, if I have a table with the Field Name "UserName" and I want to have a text box that hold that field's information, I will name the text box "txtUserName". Then, when I reference the control in VBA, I always know what I am referring to. In the example above, the following code could produce different results, depending on the status of the record:

strUserName = Me.txtUserName
strUserName = Me.UserName

It might be rare that the field would not get updated immediately, but in your case, the text boxes are updated by the user and not bound to any table, so either way is immaterial, but it is a good practice to get into to refer to object explicitly, rather than letting Access try to figure out what you mean.