error on this VERY VERY easy select statement

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

Guest

This simple stmnt has been giving me so many problems... what is wrong..
please help!! thanks!

Dim rsLogin As Recordset
Dim strUserName As String

Set db = CurrentDb
strUserName = CStr(cmbUserName.Value)
Set rsLogin = CurrentDb.OpenRecordset("SELECT * from tblUsers WHERE
[UserName] = '" & strUserName & "'")
 
oops.. i forgot to give error.. it's "Type mismatch"... also.. the UserName
field is defined as text in the table.. thanks
 
Just guessing that you're using ACCESS 2000 or 2002, and that you don't have
a reference set to DAO library. So your code is expecting rsLogin to be an
ADODB recordset, not a DAO recordset. You also don't declare db variable.


Dim db As DAO.Database
Dim rsLogin As DAO.Recordset
Dim strUserName As String

Set db = CurrentDb
strUserName = CStr(cmbUserName.Value)
Set rsLogin = CurrentDb.OpenRecordset("SELECT * from tblUsers WHERE
[UserName] = '" & strUserName & "'")
 
Thanks so much.. yea.. it worked.. i did declare the db in the option
explicit section.. but not with "DAO" proceeding.. this is the first time i'm
working with access 2002.. do you suggest i declare the db in a global
module.. rather than where i have it now.. in the form itself.. thanks again!
: )
 
I would not declare the db variable in a public declarations section. Too
easy to forget to set it to Nothing when the database closes, and then you
can end up with ACCESS still running when you try to shut it down.

I prefer to declare it within each procedure where I use the DAO.Database
object.

--

Ken Snell
<MS ACCESS MVP>

lpjennifer said:
Thanks so much.. yea.. it worked.. i did declare the db in the option
explicit section.. but not with "DAO" proceeding.. this is the first time i'm
working with access 2002.. do you suggest i declare the db in a global
module.. rather than where i have it now.. in the form itself.. thanks again!
: )

lpjennifer said:
This simple stmnt has been giving me so many problems... what is wrong..
please help!! thanks!

Dim rsLogin As Recordset
Dim strUserName As String

Set db = CurrentDb
strUserName = CStr(cmbUserName.Value)
Set rsLogin = CurrentDb.OpenRecordset("SELECT * from tblUsers WHERE
[UserName] = '" & strUserName & "'")
Click to expand...
Click to expand...
 
k.. thanks so much for the good advice! : )

Ken Snell said:
I would not declare the db variable in a public declarations section. Too
easy to forget to set it to Nothing when the database closes, and then you
can end up with ACCESS still running when you try to shut it down.

I prefer to declare it within each procedure where I use the DAO.Database
object.

--

Ken Snell
<MS ACCESS MVP>

lpjennifer said:
Thanks so much.. yea.. it worked.. i did declare the db in the option
explicit section.. but not with "DAO" proceeding.. this is the first time i'm
working with access 2002.. do you suggest i declare the db in a global
module.. rather than where i have it now.. in the form itself.. thanks again!
: )

lpjennifer said:
This simple stmnt has been giving me so many problems... what is wrong..
please help!! thanks!

Dim rsLogin As Recordset
Dim strUserName As String

Set db = CurrentDb
strUserName = CStr(cmbUserName.Value)
Set rsLogin = CurrentDb.OpenRecordset("SELECT * from tblUsers WHERE
[UserName] = '" & strUserName & "'")
Click to expand...
Click to expand...
Click to expand...
 
Back
Top