How To Append a Filtered Recordset To Another Recordset

[James]

I am trying to create a recordset based on a filtered recordset... Each

filter returns one record. I can't figure out how to append that record

to my recordset. Here's my code. Thanks.

Option Compare Database
Private OfferingData As DataSource
Dim fm As New FormManager
Private m_sourceData As String
Dim SearchParams(0)


Private Sub cmdShowReport_Click()


Dim dataHolder As ADODB.Recordset
Dim reportInstanceOfferings As New ADODB.Recordset
Dim grabOID As Long
Dim varItm As Variant
Dim varItmCount As Integer
Dim Count As Integer
Dim OfferingsToBuild() As Variant


Count = 0


'Bring in all offerings
m_sourceData = "Offerings_Display"
Set OfferingData = New DataSource
LoadData OfferingData
'Set the DataSource = ADODB.Recordset
Set dataHolder = OfferingData.Dataset


'Set varItm = Number of Child OfferingIDs
varItm = lbOfferingInstances.ListCount


'ReDim Variant = # Of Child OfferingIDs + Parent OfferingID
ReDim OfferingsToBuild(varItm + 1)


'Get Parent OfferingID
OfferingsToBuild(Count) =
Form_DisplayOfferings.OfferingsList.Form.Offering
Count = Count + 1


'Add Child OfferingIDs
Do Until Count = (varItm + 1)
OfferingsToBuild(Count) = lbOfferingInstances.ItemData(Count - 1)
Count = Count + 1
Loop


Count = 0


'Filter through all offerings... Pull record with specified OfferingID
Do Until Count = (varItm + 1)
dataHolder.Filter = "OfferingID = " & OfferingsToBuild(Count)
'I want to append the record from the filtered DataHolder
'to reportInstanceOfferings every time I run through the loop, How
Do I Do This??
Set reportInstanceOfferings = dataHolder
Count = Count + 1
Loop


Thanks for the help. If you need more information, Let me know. Thanks.

 

[John Vinson]

I am trying to create a recordset based on a filtered recordset... Each

filter returns one record. I can't figure out how to append that record

What are you trying to accomplish? Do you want to store the record in
the second table, permanently (an Append query or append operation)?
or do you want to return a two-record dynamic recordset (a UNION query
might be the best choice)?

John W. Vinson[MVP]

 

[James]

I'm trying to compile a temporary recordset in memory so that it can be
used to create a form then erased when the form is closed. ...

I've tried using this format:

reportInstanceOfferings.Open
reportInstanceOfferings.AddNew
reportInstanceOfferings!OfferingID = dataHolder.Fields(0)
reportInstanceOfferings.update

but I don't have anything for it to open and AddNew won't work without
..open statement.

Any ideas?

 

[John Vinson]

I'm trying to compile a temporary recordset in memory so that it can be
used to create a form then erased when the form is closed. ...

I've tried using this format:

reportInstanceOfferings.Open
reportInstanceOfferings.AddNew
reportInstanceOfferings!OfferingID = dataHolder.Fields(0)
reportInstanceOfferings.update

but I don't have anything for it to open and AddNew won't work without
.open statement.

Recordsets need to be based on data in tables. You can't "store" a
record in a recordset in memory. It looks like you're treating a
Report as if it were a Table, a data repository - and unfortunately it
simply doesn't work that way!

Can you not simply create a Query object, and use that query as the
Recordsource for the report? A *lot* less coding needed!

John W. Vinson[MVP]