Results 1 to 6 of 6
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    A simple DAO Recordset Question (A2K SR1)

    I am testing a very simple logon form for a database. Usernames and Passwords are stored in a table. On Database Startup, the user inputs their Username and Password on a form and presses a Command Button named Verify. If the Username and Password are correct, they receive a good message, if incorrect, bad message.

    Here is the problem.

    If a correct Username and ID are entered, everything works fine. The If Then statement provides the message box "Identify Verified"

    HOWEVER - If the username and ID are incorrect, I get Run Time Error 3021, No Current Record.
    *** Why Does this happen. If the username and Id are incorrect, why won't the If Then statement evaluate to the "Incorrect" Statement.

    Please don't read into using Access Security, I am just trying a simple example to better understand DAO recordsets. (Of course, ADO is even more confusing, however, I may try this next....)

    Thanks,


    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSql As String, Name As String, Pword As String

    ' Code checks a UserName and Password input in Form.LogonForm
    ' against a Username and Password stored in a table.


    ' Get Logon Form Parameters for UserName and Password from LogonForm
    Name = Forms!LogonForm!InputUsername
    Pword = Forms!LogonForm!InputPassword

    ' Set strSql statement to get recordset

    strSql = "Select * from [StoreLogon]" _
    & " Where [Username] = '" & Name & "' and [Password] = '" & Pword & "';"


    Set db = CurrentDb()
    Set rst = db.OpenRecordset(strSql)

    'Provide message if Username and ID IS or IS NOT Valid as Validated against Table.StoreLogon


    If rst![Username] = Name And rst![Password] = Pword Then
    MsgBox "Identity Verified"
    Else
    MsgBox "Userid and Password Incorrect, Try Again"
    End If

    rst.Close
    Set rst = Nothing
    Set db = Nothing

    End Sub
    Regards,

    Gary
    (It's been a while!)

  2. #2
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A simple DAO Recordset Question (A2K SR1)

    Gary,

    the problem is your SQL and If Then statements

    strSql = "Select * from [StoreLogon]" _
    & " Where [Username] = '" & Name & "' and [Password] = '" & Pword & "';"

    If rst![Username] = Name And rst![Password] = Pword Then

    Your SQL is asking for U/N and P/W Matches. You will either have 1 or 0 records returned.

    If the UN and PW match, you will have 1 record, which will make the the IF Then work.

    If they don't match, then you won't have any records returned, giving you the 3021 error on the If Then Line.

    You can get around this one of two ways:

    1) Change your SQL to:
    strSql = "Select * from [StoreLogon]" _
    & " Where [Username] = '" & Name & "'';"

    OR

    2) Change your If Then statement

    If rst.RecordCount = 1 then
    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: A simple DAO Recordset Question (A2K SR1)

    Thanks Bryan,

    What you say makes sense.

    I will try suggestion 2 since if there are multiple people with the same username, suggestion 1 may cause some problems.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: A simple DAO Recordset Question (A2K SR1)

    Bryan,

    Solution 2 worked fine (Thanks). One last question.

    How can you show the results of the current recordset? I can't show it via a message box or debug.print. For example, to see what was in the recordset, I tried MSGBOX RST which of course did not work.

    I guess I could write the data to a table, however, was wondering if there was a way to review the data without writing it.
    Regards,

    Gary
    (It's been a while!)

  5. #5
    5 Star Lounger
    Join Date
    Jul 2002
    Location
    Toronto, Ontario, Canada
    Posts
    1,139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A simple DAO Recordset Question (A2K SR1)

    Gary,

    You have to loop through all the records and then all the fields

    <pre>Dim fld As DAO.Field

    Do While Not (rst.EOF)
    For Each fld In rst.Fields
    Debug.Print fld.Name & vbTab & fld
    Next
    rst.MoveNext
    Loop
    </pre>

    --
    Bryan Carbonnell - Toronto <img src=/S/flags/Ontario.gif border=0 alt=Ontario width=30 height=18> <img src=/S/flags/Canada.gif border=0 alt=Canada width=30 height=18>
    Unfortunately common sense isn't so common!!
    Visit my website for useful Word, Excel and Access code, templates and Add-Ins

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: A simple DAO Recordset Question (A2K SR1)

    Another suggestion here:
    whenever you create a recordset test for .eof before doing any processing with the recordset

    e.g. if not RST.EOF then


    EOF means end of file, and will be true if there are no records in the recordset.
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •