Charlotte Foust
cfoust at infostatsystems.com
Thu Oct 2 18:02:12 CDT 2003
No, the fast way is to use an unbound subform control and set its sourceobject in code to a particular subform, not a bound subform control with a change to the recordsource. If you use SQL strings like that, it means that the SELECT statements are not optimized in Access 2000 and 2002 and can lead to significant delays in loading the form.
Charlotte Foust
-----Original Message-----
From: David Fenton [mailto:dfe at nudgeeinternational.com]
Sent: Thursday, October 02, 2003 2:57 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] Just in Time (off shoot of Can't open any more databases)
Charlotte and William, you have both got me thinking!
Yes I do use the Master/Child filter, but when the form is first loaded all of its subforms sit there blank behind their appropriate tabs (each with no recordsource).
When the user clicks on each tab I assign an SQL string to the recordsource of the subforms under that tabb, the form opens the record source and each child subform does it's master/child filtering. All is well, but it IS slow. Is this the accepted way to do it? Because if you are saying it should be faster, then I'd like to do it properly so that it IS faster on my application. I must be doing something wrong.
A sample of my code:
...
Case 6 ' Transfers IN
'Simple procedure to remove recordsources from forms hidden when user leaves previous tab
ClearRecordSource (Forms!frmEN_AddEnrolmentFees!sfrmTransferOUT.Form)
ClearRecordSource (Forms!frmEN_AddEnrolmentFees!sfrmTransferDetailsOUT.Form)
ClearRecordSource (Forms!frmEN_AddEnrolmentFees!sfrmTransferDetailsIN.Form)
'Set up the recordsource strings for the three visible subforms (master and two children)
Dim rsIN As String 'Master subform recordsource
Dim rsINfromDetails As String 'child 1 subform recordsource
Dim rsINtoDetails As String 'child 2 subform recordsource
'Assign the strings to the variables
rsIN = "SELECT tbl_Transfer.Trf_ID AS TransferID, tbl_Transfer.Trf_Num AS TrfNumber,
tbl_Transfer.Trf_Printed AS Printed, tbl_Transfer.Trf_Date AS DateIssued, tbl_Transfer.Trf_Description,
tbl_Transfer.Trf_Enrol_ID_out AS fromEnrolID, tbl_Transfer.Trf_Enrol_ID_in AS toEnrolID,
tbl_Transfer.Trf_Amount AS AmtTransferred FROM tbl_Transfer;"
rsINfromDetails = "qryEN_FI_TransferDetailsOUT"
rsINtoDetails = "qryEN_FI_TransferDetailsIN"
'Assign recordsources to the three subforms under tab user just clicked on
mainTrfINfrm.RecordSource = rsIN 'Where mainTrfINform is a form object previously set
detailTrfINfrom.RecordSource = rsINfromDetails
detailTrfINto.RecordSource = rsINtoDetails
etc....
Any ideas?
Cheers
David Ô¿Ô¬
Ext 0751
Your messages:
Date: Thu, 2 Oct 2003 15:31:12 -0700
From: "Charlotte Foust" <cfoust at infostatsystems.com>
Subject: RE: [AccessD] Can't open any more databases
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Message-ID:
<E61FC1D4B1918244905B113C680BEA86312342 at infoserver01.infostat.local>
Content-Type: text/plain; charset="iso-8859-1"
Why are they waiting for records to be filtered? Aren't you also setting the master and child links for the subforms? That is generally a much faster way to filter a subform than changing the recordsource.
Charlotte Foust
........................................................................................................................................................................
Date: Thu, 2 Oct 2003 18:33:26 -0400
From: "William Hindman" <wdhindman at bellsouth.net>
Subject: Re: [AccessD] Can't open any more databases
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Message-ID: <002601c38935$321980b0$6001a8c0 at dejpolsys>
Content-Type: text/plain; charset="iso-8859-1"
RE: [AccessD] Can't open any more databases...something wrong there David ...I use JIT to speed up a db, not slow it down :(((
William Hindman