Problem with a Do While Loop

rgagli1
rgagli1 used Ask the Experts™
on
I am having a problem gettign this code to run. I can get ti to run but I get a duplicate error. I just want the code to check to see if it is the right password and go through all the names. A user can not type in a name it has to be in teh drop down box. Can someone please help. I tried using MoveNext but that is why I am receiving this error. Any help would be greatly appreciated.

    If Len(Trim(txtPassword.Text)) > 0 Then
        Do While adoPassword.Recordset.EOF = False
        If adoPassword.Recordset.Fields("Password") = Trim(txtPassword.Text) And adoPassword.Recordset.Fields("SalesPerson") = dcboEmployee.Text Then
            LoginSucceeded = True
            Me.Hide
        ElseIf adoPassword.Recordset.Fields("Password") <> Trim(txtPassword.Text) And adoPassword.Recordset.Fields("SalesPerson") = dcboEmployee.Text Then
            'pwd is incorrect
            MsgBox "Invalid Password, try again!", , "Login"
            LoginSucceeded = False
            txtPassword.SetFocus
            SendKeys "{Home}+{End}"
        End If
            adoPassword.Recordset.MoveNext
            Loop

    Else
    MsgBox "Enter a Password", , "Login"
    txtPassword.SetFocus
    LoginSucceeded = False
    SendKeys "{Home}+{End}"
    End If

Thanks,

Ryan Gagliano
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Perhaps you could try this:


loginsucceeded = false

Do While adoPassword.Recordset.EOF = False
       If adoPassword.Recordset.Fields("Password") = Trim(txtPassword.Text) And adoPassword.Recordset.Fields("SalesPerson") = dcboEmployee.Text Then
           LoginSucceeded = True
           exit do
       end if
       adoPassword.Recordset.MoveNext

    loop
    if LoginSucceeded = true then
        me.hide   ' or maybe exit sub??
    else
           MsgBox "Invalid Password, try again!", , "Login"
           
           txtPassword.SetFocus
           SendKeys "{Home}+{End}"
    End If


----------From what I am seeing - you are only looking at the first name in the recordset.  This will walk the entire recordset - unless you find the password.  If it is found - it will exit the loop so you don't have to waste cycles looping through.

Author

Commented:
This still does not work. It thinks I am trying to add a new record adn I get a duplicate primary key error. Can anyone help me with this?

Commented:
adoPassword.open "select * from table where password= Trim(txtPassword.Text) and user = dcboEmployee.Text


if adopassword.eof = false then login successful

if .eof= true then login failed

any questions email me avi1818@yahoo.com

Enjoy,
Avi
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Looks like you haven't open the recordset or are using the connection instead of the recordset (can't tell wich since you posted only partial code.

Anyhow it should go like this:

Dim adoPassword As New ADODB.Connection
Dim adoRecordsetRecordset As ADODB.Recordset

If Len(Trim(txtPassword.Text)) > 0 Then
 
  adoPassword.Open "ConnectionString"
  Set adoRecordsetRecordset = adoPassword.Execute("select * from PasswordTable where SalesPerson = '" & dcboEmployee.Text & "' and Password = '" & Trim(txtPassword.Text) & "'")

  If Not adoRecordsetRecordset.EOF Then
    LoginSucceeded = True
  Else
    'pwd is incorrect
    MsgBox "Invalid Password, try again!", , "Login"
    LoginSucceeded = False
    txtPassword.SetFocus
    SendKeys "{Home}+{End}"
  End If

  adoRecordsetRecordset.Close
  adoPassword.Close

Else
  MsgBox "Enter a Password", , "Login"
  txtPassword.SetFocus
  LoginSucceeded = False
  SendKeys "{Home}+{End}"
End If

If LoginSucceded Then Me.Hide

'if you sure dcboEmployee.Text is a existed SalesPerson
    If Len(Trim(txtPassword.Text)) > 0 Then
        adoPassword.Recordset.Find "SalePerson = '" & dcboEmployee.Text & "'", , , adBookmarkFirst
        If adoPassword.Recordset.Fields("Password") = Trim(txtPassword.Text) Then
            LoginSucceeded = True
            Me.Hide
        Else
            'pwd is incorrect
            MsgBox "Invalid Password, try again!", , "Login"
            LoginSucceeded = False
            txtPassword.SetFocus
            SendKeys "{Home}+{End}"
        End If
    Else
        MsgBox "Enter a Password", , "Login"
        txtPassword.SetFocus
        LoginSucceeded = False
        SendKeys "{Home}+{End}"
    End If
VK

Commented:
Hello, try this


    Tries = 3
    LoginSucceeded = False
   
    For i = 1 To Tries
        Select Case 0
            Case Len(Trim(dcboEmployee.Text))
                MsgBox "Empty User, try again!", , "Login"
                dcboEmployee.SetFocus
            Case Len(Trim(xtPassword.Text))
                MsgBox "Empty Password, try again!", , "Login"
                txtPassword.SetFocus
            Case Else
                Do While Not adoPassword.Recordset.EOF
                    If adoPassword.Recordset.Fields("SalesPerson") = dcboEmployee.Text Then
                        Select Case False
                            Case adoPassword.Recordset.Fields("Password") = Trim(txtPassword.Text)
                                MsgBox "Invalid Password, try again!", , "Login"
                                txtPassword.SetFocus
                            Case Else
                                LoginSucceeded = True
                                Me.Hide
                                Exit Do
                        End Select
                        SendKeys "{Home}+{End}"
                    End If
                    adoPassword.Recordset.MoveNext
                Loop
                If .EOF Then
                    MsgBox "Invalid User, try again!", , "Login"
                    dcboEmployee.SetFocus
                End If
        End Select
    Next

v.k.
VK

Commented:
Sorry forgot something essential :-)

    Tries = 3
    LoginSucceeded = False
   
    For i = 1 To Tries
        Select Case 0
            Case Len(Trim(dcboEmployee.Text))
                MsgBox "Empty User, try again!", , "Login"
                dcboEmployee.SetFocus
            Case Len(Trim(xtPassword.Text))
                MsgBox "Empty Password, try again!", , "Login"
                txtPassword.SetFocus
            Case Else
                adoPassword.Recordset.Movefirst
                Do While Not adoPassword.Recordset.EOF
                    If adoPassword.Recordset.Fields("SalesPerson") = dcboEmployee.Text Then
                        Select Case False
                            Case adoPassword.Recordset.Fields("Password") = Trim(txtPassword.Text)
                                MsgBox "Invalid Password, try again!", , "Login"
                                txtPassword.SetFocus
                            Case Else
                                LoginSucceeded = True
                                Me.Hide
                                Exit Do
                        End Select
                        SendKeys "{Home}+{End}"
                    End If
                    adoPassword.Recordset.MoveNext
                Loop
                If .EOF Then
                    MsgBox "Invalid User, try again!", , "Login"
                    dcboEmployee.SetFocus
                End If
        End Select
    Next


v.k.

Author

Commented:
I still cannot get this code to work. I am connecting to the pestbegone database and usign the tblsales table. Can someone please help. I must be missign some information becuase I get a location error or a duplicate primary key error.

Thanks,

Ryan

I have tried everyone's suggestion and still cannot get this program to log people in. Can soemone please look at the code and help me. I have an adocontrol called adopassword which connects to the pestbegone database and I am pullign the employees from the tblsales table and the SalesPerson field. The password is from the Password field.

Any help would be greatly appreciated,

Ryan Gagliano

Option Explicit
'Set a reference to:
'Microsoft ActiveX data object (highest version) library
Public LoginSucceeded As Boolean

Private Sub cmdCancel_Click()
    'set the global var to false
    'to denote a failed login
    LoginSucceeded = False
    Me.Hide
End Sub
Private Sub Form_Load()
   'connect do Db
        adoPassword.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                    "Data Source=" & App.Path & "\Pestbegone.mdb;" & _
                                    "Persist Security Info=False"
        adoPassword.RecordSource = "tblsales"
        adoPassword.Refresh

End Sub

Private Sub cmdOK_Click()

    'check for correct password

     LoginSucceeded = False
   
    If Len(Trim(txtPassword.Text)) > 0 Then
       adoPassword.Recordset.Find "SalePerson = '" & dcboEmployee.Text & "'", , , adBookmarkFirst
       If adoPassword.Recordset.Fields("Password") = Trim(txtPassword.Text) Then
           LoginSucceeded = True
           Me.Hide
       Else
           'pwd is incorrect
           MsgBox "Invalid Password, try again!", , "Login"
           LoginSucceeded = False
           txtPassword.SetFocus
           SendKeys "{Home}+{End}"
       End If
   Else
       MsgBox "Enter a Password", , "Login"
       txtPassword.SetFocus
       LoginSucceeded = False
       SendKeys "{Home}+{End}"
   End If



If LoginSucceeded = True Then

Load frmCustInfo
frmCustInfo.Show

End If

End Sub
where Error happen, and show me the Err.Num, Err.Src, Err.Description

Author

Commented:
Ok, I get the error on this line: (the 2nd line of code on my program):

adoPassword.Recordset.Find "SalePerson = '" & dcboEmployee.Text & "'", , , adBookmarkFirst

The error message is Run-Time Error 3265. Item can not be found in the collection corresponding to the requested name or ordinal.

I am choosing the Employee Ryan and typing in the wrong password. There is a Ryan in the table and that is not the correct password. Can you help?

Thanks,

Ryan
Ryan,

You are trying to use adoPassword that is probably an ADO connection object as if it was a recordset object appending .Recordset at the end of it. That doesn't work.

You need to declare a recordset object, initialize it using the connection object and the use it (MyRecordset.Whatever).

Please, take a look at my example above.

Carlo

Author

Commented:
I did get it to work but I dont think it is working correctly. I do not receive any errors but the problem is when I am pulling the salesperson who just logged in. Say I pull Ryan as the salesperson for some reason on teh next form it pulls Laura the first person. This is the code I have in form load should I move it to form activate?

lblEmployee.Caption = frmLogin.adoPassword.Recordset.Fields("SalesPerson")

Also, how to I distribute the points to the person that helped me get the right answer?

1.check "SalePerson" is a existed field name
    Debug.print adoPassword.Recordset is nothing
    Debug.Print adoPassword.Recordset("SalePerson").Name
2.check is there some spical char in dcboEmployee.Text, like "'", "*", "#", etc.
    show me the dcboExployee.Text

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial