Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Can't Create Recordset

Discussion in 'Information Technology' started by BenL712, Nov 3, 2005.

  1. BenL712

    BenL712
    Expand Collapse
    Guest

    I'm new to programming Access, and I'm having a big problem creating a
    recordset. This is a very basic (and not very secure) login form, and I need
    to search a table of user ID's and passwords to verify the entry. I do not
    want to use the standard Access security features.

    I've read through several of the posts so far, but none seem to be helping
    me. My source code is:

    Dim myDB As Database
    Dim myQry As QueryDef
    Dim rsLogin As Recordset
    Dim myUserName As String
    Dim myPassword As String
    Dim chkUserName As String
    Dim chkPassword As String
    Dim chkUserRole As String
    Dim mySQL As String

    myUserName = Forms![frm_Login].[ent_UserName]
    myPassword = Forms![frm_Login].[ent_Password]

    chkUserName = ""
    chkPassword = ""
    chkUserRole = ""

    mySQL = "SELECT * FROM tbl_Security WHERE EmployeeID = '" & myUserName & "'
    AND Password = '" & myPassword & "'"

    MsgBox mySQL

    Set myDB = CodeDb()

    MsgBox "Set DB worked"

    Set myQry = myDB.CreateQueryDef("", mySQL)

    MsgBox "Create querydef worked"

    Set rsLogin = myQry.OpenRecordset()

    MsgBox "Open recordset worked"

    rsLogin.MoveFirst

    MsgBox "Move to first record worked"

    Do While Not rsLogin.EOF

    chkUserName = rsLogin!Fields!EmployeeID.Value
    chkPassword = rsLogin!Fields!Password.Value
    chkUserRole = rsLogin!Fields!UserType.Value

    MsgBox chkUserName, vbOKOnly
    MsgBox chkPassword, vbOKOnly
    MsgBox chkUserRole, vbOKOnly

    Loop

    .....


    I've resolved the form inputs into a text string for the SQL based on other
    posts I read. This seems to have gotten me past the "Expected 1 parameter"
    error, but now I get an error of "Type Mismatch" when I try to open the
    recordset. I've tried the open recordset method several different ways, and
    I've written the SQL using Like, =, "", and ''. Nothing seems to get past
    however.

    Any help that can be provided would be tremendously appreciated.

    Thanks!
     
  2. Loading...


  3. Dirk Goldgar

    Dirk Goldgar
    Expand Collapse
    Guest

    "BenL712" <BenL712@discussions.microsoft.com> wrote in message
    news:5D44D21D-B7D1-4321-BC93-6C09628846C0@microsoft.com
    > I'm new to programming Access, and I'm having a big problem creating a
    > recordset. This is a very basic (and not very secure) login form,
    > and I need to search a table of user ID's and passwords to verify the
    > entry. I do not want to use the standard Access security features.
    >
    > I've read through several of the posts so far, but none seem to be
    > helping me. My source code is:
    >
    > Dim myDB As Database
    > Dim myQry As QueryDef
    > Dim rsLogin As Recordset
    > Dim myUserName As String
    > Dim myPassword As String
    > Dim chkUserName As String
    > Dim chkPassword As String
    > Dim chkUserRole As String
    > Dim mySQL As String
    >
    > myUserName = Forms![frm_Login].[ent_UserName]
    > myPassword = Forms![frm_Login].[ent_Password]
    >
    > chkUserName = ""
    > chkPassword = ""
    > chkUserRole = ""
    >
    > mySQL = "SELECT * FROM tbl_Security WHERE EmployeeID = '" &
    > myUserName & "' AND Password = '" & myPassword & "'"
    >
    > MsgBox mySQL
    >
    > Set myDB = CodeDb()
    >
    > MsgBox "Set DB worked"
    >
    > Set myQry = myDB.CreateQueryDef("", mySQL)
    >
    > MsgBox "Create querydef worked"
    >
    > Set rsLogin = myQry.OpenRecordset()
    >
    > MsgBox "Open recordset worked"
    >
    > rsLogin.MoveFirst
    >
    > MsgBox "Move to first record worked"
    >
    > Do While Not rsLogin.EOF
    >
    > chkUserName = rsLogin!Fields!EmployeeID.Value
    > chkPassword = rsLogin!Fields!Password.Value
    > chkUserRole = rsLogin!Fields!UserType.Value
    >
    > MsgBox chkUserName, vbOKOnly
    > MsgBox chkPassword, vbOKOnly
    > MsgBox chkUserRole, vbOKOnly
    >
    > Loop
    >
    > ....
    >
    >
    > I've resolved the form inputs into a text string for the SQL based on
    > other posts I read. This seems to have gotten me past the "Expected
    > 1 parameter" error, but now I get an error of "Type Mismatch" when I
    > try to open the recordset. I've tried the open recordset method
    > several different ways, and I've written the SQL using Like, =, "",
    > and ''. Nothing seems to get past however.
    >
    > Any help that can be provided would be tremendously appreciated.
    >
    > Thanks!


    1. Click Tools -> References... (in the VB Editor environment) and make
    sure the reference to "Microsoft DAO 3.6 Object Library" is checked. I
    think it probably is, or you'd be getting an error on "Dim myDB As
    Database".

    2. Change these declarations ...

    > Dim myDB As Database
    > Dim myQry As QueryDef
    > Dim rsLogin As Recordset


    to these ...

    Dim myDB As DAO.Database
    Dim myQry As DAO.QueryDef
    Dim rsLogin As DAO.Recordset

    Technically, you only really need to change the recordset declaration,
    because the other DAO objects are unique, but a Recordset object is
    defined in both the DAO and the ADO libraries.

    Incidentally, you should be aware that you don't really need to use a
    QueryDef object to open the recordset. You could just as easily have
    written:

    Set rsLogin = myDB.OpenRecordset(mySQL)


    --
    Dirk Goldgar, MS Access MVP
    www.datagnostics.com

    (please reply to the newsgroup)
     

Share This Page