Recordset.Clone Help Needed

  • Thread starter Thread starter stuman
  • Start date Start date
S

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?
 
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.
 
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?
 
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
 
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.
 
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.
 
The query is retrieving (1) only current record (cursor position) from the
subform, not TOP 50.

KARL DEWEY said:
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.


stuman said:
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
Click to expand...
Click to expand...
 
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.


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

KARL DEWEY said:
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.


stuman said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top