我有一個用于輸入庫存交易的表格。我添加了一個按鈕，當操作員按下它時，會打開一個報告并顯示分配給專案的材料。當我按下按鈕并且報告沒有打開時，我收到此錯誤。 在此處輸入圖片說明
我添加了一個代碼來打開我的報告的事件，該事件創建了一個 dao 記錄集，我想將該記錄集附加到我的報告中。打開報表的代碼如下：
Private Sub Report_Open(Cancel As Integer)
'Create the necessary recordset and connections
Dim dbs As DAO.Database
Dim rsInventoryControl As DAO.Recordset
Dim rsInventoryAssigned As DAO.Recordset
Dim rsFiltered As DAO.Recordset
Dim strSQLInventory As String
Dim strSQLAssigned As String
Dim strAssignableAmount As String
Dim lngStockID As Long
Dim lngInventoryID As Long
Dim strInventoryNumber As String
'get the data from form
lngStockID = Forms("frmInventoryPermission")!StockID
lngInventoryID = Forms("frmInventoryPermission")!frmInventoryPermissionDetailSubform.Form!cboInventoryID
'set the connection and recordsets
Set dbs = CurrentDb
'Create and run rsInventoryAssigned recordset
strSQLAssigned = "SELECT tblInventoryPermission.Assigned,Sum(tblInventoryPermissionDetail.AssignedQty) AS SumOfAssignedQty, tblInventory.InventoryID, tblStocks.StockID " & _
"FROM (tblInventoryPermission INNER JOIN tblStocks ON tblInventoryPermission.StockID = tblStocks.StockID) INNER JOIN (tblInventoryPermissionDetail INNER JOIN tblInventory ON tblInventoryPermissionDetail.InventoryID = tblInventory.InventoryID) ON tblInventoryPermission.TransferPermissionID = tblInventoryPermissionDetail.InventoryPermissionID " & _
"GROUP BY tblInventoryPermission.Assigned, tblInventory.InventoryID, tblStocks.StockID " & _
"HAVING (((tblInventoryPermission.Assigned)=False));"
Set rsInventoryAssigned = dbs.OpenRecordset(strSQLAssigned)
'find the record based on the information in form
'Control that Stock and Inventory Id is specified
rsInventoryAssigned.Filter = "[InventoryID]='" & lngInventoryID & "' AND [StockID]= '" & lngStockID & "'"
'set the recordsource of the report to filtered recordsource
rsFiltered = rsInventoryAssigned.OpenRecordset
Me.RecordSource = rsFiltered
End Sub
uj5u.com熱心網友回復：
避免使用 DAO 記錄集使用任何 VBA 代碼，只需使用表單控制元件保存引數化查詢，然后將其永久分配到報告的記錄源中。引數化后，每次打開報表時查詢都會進行調整。
下面的 SQL 使用PARAMETERS子句（在 Access 的 SQL 方言中是允許的）并在WHERE子句中使用引數。另外查詢使用表別名來避免重復寫出長表名。
SQL （另存為Access查詢物件，然后手動將查詢分配給RecordSource報表）
PARAMETERS Forms!frmInventoryPermission!frmInventoryPermissionDetailSubform.Form!cboInventoryID TEXT(255),
Forms!frmInventoryPermission!StockID TEXT(255);
SELECT p.Assigned,
i.InventoryID,
s.StockID,
SUM(pd.AssignedQty) AS SumOfAssignedQty
FROM (tblInventoryPermission p
INNER JOIN tblStocks s
ON p.StockID = s.StockID)
INNER JOIN (tblInventoryPermissionDetail pd
INNER JOIN tblInventory i
ON pd.InventoryID = i.InventoryID)
ON p.TransferPermissionID = pd.InventoryPermissionID
WHERE p.Assigned = False
AND i.[InventoryID] = Forms!frmInventoryPermission!frmInventoryPermissionDetailSubform.Form!cboInventoryID
AND s.[StockID]= Forms!frmInventoryPermission!StockID
GROUP BY p.Assigned,
i.InventoryID,
s.StockID;
VBA （放置在表單按鈕后面并洗掉整個Report_Open事件）
DoCmd.OpenReport "myreport", acViewPreview
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/395464.html