By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,191 Members | 1,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,191 IT Pros & Developers. It's quick & easy.

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

P: 3
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
Dec 13 '16 #1
Share this Question
Share on Google+
3 Replies


jforbes
Expert 100+
P: 1,107
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:
Expand|Select|Wrap|Line Numbers
  1. Public Sub loadDataMainProFiltered()
  2.  Dim rsPro As DAO.Recordset
  3.  Dim strQuery As String
  4.  
  5.  strQuery = "SELECT TabProduct.ProductDescription, TabTransaction.TransactionDescription" _
  6.  & " FROM ((TabProcess INNER JOIN TabEmployeeRegion ON TabProcess.RegionID = TabEmployeeRegion.ID) INNER JOIN" _
  7.  & " TabEmployeeTower ON TabProcess.TowerID = TabEmployeeTower.ID) INNER JOIN (TabProduct INNER JOIN TabTransaction" _
  8.  & " ON TabProduct.ID = TabTransaction.ProductID) ON TabProcess.ID = TabProduct.ProcessID" _
  9.  & " WHERE (((TabEmployeeRegion.EmpRegion)='" & cmbRegion.Value & "')" _
  10.  & " AND ((TabEmployeeTower.Tower)='" & cmbTower.Value & "')" _
  11.  & " AND ((TabProcess.ProcessDescription)='" & cmbProcess.Value & "'));"
  12.  
  13.  Me.RecordSource = strQuery 
  14.  
  15.  ' Might not need this if the Control Properties are set through the Designer
  16.  '---------------------------------------------------
  17.  With frm
  18.  .ProductDescription.ControlSource = "ProductDescription"
  19.  .TransactionDescription.ControlSource = "TransactionDescription"
  20.  .ProductDescription.ColumnWidth = -2
  21.  .TransactionDescription.ColumnWidth = -2
  22.  End With
  23.  '---------------------------------------------------
  24.  
  25.  End Sub 
Dec 13 '16 #2

P: 3
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.
Dec 14 '16 #3

jforbes
Expert 100+
P: 1,107
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
Dec 14 '16 #4

Post your reply

Sign in to post your reply or Sign up for a free account.