Error 3828 when exporting queries to Excel (1 Viewer)

[arnelgp]

here again with some modifications.
before running the code, Kill Excel from Task Manager(Ctrl-Shit-ESC), then find Excel.
Right-click on the Excel Process, the choose End Task.

 

[ChrisMore]

here again with some modifications.
before running the code, Kill Excel from Task Manager(Ctrl-Shit-ESC), then find Excel.
Right-click on the Excel Process, the choose End Task.

With these changes I'm getting run-time error 3061 Too few parameters. Expected 1.

I tried running the code with your attachment and can see it works but not with my setup currently.

Still not sure whether I should be including SalesOrder_Query in the below because if not I get error 3828 again, but it's not in your code.

If arrQueries(i) = "SalesOrder_Query" Or arrQueries(i) = "SalesInvoice_Query" Or arrQueries(i) = "PurchaseOrder_Query" Then

 

[arnelgp]

this is very fast, total re-code.
i remove the module and instead, change the query string if there is parameter.
see the code on the button

 

[ChrisMore]

this is very fast, total re-code.
i remove the module and instead, change the query string if there is parameter.
see the code on the button

I really appreciate your time to create this code but this time I'm back with the error 3828. Your approach from post #17 is the closest it's been to working for me.

 

[Gasman]

Worked for me?, so examine what is different with what @arnelgp supplied

Remove the .txt extension

 

[Ken Sheridan]

Thank you for the suggestion, however my lack of knowledge in the terminology you've used is holding me back in understanding what you mean. Are you able to simplify what you said or provide an example of what you mean?

The following is a simple example (untested) which replaces a parameter referencing a txtDateApponted parameter with the value of the parameter:

    Dim strSQL_Current  as String
    Dim strSQL_Temp As String
    Dim qdf As DAO.Querydef

    ' replace queries SQL statement , substituting  a date literal for the parameter
    Set qdf = CurrentDb.querydefs("QueryNameGoesHere")
    strSQL_Current = qdf.SQL
    strSQL_Temp = Replace(trSQL_Current,"Forms!frmEmployees!txtDateAppointed", "#" & Format(Me.txtAppointed,"yyyy-mm-dd") & "#")
    qdf.SQL = strSQL_Temp

    ' export query's result table to Excel
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "QueryNameGoesHere", strFilePath, True

    ' reinstate query's original SQL property
    qdf.SQL = strSQL_Current

Rather than having separate code for each query, however, it would be better to wrap the code in a function in the form's module, into which the name of the query, the parameter, the date value, and the path to the Excel file could be passed as arguments. The function could then be called for each relevant query.

 

[Gasman]

Ken, that is what @arnelgp's code does.

 

[arnelgp]

I really appreciate your time to create this code but this time I'm back with the error 3828. Your approach from post #17 is the closest it's been to working for me.

maybe you can show us the SQL String of those two problematic queries.

 

[ChrisMore]

maybe you can show us the SQL String of those two problematic queries.

SalesOrder_Query

SELECT Shipments.Order_Number, Shipments.Order_Shipment_Number, Order_Status.Order_Status, Customer_Orders.Customer_Order_Number, Customers.Customer_Ref, Shipments.Delivery_Date, Customer_Orders.Date_Order_Received, Customer_Orders.Order_Value, [Order_Value]/100*20 AS TaxValue, [Order_Value]+([Order_Value]/100*20) AS TotalGrossValue, Customer_Orders.Database_Entry_Created_By, Works_Order_Items.Manufactured_Product_Name, Works_Order_Items.Quantity, Shipments.Line_Value, [Line_Value]/100*20 AS LineTaxValue
FROM Customers INNER JOIN (((Customer_Orders INNER JOIN Shipments ON Customer_Orders.Order_Number = Shipments.Order_Number) INNER JOIN Works_Order_Items ON Shipments.Order_Shipment_Number = Works_Order_Items.Order_Shipment_Number) INNER JOIN Order_Status ON Shipments.Shipment_Status = Order_Status.Order_Status_Code) ON Customers.Customer_Ref = Customer_Orders.Customer_Ref
GROUP BY Shipments.Order_Number, Shipments.Order_Shipment_Number, Order_Status.Order_Status, Customer_Orders.Customer_Order_Number, Customers.Customer_Ref, Shipments.Delivery_Date, Customer_Orders.Date_Order_Received, Customer_Orders.Order_Value, [Order_Value]/100*20, [Order_Value]+([Order_Value]/100*20), Customer_Orders.Database_Entry_Created_By, Works_Order_Items.Manufactured_Product_Name, Works_Order_Items.Quantity, Shipments.Line_Value, [Line_Value]/100*20
HAVING (((Customer_Orders.Date_Order_Received)=[Forms]![PowerBI_Export_Pop_Up_Form]![Text2]));

SalesInvoice_Query

SELECT Shipments.Invoice_Number, Shipments.Invoice_Date, Shipments.Order_Shipment_Number, Order_Status.Order_Status, Customer_Orders.Customer_Order_Number, Customers.Customer_Ref, Shipments.Line_Value, [Line_Value]/100*20 AS LineTaxValue, Customer_Orders.Database_Entry_Created_By, Customer_Orders_Items.Product_Code, Products.Unit_of_Purchase, Customer_Orders_Items.Order_Quantity, Round(ACPbyDate([Customer_Orders_Items].[Product_Code],[Production_Complete_Date]),2)*[Customer_Orders_Items].[Order_Quantity] AS CostofSale, Sum(Shipment_CostofSale_Query.CostofSale) AS TotalCostofSale
FROM ((Shipment_CostofSale_Query INNER JOIN Shipments ON Shipment_CostofSale_Query.Order_Shipment_Number = Shipments.Order_Shipment_Number) INNER JOIN Order_Status ON Shipments.Shipment_Status = Order_Status.Order_Status_Code) INNER JOIN (Products INNER JOIN (Customers INNER JOIN (Customer_Orders INNER JOIN Customer_Orders_Items ON Customer_Orders.Order_Number = Customer_Orders_Items.Order_Number) ON Customers.Customer_Ref = Customer_Orders.Customer_Ref) ON Products.Product_Code = Customer_Orders_Items.Product_Code) ON Shipments.Order_Shipment_Number = Customer_Orders_Items.Order_Shipment_Number
GROUP BY Shipments.Invoice_Number, Shipments.Invoice_Date, Shipments.Order_Shipment_Number, Order_Status.Order_Status, Customer_Orders.Customer_Order_Number, Customers.Customer_Ref, Shipments.Line_Value, [Line_Value]/100*20, Customer_Orders.Database_Entry_Created_By, Customer_Orders_Items.Product_Code, Products.Unit_of_Purchase, Customer_Orders_Items.Order_Quantity, Round(ACPbyDate([Customer_Orders_Items].[Product_Code],[Production_Complete_Date]),2)*[Customer_Orders_Items].[Order_Quantity]
HAVING (((Shipments.Invoice_Date)=[Forms]![PowerBI_Export_Pop_Up_Form]![Text2]) AND ((Customer_Orders_Items.Order_Quantity)>0));

The queries run when I remove the Customer_Orders table from both. But the queries also run with the Customer_Orders table when I remove the parameter form control reference.

 

[arnelgp]

you don't get error when opening these 2 queries directly from the Navigation Pane. right? and only got error when Exporting them?

is the Export code from the same form, PowerBI_Export_Pop_Up_Form?

 

[ChrisMore]

you don't get error when opening these 2 queries directly from the Navigation Pane. right? and only got error when Exporting them?

is the Export code from the same form, PowerBI_Export_Pop_Up_Form?

That's all correct

 

[arnelgp]

can you test this again, i made another module. and modify the code a bit.

 

[ChrisMore]

can you test this again, i made another module. and modify the code a bit.

That did the trick! Thank you so much for your time to help me with the code .

What was the issue with the form reference in the query for it to require the code you created? I'm struggling to understand how that relates to the error 3828...

 

[arnelgp]

what the code does is Replace the form parameter criteria of the query with the actual value of Text2 on the form.
after running the TransferSpreadsheet, the query is re-instated to it's original.

 

[ChrisMore]

what the code does is Replace the form parameter criteria of the query with the actual value of Text2 on the form.
after running the TransferSpreadsheet, the query is re-instated to it's original.

Thanks for the explanation. Same approach as Ken suggested in his post. Still don't understand why I was getting error 3828 or why it didn't like the form reference in the query criteria.

Anyway, thank you very much again for your help