Too Few Parameters - Help!

[Amy E. Baggott]

Let's try this again (I accidentally hit <Enter> instead of <Shift>). I have
a dialog box that feeds parameters to a rather compex query that calculates
the priority points we award to our exhibitors. I used to just generate an
Excel file and send that to Data Services to be entered in another database
altogether, which then fed information back into Access. This year, my boss
decided we should simply be updating the data in Access and cut out the
middleman. However, when I try to turn the query into an Update or Append
query, it gives me an "Operation must use an updatable query" error. In
similar situations, I have always managed by using VBA code to open the query
as a recordset, open the table as a recordset, and updating the table from
the query that way. However, in this case, as soon as it tries to open the
query as a recordset, it gives me "Too few parameters (expected 4)". Is
there an easy way around this? I need to make these updates quickly so that
I can post the show just ended to history and start working on the next one,
for which we are already receiving applications.

 

[Douglas J. Steele]

If your query included parameters, you cannot use it as the source for a
recordset.

Instead, try something like:

Dim qdfCurr As DAO.QueryDef
Dim rsCurr As DAO.Recordset
Dim prmCurr As DAO.Parameter

Set qdfCurr = CurrentDb.QueryDefs("NameOfQuery")
For Each prmCurr In qdfCurr.Parameters
prmCurr.Value = Eval(prmCurr.Name)
Next prmCurr
Set rsCurr = qdfCurr.OpenRecordset

 

[Amy E. Baggott]

The parameters are actually in subqueries to the main query. How do I tell
what order they are in?
--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson

If your query included parameters, you cannot use it as the source for a
recordset.

Instead, try something like:

Dim qdfCurr As DAO.QueryDef
Dim rsCurr As DAO.Recordset
Dim prmCurr As DAO.Parameter

Set qdfCurr = CurrentDb.QueryDefs("NameOfQuery")
For Each prmCurr In qdfCurr.Parameters
prmCurr.Value = Eval(prmCurr.Name)
Next prmCurr
Set rsCurr = qdfCurr.OpenRecordset

 

[Douglas J. Steele]

Try using the code as I wrote it. If it doesn't work, post back with the SQL
of the queries involved.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The parameters are actually in subqueries to the main query. How do I
tell
what order they are in?

 

[Dirk Goldgar]

The parameters are actually in subqueries to the main query. How do I
tell
what order they are in?

Using Doug's code, you don't need to -- not if the parameters are form
references which can be evaluated.

 

[Amy E. Baggott]

It worked! Thank you!