ACCESS FEED: RE: [MS_AccessPros] Error 3061 Too few parameters

Kamis, 27 Juni 2013

RE: [MS_AccessPros] Error 3061 Too few parameters

Doyce-

You cannot put a parameter in SQL that you plan to Execute. You can,
however, do direct substitution, like this:

strSQL = "UPDATE VinMaster SET VinMaster.CustPONo = " &
[Forms]![frmAddInvoiceDetail]![txtCustPO] & ", " & _
"VinMaster.Mat_Labor = '" &
[Forms]![frmAddInvoiceDetail]![txtMatAndLabor] & "', " & _
"VinMaster.FET = '" & [Forms]![frmAddInvoiceDetail]![txtFET] & "', "
& _
"VinMaster.Freight = '" & [Forms]![frmAddInvoiceDetail]![txtDelChg]
& "', " & _
"VinMaster.BOM = '" & [Forms]![frmAddInvoiceDetail]![cboBOM] & "' "
& _
"WHERE (((VinMaster.[Cust ID])= " &
[Forms]![frmAddInvoiceDetail]![cboCustomer] & ") " & _
"AND ((VinMaster.[Unit #]) Between " &
[Forms]![frmAddInvoiceDetail]![cboStartNo] & " " & _
"And " & [Forms]![frmAddInvoiceDetail]![cboEndNo] & "));"

What I've done is supply the values directly in the SQL by resolving the
control references in code BEFORE I pass it to the SQL parser. I've made
some assumptions as to which fields might be number and which might be text.
You may have to alter the insertion of ' characters accordingly.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of dnwinberry
Sent: Thursday, June 27, 2013 9:10 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Error 3061 Too few parameters

I need some help with this error. I'm trying to update some records in a
table using a button on a form and using controls on the form for parameters
and data to update the records with. This form is unbound. Here is my code:

Dim db As Database, strSQL As String
Set db = CurrentDb
strSQL = "UPDATE VinMaster SET VinMaster.CustPONo =
[Forms]![frmAddInvoiceDetail]![txtCustPO], " & _
"VinMaster.Mat_Labor =
[Forms]![frmAddInvoiceDetail]![txtMatAndLabor], " & _
"VinMaster.FET = [Forms]![frmAddInvoiceDetail]![txtFET], " & _
"VinMaster.Freight = [Forms]![frmAddInvoiceDetail]![txtDelChg], " &
_
"VinMaster.BOM = [Forms]![frmAddInvoiceDetail]![cboBOM] " & _
"WHERE (((VinMaster.[Cust
ID])=[Forms]![frmAddInvoiceDetail]![cboCustomer]) " & _
"AND ((VinMaster.[Unit #]) Between
[Forms]![frmAddInvoiceDetail]![cboStartNo] " & _
"And [Forms]![frmAddInvoiceDetail]![cboEndNo]));"
db.Execute strSQL, dbFailOnError
'Debug.Print strSQL
Set db = Nothing

I get Error 3061 Too few parameters expected 8.

Thanks for any help.
Doyce

------------------------------------

Yahoo! Groups Links