Passing recordset from module to a query

klove1209

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

willakawill

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

klove1209

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.

Feb 26 '07 #3

willakawill

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

klove1209

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

willakawill

It's an access project.

Great. so I am moving you to the access forum

Feb 26 '07 #6

ADezii

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:

Feb 26 '07 #7

klove1209

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