Solved

Can I use the same recordset in different subforms?

Posted on 2007-11-23
8
558 Views
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?

regards,
Michiel
0
Comment
Question by:altiplano
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 77

Expert Comment

by:peter57r
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.


0
 
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.

ET
0
 
LVL 44

Accepted Solution

by:
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

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:altiplano
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?

thanks,
Michiel
0
 
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.
0
 

Author Comment

by:altiplano
ID: 20346186
Thanks a lot!
regards,
Michiel
0
 
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. ;-)

Cheers!
0
 

Author Comment

by:altiplano
ID: 20356776
Sorry guys, didn't realise that. I created bonus point questions for you. You can pick them up at
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22984537.html
and
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_22984539.html

Thanks again, this website rocks!!
Michiel
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

705 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