VBA SQL Error Message

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

I have a report that runs a VBA routine from the OnOpen
event. One of the things this VBA routine does is gather
information and calculate a value to be placed into a
particular unbound field on the report. To do this I
initiate a Querydef object which calls a query that is in
the Query Tab of the database. This query has several
subqueries. The very first query initiated has a parameter
which pulls information from the form from which the
report is generated. From the query window everything
works fine, when I try to execute it from VBA, from the
OnOpen event I get an error that says Too few parameters,
1 expected. Generally when I have seen this in the past it
is because I am trying to run a parameter query directly
from within VBA. The SQL statement is in VBA. In this
case, that is not happening. I call an external query.

Any suggestions? Thanks in advance for the help!

Kevin
 
Hi,

The query designer resolves the parameter of the form:
Forms!FormName!ControlName, same with DoCmd, but NOT with CurrentDb.
CurrentDb sees those as unresolved parameter, unless you explicitly assign
the Parameters collection:

Dim qdf As QueryDef
Set qdf=CurrentDb.QueryDefs("query name here")

Dim Param As DAO.Parameter

For Each Param In qdf.Parameters
Param.Value = eval(Param.Name) '<<
Next Param


Set rst= qdf.OpenRecordset( ... )


would solve the parameters if their NAME is in the form
FORMS!FormName!ControlName



Hoping it may help,
Vanderghast, Access MVP
 
-----Original Message-----
Hi,

The query designer resolves the parameter of the form:
Forms!FormName!ControlName, same with DoCmd, but NOT with CurrentDb.
CurrentDb sees those as unresolved parameter, unless you explicitly assign
the Parameters collection:

Dim qdf As QueryDef
Set qdf=CurrentDb.QueryDefs("query name here")

Dim Param As DAO.Parameter

For Each Param In qdf.Parameters
Param.Value = eval(Param.Name) '<<
Next Param


Set rst= qdf.OpenRecordset( ... )


would solve the parameters if their NAME is in the form
FORMS!FormName!ControlName



Hoping it may help,
Vanderghast, Access MVP






.
Click to expand...
 
Thanks!!
-----Original Message-----
Hi,

The query designer resolves the parameter of the form:
Forms!FormName!ControlName, same with DoCmd, but NOT with CurrentDb.
CurrentDb sees those as unresolved parameter, unless you explicitly assign
the Parameters collection:

Dim qdf As QueryDef
Set qdf=CurrentDb.QueryDefs("query name here")

Dim Param As DAO.Parameter

For Each Param In qdf.Parameters
Param.Value = eval(Param.Name) '<<
Next Param


Set rst= qdf.OpenRecordset( ... )


would solve the parameters if their NAME is in the form
FORMS!FormName!ControlName



Hoping it may help,
Vanderghast, Access MVP






.
Click to expand...
 
Back
Top