We help IT Professionals succeed at work.

Access 2007/2010 Validate Userid and password using ADO

Hi and thank you for helping me!

I have a signon screen for users to input a templateID and Password and am looking to have this corrected.  Right now it only works on the first record.  

Here is my code:


Private Sub ContinToDisp_Click()

'Check to see if data is entered into the TemplateID combo box
    If IsNull(Me.cboTemplateID) Or Me.cboTemplateID = "" Then
      MsgBox "You must enter a TemplateID.", vbOKOnly, "Required Data"
        Me.cboTemplateID.SetFocus
        Exit Sub
    End If

    'Check to see if data is entered into the password box
    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.txtPassword.SetFocus
        Exit Sub
    End If
   
'Check database for valid Template ID
Set rs = New ADODB.Recordset
Me.cboTemplateID.SetFocus
sSQL = "select Passlist from MainTable where TemplateID = '" & cboTemplateID.Text & "'"
'and PassList ='" & txtPassword.Text & "'"
 
rs.Open sSQL, CurrentProject.Connection
If rs.EOF Then
    MsgBox "Invalid Login, please try again", vbOKOnly, "Required Data"
        Me.cboTemplateID.SetFocus
    Exit Sub
    Else

' Check the password on the current record that is selected
If Me.txtPassword = PassList Then
   
'******************************* Open Template Named Excel WorkBook **********************
Path1 = CurrentProject.Path
Path2 = "\FleetMarineUser\"
Path3 = Me.cboTemplateID
Strtxt1 = SCGTitle
SCGPassword = "SCGTemplate"

DoCmd.Close acForm, "SignOn", acSaveNo
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open(Path1 & Path2 & Path3)
    Set objXLSheet = objXLBook.Worksheets
    objXLApp.Application.Visible = True

DoCmd.OpenForm "MyActions"
Else
MsgBox "Invalid Login, please try again", vbOKOnly, "Required Data"
        Me.txtPassword.SetFocus
Exit Sub
End If
End If
  rs.Close
  Set rs = Nothing

    'If User Enters incorrect password 3 times database will shutdown
    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
      MsgBox "You do not have access to this database.Please contact admin.", _
               vbCritical, "Restricted Access!"
        Application.Quit
    End If
   
End Sub




Right now it is working like:

Record 1:
TemplateId=02-01-0001 and Password=GoGo123#
Record 2:
Templateid=02-02-0001 and Password=ABCabc123#


user inputs:
Templateid = 02-02-0001
Password = ABCabc123#

I would get invalid login error (which is not correct)

Yet:
Templateid = 02-02-0001
Password = GoGo123#

it would pass (which is not correct).  So it looks like password is only valid on first record


Please help me and thank you!
Comment
Watch Question

Most Valuable Expert 2012
Top Expert 2013
Commented:
Try this revision:


Private Sub ContinToDisp_Click()

'Check to see if data is entered into the TemplateID combo box
    If IsNull(Me.cboTemplateID) Or Me.cboTemplateID = "" Then
      MsgBox "You must enter a TemplateID.", vbOKOnly, "Required Data"
        Me.cboTemplateID.SetFocus
        Exit Sub
    End If

    'Check to see if data is entered into the password box
    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.txtPassword.SetFocus
        Exit Sub
    End If
    
'Check database for valid Template ID
Set rs = New ADODB.Recordset
Me.cboTemplateID.SetFocus
sSQL = "select Passlist from MainTable where TemplateID = '" & cboTemplateID  & "' AND  PassList ='" & txtPassword & "'"
 
rs.Open sSQL, CurrentProject.Connection
If rs.EOF Then
    MsgBox "Invalid Login, please try again", vbOKOnly, "Required Data"
        Me.cboTemplateID.SetFocus
    Exit Sub
    Else

' Check the password on the current record that is selected 
If Me.txtPassword = PassList Then
    
'******************************* Open Template Named Excel WorkBook **********************
Path1 = CurrentProject.Path
Path2 = "\FleetMarineUser\"
Path3 = Me.cboTemplateID
Strtxt1 = SCGTitle
SCGPassword = "SCGTemplate"

DoCmd.Close acForm, "SignOn", acSaveNo
    Set objXLApp = CreateObject("Excel.Application")
    Set objXLBook = objXLApp.Workbooks.Open(Path1 & Path2 & Path3)
    Set objXLSheet = objXLBook.Worksheets
    objXLApp.Application.Visible = True

DoCmd.OpenForm "MyActions"
Else
MsgBox "Invalid Login, please try again", vbOKOnly, "Required Data"
        Me.txtPassword.SetFocus
Exit Sub
End If
End If
  rs.Close
  Set rs = Nothing

    'If User Enters incorrect password 3 times database will shutdown
    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
      MsgBox "You do not have access to this database.Please contact admin.", _
               vbCritical, "Restricted Access!"
        Application.Quit
    End If
    
End Sub

Open in new window



Author

Commented:
mbizup

Thank You for helping me!

I tried:

Me.cboTemplateID.SetFocus
sSQL = "select Passlist from MainTable where TemplateID = '" & cboTemplateID  & "' AND  PassList ='" & txtPassword & "'"


Before and received setfocus errors, it said that txtPassword did not have focus set to it

So I tried:
Me.txtPassword.SetFocus
Me.cboTemplateID.SetFocus
sSQL = "select Passlist from MainTable where TemplateID = '" & cboTemplateID  & "' AND  PassList ='" & txtPassword & "'"

and that gave errors that I could not setFocus to two at the sametime.

Please help me Thank you!
Most Valuable Expert 2012
Top Expert 2013

Commented:
Are you programming in Access VBA or in Visual Basic (VB 6)?
Most Valuable Expert 2012
Top Expert 2013

Commented:
Also - did you remove the .Text like I suggested?  Try it *exactly* like this:

sSQL = "select Passlist from MainTable where TemplateID = '" & Me.cboTemplateID  & "' AND  PassList ='" & Me.txtPassword & "'"

Open in new window

If you are using Access VBA, if you remove the  ".Text"  that you had in your original code, you do not have to set focus to the controls at all before referring to them.

Author

Commented:
mbizup

Thank you for helping.

I am new to all this and slow.

I take it that VBA is for Excel and VB6.5 is for Access 2007/2010 (I tried looking it up to see what version I am using of VB and was totally confused).  Maybe I should ask you what version comes with Access 2007?

So I guess I am using VB6.5

Thank You for helping me!

Author

Commented:
mbizup

Thank you that did the job.

All I had to do is change the SQL statement to your code and delete the If then statement:
If Me.txtPassword = PassList Then


Thank You and Merry Christmas

PS  I need help with another posting:
Title: Excel 2007/2010 Linking an object
I am getting no where with this one hopefully you can help? and as a matter of fact that was a same posting from another before that post.  So If you can help with that one you can get both posting and all the points?

Author

Commented:
Great job and Merry Christmas
Most Valuable Expert 2012
Top Expert 2013

Commented:
Glad this worked out and Merry Christmas to you too!

I'll take a look for your other post a little bit later...  Dinnertime here.
Most Valuable Expert 2012
Top Expert 2013

Commented:
Is it this one?
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27474360.html

My Excel skills are 'intermediate' at best, but I'm afraid my skills are not good enough to be put to use there :(