Results 1 to 2 of 2
  1. #1
    slimjen is offline Expert
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    727

    logon


    Im using Access 2003. I have simple login form for multiple users to login to a database. Just username and password. I also have a form for them to change their password the very first time. My problem is that I did this a while ago for a sql backend and don't know what to change for just an access be. Here's the code behind the logon button:The first is the logon and the second code is for change password. What do I take out to make this work with access. I'v tried to take out the obvious sql lang but I recieved all kinds of errors. Thanks

    Code:
    Private Sub cmdLogon_Click()
    On Error GoTo Err_cmdLogon_Click
    Dim myDB As DAO.Database
    Dim myRS As DAO.Recordset
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim stMessage As String
    Dim stUserName As String
        Set myDB = CurrentDb()
        Set myRS = myDB.OpenRecordset("tblUsers", dbOpenDynaset, dbSeeChanges)
        
        If IsNull(Me.UserName) Or IsNull(Me.txtPW) Then
            stMessage = MsgBox("You must enter your user name and password.", vbExclamation, "Incorrect Login")
            Me.UserName.SetFocus
            GoTo Exit_Here
        End If
        
        gblstUserName = Me.UserName
            
        ' if the LogOn fields are not empty then check if user exist
        If Not IsNull(Me.UserName) And Not IsNull(Me.txtPW) Then
            myRS.FindFirst "UserName= '" & Me.UserName & "'" & " And PW= '" & Me.txtPW & "'"
            
            ' if the user exists but the password is the default "PASSWORD, then notify user and open
            ' the form where the user can change the password to whatever they desire
            
            If myRS.NoMatch = False And Me!txtPW = "password" Then
             Me.Visible = False
                'DoCmd.Close
                DoCmd.OpenForm "frmChangePassword"
                Forms![frmChangePassword]![txtUserName] = gblstUserName
                GoTo Exit_Here
            End If
            
            If myRS.NoMatch = False And Me.ckChangePW = True Then
            Me.Visible = False
                'DoCmd.Close
                DoCmd.OpenForm "frmChangePassword"
                Forms![frmChangePassword]![txtUserName] = gblstUserName
                GoTo Exit_Here
            End If
                    
            ' if the user does not match, notify user and exit function
            If myRS.NoMatch = True Then
                stMessage = MsgBox("Incorrect Login.  Enter your user name and password " & Chr(13) & _
                    "or contact the Database Administrator at ext. 4738.", vbExclamation, "Incorrect Login")
                GoTo Exit_Here
            End If
        End If
        
        gblbAdmin = DLookup("Admin", "tblUsers", "UserName = '" & gblstUserName & "'")
        Me.Visible = False
        'DoCmd.Close
        DoCmd.OpenForm "frmMainMenu"
       
    Exit_Here:
        myRS.Close
        
        Set myRS = Nothing
        Set myDB = Nothing
    Exit_cmdLogon_Click:
        Exit Sub
        
    Err_cmdLogon_Click:
        MsgBox Err.Description
        Resume Exit_cmdLogon_Click
        Resume
    End Sub
    Private Sub cmdCancel_Click()
    On Error GoTo Err_cmdCancel_Click
    Dim stMessage As String
        DoCmd.Quit
    Exit_cmdCancel_Click:
        Exit Sub
    Err_cmdCancel_Click:
        MsgBox Err.Description
        Resume Exit_cmdCancel_Click
        
    End Sub
    Private Sub Form_Open(Cancel As Integer)
        Me.cboUserName.SetFocus
        Me.txtStatus.Value = "Welcome to the Occurrence Database.  " _
            & "For access or assistance, call the Database Developer at ext. 4738."
    End Sub
    Private Sub cboUserName_AfterUpdate()
        ' Find the record that matches the control.
        Dim rs As Object
        Set rs = Me.Recordset.Clone
        rs.FindFirst "[ID] = " & Str(Nz(Me![cboUserName], 0))
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End Sub
    Code:
    Private Sub cmdCancel_Click()
    On Error GoTo Err_cmdCancel_Click
    Dim stMessage As String
    stMessage = MsgBox("By canceling, you are about to completely exit the database." & Chr(13) & _
        "Are you sure you want to do this?", vbYesNo, "Cancel Password Change")
        
        If stMessage = vbYes Then
            DoCmd.Quit
        Else
            Me.txtNewPW.SetFocus
            Exit Sub
        End If
    Exit_cmdCancel_Click:
        Exit Sub
    Err_cmdCancel_Click:
        MsgBox Err.Description
        Resume Exit_cmdCancel_Click
        
    End Sub
    
    Private Sub cmdChangePW_Click()
    On Error GoTo Err_cmdChangePW_Click
        Dim stDocName As String
        Dim stMessage As String
        Dim stUserName As String
        Dim myDB As DAO.Database
        Dim myRS As DAO.Recordset
        
        Set myDB = CurrentDb()
        Set myRS = myDB.OpenRecordset("tblUsers", dbOpenDynaset, dbSeeChanges)
        
        If IsNull(Me.txtUserName) Or IsNull(Me.txtOldPassword) Then
            stMessage = MsgBox("Please enter your user name and password.", vbExclamation, "Password Change Error")
            Me.txtUserName.SetFocus
            GoTo Exit_Here
        End If
        
        If IsNull(Me.txtNewPW) Or IsNull(Me.txtConfirmPW) Then
            stMessage = MsgBox("Please enter your new and confirmed password.", vbExclamation, "Password Change Error")
            Me.txtNewPW.SetFocus
            GoTo Exit_Here
        End If
        
        ' if the LogOn fields are not empty then check if user exists
        If Not IsNull(Me.txtUserName) And Not IsNull(Me.txtOldPassword) _
            And Not IsNull(Me.txtNewPW) And Not IsNull(Me.txtConfirmPW) Then
            myRS.FindFirst "UserName= '" & Me.txtUserName & "'" & " And PW= '" & Me.txtOldPassword & "'"
     
             If myRS.NoMatch Then
                stMessage = MsgBox("Login incorrect. Re-enter user name and password" & Chr(13) & _
                    "or contact the Database Administrator at ext. 4738.", vbExclamation, "Password Change Error")
                Me.txtUserName.SetFocus
                GoTo Exit_Here
            End If
            
        End If
        If Me.txtNewPW = Me.txtConfirmPW Then
            stDocName = "qryChangeePW"
            DoCmd.SetWarnings False
            DoCmd.OpenQuery stDocName, acNormal, acEdit
            DoCmd.SetWarnings True
            Me.cmdCancel.Visible = False
            Me.cmdContinue.Visible = True
            Me.Requery
            Me.txtStatus.Value = "Your password has been changed. Press continue to go to the main menu."
        Else
            stMessage = MsgBox("New and confirmed passwords don't match." & Chr(13) & _
                "Please try again.", vbExclamation, "Password Change Error")
            Me.txtNewPW.SetFocus
            Exit Sub
        End If
        
            gblbAdmin = DLookup("Admin", "tblUsers", "txtUserName = '" & gblstUserName & "'")
    Exit_Here:
        myRS.Close
        
        Set myRS = Nothing
        Set myDB = Nothing
        
    Exit_cmdChangePW_Click:
        Exit Sub
    Err_cmdChangePW_Click:
        MsgBox Err.Description
        Resume Exit_cmdChangePW_Click
        Resume
    End Sub
    Private Sub Form_Load()
        Me.cmdContinue.Visible = False
        Me.cmdCancel.Visible = True
    End Sub
    Private Sub Form_Open(Cancel As Integer)
        Me.txtStatus.Value = ""
    End Sub
    Private Sub cmdContinue_Click()
    On Error GoTo Err_cmdContinue_Click
        Dim stDocName As String
        Dim stLinkCriteria As String
        DoCmd.Close
        stDocName = "frmMainMenu"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        
    Exit_cmdContinue_Click:
        Exit Sub
    Err_cmdContinue_Click:
        MsgBox Err.Description
        Resume Exit_cmdContinue_Click
        
    End Sub

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't see anything glaringly wrong. Step debug. What line errors? What is the error message?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Users logon and permissions
    By eman in forum Programming
    Replies: 10
    Last Post: 10-03-2011, 11:10 PM
  2. Replies: 0
    Last Post: 09-27-2011, 12:25 PM
  3. Logon form
    By Andyjones in forum Forms
    Replies: 5
    Last Post: 09-13-2011, 01:58 PM
  4. Security Logon to database
    By sdondeti in forum Security
    Replies: 1
    Last Post: 07-05-2011, 11:41 AM
  5. XP Logon type form
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 08-31-2010, 02:00 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums