[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Can I use the same recordset in different subforms?

Posted on 2007-11-23
8
Medium Priority
?
583 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
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 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

0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

830 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