VBA Delete sql "Too Few Parameters"

"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

Sorry but just gives me the:

Expected End of Statement at: [forms

That did it...

Just a question, I just don't understand the statement with the Eval?

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.

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:

UPDATE Table1
SET Table1.FIELD0 = "Update Test"
WHERE Table1.FIELD1=[Forms]![Form9].[txtTest]

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."

thanks so much!