Recordset.Clone Help Needed

[stuman]

I have an Unbound Main Form with 8 unbound fields. I enter broad search data
and the records are displayed in my Subform. From here, I further filter the
subform (contains, does ot contain, equals, etc.)to get a smaller recordset.
Once narrowed (final recordset), I would like to have a Command Button on the
Main form when pushed that will change the recordsource of the subform to the
TOP 50 records from the FINAL previous recordset. Any ideas on how to
accomplish?

 

[Arvin Meyer [MVP]]

I'm not sure that it's directly possible. A RecordsetClone is an Access DAO
object. The TOP predicate is SQL language statement.

Just a thought but you might consider filling a temporary table with the
records from the subform, then running a TOP 50 query on those, and using
the result as the recordsource for whatever you are looking for. The
drawback is if there are very many records in the set, it may take some time
to fill the table.

 

[KARL DEWEY]

I would like to have a Command Button on the Main form when pushed that
will change the recordsource of the subform to the TOP 50 records
A TOP 50 requires you to utilize a sort order. Where in this process wold
you select that?
Why change source for sub form when all you need is to run another query
(TOP 50) for another subform or form?

 

[stuman]

I have a field 'Sales' on the subform. I was going to Sort descending and
Select the Top 50 from the narrowed list of say 500 records. I start with
60,000 - down to 2000 - down to 500 records. I don't know how to write the
code - something like

SELECT TOP 50
FROM Recordset.Clone
SORT Sales DESC
WHERE Record Count <>0

 

[stuman]

How would I write the code to append FINAL recorset to the Temp table and
then use the results of the TOP 50 query as the new recordsource of the open
subform. I can handle the TOP 50 query on the table, but the rest stumps me.
Any help appreciated.

 

[KARL DEWEY]

Try this using your table and form names.

SELECT TOP 50 YourTable.*
FROM YourTable
WHERE YourTable.[Field1] = [Forms]![YourForm]![YouSubForm]![YourTextBox1]
AND YourTable.[Field2] = [Forms]![YourForm]![YouSubForm]![YourTextBox2] AND
YourTable.[Field3] = [Forms]![YourForm]![YouSubForm]![YourTextBox31]
ORDER BY Sales DESC;

Use all fields that would be needed for criteria.

 

[Arvin Meyer [MVP]]

This is off the top if my head:

With Me.RecordSetClone
.MoveFirst
For i = 1 To .RecordCount
CurrentDB.Execute "INSERT INTO tmpTable _
( Field1, Field2, Field3, Field4) _
VALUES( .Field1, .Field2, .Field3, .Field4);"
Next i
End With
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

 

[stuman]

The query is retrieving (1) only current record (cursor position) from the
subform, not TOP 50.

Try this using your table and form names.

SELECT TOP 50 YourTable.*
FROM YourTable
WHERE YourTable.[Field1] = [Forms]![YourForm]![YouSubForm]![YourTextBox1]
AND YourTable.[Field2] = [Forms]![YourForm]![YouSubForm]![YourTextBox2] AND
YourTable.[Field3] = [Forms]![YourForm]![YouSubForm]![YourTextBox31]
ORDER BY Sales DESC;

Use all fields that would be needed for criteria.

--
Build a little, test a little.

I have a field 'Sales' on the subform. I was going to Sort descending and
Select the Top 50 from the narrowed list of say 500 records. I start with
60,000 - down to 2000 - down to 500 records. I don't know how to write the
code - something like

SELECT TOP 50
FROM Recordset.Clone
SORT Sales DESC
WHERE Record Count <>0

 

[KARL DEWEY]

Ok. Next try. Add the query that feeds the first subform without
joining --
SELECT TOP 50 YourTable.*
FROM YourTable, qryFirstSubForm
WHERE YourTable.[Field1] = qryFirstSubForm.[Field1] AND YourTable.[Field2] =
qryFirstSubForm.[Field2] AND YourTable.[Field3] = qryFirstSubForm.[Field3]
AND YourTable.[Field4] = qryFirstSubForm.[Field4]
ORDER BY Sales DESC;

--
Build a little, test a little.

The query is retrieving (1) only current record (cursor position) from the
subform, not TOP 50.

Try this using your table and form names.

SELECT TOP 50 YourTable.*
FROM YourTable
WHERE YourTable.[Field1] = [Forms]![YourForm]![YouSubForm]![YourTextBox1]
AND YourTable.[Field2] = [Forms]![YourForm]![YouSubForm]![YourTextBox2] AND
YourTable.[Field3] = [Forms]![YourForm]![YouSubForm]![YourTextBox31]
ORDER BY Sales DESC;

Use all fields that would be needed for criteria.

--
Build a little, test a little.

I have a field 'Sales' on the subform. I was going to Sort descending and
Select the Top 50 from the narrowed list of say 500 records. I start with
60,000 - down to 2000 - down to 500 records. I don't know how to write the
code - something like

SELECT TOP 50
FROM Recordset.Clone
SORT Sales DESC
WHERE Record Count <>0

:

I would like to have a Command Button on the Main form when pushed that
will change the recordsource of the subform to the TOP 50 records
A TOP 50 requires you to utilize a sort order. Where in this process wold
you select that?
Why change source for sub form when all you need is to run another query
(TOP 50) for another subform or form?

--
Build a little, test a little.


:

I have an Unbound Main Form with 8 unbound fields. I enter broad search data
and the records are displayed in my Subform. From here, I further filter the
subform (contains, does ot contain, equals, etc.)to get a smaller recordset.
Once narrowed (final recordset), I would like to have a Command Button on the
Main form when pushed that will change the recordsource of the subform to the
TOP 50 records from the FINAL previous recordset. Any ideas on how to
accomplish?