为了同时导出采购订单和采购订单明细,所以修改了原导出代码。
我在使用金宇老师代码中发现,一旦查询筛选,导出的即为空白,而debug.print出的代码也没错。是否以下被注释掉的代码有错误?
Public Sub btnExport_Click()
If Not Me.sfrList.Form.CurrentRecord > 0 Then Exit Sub
ExportToExcel DataForm:=Me.sfrList
ExportToExcel DataForm:=Me.sfrList!frm采购订单_List_Detail
'Dim strSQL As String
'Dim strSQL1 As String
'Dim strSQL2 As String
'Dim strWhere As String
' If Not Me.sfrList.Form.CurrentRecord > 0 Then Exit Sub
' If Me.sfrList.Form.FilterOn Then
' strWhere = Me.sfrList.Form.Filter
' strSQL1 = "SELECT Prch_采购订单.状态, Prch_采购订单.采购订单号, Prch_采购订单.采购日期," _
& " Bsc_供应商信息.供应商名称, Prch_采购订单.经办人, Prch_采购订单.制单人," _
& " Prch_采购订单.制单日期, Prch_采购订单.审核人, Prch_采购订单.审核日期, Prch_采购订单.备注, Prch_采购订单.供应商ID" _
& " FROM Bsc_供应商信息 INNER JOIN Prch_采购订单 ON Bsc_供应商信息.供应商ID = Prch_采购订单.供应商ID" _
& " WHERE " & strWhere & " ORDER BY Prch_采购订单.状态, Prch_采购订单.采购订单号;"
' strSQL2 = "SELECT Prch_采购订单明细.采购订单号, Prch_采购订单明细.商品ID, Prch_商品分类.分类名称," _
& " Prch_商品.品名规格, Prch_商品.单位, Prch_采购订单明细.数量, Prch_采购订单明细.单价," _
& " [单价]*[数量] AS 金额, Prch_商品.库存数量, 采购订单查询_明细_入库.已入库数量," _
& " [数量]-Nz([已入库数量],0) AS 未入库数量" _
& " FROM Prch_商品分类 INNER JOIN (Prch_商品 INNER JOIN (Prch_采购订单明细" _
& " LEFT JOIN 采购订单查询_明细_入库 ON (Prch_采购订单明细.采购订单号 = 采购订单查询_明细_入库.相关订单号)" _
& " AND (Prch_采购订单明细.商品ID = 采购订单查询_明细_入库.商品ID)) ON Prch_商品.商品ID = Prch_采购订单明细.商品ID)" _
& " ON Prch_商品分类.分类编号 = Prch_商品.分类编号" _
& " WHERE " & strWhere
' Debug.Print strSQL2
' Else
' strSQL1 = "SELECT Prch_采购订单.状态, Prch_采购订单.采购订单号, Prch_采购订单.采购日期," _
& " Bsc_供应商信息.供应商名称, Prch_采购订单.经办人, Prch_采购订单.制单人," _
& " Prch_采购订单.制单日期, Prch_采购订单.审核人, Prch_采购订单.审核日期, Prch_采购订单.备注, Prch_采购订单.供应商ID" _
& " FROM Bsc_供应商信息 INNER JOIN Prch_采购订单 ON Bsc_供应商信息.供应商ID = Prch_采购订单.供应商ID" _
& " ORDER BY Prch_采购订单.状态, Prch_采购订单.采购订单号;"
' strSQL2 = "SELECT Prch_采购订单明细.采购订单号, Prch_采购订单明细.商品ID, Prch_商品分类.分类名称," _
& " Prch_商品.品名规格, Prch_商品.单位, Prch_采购订单明细.数量, Prch_采购订单明细.单价," _
& " [单价]*[数量] AS 金额, Prch_商品.库存数量, 采购订单查询_明细_入库.已入库数量," _
& " [数量]-Nz([已入库数量],0) AS 未入库数量" _
& " FROM Prch_商品分类 INNER JOIN (Prch_商品 INNER JOIN (Prch_采购订单明细" _
& " LEFT JOIN 采购订单查询_明细_入库 ON (Prch_采购订单明细.采购订单号 = 采购订单查询_明细_入库.相关订单号)" _
& " AND (Prch_采购订单明细.商品ID = 采购订单查询_明细_入库.商品ID)) ON Prch_商品.商品ID = Prch_采购订单明细.商品ID)" _
& " ON Prch_商品分类.分类编号 = Prch_商品.分类编号"
' End If
' strSQL = strSQL1 & "|" & strSQL2
' ExportToExcelQueryTables_Multiple "采购订单-明细", strSQL
End Sub