Run-Time error 3061: Too few parameters. Expected 1

[rogerarce]

Can someone help me and tell me what is wrong with this code?:


dbs.Execute " INSERT INTO Pagar ( Factura, Bodega, item, Cantidad, Monto )" _
& " SELECT Facturas.FacturaID, Factura2.Bodega, Factura2.item, Sum(Factura2.Cantidad) AS SumOfCantidad, Sum(Factura2.Monto) AS SumOfMonto " _
& " FROM Facturas INNER JOIN Factura2 ON Facturas.FacturaID = Factura2.Factura " _
& " GROUP BY Facturas.FacturaID, Factura2.Bodega, Factura2.item " _
& " HAVING (((Facturas.FacturaID) = Forms.Facturas.FacturaID ));"


Thanks in advance!

 

[Remou]

You must keep the reference to the form outside the quotes when running SQL in VBA:

dbs.Execute " INSERT INTO Pagar ( Factura, Bodega, item, Cantidad, Monto )" _
& " SELECT Facturas.FacturaID, Factura2.Bodega, Factura2.item, Sum(Factura2.Cantidad) AS SumOfCantidad, Sum(Factura2.Monto) AS SumOfMonto " _
& " FROM Facturas INNER JOIN Factura2 ON Facturas.FacturaID = Factura2.Factura " _
& " GROUP BY Facturas.FacturaID, Factura2.Bodega, Factura2.item " _
& " HAVING Facturas.FacturaID = " & _
Forms.Facturas.FacturaID, dbFailOnError

It can be useful to create the sql string (strSQL= "Select ...") and the execute, it is easier to debug. dbFailOnError is essential if you do not want SQL to fail silently.

http://lessthandot.com

 

[dhookom]

I like to build a sql variable then run it as Remou suggests. It helps me debug later if needed.

Also the query will be a little more efficient if you move the criteria from the HAVING clause to the WHERE clause. The SQL assumes the FacturaID field is numeric.

Dim strSQL as String
strSQL = " INSERT INTO Pagar ( Factura, Bodega, item, Cantidad, Monto )" _
  & " SELECT Facturas.FacturaID, Factura2.Bodega, Factura2.item, Sum(Factura2.Cantidad), Sum(Factura2.Monto) " _
  & " FROM Facturas INNER JOIN Factura2 ON Facturas.FacturaID = Factura2.Factura " _
  & " WHERE Facturas.FacturaID = " & _
  Forms.Facturas.FacturaID & _
  " GROUP BY Facturas.FacturaID, Factura2.Bodega, Factura2.item "
  
dbs.Execute strSQL, dbFailOnError

Duane
Hook'D on Access
MS Access MVP

 

[rogerarce]

Hey! dhookom it worked great! Thank you for your help! Is there a web that you can recommend I should read to lean more about this?

Regards!

 

[Remou]

Great.

http://lessthandot.com

 

[dhookom]

Tek-tips is a good place to learn. Check out the FAQs. This might raise other questions that you can search out here or on the web.

Duane
Hook'D on Access
MS Access MVP

 

[Remou]

I wonder why I answered this question.

http://lessthandot.com