Call subform only when needed

R

redFred

Hi...I have a main form that has a subform that has many subforms. I only
need one at a time. (1 form, 1 subform, which has many subforms.)

I call the first subform from a command button on the main form. I use a
tab control on the lower subform for form control. One tab has many possible
subforms.

I wish to only load/show the subform appropriate based on criteria in the
first subform. I can accomplish this by making the subforms invisible until
needed then making them visible. That works but really creates a speed issue
as there are many possibilities.

How do I call the desired sub-subform only based on criteria in the subform?
Bottom line -- the sub-subform is not loaded as a subform until its called.

Is there a way? How?

Thanks.
 
K

Klatuu

I did a similar setup where I use one subform control for all possible
subforms. I left the height the same, but set the width depending on the sub
form I was loading. Here are click events from two command buttons that load
the desired form. There are about 6 or 7, but these should give you the idea.

Private Sub cmdChartOfAccounts_Click()
On Error GoTo Err_cmdChartOfAccounts_Click
Dim rst As Recordset

Me.fsubTableEdit.SourceObject = "frmsubChartOfAccounts"
DoCmd.Maximize
Me.fsubTableEdit.Width = 5616
Me.cmdChartOfAccounts.FontBold = True
Me.cmdPerformAcctUnit.FontBold = False
Me.cmdTaskTable.FontBold = False
Me.cmdAttributes.FontBold = False

Set rst = Me!fsubTableEdit.Form.RecordsetClone
rst.FindFirst "[CIS] = '" & Me!frmSubTaskValidation!AcctCategory & "'"
If Not rst.NoMatch Then
Me!fsubTableEdit.Form.Bookmark = rst.Bookmark
End If
Set rst = Nothing

Exit_cmdChartOfAccounts_Click:
Exit Sub

Err_cmdChartOfAccounts_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdChartOfAccounts_Click

End Sub
***************
Private Sub cmdPerformAcctUnit_Click()
On Error GoTo Err_cmdPerformAcctUnit_Click
Dim rst As Recordset

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

Set rst = Me!fsubTableEdit.Form.RecordsetClone
rst.FindFirst "[PerformAcctUnit] = '" &
Me!frmSubTaskValidation!PerformAcctUnit & "'"
If Not rst.NoMatch Then
Me!fsubTableEdit.Form.Bookmark = rst.Bookmark
End If
Set rst = Nothing

Exit_cmdPerformAcctUnit_Click:
Exit Sub

Err_cmdPerformAcctUnit_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdPerformAcctUnit_Click

End Sub
 
R

redFred

Thank you so much, Dave. I think that will work for me.

Klatuu said:
I did a similar setup where I use one subform control for all possible
subforms. I left the height the same, but set the width depending on the sub
form I was loading. Here are click events from two command buttons that load
the desired form. There are about 6 or 7, but these should give you the idea.

Private Sub cmdChartOfAccounts_Click()
On Error GoTo Err_cmdChartOfAccounts_Click
Dim rst As Recordset

Me.fsubTableEdit.SourceObject = "frmsubChartOfAccounts"
DoCmd.Maximize
Me.fsubTableEdit.Width = 5616
Me.cmdChartOfAccounts.FontBold = True
Me.cmdPerformAcctUnit.FontBold = False
Me.cmdTaskTable.FontBold = False
Me.cmdAttributes.FontBold = False

Set rst = Me!fsubTableEdit.Form.RecordsetClone
rst.FindFirst "[CIS] = '" & Me!frmSubTaskValidation!AcctCategory & "'"
If Not rst.NoMatch Then
Me!fsubTableEdit.Form.Bookmark = rst.Bookmark
End If
Set rst = Nothing

Exit_cmdChartOfAccounts_Click:
Exit Sub

Err_cmdChartOfAccounts_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdChartOfAccounts_Click

End Sub
***************
Private Sub cmdPerformAcctUnit_Click()
On Error GoTo Err_cmdPerformAcctUnit_Click
Dim rst As Recordset

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

Set rst = Me!fsubTableEdit.Form.RecordsetClone
rst.FindFirst "[PerformAcctUnit] = '" &
Me!frmSubTaskValidation!PerformAcctUnit & "'"
If Not rst.NoMatch Then
Me!fsubTableEdit.Form.Bookmark = rst.Bookmark
End If
Set rst = Nothing

Exit_cmdPerformAcctUnit_Click:
Exit Sub

Err_cmdPerformAcctUnit_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdPerformAcctUnit_Click

End Sub

--
Dave Hargis, Microsoft Access MVP


redFred said:
Hi...I have a main form that has a subform that has many subforms. I only
need one at a time. (1 form, 1 subform, which has many subforms.)

I call the first subform from a command button on the main form. I use a
tab control on the lower subform for form control. One tab has many possible
subforms.

I wish to only load/show the subform appropriate based on criteria in the
first subform. I can accomplish this by making the subforms invisible until
needed then making them visible. That works but really creates a speed issue
as there are many possibilities.

How do I call the desired sub-subform only based on criteria in the subform?
Bottom line -- the sub-subform is not loaded as a subform until its called.

Is there a way? How?

Thanks.
Click to expand...
Click to expand...
 
K

Klatuu

Good. If you have any questions on the code or have any issues getting it to
work, post back.
--
Dave Hargis, Microsoft Access MVP


redFred said:
Thank you so much, Dave. I think that will work for me.

Klatuu said:
I did a similar setup where I use one subform control for all possible
subforms. I left the height the same, but set the width depending on the sub
form I was loading. Here are click events from two command buttons that load
the desired form. There are about 6 or 7, but these should give you the idea.

Private Sub cmdChartOfAccounts_Click()
On Error GoTo Err_cmdChartOfAccounts_Click
Dim rst As Recordset

Me.fsubTableEdit.SourceObject = "frmsubChartOfAccounts"
DoCmd.Maximize
Me.fsubTableEdit.Width = 5616
Me.cmdChartOfAccounts.FontBold = True
Me.cmdPerformAcctUnit.FontBold = False
Me.cmdTaskTable.FontBold = False
Me.cmdAttributes.FontBold = False

Set rst = Me!fsubTableEdit.Form.RecordsetClone
rst.FindFirst "[CIS] = '" & Me!frmSubTaskValidation!AcctCategory & "'"
If Not rst.NoMatch Then
Me!fsubTableEdit.Form.Bookmark = rst.Bookmark
End If
Set rst = Nothing

Exit_cmdChartOfAccounts_Click:
Exit Sub

Err_cmdChartOfAccounts_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdChartOfAccounts_Click

End Sub
***************
Private Sub cmdPerformAcctUnit_Click()
On Error GoTo Err_cmdPerformAcctUnit_Click
Dim rst As Recordset

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

Set rst = Me!fsubTableEdit.Form.RecordsetClone
rst.FindFirst "[PerformAcctUnit] = '" &
Me!frmSubTaskValidation!PerformAcctUnit & "'"
If Not rst.NoMatch Then
Me!fsubTableEdit.Form.Bookmark = rst.Bookmark
End If
Set rst = Nothing

Exit_cmdPerformAcctUnit_Click:
Exit Sub

Err_cmdPerformAcctUnit_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdPerformAcctUnit_Click

End Sub

--
Dave Hargis, Microsoft Access MVP


redFred said:
Hi...I have a main form that has a subform that has many subforms. I only
need one at a time. (1 form, 1 subform, which has many subforms.)

I call the first subform from a command button on the main form. I use a
tab control on the lower subform for form control. One tab has many possible
subforms.

I wish to only load/show the subform appropriate based on criteria in the
first subform. I can accomplish this by making the subforms invisible until
needed then making them visible. That works but really creates a speed issue
as there are many possibilities.

How do I call the desired sub-subform only based on criteria in the subform?
Bottom line -- the sub-subform is not loaded as a subform until its called.

Is there a way? How?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

Hi...I have a main form that has a subform that has many subforms. I only
need one at a time. (1 form, 1 subform, which has many subforms.)

I call the first subform from a command button on the main form. I use a
tab control on the lower subform for form control. One tab has many possible
subforms.
Click to expand...

What's the code on this command button? Are you actually opening a new form;
or is this in fact a Subform, a box on the main form which contains your
subsidiary form? If it's a Subform then you don't - can't!!! - "open" it; it
in fact opens before the mainform does!
I wish to only load/show the subform appropriate based on criteria in the
first subform. I can accomplish this by making the subforms invisible until
needed then making them visible. That works but really creates a speed issue
as there are many possibilities.

How do I call the desired sub-subform only based on criteria in the subform?
Bottom line -- the sub-subform is not loaded as a subform until its called.
Click to expand...

You can set the SourceObject property of a Subform control in code. But this
might be better done by using just ONE subform, and linking it to the mainform
by an appropriate choice of Master/Child Link Fields rather than changing the
query.

How do all these subforms differ? Are they based on different subsets of the
same table, different tables, or what...?

John W. Vinson [MVP]
 
D

Dale Fye

I've used techniques similiar to Dave, where I use a combo box to select the
sub-form I want to display, and have the actual control source name as the
second column (hidden) in the combo box. The code would look something like:

Private Sub cbo_WhichSubForm_AfterUpdate

me.sub_MySubform.SourceObject = me.cbo_WhichSubForm.column(1)

End sub

Obviously, Dave has a bunch of command buttons which look like they are on
the main sub form that he is also changing. I generally put this in the
lowest level subform, so that I don't have to maniuplate them whenever I
change the SourceObject.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



Klatuu said:
I did a similar setup where I use one subform control for all possible
subforms. I left the height the same, but set the width depending on the sub
form I was loading. Here are click events from two command buttons that load
the desired form. There are about 6 or 7, but these should give you the idea.

Private Sub cmdChartOfAccounts_Click()
On Error GoTo Err_cmdChartOfAccounts_Click
Dim rst As Recordset

Me.fsubTableEdit.SourceObject = "frmsubChartOfAccounts"
DoCmd.Maximize
Me.fsubTableEdit.Width = 5616
Me.cmdChartOfAccounts.FontBold = True
Me.cmdPerformAcctUnit.FontBold = False
Me.cmdTaskTable.FontBold = False
Me.cmdAttributes.FontBold = False

Set rst = Me!fsubTableEdit.Form.RecordsetClone
rst.FindFirst "[CIS] = '" & Me!frmSubTaskValidation!AcctCategory & "'"
If Not rst.NoMatch Then
Me!fsubTableEdit.Form.Bookmark = rst.Bookmark
End If
Set rst = Nothing

Exit_cmdChartOfAccounts_Click:
Exit Sub

Err_cmdChartOfAccounts_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdChartOfAccounts_Click

End Sub
***************
Private Sub cmdPerformAcctUnit_Click()
On Error GoTo Err_cmdPerformAcctUnit_Click
Dim rst As Recordset

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

Set rst = Me!fsubTableEdit.Form.RecordsetClone
rst.FindFirst "[PerformAcctUnit] = '" &
Me!frmSubTaskValidation!PerformAcctUnit & "'"
If Not rst.NoMatch Then
Me!fsubTableEdit.Form.Bookmark = rst.Bookmark
End If
Set rst = Nothing

Exit_cmdPerformAcctUnit_Click:
Exit Sub

Err_cmdPerformAcctUnit_Click:
MsgBox Err.DESCRIPTION
Resume Exit_cmdPerformAcctUnit_Click

End Sub

--
Dave Hargis, Microsoft Access MVP


redFred said:
Hi...I have a main form that has a subform that has many subforms. I only
need one at a time. (1 form, 1 subform, which has many subforms.)

I call the first subform from a command button on the main form. I use a
tab control on the lower subform for form control. One tab has many possible
subforms.

I wish to only load/show the subform appropriate based on criteria in the
first subform. I can accomplish this by making the subforms invisible until
needed then making them visible. That works but really creates a speed issue
as there are many possibilities.

How do I call the desired sub-subform only based on criteria in the subform?
Bottom line -- the sub-subform is not loaded as a subform until its called.

Is there a way? How?

Thanks.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subform help for a case management database 7
Access Reference Subform's Control 0
Access Main form/Subform Navigation 0
Subform shows as continuous in design mode but as datasheet in form mode?? 1
PLS HELP~! Simple Question: Master & Child Link without subform 3
Linking one subform to another subform 7
How to always get the top level form reference, when calling from any subform/subform... 0
Printing a single record from a tab control subform 10

Top