Query that is used in the openrecordset method

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

David9746 - 29 Nov 2007 21:16 GMT

I have a query that I am using in a sub procedure and I am opening a
recordset using that query as the recordsource. The query has a parameter
that references a field on the form that the sub procdure is run from.

Problem is I am getting an error message all the time that says too few
parameters expected 1. When you run this query on it's own it runs fine no
problem.

Can anyone help me on this.

Reply to this Message

Dale Fye - 29 Nov 2007 21:21 GMT

Can you post the SQL?

Signature

Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

> I have a query that I am using in a sub procedure and I am opening a
> recordset using that query as the recordsource. The query has a parameter
[quoted text clipped - 5 lines]
>
> Can anyone help me on this.

Reply to this Message

David9746 - 29 Nov 2007 21:31 GMT

I can but it is a pretty large sql statement consisting of other querries.

I wiil post it a s soon my working partner is done to the daily backups

> Can you post the SQL?
>
[quoted text clipped - 7 lines]
> >
> > Can anyone help me on this.

Reply to this Message

David9746 - 29 Nov 2007 21:44 GMT

SELECT Date() AS [Report Date],
q_Rdy_ConvDate_ClientCateg_Summ.conv_date_mod,
q_Rdy_ConvDate_ClientCateg_Summ.Clients, Null AS Profiled, Null AS
ProvidedDoc, Null AS [Not submitted], Null AS IBTfund,
q_Rdy_ConvDate_BeneCateg_Summ.SSCfund, [SSCfund]-[NotExported] AS
BeneExported, q_Rdy_ConvDate_BeneCateg_Summ.NotExported AS BeneNotExported,
([SSCfund]-[NotExported])/[SSCfund] AS BeneExportComplete,
[NotExported]/[SSCfund] AS BeneExportRemaining, [SSCfund]-[GBEpend] AS
GBEComplete, ([SSCfund]-[GBEpend])/[SSCfund] AS GBEsetupComplete,
q_Rdy_ConvDate_BeneCateg_Summ.GBEpend, [GBEpend]/[SSCfund] AS
GBEsetupRemaining, q_Rdy_ConvDate_MarketCateg_Summ.TotMkts,
q_Rdy_ConvDate_MarketCateg_Summ.TotDocMkts, [TotMkts]-[MktNotExported] AS
MktsExported, IIf([TotMkts]=0,1,([TotMkts]-[MktNotExported])/[TotMkts]) AS
MktExportComplete, q_Rdy_ConvDate_MarketCateg_Summ.MktNotExported,
IIf([TotMkts]=0,1,[MktNotExported]/[TotMkts]) AS MktExportRemaining,
[TotMkts]-[MktNotExported] AS [AOM Recvd],
([TotMkts]-[MktNotExported])-[LACpend] AS LACcomplete,
IIf([TotMkts]-[MktNotExported]=0,1,(([TotMkts]-[MktNotExported])-[LACpend])/([TotMkts]-[MktNotExported]))
AS LACsetupComplete, Null AS Cash, q_Rdy_ConvDate_MarketCateg_Summ.LACpend,
IIf(([TotMkts]-[MktNotExported])=0,1,[LACpend]/([TotMkts]-[MktNotExported]))
AS LACsetupRemaining, q_Rdy_ConvDate_MarketCateg_Summ.LACpendDocMkt,
IIf(([TotDocMkts]-[MktNotExported])=0,1,[LACpendDocMkt]/([TotDocMkts]-[MktNotExported]))
AS DocMktLACsetupRemaining, IIf([Forms]![Menu: Canned
Reports]![cmb_NewMktBeneRecords]="Yes","New Mkts","No") AS NewMkts
FROM (q_Rdy_ConvDate_ClientCateg_Summ INNER JOIN
q_Rdy_ConvDate_BeneCateg_Summ ON
q_Rdy_ConvDate_ClientCateg_Summ.conv_date_mod =
q_Rdy_ConvDate_BeneCateg_Summ.conv_date_mod) LEFT JOIN
q_Rdy_ConvDate_MarketCateg_Summ ON
q_Rdy_ConvDate_BeneCateg_Summ.conv_date_mod =
q_Rdy_ConvDate_MarketCateg_Summ.conv_date_mod
ORDER BY q_Rdy_ConvDate_ClientCateg_Summ.conv_date_mod;

> Can you post the SQL?
>
[quoted text clipped - 7 lines]
> >
> > Can anyone help me on this.

Reply to this Message

Dale Fye - 30 Nov 2007 01:50 GMT

David,

Try declaring the datatype of the parameter (form control) that you are
passing the query. You can do it by going into the SQL view and pasting the
following at the beginning of the SQL statement:

PARAMETERS [Forms]![Menu: Canned Reports]![cmb_NewMktBeneRecords] Text
(255);
SELECT ....

Make sure there is a semi-colon at the end of the Parameters line and the
SELECT statement begins on the line immediately following the PARAMETERS
line.

HTH
Dale

> SELECT Date() AS [Report Date],
> q_Rdy_ConvDate_ClientCateg_Summ.conv_date_mod,
[quoted text clipped - 44 lines]
>> >
>> > Can anyone help me on this.

Reply to this Message

Michel Walsh - 29 Nov 2007 21:36 GMT

A query with a parameter like FORMS!formName!controlName will run fine in
the user interface, or as rowsource of a list box, or even as record source
of another form... but won't run if you NEED to use CurrentDb to work with
it.

See http://www.mvps.org/access/queries/qry0013.htm for some more details.

Vanderghast, Access MVP

>I have a query that I am using in a sub procedure and I am opening a
> recordset using that query as the recordsource. The query has a parameter
[quoted text clipped - 5 lines]
>
> Can anyone help me on this.

Reply to this Message