I have written a module to get compare the username and password saved in table userlist and then to compare it with the data entered in the a form. if they match user is allowed to enter the database, but when i open the table with recordset i am just getting the first record and other records are not read by the recordset object and when i try to use movenext method i get an error either eof or bof is true, or the current record is deleted but there are records in the table. Why I am accessing not the other records of the table. what is wrong with my code? when i use debug.print command i get all the records but with find method and move methods i am getting just one record.

Hi,

   Try changing the group of three double quotes to a single quote surrounded by two double quotes:

Go from: criteria = "[USER] = """ & Forms![Login]!USER & """"

To: criteria = "[USER] = " & "'" & Forms![Login]!USER & "'"

(Wow, four double quotes in the last group. Impressive.)

If USER is an integer, then change it to this:

criteria = "[USER] = " & Forms![Login]!USER

I think since you are passing the wrong criteria structure, you are getting an empty recordset. This should not return an error if the recordset is empty.

Try adding the criteria to your original SQL statement like this:

rs.Open _
      "SELECT * FROM Userlist WHERE USER = " & "'" & Forms![Login]!USER & "'", conn, adOpenDynamic, adLockOptimistic

Then try changing your check to:

If rs.RecordCount <> 0 Then ...

instead of then looking up the recordset with your criteria.

HTH


mmcdonal

well i have changed my code according to your suggestions but it is not working and as stated before i am getting one record and this single record is also not compared in with the data entered in the form. i m confused. Thanks ADO

How did you change the code? Please post it.

Also, you say you are getting one record now where before you were getting no records. What record are you getting and how is it wrong?

What sort of control is "Forms![Login]!USER"? Is this a combo box? If so, where is it looking up? What column is it bound to?


mmcdonal

I was getting before also one record. Login is form and User is textfield on the form Login. there is a button ok on form login and the given module is written behing the ok button to check whether a user with the certain name and password exists, user informations are stored in table Userlist. when i am giving a valid name and password, even then it is not working, by the way I am converting my application from DAO to ADO.

I am not sure why you are writing this as a function instead of just putting it on the login button. Here is what I would do. I am not sure why you have the additional code. Your code also has formatting errors:

'==========
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim criteria As String
Dim stSQL As String
Dim stPassword As String
Dim loc_user_ID As Integer
Dim intUser As Integer

intUser = Forms![Login]!USER
stSQL = "SELECT * FROM UserList WHERE USER = " & intUser
stPassword = Forms![Login]!Password

Set conn = CurrentProject.Connection
Set rs= New ADODB.Recordset

rs.Open stSQL, conn, adOpenDynamic, adLockOptimistic"

If rs.RecordCount <> 0 Then
        If stPassword = rs!Password Then
          loc_user_ID = rs![ID]
          dummy = True
        End If
End If
rs.Close
conn.Close

curr_user = intUser
'==========

Here is how I do the same thing in one of my databases in DAO:
'==========
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim stOwner As String
    Dim stSQL As String
    Dim rs As Recordset
    Dim db As Database
    Dim stPassword As String
    Dim stConfirm As String

    Set db = CurrentDb()

    If IsNull(Me.cboUserName) Then
        MsgBox ("Please enter a User Name")
        GoTo Exit_btnLogin_Click
    Else
        stOwner = Me.cboUserName
    End If

    If IsNull(Me.txtPass.Value) Then
        MsgBox ("Please enter a password")
        GoTo Exit_btnLogin_Click
    Else
        stPassword = Me.txtPass.Value
    End If

    stSQL = "SELECT * FROM tblOwner"

    Set rs = db.OpenRecordset(stSQL)

    rs.MoveFirst
    Do Until rs.EOF
        If rs!OwnerID = stOwner Then
            stConfirm = rs!Password
        End If
        rs.MoveNext
    Loop

    If stPassword <> stConfirm Then
        MsgBox "Please enter a valid password."
        Exit Sub
    End If
    If stPassword = stConfirm Then
        stLinkCriteria = "[OwnerID] = " & stOwner
        stDocName = "frmMain"
        DoCmd.Close

        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If
'==========


mmcdonal

when i try to open the next record then get an error message" either BOF or EOF is true or the current record is deleted, the process needs a current record".

I have one question relating the find method. how to implement the DAO find methods in ADO. Did I convert them correctly in my code. If not could you please tell me the syntax or findfirst,last,next and previous.

thanks in advance

First of all, if you are passing the PK for the User, then there should only bo one record, so you can't scroll forward or backwards through it. So since you passed the criteria with your SELECT statement, you should either have one record (user found) or 0 records (no user found) so you should be applying criteria to the user PK. You could apply criteria to another field, but all you are doing is confirming the user PK, and the password.

Therefore if the RecordCount <> 0, you found your user.

If the password field value on the form matches the password field value in the table, then it is the proper password for that user.

No record parsing is necessary. So don't apply criteria after you have your recordset. It has already been applied in your SELECT statement.

HTH


mmcdonal