Improve company productivity with a Business Account.Sign Up


Can I use the same recordset in different subforms?

Posted on 2007-11-23
Medium Priority
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 2000 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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.


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?

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

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

595 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