Trying to write a select statement in VBA; Access 2002

[Jim H]

Hi, looking for a little help. I'm writing some code in VBA, (database is
Access 2002). What I want to do is a select statement where I have the
"Where" value set to a variable. What I'm writing is this:

Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb()

strSQL = " SELECT DISTINCTROW tblBADataEntry.* "
strSQL = strSQL + "FROM tblBADataEntry "
strSQL = strSQL + "WHERE tblBADataEntry.[Request ID]= " &
txtSelectedProject
Set ds = db.CreateDynaset(strSQL)

Wondering if I have to set a particular reference to allow this to work and
if so which one? In my globals I've declared ds as a dynaset but when I
compile I also get an error message that says Dynaset a user defined type not
defined.

How can I do a select statement like this??

thanks,
Jim

 

[Ed Metcalfe]

Hi, looking for a little help. I'm writing some code in VBA, (database is
Access 2002). What I want to do is a select statement where I have the
"Where" value set to a variable. What I'm writing is this:

Dim db As DAO.Database
Dim strSQL As String
Set db = CurrentDb()

strSQL = " SELECT DISTINCTROW tblBADataEntry.* "
strSQL = strSQL + "FROM tblBADataEntry "
strSQL = strSQL + "WHERE tblBADataEntry.[Request ID]= " &
txtSelectedProject
Set ds = db.CreateDynaset(strSQL)

Wondering if I have to set a particular reference to allow this to work
and
if so which one? In my globals I've declared ds as a dynaset but when I
compile I also get an error message that says Dynaset a user defined type
not
defined.

How can I do a select statement like this??

thanks,
Jim

Try:

Dim ds as DAO.Recordset
Set ds = db.OpenRecordset(strSQL, dbOpenDynaset)

Ed Metcalfe.