Help! Too Few parameters. Expected 3.

[Ben Adams]

Trying to Export items from a Query "CalenderExport Q" (See Query Below) that
uses imputs from a combo box in a form. Using a function to export the
records to Outlook - However, When it gets to Set rst =
oDataBase.OpenRecordset("CalendarExport Q"), it gives me an error "Too Few
parameters. Expected 3." How can I have it call the records that show when I
open the query?

Function ExportCalendartoOutlook()

Dim oDataBase As DAO.Database
Dim rst As DAO.Recordset
Set oDataBase = CurrentDb
Set rst = oDataBase.OpenRecordset("CalendarExport Q")

More code here, doesn't matter.....

End Function


CalendarExport Q

SELECT [Master Planned].Date, [Activity] & " / " & [Vendor] & " / " &
[Customer Forecasts]!Name AS Subject, [Master Planned].Comments AS Body,
"Sales Plan" AS Category
FROM [Branch Master] LEFT JOIN ([Customer Forecasts] RIGHT JOIN [Master
Planned] ON [Customer Forecasts].[Customer ID] = [Master Planned].[Customer
ID]) ON [Branch Master].[Branch ID] = [Master Planned].[Branch ID]
WHERE ((([Master Planned].Date) Is Not Null) AND
((IIf([Forms]![CalendarExport]![Vendor] Is Not
Null,[Vendor]=[Forms]![CalendarExport]![Vendor],[Vendor] Is Not
Null))<>False) AND ((IIf([Forms]![CalendarExport]![Activity] Is Not
Null,[Activity]=[Forms]![CalendarExport]![Activity],[Activity] Is Not
Null))<>False) AND ((IIf([Forms]![CalendarExport]![Salesperson] Is Not
Null,[Salesperson]=[Forms]![CalendarExport]![Salesperson],[Salesperson] Is
Not Null))<>False))
ORDER BY [Master Planned].Date;

 

[Brendan Reynolds]

To programmatically open a recordset based on a parameter query, you have to
open a QueryDef on the query, assign values to the parameters, then use the
OpenRecordset method of the QueryDef object instead of the more commonly
used OpenRecordset method of the Database object. The following is untested,
from memory, but hopefully should serve to get you started ..

Dim db As DAO.Database
dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef

Set db = CurrentDb
set qdf = db.OpenQueryDef("SomeParameterQuery")
qdf.Parameters(0) = SomeValue
qdf.Parameters(1) = SomeOtherValue
set rst = qdf.OpenRecordset

 

[Michel Walsh]

Hi,


The syntax FORMS!formName!ControlName works only is some circumstances,
like when using the Designer, when using DoCmd, when used as rowSource or as
recordSource, when used in DXXX functions (DLookup, DMax, ... ) BUT does
not work when used with CurrentDb.

When using CurrentDb, you have to resolve the parameters YOURSELF like:


Dim p As Param
Dim q As queryDef

Set
q=CurrentDb.QueryDefs("[mySavedQueryWIthFORMS!FormName!ControlNameSyntax]")

For each p in q.Parameters
p.Value = eval(p.Name)
Next p


Set rst = q.OpenRecordset( ... )


that works for parameters where the value can be obtained through their
name, like those FORMS!MyForm!MyControl, but won't work if you have other
kind of parameters name, like [Enter the day, please: ]


See also http://www.mvps.org/access/queries/qry0013.htm



Hoping it may help,
Vanderghast, Access MVP