Avatar of Ernest Grogg
Ernest Grogg
 asked on

VBA Delete sql "Too Few Parameters"


Not sure what I am missing here:  Says "Too Few Parameters. Expected 1"

Dim strSql As String

strSql = "DELETE tblMakeVehicle.Make " & vbCrLf & _
"FROM tblMakeVehicle " & vbCrLf & _
"WHERE (((tblMakeVehicle.Make)=[forms]![frmMakeVehicle_Edit]![frmMakeVehicle_Edit_subform].[Form]![cboMakeEdit]));"

CurrentDb.Execute strSql, dbFailOnError

Open in new window

Microsoft AccessVBA

Avatar of undefined
Last Comment
Ernest Grogg

8/22/2022 - Mon
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

"WHERE (((tblMakeVehicle.Make)=EVAL("[forms]![frmMakeVehicle_Edit]![frmMakeVehicle_Edit_subform].[Form]![cboMakeEdit]") ));"

Wrap your Form Reference in the Eval() function  and note the Double Quotes
Ernest Grogg

Sorry but just gives me the:

Expected End of Statement at: [forms
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Ernest Grogg

That did it...

Just a question, I just don't understand the statement with the Eval?
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

So ... when you use CurrentDB. Execute or CurrentDB.OpenRecordset etc ... and the SQL has a Forms Reference like you do, JET does not understand that reference at all.  Note this is different that if you that a Forms reference in a Stored Query and you just run the query or use it in say a RecordSource or Row Source.

EVAL() gets around this issue as it turns out ... a bit of an old trick.

You can also use the Parameters property in the code itself ... but that required more lines of code.
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Here is another explanation I found in my notes - pretty much the same thing:
This shows how to user the Parameters gig ...

The reason for the Too Few Parameters error is because ... CurrentDb.Execute sends the query SQL directly to JET, which does not 'see' your Forms.  Consequently, it does not know what Forms!SomeFormName!SomeControlName is, resulting in the Parameter error message ... just like if you were running a SELECT query, and misspelled a Form or Field name used in a criteria expression.

However, VBA does "see" your Forms, so that you can (and need to) evaluate the parameters in VBA before running the query using the Execute Method.

This can be done as follows:

Function mRunActionQuery(sYourActionQryName As String) As Long
    Dim parm As DAO.Parameter
    With CurrentDb.QueryDefs(sYourActionQryName)
        For Each parm In .Parameters
            parm.Value = Eval(parm.Name)   'evaluates the parameters here
        Next parm
        .Execute dbFailOnError    ' add error trapping
       'Optional ...        
        mRunActionQuery = .RecordsAffected      ' Function also returns the number of records affected as a bonus
    End With
End Function

And your action query SQL might look like this:

SET Table1.FIELD0 = "Update Test"
WHERE Table1.FIELD1=[Forms]![Form9].[txtTest]
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

One more similar explanation

Courtesy of EE's mbizup:

"References to form controls work fine in stored queries.  If you take that same query and open it through VBA, the same form reference that worked before will cause an error -- unless you evaluate that reference seperately.  This holds true for OpenRecordset statements, and also CurrentDB.Execute and Docmd.RunSQL."
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Ernest Grogg

thanks so much!