Open a report and attach the DAO Recordset to it-CodePudding

I have a form that is for entering inventory transactions. I added a button to it that when the operator press it, a report opens and show the assigned material to a projects. I get this error when I press the button and the report doesn't open. enter image description here

I added a code to open event of my report that make a dao recordset and I want to attach the recordset to my report. The code for opening the report is as follow:

 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

CodePudding user response：

Avoid any VBA code with DAO recordsets and simply save your parameterized query using form controls and then assign it permanently in recordsource of report. Being parameterized, query will adjust each time the report is opened.

Below SQL uses the PARAMETERS clause (allowable in Access' SQL dialect) and uses parameters in WHERE clause. Additionally query uses table aliases to avoid repeatedly writing out the long table names.

SQL (save as Access query object and then manually assign query to RecordSource of report)

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 (place behind form button and delete entire Report_Open event)

DoCmd.OpenReport "myreport", acViewPreview