Can I use the same recordset in different subforms?

Posted on 2007-11-23
Last Modified: 2009-12-16
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?

Question by:altiplano
LVL 77

Expert Comment

ID: 20339301
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.

LVL 19

Expert Comment

by:Eric Sherman
ID: 20339310
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.

LVL 44

Accepted Solution

Leigh Purvis earned 500 total points
ID: 20343006
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


Author Comment

ID: 20345430
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?

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

LVL 44

Expert Comment

by:Leigh Purvis
ID: 20346161
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.

Author Comment

ID: 20346186
Thanks a lot!
LVL 44

Expert Comment

by:Leigh Purvis
ID: 20346231
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. ;-)


Author Comment

ID: 20356776
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now