to few parameters? Expecting 1?

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

OK, Strange problem here.

I have a bunch of reports that are actually letters. On
the close of the report I offer the option to update a
field in every record.

The way that I do this, and it might not be the BEST way
is that I have a query that I call and update vie code.

I have many modifications of this report/query combination
all of which work fine.

I am trying to make a NEW variation of the above only to
suddenly receive an error that makes no sense (to me).
The error indicates that I have to few parameters, and
that I need one. Strangely only in this instance do I get
the error.

Set dbs = currentDB
Set rst = dbs.OpenRecordset("MyQueryNameHere") 'Where the
error occures

The only thing that I could imagine is causing the problem
is that this query looks to a form for one of its
selection criteria. This only seems to be a problem in
the module, if I break the code at the point of this error
and manually run the query it returns the desired record
set!

I have at least 8 other instances where this works! I
can't figure out the difference otherwise!

Any idea what else could be causing this??

Rick
 
Rick said:
OK, Strange problem here.

I have a bunch of reports that are actually letters. On
the close of the report I offer the option to update a
field in every record.

The way that I do this, and it might not be the BEST way
is that I have a query that I call and update vie code.

I have many modifications of this report/query combination
all of which work fine.

I am trying to make a NEW variation of the above only to
suddenly receive an error that makes no sense (to me).
The error indicates that I have to few parameters, and
that I need one. Strangely only in this instance do I get
the error.

Set dbs = currentDB
Set rst = dbs.OpenRecordset("MyQueryNameHere") 'Where the
error occures

The only thing that I could imagine is causing the problem
is that this query looks to a form for one of its
selection criteria. This only seems to be a problem in
the module, if I break the code at the point of this error
and manually run the query it returns the desired record
set!

I have at least 8 other instances where this works! I
can't figure out the difference otherwise!
Click to expand...


It sounds like the querys where it works do not have a
reference to a form control.

The issue is that the VBA environment does not automatically
take care of query parameter resolution the way Access does
in record source, row source, etc. So, you have to do it in
your code:

Dim qdf As QueryDef
Dim parm As Parameter
Set dbs = currentDB
Set qdf = dbs.QueryDefs("MyQueryNameHere")
For Each parm in qdf.Parameters
parm.Value = Eval(parm.Name)
Next
Set rst = qdf.OpenRecordset(dbOpenDynaset)

I don't know what you're doing to the records in the
recordset once you get it to open, but, if possible, you
should consider executing an Update query instead:

Dim strSQL As String
strSQL = "UPDATE table " _
& " SET field = " & somevalue _
& " WHERE somefield =" & something
dbs.Execute strSQL, dbFailOnError
 
That hit the nail right on the head. Tried it as you
said, nearly exact code and it worked!

Thanks so much, I really appreciate the help!

Rick
Ohio
 
Back
Top