Retreiving a recordset from a function

[nigelbloomy]

I have a query that needs to be repeated many times. Instead of typing it out each time I tied to make a function that would run the query and get the results back to the main block of code. I keep getting an error stating "object variable not set." What am I doing wrong?



Function I created to run the query

Function GetComponents(PartNumber As String) As DAO.Recordset
Dim rsComponents as DAO.RecordSet
stPart = PartNumber
stQuery ="Select Long Query returning many records using the part number string"
Set rsComponents = CurrentDb.OpenRecordset(stQuery)
End Function

Main code

Dim stPart as integer
Dim rsLevel2 as DAO.RecordSet

stPart = 123456
Set rsLevel2 = GetComponents(stPart)
rsLevel2.MoveFirst

I get the error on the MoveFirst line.

[ssanfu]

One thing.....You have not declared a variable "stQuery" in the function.

Add

Dim stQuery as string

But your function doesn't do anything.

Can you describe what you are trying to do?


Edit:

Are you trying to do something like this?

Function GetRS() As DAO.Recordset

    Set GetRS = CurrentDb.OpenRecordset("foo")

End Function

Sub CallGetRS()

    Dim rs As DAO.Recordset

    Set rs = GetRS()

    ' ... do stuff with rs ...

    rs.Close
    Set rs = Nothing

End Sub

[nigelbloomy]

I take a part number, run a query to get all of the sub-parts related to it and then post all of the sub-parts into a table. I have to repeat this several times in the code because the sub-parts can also have sub-parts. So I have to repeat this until all of the subparts have been found no mater how deep they go.

Right now I have that query written out each time in the code. I would much rather have the query in a function where I can pass in the top part number, find all of the sub-parts and return the list of sub-parts back to the main code.

For some reason my code is not setting rsLevel2 to have all of the results from the GetComponent function. I did a debug and I can see the code running just fine in the function. At the end, rsComponents is full of great information. I just want to get it all back out to the main function and call it rsLevel2.

[ssanfu]

Since I do not know what your table or field names or what your query is, see if you can adapt the following:

(Untested)

'function to return a recordset
Function GetComponents(PartNumber As String) As DAO.Recordset
   Dim stQuery As String

   'change this to your query string
   stQuery = "Select Field1, Field2 from MyTable WHERE Field3 = " & PartNumber

   Set GetComponents = CurrentDb.OpenRecordset(stQuery)

End Function


Sub MainCode()   '<== don't know your sub name

   Dim stPart As Integer
   Dim rsLevel2 As DAO.Recordset

   stPart = 123456
   Set rsLevel2 = GetComponents(stPart)

   If rsLevel2.BOF And rsLevel2.EOF Then
      MsgBox "No records"
   Else
      rsLevel2.MoveFirst

      'do stuff

   End If

   rsLevel2.Close
   Set rsLevel2 = Nothing
End Sub

[nigelbloomy]

The only difference I am seeing is that you are using a sub for the main code. Why wouldn't it work with two functions in the same module? I am new to creating functions and subs and how they relate.

It would be hard to have the main code be a sub because I need to call it from several forms.

[ssanfu]

I was guessing as to your code.... there wasn't a lot posted. I have no idea how your dB is designed.

Why wouldn't it work with two functions in the same module?

Two functions can be in the same module.

I was just providing an example of how to return a recordset from a function.

[orange]

How about showing us your tables and relationships as a jpg.

A few samples of your parts, subparts, subsub parts would be useful too.

[June7]

One approach is to declare the recordset object variable in module header then the object is available to multiple procedures.

This example is code behind a form so the recordset object is available only to procedures behind that form. However, it does not repeatedly run the query.

Option Compare Database
Option Explicit
Public strOldLabNum
Dim cn As ADODB.Connection
Dim rsOldSample As ADODB.Recordset

Private Sub cbxOldLabNum_AfterUpdate()
Dim strPV As String
Set cn = CurrentProject.Connection
Set rsOldSample = New ADODB.Recordset
...
    rsOldSample.Open "SELECT zSampleInformation.* " & _
    "FROM zSampleInformation WHERE LabNum='" & strOldLabNum & "';", cn, adOpenStatic, adLockPessimistic
...
End Sub

Function Transfer() As String
...
If rsOldSample!Metric <> IIf([lbxProjectRecord].[Column](5) = 0, False, True) Then
...
End Function

Declare the recordset object in a general module and it will be available from anywhere. Have to be sure to close the recordset object before opening it again.

[amrut]

Function GetComponents(PartNumber As String) As DAO.Recordset

The code above has PartNumber as string while you are declaring it as integer in "main code" ?

Dim stPart as integer

[nigelbloomy]

One thing.....You have not declared a variable "stQuery" in the function.

Add

Dim stQuery as string

But your function doesn't do anything.

Can you describe what you are trying to do?


Edit:

Are you trying to do something like this?

Function GetRS() As DAO.Recordset

    Set GetRS = CurrentDb.OpenRecordset("foo")

End Function

Sub CallGetRS()

    Dim rs As DAO.Recordset

    Set rs = GetRS()

    ' ... do stuff with rs ...

    rs.Close
    Set rs = Nothing

End Sub

I just realized that in your example you set the function GRS() to the query result. In my example I created another recordset but never assigned the result back to the function. That's what I was missing! The variable in my main function was not assigned anything because the function I built for the query was never assigned the results of the query. Thank you for your help. I guess I really am new to functions if I missed something like that.