When setting my recordset after opening the query it hangs.m Im using a subform.

[jeckoi]

This hangs on this line Set frm.Recordset = rsPro
when my query is already set.

Public Sub loadDataMainProFiltered()
Dim rsPro As DAO.Recordset
Dim strQuery As String

strQuery = "SELECT TabProduct.ProductDescription, TabTransaction.TransactionDescription" _
& " FROM ((TabProcess INNER JOIN TabEmployeeRegion ON TabProcess.RegionID = TabEmployeeRegion.ID) INNER JOIN" _
& " TabEmployeeTower ON TabProcess.TowerID = TabEmployeeTower.ID) INNER JOIN (TabProduct INNER JOIN TabTransaction" _
& " ON TabProduct.ID = TabTransaction.ProductID) ON TabProcess.ID = TabProduct.ProcessID" _
& " WHERE (((TabEmployeeRegion.EmpRegion)='" & cmbRegion.Value & "')" _
& " AND ((TabEmployeeTower.Tower)='" & cmbTower.Value & "')" _
& " AND ((TabProcess.ProcessDescription)='" & cmbProcess.Value & "'));"

Set daoDb = DBEngine.OpenDatabase(strPathForSub, False, False)
Set frm = Form_subViewProcess
Set rsPro = daoDb.OpenRecordset(strQuery)
Set frm.Recordset = rsPro

With frm
.ProductDescription.ControlSource = "ProductDescription"
.TransactionDescription.ControlSource = "TransactionDescription"
.ProductDescription.ColumnWidth = -2
.TransactionDescription.ColumnWidth = -2
End With

Set rsPro = Nothing
Set daoDb = Nothing
End Sub

[jforbes]

I've never used this approach, but it looks like it could work. Mostly what concerns me is setting the Form.Recordset to rsPro and then later setting rsPro to Nothing. I believe rsPro would be a pointer, so when setting rsPro to Nothing it sets the Form.Recordset to Nothing.

You might want to try an approach where Access creates and manages the Recordset for you:

Public Sub loadDataMainProFiltered()
 Dim rsPro As DAO.Recordset
 Dim strQuery As String
 
 strQuery = "SELECT TabProduct.ProductDescription, TabTransaction.TransactionDescription" _
 & " FROM ((TabProcess INNER JOIN TabEmployeeRegion ON TabProcess.RegionID = TabEmployeeRegion.ID) INNER JOIN" _
 & " TabEmployeeTower ON TabProcess.TowerID = TabEmployeeTower.ID) INNER JOIN (TabProduct INNER JOIN TabTransaction" _
 & " ON TabProduct.ID = TabTransaction.ProductID) ON TabProcess.ID = TabProduct.ProcessID" _
 & " WHERE (((TabEmployeeRegion.EmpRegion)='" & cmbRegion.Value & "')" _
 & " AND ((TabEmployeeTower.Tower)='" & cmbTower.Value & "')" _
 & " AND ((TabProcess.ProcessDescription)='" & cmbProcess.Value & "'));"
 
 Me.RecordSource = strQuery 
 
 ' Might not need this if the Control Properties are set through the Designer
 '---------------------------------------------------
 With frm
 .ProductDescription.ControlSource = "ProductDescription"
 .TransactionDescription.ControlSource = "TransactionDescription"
 .ProductDescription.ColumnWidth = -2
 .TransactionDescription.ColumnWidth = -2
 End With
 '---------------------------------------------------
 
 End Sub 

[jeckoi]

Thanks for the response Jforbes. But I forgot to mention my database is from a separate file which is the backend database of my access user form. That's why the suggestion you have given doesn't work if my database is separate from my front end database.

[jforbes]

I'm curious why you don't Link to the tables in the other Database. If you Link to them, then you can use them as they were tables in the current Database and all the hoops you are jumping through go away.

Maybe this could be of use to you: Working with Front and Back-Ends