Too few paramaters. Expected 2 in OpenRecordset

[Timax]

Hi, I have done this million times with no problem and this simple statement gives me problem "Too few parameters. Expected 2:. What am I missing here?

Dim sSQL As String
Dim db As Database
Dim rs As Recordset

sSQL = "SELECT EmailsPeopleM.JobID, EmailsPeopleM.CustName, EmailsPeopleM.[First Name], EmailsPeopleM.[Last Name], EmailsQtyUnion.PartNumber, EmailsQtyUnion.Description, EmailsQtyUnion.Rev, EmailsQtyUnion.InitialQty, EmailsQtyUnion.StateName, EmailsQtyUnion.RealQty, EmailsPeopleM.email, EmailsPeopleM.CustID FROM EmailsPeopleM LEFT JOIN EmailsQtyUnion ON EmailsPeopleM.JobID = EmailsQtyUnion.JobID;"

Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)

Query works fine by itself... Please guide me here.

 

[arnelgp]

change these two to:

Dim db As DAO.Database
Dim rs As DAO.Recordset

 

[Timax]

Thank you but same problem. Did not help

 

[arnelgp]

how about querydef:

with currentdb.createquerydef("", sSQL)
Set rs=.OpenRecordset(dbOpenDynaset)
End With

 

[The_Doc_Man]

Are there any real line breaks in the string declaration?

 

[Gasman]

Hi, I have done this million times with no problem and this simple statement gives me problem "Too few parameters. Expected 2:. What am I missing here?

Dim sSQL As String
Dim db As Database
Dim rs As Recordset

sSQL = "SELECT EmailsPeopleM.JobID, EmailsPeopleM.CustName, EmailsPeopleM.[First Name], EmailsPeopleM.[Last Name], EmailsQtyUnion.PartNumber, EmailsQtyUnion.Description, EmailsQtyUnion.Rev, EmailsQtyUnion.InitialQty, EmailsQtyUnion.StateName, EmailsQtyUnion.RealQty, EmailsPeopleM.email, EmailsPeopleM.CustID FROM EmailsPeopleM LEFT JOIN EmailsQtyUnion ON EmailsPeopleM.JobID = EmailsQtyUnion.JobID;"

Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)

Query works fine by itself... Please guide me here.

What about the query this sql is getting it's data from?

 

[arnelgp]

gasman you are right, it might be qbf.

 

[Timax]

Same result with QueryDef... Can't believe this. Query gets data from SQL Server originally. It's going thru multiple layers of query including union query but query itself works by itself as I mentioned... What else can it be? Can't believe I stock on this simple statement.

 

[Timax]

Are there any real line breaks in the string declaration?

No, it's simple 2 query joins... Nothing fancy.No even criteria in final query

 

[pbaldy]

If there are form references in any of the queries, OpenRecordset will not be able to resolve them. Simplest solution is probably to to wrap each in the Eval() function.

 

[Timax]

Thank you Paul. Yes, there is a form reference inside of precursor query but not in openrecordset statement. Would that still be a problem? Also, I never used this Eval() function. Do I need to use it wrapping form reference?

 

[Galaxiom]

Yes, there is a form reference inside of precursor query but not in openrecordset statement. Would that still be a problem?

Definitely. OpenRecordset is a Method of the database, not the Application so the SQL is sent directly to the engine without interpreting any references.

Also, I never used this Eval() function. Do I need to use it wrapping form reference?

Eval() is an Application function which can be called from the database. It understands the form reference and returns a value.

 

[Timax]

Eval() did not help. I wrapped references with Eval() and query works fine but still same error in OpenRecordset... What else can I try?

 

[Timax]

Form is open. I researched that one first

 

[Gasman]

Use Tempvars? in the precursor query?

 

[Timax]

Dim db As ADO.Database
Dim rs As ADO.Recordset
Set db = CurrentDb
sql = SQLStatement
Set rs = db.OpenRecordset(sql, dbOpenSnapshot)

Anything is wrong here? sql query is no problem when open outside of the function

 

[Timax]

No, no tempvars in presecur. standard form references.

 

[Gasman]

What I was suggesting was to USE Tempvars as the parameters instead of the form references. ?

That way the form does not have to be open.?

That is an approach I have used when I wanted a query to be used from several forms.?

 

[Timax]

Can't do that. I use form references in queries that are way before this final query I use in openrecordset. Form is open. If I use debug.print and put this into query, everything works.

 

[Timax]

I resolved this by replacing form references with TempVars and it worked Thank you all!!!!!