Solved VBA Execute (1 Viewer)

[Reshmi mohankumar]

VBA Execute, cannot getting data and throwing runtime error 3061 with too few parameters required.
Here i am executing an SQL insert statement, getting values from another mainQuery which representation of various join queries(of 6 child queries).
where as mainQuery showing the data in datasheetview of query, but the same data is not getting in VBA Execution call.
All child queries are getting passed by a parameter as date.

tried this one >>>
stock = "INSERT INTO Tbl_stock_opening_temp " _
& "SELECT * from Qry_stock_today1"
Db.Execute stock;

SQL in query:
"INSERT INTO Tbl_stock_opening_temp SELECT Tbl_SubProduct.ID, Tbl_SubProduct.product, Tbl_SubProduct.subproduct, Nz([pcqty],0) AS [Purchase Qty], Nz([srtqty],0) AS [Sales RtnQty], Nz([rtqty],0) AS [Dealer RtnQty], Nz([slqty],0) AS [Sales Qty], Nz([OpenQty],0) AS [Opening Qty], Nz((CDbl([Opening Qty])+CDbl([purchase qty])+CDbl([sales rtnQty])-CDbl([Dealer rtnQty])-CDbl([sales qty])),0) AS ClosingQty, Nz([Open Units],0) AS [opening Units], Nz([pcunits],0) AS [Purchase Units], Nz([srtunit],0) AS [Sale RtnUnits], Nz([rtunits],0) AS [Dealer RtnUnits], Nz([slunits],0) AS [Sales Units], CDbl((Nz([Opening units],0))+CDbl(Nz([purchase Units],0))+CDbl(Nz([Sale rtnunits],0))-CDbl(Nz([dealer Rtnunits],0))-CDbl(Nz([Sales units],0))) AS [Closing Unit]" _
& "FROM ((((Tbl_SubProduct LEFT JOIN Qry_stock_cust_rtn1 ON Tbl_SubProduct.ID = Qry_stock_cust_rtn1.combinationid) LEFT JOIN Qry_stock_dlr_purch1 ON Tbl_SubProduct.ID = Qry_stock_dlr_purch1.combinationid) LEFT JOIN Qry_stock_dlr_rtn1 ON Tbl_SubProduct.ID = Qry_stock_dlr_rtn1.combinationID) LEFT JOIN Qry_stock_cust_sale1 ON Tbl_SubProduct.ID = Qry_stock_cust_sale1.uidn) LEFT JOIN Qry_stock_Open1 ON Tbl_SubProduct.ID = Qry_stock_Open1.ID;"

Child Queries like:
SELECT Tbl_STOCK_Purchase.trandate, Tbl_STOCK_Purchase.fy, Sum(Nz([qty])) AS pcqty, Sum(Nz([unit])) AS pcunits, Tbl_STOCK_Purchase.combinationid
FROM Tbl_STOCK_Purchase
GROUP BY Tbl_STOCK_Purchase.trandate, Tbl_STOCK_Purchase.fy, Tbl_STOCK_Purchase.combinationid
HAVING (((Tbl_STOCK_Purchase.trandate)=[Forms]![Frm_Dayend]![txtdate]));



Thanks in advance for a your medicine.

 

[ebs17]

The problem is ... Forms!Frm_Dayend!txtdate

The Execute method passes the query directly to the Access Database Engine. This only understands SQL, but knows no objects from the Access object and no VBA variables. Therefore, the form textbox and its content remains unknown and triggers a query.

Maybe it will help ... Eval(Forms!Frm_Dayend!txtdate)
This determines the value of the textbox and transfers it to the ACE. There are other possible further methods up to correct parameter queries.

 

[Pat Hartman]

copy the string for the child query and paste it into the QBE. Run it. If you don't get an error, that query is not the problem.
copy the string for the big query and paste it into the QBE. Run it. You may get a better error message to help to identify where the typo is.

divide and conquer.

@ebs17 Unless this is a pass through query, it is processed by Access BEFORE it is passed to SQL Server so variables should be resolved.

 

[ebs17]

paste it into the QBE. Run it

This corresponds to an execution via DoCmd.OpenQuery, not db.Execute. That can also be a solution.

I myself am very economical with DoCmd methods and with using form references directly in queries. There are parameter queries for the defined transfer of parameters.

 

[theDBguy]

Just in case it helps, try out this generic function.

Code Snippets

 

[June7]

Why do you need to save query results to table?

Review http://allenbrowne.com/AppInventory.html

DoCmd.RunSQL can resolve the form control reference embedded in SQL string.

For Execute, Eval() does not work for this - should concatenate.

 

[Reshmi mohankumar]

Just in case it helps, try out this generic function.

Code Snippets

The best option. Thanks to MVP, Leigh Purvis (LPurvis) and #theDBguy

 

[theDBguy]

The best option. Thanks to MVP, Leigh Purvis (LPurvis) and #theDBguy

Glad to hear you got it sorted out. Good luck with your project.