Too few parameters error in db.OpenRecordset

I am getting this error:
Runtime Error 3061
Too few parameters. Expected 1

at the line:
Set rstSector = db.OpenRecordset(strSQL, dbOpenDynaset)

When I run this code :

thisSector = lstSectors.Column(1)
Dim db As Database, rstSector As Recordset, strSQL As String

strSQL = "SELECT Reps.Email
FROM (Reps INNER JOIN Companies
ON Reps.CompanyID = Companies.CompanyID)
INNER JOIN Sector ON Companies.SectorID = Sector.SectorID
WHERE (Sector.SectorName)=thisSector"

Set db = CurrentDb()
Set rstSector = db.OpenRecordset(strSQL, dbOpenDynaset)

I would be grateful if someone could tell me where I am going wrong.
Thanks
Reg

Try changing your code to:

Dim db As Database, rstSector As DAO.Recordset, strSQL As String

I'm guessing that you have a reference to both ADO and DAO, and that the ADO
reference is higher in the list. When that's the case, simply using As
Recordset will result in an ADO recordset, whereas you need a DAO recordset.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Reg" wrote in message
...
I am getting this error:
Runtime Error 3061
Too few parameters. Expected 1

at the line:
Set rstSector = db.OpenRecordset(strSQL, dbOpenDynaset)

When I run this code :

thisSector = lstSectors.Column(1)
Dim db As Database, rstSector As Recordset, strSQL As String

strSQL = "SELECT Reps.Email
FROM (Reps INNER JOIN Companies
ON Reps.CompanyID = Companies.CompanyID)
INNER JOIN Sector ON Companies.SectorID = Sector.SectorID
WHERE (Sector.SectorName)=thisSector"

Set db = CurrentDb()
Set rstSector = db.OpenRecordset(strSQL, dbOpenDynaset)

I would be grateful if someone could tell me where I am going wrong.
Thanks
Reg

Try Sector.SectorName='thisSector'

HTH;

Amy

"Reg" wrote in message
...
I am getting this error:
Runtime Error 3061
Too few parameters. Expected 1

at the line:
Set rstSector = db.OpenRecordset(strSQL, dbOpenDynaset)

When I run this code :

thisSector = lstSectors.Column(1)
Dim db As Database, rstSector As Recordset, strSQL As String

strSQL = "SELECT Reps.Email
FROM (Reps INNER JOIN Companies
ON Reps.CompanyID = Companies.CompanyID)
INNER JOIN Sector ON Companies.SectorID = Sector.SectorID
WHERE (Sector.SectorName)=thisSector"

Set db = CurrentDb()
Set rstSector = db.OpenRecordset(strSQL, dbOpenDynaset)

I would be grateful if someone could tell me where I am going wrong.
Thanks
Reg

Reg:

Concatenate the value of the thisSector variable into the string expression.
If it’s a number:

WHERE (Sector.SectorName)=" & thisSector

Or if thisSector is text:

WHERE (Sector.SectorName)=""" & thisSector & """"

Ken Sheridan
Stafford, England

"Reg" wrote:

I am getting this error:
Runtime Error 3061
Too few parameters. Expected 1

at the line:
Set rstSector = db.OpenRecordset(strSQL, dbOpenDynaset)

When I run this code :

thisSector = lstSectors.Column(1)
Dim db As Database, rstSector As Recordset, strSQL As String

strSQL = "SELECT Reps.Email
FROM (Reps INNER JOIN Companies
ON Reps.CompanyID = Companies.CompanyID)
INNER JOIN Sector ON Companies.SectorID = Sector.SectorID
WHERE (Sector.SectorName)=thisSector"

Set db = CurrentDb()
Set rstSector = db.OpenRecordset(strSQL, dbOpenDynaset)

I would be grateful if someone could tell me where I am going wrong.
Thanks
Reg

Thanks for the suggestions everyone.
This last solution fixed it for me.
Thanks a lot Ken!

Reg


"Ken Sheridan" wrote in message
...
Reg:

Concatenate the value of the thisSector variable into the string
expression.
If it's a number:

WHERE (Sector.SectorName)=" & thisSector

Or if thisSector is text:

WHERE (Sector.SectorName)=""" & thisSector & """"

Ken Sheridan
Stafford, England

"Reg" wrote:

I am getting this error:
Runtime Error 3061
Too few parameters. Expected 1

at the line:
Set rstSector = db.OpenRecordset(strSQL, dbOpenDynaset)

When I run this code :

thisSector = lstSectors.Column(1)
Dim db As Database, rstSector As Recordset, strSQL As String

strSQL = "SELECT Reps.Email
FROM (Reps INNER JOIN Companies
ON Reps.CompanyID = Companies.CompanyID)
INNER JOIN Sector ON Companies.SectorID = Sector.SectorID
WHERE (Sector.SectorName)=thisSector"

Set db = CurrentDb()
Set rstSector = db.OpenRecordset(strSQL, dbOpenDynaset)

I would be grateful if someone could tell me where I am going wrong.
Thanks
Reg