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

Passing recordset from module to a query

P: 32
Good morning,

I would like to know if someone can guide me as to how do I return a specific recordset, from a SQL query that is being ran in a module, to a query. This query would use the return string as a parameter to return specific information.
Any ideas?
Feb 26 '07 #1
Share this Question
Share on Google+
7 Replies


100+
P: 1,646
Hi. The wording of your question seems to be fairly common in this forum. What I mean by that is you are posing a problem in terms of the answer. In other words you have an answer in your head and you don't know how to implement it.
Rather than make the problem worse, perhaps you could tell us what the 'real world' problem is. What is it your are trying to achieve without talking in code.
Feb 26 '07 #2

P: 32
ok so basically, i have a SQL string in a module. When this module is ran, I would like the third recordset to be returned to a query in the database.
I found some code to help me, but it's giving me a type mismatch with the object rs.

Expand|Select|Wrap|Line Numbers
  1. Dim rs As Recordset
  2. Dim qdf As QueryDef
  3. Dim db As Database
  4.  
  5. Set db = CurrentDb
  6. Debug.Print IndName
  7. Set qdf = db.QueryDefs("Query1")
  8.  
  9. qdf.Parameters("Enter Number") = 12 (for now lets sat that 12 is the value being returned from the sql statement)
  10.  
  11. 'Set rs = qdf.OpenRecordset (this is giving me errors)
Feb 26 '07 #3

100+
P: 1,646
The code you have just posted is not vb. It is access vba. Is your project a vb project or and access project?
Feb 26 '07 #4

P: 32
The code you have just posted is not vb. It is access vba. Is your project a vb project or and access project?
It's an access project.
Feb 26 '07 #5

100+
P: 1,646
It's an access project.
Great. so I am moving you to the access forum
Feb 26 '07 #6

ADezii
Expert 5K+
P: 8,668
ok so basically, i have a SQL string in a module. When this module is ran, I would like the third recordset to be returned to a query in the database.
I found some code to help me, but it's giving me a type mismatch with the object rs.

Expand|Select|Wrap|Line Numbers
  1. Dim rs As Recordset
  2. Dim qdf As QueryDef
  3. Dim db As Database
  4.  
  5. Set db = CurrentDb
  6. Debug.Print IndName
  7. Set qdf = db.QueryDefs("Query1")
  8.  
  9. qdf.Parameters("Enter Number") = 12 (for now lets sat that 12 is the value being returned from the sql statement)
  10.  
  11. 'Set rs = qdf.OpenRecordset (this is giving me errors)
I am not exactly sure what you are trying to accomplish, but I do know why you are getting an Error at the OpenRecordset line. When you use the OpenRecordset() Method in conjunction with QueryDef Objects you must, as a bare minimum, supply the RecordsetType Argument. The Options and LockEdits Arguments are Optional. The following code will now work:
Expand|Select|Wrap|Line Numbers
  1. Dim rs As Recordset
  2. Dim qdf As QueryDef
  3. Dim db As Database
  4.  
  5. Set db = CurrentDb
  6.  
  7. Set qdf = db.QueryDefs("Query1")
  8. qdf.Parameters("Enter Number") = 9
  9.  
  10. Set rs = qdf.OpenRecordset(dbOpenDynaset)    'previous Error
  11. rs.MoveLast: rs.MoveFirst
  12.  
  13. If rs.RecordCount > 0 Then
  14.   Do While Not rs.EOF
  15.     'some Recordset processing
  16.     Debug.Print rs![LastName]
  17.     rs.MoveNext
  18.   Loop
  19. End If
  20.  
  21. rs.Close
Feb 26 '07 #7

P: 32
Hi, thank you for your assistance, but I am still getting a type mismatch error at that same line.


I am not exactly sure what you are trying to accomplish, but I do know why you are getting an Error at the OpenRecordset line. When you use the OpenRecordset() Method in conjunction with QueryDef Objects you must, as a bare minimum, supply the RecordsetType Argument. The Options and LockEdits Arguments are Optional. The following code will now work:
Expand|Select|Wrap|Line Numbers
  1. Dim rs As Recordset
  2. Dim qdf As QueryDef
  3. Dim db As Database
  4.  
  5. Set db = CurrentDb
  6.  
  7. Set qdf = db.QueryDefs("Query1")
  8. qdf.Parameters("Enter Number") = 9
  9.  
  10. Set rs = qdf.OpenRecordset(dbOpenDynaset)    'previous Error
  11. rs.MoveLast: rs.MoveFirst
  12.  
  13. If rs.RecordCount > 0 Then
  14.   Do While Not rs.EOF
  15.     'some Recordset processing
  16.     Debug.Print rs![LastName]
  17.     rs.MoveNext
  18.   Loop
  19. End If
  20.  
  21. rs.Close
Feb 26 '07 #8

Post your reply

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