Hello,
I am trying to export multiple queries onto one workbook (one query per worksheet) using the below code:
"SalesOrder_Query", "SalesInvoice_Query" and "PurchaseOrder_Query" require a date which is entered on a form textbox [Text2]. The form also has a command button to run the above code. These queries reference the form as below:
The issue I'm having is when I run this code I am getting error 3828 Cannot reference a table with a multi-valued field using an IN clause that refers to another database. This is very puzzling because:
Thanks in advance,
Chris
I am trying to export multiple queries onto one workbook (one query per worksheet) using the below code:
Code:
Dim strFilePath As String
strFilePath = "Z:\Daily PowerBI Exports\PowerBI " & Format(Me.Text2, "ddmmyy") & ".xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "SalesOrder_Query", strFilePath, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "SalesInvoice_Query", strFilePath, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Customers_Query", strFilePath, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Products-Stock_Query", strFilePath, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "ProductGroup_Query", strFilePath, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "PurchaseOrder_Query", strFilePath, True
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Suppliers_Query", strFilePath, True
"SalesOrder_Query", "SalesInvoice_Query" and "PurchaseOrder_Query" require a date which is entered on a form textbox [Text2]. The form also has a command button to run the above code. These queries reference the form as below:
Code:
[Forms]![PowerBI_Export_Pop_Up_Form]![Text2]
The issue I'm having is when I run this code I am getting error 3828 Cannot reference a table with a multi-valued field using an IN clause that refers to another database. This is very puzzling because:
- My tables don't have any multi-valued fields.
- The error isn't there if I hard code the dates in the queries.
- If I remove a particular table from the query there is no error (but the table has no multi-valued fields)
Thanks in advance,
Chris