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