Can't Create Recordset

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!
 
BenL712 said:
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!
Click to expand...

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
Click to expand...

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)
 
Back
Top