• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 588
  • Last Modified:

Can I use the same recordset in different subforms?

Hi experts,

I have a form with a tab control and on each tab page there is a subform. Each subform uses the same table, but displays the data in a different way and using a different subset (to be more specific: customers, suppliers and prospects are all stored in the same table, but each tab shows only the relevant subset).

The way it works now is that each subform has its own record source and therefore (I suppose) the table is transferred three times over the local network. As it is a big table and a busy network, I was wondering if this could be done more efficient.

Would it be possible to load the table in the parent form and link the subforms tot this recordset? Or is there some other smart solution?

1 Solution
You can re-use the same recordset.

You do stuff like :

me.recordset = forms!mainform!subformcontrolname.Form.Recordset
(recordset NOT recordsource)
The diifficulty I would anticipate with multiple subforms will be getting the timing right, as you are dependent on the subform you are getting the recordset from having already got it when you want to re-use it.
You will have to experiment to sort this aspect out.

Eric ShermanAccountant/DeveloperCommented:
One of the only things I can think of right now would be to try using Pass-Through queries as the record source for each sub-form.  This is only useful if your back-end tables are linked to your front-end application using an ODBC Connection.

The Pass-Through query would run on directly on the ODBC database and only return the records that matched your criteria over the network, not the entire table.

Leigh PurvisDatabase DeveloperCommented:
If I may - I'll just comment on the general direction of all this.

I agree with Pete - you can and (if your situation lends itself well) should reuse a recordset to achieve a more efficient fetch of data.
However - to comment in general on something you say.
"the table is transferred three times"

Which implies you're binding the form to an entire table?  Or a non limiting query (i.e. SELECT * FROM tblTableName).
This more than anything is a harmful practice.
Yes - making three requests of data from the same table is wasteful.  Though in the large scheme of things - likely less wateful than requesting *all* records - when you only need one (or so).
Especially given another quote of yours "it is a big table and a busy network"!

Pete's other concern of data order loading is worth noting.  However I don't think there's any problem in assigning a recordset - even though in certain events it might not have fully loaded yet.
Issues may arise if you have any code running in your various forms Open events assuming data present.
None the less - I doubt I'd rely in default functionality for this.  e.g. not just allowing the parent form to load and share it's recordset with the subforms.
I'd be more inclined to open a targetted recordset - and assign it (filtered) to the appropriate forms as required.
For example see attached code snippet.

It's all just aircode of course.  And the functions fRequiredCustomerID etc are however you choose the individual records you require.  Could be from foreign key fields, or combos etc..

In Access 2002 or newer you can equally share your recordsets with list controls. (Though you'd be more inclined to select a whole table then :-s).
But it can be more efficient to load combo lists partially for long lists based on the initial characters entered.
(For a working example of that list filtering see the "Combo Filtering" demo in the examples page linked to in my profile.)

If the records are related to each other in some way (by primary and foreign keys) then in ADO you could open a recordset based on the parent row - and automatically fetch the related rows in a shaped recordset).

Finally, just to expand a bit on ET's post... You don't mention anywhere that this is a non-Jet application.
If it were then you can, of course, use passthroughs as mentioned.
This could be employed in a similar vein as above - making a request for all three rows you want at once - rather than individually.
A passthrough would be one way - you could also open a recordset directly on the server data (using ODBC or OLEDB).
But I don't want you to feel that the efficiency of selecting only a single record is limited to a server source.  Jet will also make the individual record selections based on the appropriate key or index.
Yes the query engine is running locally and so a table scan must be performed locally - but when reading indexes the data page request is far smaller and you *will* then only be pulling the required full records over the network.
Dim lngEnt(1 to 3) as Long
Dim rst as DAO.Recordset 'assuming you're using DAO - this'll work similarly in ADO too.
Dim strSQL as String 
lngEnt(1) = fRequiredCustomerID
lngEnt(2) = fRequiredSupplierID
lngEnt(3) = fRequiredProspectID
'Get a record of required entities
strSQL = "SELECT * FROM tblEntities WHERE EntityID In (" & lngEnt(1) & "," & lngEnt(2) & "," & lngEnt(3) & ")"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
'Filter out each and assign
rst.Filter = "EntityID = " & lngEnt(1)
Set Me.Recordset = rst.OpenRecordset
rst.Filter = "EntityID = " & lngEnt(2)
Set Me.sfmSupplier.Form.Recordset = rst.OpenRecordset
rst.Filter = "EntityID = " & lngEnt(3)
Set Me.sfmProspect.Form.Recordset = rst.OpenRecordset

Open in new window

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

altiplanoAuthor Commented:
Hi Leigh,

Thanks for your excellent answer. Not only did you give me the solution I was looking for, but you also taught me a few things along the way. Your postings always make me aware of how much I still have to learn...

Just to get things right:
1/ Do I understand you correctly that if I run a query using (only) an indexed field, only the relevant bits are loaded? My application is entirely jet-based and I always believed that no matter what query you used, the entire table had to be loaded first.

2/ .Filter uses the "local" recordset and will not re-run the underlying query?

3/ In your example, will
   Set Me.sfmProspect.Form.Recordset = rst.OpenRecordset
create a copy of me.recordset? In other words, changing the filter in one subform will not affect the recordset on an other subform?

Leigh PurvisDatabase DeveloperCommented:
No probs.

1) Yes.  If you make a selection of records based upon an indexed field as criteria then Jet performs optimisations to select based upon the indexed values.
There are exceptions of course.  If you use a range of criteria then Jet may or may not be able to perform the optimisations required.  Using ORs rather than ANDs is a possible cause - also when any function is performed on the field before checking - e.g. DateValue(DateField) = Date() would also prohibit index use.  It's impossible to describe all possible combinations.  Experience, performance and Jet Showplan inform you as to what's a good idea and what isn't.

2) Once you have a recordset loaded - unless you explicitly request a requery then it resides in memory as is.  (ADO recordsets have no choice in this matter when working with Jet - only a Dynamic cursor ADO recordset can be requeried - and the Jet provider doesn't support them).
So your manipulations are all in memory - not performing subsequent disk reads.  Filtering included.

3) Yes, a filtered copy.  Because filtering takes no effect in DAO recordsets until you open a recordset based on the current one, the new ones are a way of creating distinct recordsets for each subform - again without requerying the database.  The opened recordset is based on the filter in the parent recordset at the time of opening.
altiplanoAuthor Commented:
Thanks a lot!
Leigh PurvisDatabase DeveloperCommented:
No worries.

I should mention that Pete first pointed out the concept of sharing recordsets, and Ray (GRayL) asked me to take a look at this thread.  (Am trying to ease off a bit at present so likely wouldn't have gotten here otherwise).
Perhaps they're owed more thanks than I. ;-)

altiplanoAuthor Commented:
Sorry guys, didn't realise that. I created bonus point questions for you. You can pick them up at

Thanks again, this website rocks!!

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now