Subform Recordsource by Code

[tobesurveyor via AccessMonster.com]

Good day to all,

I have a form which has approximately ten continous subforms each based upon
a saved query. At any given time only three are visible while the rest are
not. The load time of this form is 30-40 seconds based upon the backend
which is on a server. What I would like to try is cut down the load time by
only having three continous subforms and instead of the buttons making the
subforms visible, make it change the record source of the subform.

Before I attempt on the application I created a trial database to simulate
the above. I created a table called "tbldummy" and a continous form called
"frmsubform". The "tbldummy" table has the following fields:
dummyID & dummyname. The "frmsubform" I created a text field called
"dummyname" and who's control source is "dummyname".

On a form called frmMain I placed the frmsubform and two buttons. Button 1 I
created the following code:
Dim strNewRecord As String
strNewRecord = "SELECT * FROM tbldummy "

Me.frmsubform.RecordSource = strNewRecord

The second button's code was Me.frmsubform.Requery

I get an error message when clicking button 1 which says that command not
available and highlights the code: Me.frmsubform.RecordSource

am I doing this wrong?

Any help would be great.
Thanks,
CF

 

[Marshall Barton]

I have a form which has approximately ten continous subforms each based upon
a saved query. At any given time only three are visible while the rest are
not. The load time of this form is 30-40 seconds based upon the backend
which is on a server. What I would like to try is cut down the load time by
only having three continous subforms and instead of the buttons making the
subforms visible, make it change the record source of the subform.

Before I attempt on the application I created a trial database to simulate
the above. I created a table called "tbldummy" and a continous form called
"frmsubform". The "tbldummy" table has the following fields:
dummyID & dummyname. The "frmsubform" I created a text field called
"dummyname" and who's control source is "dummyname".

On a form called frmMain I placed the frmsubform and two buttons. Button 1 I
created the following code:
Dim strNewRecord As String
strNewRecord = "SELECT * FROM tbldummy "

Me.frmsubform.RecordSource = strNewRecord

The second button's code was Me.frmsubform.Requery

I get an error message when clicking button 1 which says that command not
available and highlights the code: Me.frmsubform.RecordSource

If all the subforms have the same fields in their record
source query, then you can do this.

If each subform's record source has a different field list,
then you need a separate subform for each query, then you
can get the desired effect by setting the appropriate
subform control's SourceObject property.

I think the error message is because you forgot to use the
Form property:

Me.frmsubform.FORM.RecordSource = strNewRecord

 

[Guest]

I use a similar technique to simplify an otherwise cluttered form my users
use to correct imported data. There are five different tables they need to
view or modify. In this case I use only one subform object on my form and
change the size and source object of the subform object. Here is the code
from one as an example:

Private Sub cmdPerformAcctUnit_Click()
On Error GoTo Err_cmdPerformAcctUnit_Click

Me.fsubTableEdit.SourceObject = "frmAcctUnit"
DoCmd.Maximize
Me.fsubTableEdit.Width = 2940
Me.cmdChartOfAccounts.FontBold = False
Me.cmdPerformAcctUnit.FontBold = True
Me.cmdTaskTable.FontBold = False
Me.cmdAttributes.FontBold = False
Me.cmdEmpTable.FontBold = False

Exit_cmdPerformAcctUnit_Click:
Exit Sub

Err_cmdPerformAcctUnit_Click:
MsgBox Err.Description
Resume Exit_cmdPerformAcctUnit_Click

End Sub