Execute Method Failure

  • Thread starter Thread starter TC
  • Start date Start date
T

TC

I don't have Access here to check, but I suspect you will need to set the
parameters using the Parameters collection of the Querydef object, then use
the Execute method of that object. Something like:

set db = currentdb()
set qd = db.querydefs![myQry]
qd.parameters![myPrm1] = 11
qd.parameters![myPrm2] = "xyz"
qd.execute
set qd = nothing
set db = nothing

HTH,
TC
 
Given an update query (myQry) that references a form for
two parameters (ie forms!myForm.Field1, etc). Executing
the following code:

set db = CurrentDb()
db.execute "myQry", dbFailOnError

Produces Error 3061 - Too Few Parameters, expected 2.

With myForm open, double-clicking myQry in the database
window will execute it without error. If myForm is
closed, myQry will prompt for the values as expected, and
run without error.

What's up? Is this a bug?
 
Thanks for the response.

My question is not how to get it to work, but why doesn't
it as written (and documented).

Note that the form references are resolved correctly when
run directly, but not when run via the execute method.
This seems inconsistant to me. Granted, many things from
Redmond are inconsistant.......

Has anyone observed this behavior before? I can't find
anything in the knowledgebase about it.
 
It sounds like your action query refers to text boxes on an open form, e.g.:
WHERE [SomeField] = [Forms]![MyForm]![MyTextbox]
If you run this query from the query design window, the Expression Service
resolves the text box reference if the form is open. It it is not, the ES
assumes that the value is to be supplied at runtime as a parameter.

The Execute method cannot use the Expression Service like that.

Instead of using a saved query, build the SQL statement as a string variable
in your code. Concatenate the value from the form into the string. You will
then be able to execute the string.

Dim strSql As String
strSQL = "INSERT INTO ... WHERE [SomeField] = " & _
Forms![MyForm]![MyTextbox] & ";"
db.Execute strSQL, dbFailOnError

If you prefer to use a parameter, you can assign a value to the Parameters
collection of the QueryDef.
 
Bill,
this is not a bug. as you are using DAO library (jet itself) to execute your
query it does not know about your access forms. so you get error message
to make it runnnig like you want you have to use Docmd.RunSQL method - this
is access method, it knows about forms

HTH
 
Back
Top