How to put records into the temporary table

[sabavno]

Hi
I loop through the recordset and want to put aside some of them into the temporary table (or somewhere else) in order to send them later to the excel file

Please suggest the techniques.

Thanks a lot

 

[SBendBuckeye]

Assuming the temporary table has the same design as your current table, you can do something like the following as you loop through your recordset.

INSERT INTO tblTemp
SELECT tblCurTable.* FROM tblCurTable WHERE tblCurTable.RecordNumber = CurrentRecordNumber

Basically, in the where clause you need some kind of condition that will limit the insert to only the current record. I am at work and cannot verify this, but it is close.

Good Luck!

 

[DK77]

I know how to do this using DAO techniques:

You say you loop through a recordset, so you have one recordset open. You DIMensioned a RecordSet object variable rst to do this. (maybe you called it rst) You DIMensioned a Database object variable too (call it dbs)

Dim dbs as Database
Dim rst as RecordSet

Set dbs = CurrentDb
set rst = dbs.OpenRecordset("MyTable")

You must DIMension another RecordSet object variable (call it rst2 for this example). SET rst2 to the name of your temporary table using the OpenRecordset method of the database object.

Dim rst2 as RecordSet

Set rst2 = dbs.OpenRecordset("MyTemporaryTable")

Does the temporary table have records in it? Check this using the RecordCount property. If it is greater than Zero loop through and delete them all like this:

Do While rst2.RecordCount > 0
   rst2.Delete
Loop

Now you start looping through the first table. You have some criteria for the records you want to find, so build an If statement.

If rst.field1 = "SomethingIWantToFind" Then
   rst2.AddNew   'this creates a new, blank record in your temporary table
   rst2!field1 = rst.field1 
   rst2!field2 = rst.field2 '(and so on)

If you do this explicitly, you don't necessarily have to have identical field names in the two recordsets ( tables ).
It depends on what data you want to copy and what you want to do with them.

You can specify the only the fields you wantOR make a For each...Next statement to loop through the record and copy all the fields.

Dim fld as Field '(put this in the Declarations section)

For Each Field in rst.Fields
      rst2.fld = rst.fld 
      'the field in the temporary table's new record will have the same info as in the old record's field.
   Next

   rst2.Update 'write the new record to the temporary table.

End If  'close out the If...Then block statement!

If you want to MOVE records from one recordset to another, add a statement that says "rst.Delete" AFTER you have copied the record to the temporary table.

Hope this helps.

 

[cmmrfrds]

You can create an in memory recordset and it will go away when it is closed and set to nothing. You can paste this function into your standard module and experiment.


Public Function FabricatedRS()

Dim rs As ADODB.Recordset
Dim varArray As Variant
Set rs = New ADODB.Recordset

With rs.Fields
.Append "myid", adInteger
.Append "mydesc", adVarChar, 50, adFldIsNullable
End With

varArray = Array("myid", "mydesc&quot

With rs
.Open
.AddNew varArray, Array(1, "first desc&quot
.AddNew varArray, Array(2, "second desc&quot
.AddNew varArray, Array(3, "third desc&quot
.AddNew varArray, Array(4, "fourth desc&quot
End With
rs.MoveFirst
While Not rs.EOF
Debug.Print rs!myid; " "; rs!mydesc
rs.MoveNext
Wend

rs.Sort = "myid DESC"

rs.MoveFirst
While Not rs.EOF
Debug.Print rs!myid; " "; rs!mydesc
rs.MoveNext
Wend

rs.Filter = "myid = 3"

rs.MoveFirst
While Not rs.EOF
Debug.Print rs!myid; " "; rs!mydesc
rs.MoveNext
Wend

rs.Filter = adFilterNone

rs.Close
Set rs = Nothing

End Function