Trying to write a select statement in VBA; Access 2002

J

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
 
E

Ed Metcalfe

Jim H said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top