Recordset to Querydef

[aliendan]

Say I have a Recordset that has a Recordcount=20. Then I want a Querydef that pulls just the last six records of this Recordset. How can I do this?
Dan Rogotzke
Dan_Rogotzke@oxy.com

 

[DougP]

Create a new recordset
then do this in the first one

rst.movelast ' which will take you to the last record in it.
'get the record and save it to the new one
' then in a loop get the next 5

for a = 1 to 5
rst.moveprevious
'get data snd save to new one
next DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[mymou]

Hi Aliendan

Think the easy way might be to use the top values property in a query (and then open the query as a recordset).

Alternatively use the SQL

SELECT TOP 6 tblTable.*
FROM tblTable;

Might need an order by.
Never tried this in code - but see no reason why it shouldnt work.

DougP's solution should work (his solutions usually do).

Tell me what the connection is to 'Copy record from Recordset1 to Recordset2' - feel like this is a treasure hunt!!!

Stew

 

[aliendan]

Stew,

THATS IT! The TOP predicate using ORDER BY DESC! It works perfectly. Although I got it to work with DougP's solution too. I never could figure out (after trying every contortion I could think of) to copy from RS1 to RS2 so I created a temp table and populated it with the last 6 records. But using the Top predicate changed it from 52 lines of code to 18 lines of code. Thank you very much!

Dan
Dan Rogotzke
Dan_Rogotzke@oxy.com

 

[mymou]

Hi AlienDan

I reckon with a prewritten Make Table query and a DoCmd.OpenQuery "qryQueryName"

You will be down to one line. Now that must be worth a star!!

If you are obsessed with recordsets:
Set oRS = oConn.Execute("qryOtherQueryName&quot

Stew

 

[aliendan]

Hehahahaha! Yeah, I guess I am kinda obsessed with recordsets. I love the darned things. Hehehahahah! Using your solution I ran a Querydef from the SQL for the report, but now that I think about it I don't need the table or any code at all. I can populate the RecordSource of the report with the SQL containing the TOP predicate. Sheesh! What a deal, ha? Yep, this is definately worth a star. In fact, even though I don't drink I'd buy you a beer.
Dan Rogotzke
Dan_Rogotzke@oxy.com