subform recordsource

[Maureen]

I have an access 2002 DB - split frontend/backend -
devleoped on WinXP which needs to run on a WinNT server.

One of my forms has 5 tabbed controls (pages) each of
which contains a separate subform, all linked to the
mainform. The record source for each subform is a query -
all calc's have been removed from the queries.

Problem is - the form is too slow. I'm trying to follow
a suggestion to remove the recordsourse of each subform
and then only supply that info once that tab/page is
selected (clicked on).

I need to use macros since I do not know visual basic -
or I need to have some kind soul provide me with the
visbasic so I can modify the existing coding.

Can anyone offer some detailed assistance, please??

Thank you!

 

[Sandra Daigle]

Hi Maureen,

Instead of setting/unsetting the recordsources, I generally bind/unbind the
suborm by setting/unsetting the Sourceobject of the subforms. Create an
OnChange event for the tabcontrol itself that sets the SourceObject property
of correct subform based on the new value of the tabcontrol. Remember that
the value of the tab control is actually the page index of the current tab
page where 0 refers to the first tab page.

When I design a complex for this way, my onChange event also clears the
SourceObject of the subform that is being hidden. Here's some sample code -
note that on each of the cases instead of using absolute page numbers I get
the page number by getting the pageindex of a named page. I do this because
the page index of a page changes if you reorder the pages but I tend to
leave the names alone once I've set them. This just reduces maintenance and
prevents weird errors when you reorder the pages but forget to adjust the
code.

To be honest, I'm not sure how to do this with macros (or whether you'd want
to because of no error handling). Here's how to do it with VBA:

'Module level variable
Private mIntCurTabPage As Integer

Private Sub TabCtl8_Change()
'Clear the SourceObject of subform on tabpage we're leaving
Select Case mIntCurTabPage
Case Me.TabCtl8.Pages("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = vbNullString
End Select

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = "sfrmOrders"
Case Me.TabCtl8.Pages("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = "sfrmOrders2"
Case Me.TabCtl8.Pages("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = "sfrmOrders3"
Case Me.TabCtl8.Pages("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = "sfrmOrders4"
End Select
mIntCurTabPage = Me.TabCtl8
End Sub

 

[Maureen]

Thanks Sandra!

I want to try your suggestion - but I am totally
unfamiliar with vis basic coding so I have a few
questions for you first:

Since tab pages 1,2,3,4 are dependent on what is
displayed on the preceeding tab(s) in order, then I don't
think I want to include the CLEAR sourceobject part of
the code you so kindly supplied - WHAT LINES exactly to I
omit?

Also - to inset this - do I just go into the visbasic
window, click in the leftmost column on the main form
which holds all of these subforms on tabs, click on the
INSERT MODULE button and type in your code, replacing
control/form names with my specific info?

Is that all I'll need to do??? I'm so willing to give
this a try - I desperately need to get this to work!
THANKS VERY MUCH!

-----Original Message-----
Hi Maureen,

Instead of setting/unsetting the recordsources, I generally bind/unbind the
suborm by setting/unsetting the Sourceobject of the subforms. Create an
OnChange event for the tabcontrol itself that sets the SourceObject property
of correct subform based on the new value of the tabcontrol. Remember that
the value of the tab control is actually the page index of the current tab
page where 0 refers to the first tab page.

When I design a complex for this way, my onChange event also clears the
SourceObject of the subform that is being hidden. Here's some sample code -
note that on each of the cases instead of using absolute page numbers I get
the page number by getting the pageindex of a named page. I do this because
the page index of a page changes if you reorder the pages but I tend to
leave the names alone once I've set them. This just reduces maintenance and
prevents weird errors when you reorder the pages but forget to adjust the
code.

To be honest, I'm not sure how to do this with macros (or whether you'd want
to because of no error handling). Here's how to do it with VBA:

'Module level variable
Private mIntCurTabPage As Integer

Private Sub TabCtl8_Change()
'Clear the SourceObject of subform on tabpage we're leaving
Select Case mIntCurTabPage
Case Me.TabCtl8.Pages("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = vbNullString
End Select

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = "sfrmOrders"
Case Me.TabCtl8.Pages("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = "sfrmOrders2"
Case Me.TabCtl8.Pages("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = "sfrmOrders3"
Case Me.TabCtl8.Pages("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = "sfrmOrders4"
End Select
mIntCurTabPage = Me.TabCtl8
End Sub

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

I have an access 2002 DB - split frontend/backend -
devleoped on WinXP which needs to run on a WinNT server.

One of my forms has 5 tabbed controls (pages) each of
which contains a separate subform, all linked to the
mainform. The record source for each subform is a query -
all calc's have been removed from the queries.

Problem is - the form is too slow. I'm trying to follow
a suggestion to remove the recordsourse of each subform
and then only supply that info once that tab/page is
selected (clicked on).

I need to use macros since I do not know visual basic -
or I need to have some kind soul provide me with the
visbasic so I can modify the existing coding.

Can anyone offer some detailed assistance, please??

Thank you!

.

 

[Sandra Daigle]

Hi Maureen,

Omit the first Select . . . End Select structure.

To add this code select your tab control (the entire control not just a
page). Then open the property sheet (F4 or View->Properties). In the
property sheet under the event tab find the Change event. Put your cursor in
the property text box then click the down arrow and select "[Event
Procedure]". Then click the Builder button ("..."). This will open the VB
Editor in the Class module for your form. It will also create the Event
procedure itself (with the proper procedure declaration and termination).
Just copy the guts of the code into the procedure.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Thanks Sandra!

I want to try your suggestion - but I am totally
unfamiliar with vis basic coding so I have a few
questions for you first:

Since tab pages 1,2,3,4 are dependent on what is
displayed on the preceeding tab(s) in order, then I don't
think I want to include the CLEAR sourceobject part of
the code you so kindly supplied - WHAT LINES exactly to I
omit?

Also - to inset this - do I just go into the visbasic
window, click in the leftmost column on the main form
which holds all of these subforms on tabs, click on the
INSERT MODULE button and type in your code, replacing
control/form names with my specific info?

Is that all I'll need to do??? I'm so willing to give
this a try - I desperately need to get this to work!
THANKS VERY MUCH!

-----Original Message-----
Hi Maureen,

Instead of setting/unsetting the recordsources, I generally
bind/unbind the suborm by setting/unsetting the Sourceobject of the
subforms. Create an OnChange event for the tabcontrol itself that
sets the SourceObject property of correct subform based on the new
value of the tabcontrol. Remember that the value of the tab control
is actually the page index of the current tab page where 0 refers to
the first tab page.

When I design a complex for this way, my onChange event also clears
the SourceObject of the subform that is being hidden. Here's some
sample code - note that on each of the cases instead of using
absolute page numbers I get the page number by getting the pageindex
of a named page. I do this because the page index of a page changes
if you reorder the pages but I tend to leave the names alone once
I've set them. This just reduces maintenance and prevents weird
errors when you reorder the pages but forget to adjust the code.

To be honest, I'm not sure how to do this with macros (or whether
you'd want to because of no error handling). Here's how to do it
with VBA:

'Module level variable
Private mIntCurTabPage As Integer

Private Sub TabCtl8_Change()
'Clear the SourceObject of subform on tabpage we're leaving
Select Case mIntCurTabPage
Case Me.TabCtl8.Pages("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = vbNullString
End Select

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = "sfrmOrders"
Case Me.TabCtl8.Pages("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = "sfrmOrders2"
Case Me.TabCtl8.Pages("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = "sfrmOrders3"
Case Me.TabCtl8.Pages("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = "sfrmOrders4"
End Select
mIntCurTabPage = Me.TabCtl8
End Sub

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

I have an access 2002 DB - split frontend/backend -
devleoped on WinXP which needs to run on a WinNT server.

One of my forms has 5 tabbed controls (pages) each of
which contains a separate subform, all linked to the
mainform. The record source for each subform is a query -
all calc's have been removed from the queries.

Problem is - the form is too slow. I'm trying to follow
a suggestion to remove the recordsourse of each subform
and then only supply that info once that tab/page is
selected (clicked on).

I need to use macros since I do not know visual basic -
or I need to have some kind soul provide me with the
visbasic so I can modify the existing coding.

Can anyone offer some detailed assistance, please??

Thank you!

.

 

[Maureen]

Continued Thanks!!

I did just as you suggested but I'm not getting the
subforms to display - I must be missing something -

Here is the entire code I have:

Private Sub TabCtl48_Change()

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages("Quantity Break(s)").PageIndex
Me.sfrmFirst.SourceObject = "CntT QBs for current
Inquiry subform"
Case Me.TabCtl8.Pages("Mill(s) / Vendor(s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT
ProdVendors for Current Inquriy QBs subform"
Case Me.TabCtl8.Pages("Order").PageIndex
Me.sfrmThird.SourceObject = "Orders Current
Inquiry subform"
End Select
End Sub


I have 5 tabs and I really only need to get this working
on tab/pages 2, 3 & 4. Tab/pages 1 and 5 can remain as
they are (I believe - you may tell me different!).

I definitely have this code associated with the tbct148
on the change procedure - it shows that was in VisBasic
as well as in the form/tab control properties dialog box
on the OnChange event row -

Nothing happens when I click on tab/pages 2-3-4. Can you
see where I may have gone wrong?

-----Original Message-----
Hi Maureen,

Omit the first Select . . . End Select structure.

To add this code select your tab control (the entire control not just a
page). Then open the property sheet (F4 or View-
Properties). In the
property sheet under the event tab find the Change event. Put your cursor in
the property text box then click the down arrow and select "[Event
Procedure]". Then click the Builder button ("..."). This will open the VB
Editor in the Class module for your form. It will also create the Event
procedure itself (with the proper procedure declaration and termination).
Just copy the guts of the code into the procedure.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Thanks Sandra!

I want to try your suggestion - but I am totally
unfamiliar with vis basic coding so I have a few
questions for you first:

Since tab pages 1,2,3,4 are dependent on what is
displayed on the preceeding tab(s) in order, then I don't
think I want to include the CLEAR sourceobject part of
the code you so kindly supplied - WHAT LINES exactly to I
omit?

Also - to inset this - do I just go into the visbasic
window, click in the leftmost column on the main form
which holds all of these subforms on tabs, click on the
INSERT MODULE button and type in your code, replacing
control/form names with my specific info?

Is that all I'll need to do??? I'm so willing to give
this a try - I desperately need to get this to work!
THANKS VERY MUCH!

-----Original Message-----
Hi Maureen,

Instead of setting/unsetting the recordsources, I generally
bind/unbind the suborm by setting/unsetting the Sourceobject of the
subforms. Create an OnChange event for the tabcontrol itself that
sets the SourceObject property of correct subform based on the new
value of the tabcontrol. Remember that the value of the tab control
is actually the page index of the current tab page where 0 refers to
the first tab page.

When I design a complex for this way, my onChange event also clears
the SourceObject of the subform that is being hidden. Here's some
sample code - note that on each of the cases instead of using
absolute page numbers I get the page number by getting the pageindex
of a named page. I do this because the page index of a page changes
if you reorder the pages but I tend to leave the names alone once
I've set them. This just reduces maintenance and prevents weird
errors when you reorder the pages but forget to adjust the code.

To be honest, I'm not sure how to do this with macros (or whether
you'd want to because of no error handling). Here's how to do it
with VBA:

'Module level variable
Private mIntCurTabPage As Integer

Private Sub TabCtl8_Change()
'Clear the SourceObject of subform on tabpage we're leaving
Select Case mIntCurTabPage
Case Me.TabCtl8.Pages("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = vbNullString
End Select

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = "sfrmOrders"
Case Me.TabCtl8.Pages("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = "sfrmOrders2"
Case Me.TabCtl8.Pages("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = "sfrmOrders3"
Case Me.TabCtl8.Pages("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = "sfrmOrders4"
End Select
mIntCurTabPage = Me.TabCtl8
End Sub

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
I have an access 2002 DB - split frontend/backend -
devleoped on WinXP which needs to run on a WinNT server.

One of my forms has 5 tabbed controls (pages) each of
which contains a separate subform, all linked to the
mainform. The record source for each subform is a query -
all calc's have been removed from the queries.

Problem is - the form is too slow. I'm trying to follow
a suggestion to remove the recordsourse of each subform
and then only supply that info once that tab/page is
selected (clicked on).

I need to use macros since I do not know visual basic -
or I need to have some kind soul provide me with the
visbasic so I can modify the existing coding.

Can anyone offer some detailed assistance, please??

Thank you!


.

.

 

[Guest]

Forgive me - the vis basic code reads as follows:


Private Sub TabCtl48_Change()

Select Case Me.TabCtl48
Case Me.TTabCtl48.Pages("CntT QBs for current
Inquiry subform").PageIndex
Me.sfrmFirst.SourceObject = "CntT QBs for current
Inquiry subform"
Case Me.TTabCtl48.Pages("CntT ProdVendors for Current
Inquriy QBs subform").PageIndex
Me.sfrmSecond.SourceObject = "CntT
ProdVendors for Current Inquriy QBs subform"
Case Me.TabCtl48.Pages("Orders Current Inquiry
subform").PageIndex
Me.sfrmThird.SourceObject = "Orders Current
Inquiry subform"
End Select
End Sub


I saved the module, compacted & repaired the DB, and
still nothing happends when I click on the tabs - do I
need to force a recompile? Do I need to do this for ALL
tab/pages and not skip the first and last tab/pages?

-----Original Message-----
Continued Thanks!!

I did just as you suggested but I'm not getting the
subforms to display - I must be missing something -

Here is the entire code I have:

Private Sub TabCtl48_Change()

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages("Quantity Break(s)").PageIndex
Me.sfrmFirst.SourceObject = "CntT QBs for current
Inquiry subform"
Case Me.TabCtl8.Pages("Mill(s) / Vendor (s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT
ProdVendors for Current Inquriy QBs subform"
Case Me.TabCtl8.Pages("Order").PageIndex
Me.sfrmThird.SourceObject = "Orders Current
Inquiry subform"
End Select
End Sub


I have 5 tabs and I really only need to get this working
on tab/pages 2, 3 & 4. Tab/pages 1 and 5 can remain as
they are (I believe - you may tell me different!).

I definitely have this code associated with the tbct148
on the change procedure - it shows that was in VisBasic
as well as in the form/tab control properties dialog box
on the OnChange event row -

Nothing happens when I click on tab/pages 2-3-4. Can you
see where I may have gone wrong?

-----Original Message-----
Hi Maureen,

Omit the first Select . . . End Select structure.

To add this code select your tab control (the entire control not just a
page). Then open the property sheet (F4 or View-
Properties). In the
property sheet under the event tab find the Change event. Put your cursor in
the property text box then click the down arrow and select "[Event
Procedure]". Then click the Builder button ("...").

This
will open the VB

Editor in the Class module for your form. It will also create the Event
procedure itself (with the proper procedure declaration and termination).
Just copy the guts of the code into the procedure.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Thanks Sandra!

I want to try your suggestion - but I am totally
unfamiliar with vis basic coding so I have a few
questions for you first:

Since tab pages 1,2,3,4 are dependent on what is
displayed on the preceeding tab(s) in order, then I don't
think I want to include the CLEAR sourceobject part of
the code you so kindly supplied - WHAT LINES exactly to I
omit?

Also - to inset this - do I just go into the visbasic
window, click in the leftmost column on the main form
which holds all of these subforms on tabs, click on the
INSERT MODULE button and type in your code, replacing
control/form names with my specific info?

Is that all I'll need to do??? I'm so willing to give
this a try - I desperately need to get this to work!
THANKS VERY MUCH!



-----Original Message-----
Hi Maureen,

Instead of setting/unsetting the recordsources, I generally
bind/unbind the suborm by setting/unsetting the Sourceobject of the
subforms. Create an OnChange event for the

tabcontrol
itself that hidden.
Here's some macros
(or whether

you'd want to because of no error handling). Here's how to do it
with VBA:

'Module level variable
Private mIntCurTabPage As Integer

Private Sub TabCtl8_Change()
'Clear the SourceObject of subform on tabpage we're leaving
Select Case mIntCurTabPage
Case Me.TabCtl8.Pages("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = vbNullString
End Select

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages ("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = "sfrmOrders"
Case Me.TabCtl8.Pages ("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = "sfrmOrders2"
Case Me.TabCtl8.Pages("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = "sfrmOrders3"
Case Me.TabCtl8.Pages ("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = "sfrmOrders4"
End Select
mIntCurTabPage = Me.TabCtl8
End Sub

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
I have an access 2002 DB - split frontend/backend -
devleoped on WinXP which needs to run on a WinNT server.

One of my forms has 5 tabbed controls (pages) each of
which contains a separate subform, all linked to the
mainform. The record source for each subform is a query -
all calc's have been removed from the queries.

Problem is - the form is too slow. I'm trying to follow
a suggestion to remove the recordsourse of each subform
and then only supply that info once that tab/page is
selected (clicked on).

I need to use macros since I do not know visual basic -
or I need to have some kind soul provide me with the
visbasic so I can modify the existing coding.

Can anyone offer some detailed assistance, please??

Thank you!


.

.

.

 

[Sandra Daigle]

Hi Maureen,

Looking at the first case only as an example:

Is "CntT QBs for current Inquiry subform" the name of the subform or the tab
page? You should have the name of the tab page in the Case statement in
parentheses after "Pages".

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Forgive me - the vis basic code reads as follows:


Private Sub TabCtl48_Change()

Select Case Me.TabCtl48
Case Me.TTabCtl48.Pages("CntT QBs for current
Inquiry subform").PageIndex
Me.sfrmFirst.SourceObject = "CntT QBs for current
Inquiry subform"
Case Me.TTabCtl48.Pages("CntT ProdVendors for Current
Inquriy QBs subform").PageIndex
Me.sfrmSecond.SourceObject = "CntT
ProdVendors for Current Inquriy QBs subform"
Case Me.TabCtl48.Pages("Orders Current Inquiry
subform").PageIndex
Me.sfrmThird.SourceObject = "Orders Current
Inquiry subform"
End Select
End Sub


I saved the module, compacted & repaired the DB, and
still nothing happends when I click on the tabs - do I
need to force a recompile? Do I need to do this for ALL
tab/pages and not skip the first and last tab/pages?

-----Original Message-----
Continued Thanks!!

I did just as you suggested but I'm not getting the
subforms to display - I must be missing something -

Here is the entire code I have:

Private Sub TabCtl48_Change()

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages("Quantity Break(s)").PageIndex
Me.sfrmFirst.SourceObject = "CntT QBs for current
Inquiry subform"
Case Me.TabCtl8.Pages("Mill(s) / Vendor (s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT
ProdVendors for Current Inquriy QBs subform"
Case Me.TabCtl8.Pages("Order").PageIndex
Me.sfrmThird.SourceObject = "Orders Current
Inquiry subform"
End Select
End Sub


I have 5 tabs and I really only need to get this working
on tab/pages 2, 3 & 4. Tab/pages 1 and 5 can remain as
they are (I believe - you may tell me different!).

I definitely have this code associated with the tbct148
on the change procedure - it shows that was in VisBasic
as well as in the form/tab control properties dialog box
on the OnChange event row -

Nothing happens when I click on tab/pages 2-3-4. Can you
see where I may have gone wrong?

-----Original Message-----
Hi Maureen,

Omit the first Select . . . End Select structure.

To add this code select your tab control (the entire control not
just a page). Then open the property sheet (F4 or View-
Properties). In the
property sheet under the event tab find the Change event. Put your
cursor in the property text box then click the down arrow and
select "[Event Procedure]". Then click the Builder button ("...").
This will open the VB Editor in the Class module for your form. It
will also create the Event procedure itself (with the proper
procedure declaration and termination). Just copy the guts of the
code into the procedure.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
Thanks Sandra!

I want to try your suggestion - but I am totally
unfamiliar with vis basic coding so I have a few
questions for you first:

Since tab pages 1,2,3,4 are dependent on what is
displayed on the preceeding tab(s) in order, then I don't
think I want to include the CLEAR sourceobject part of
the code you so kindly supplied - WHAT LINES exactly to I
omit?

Also - to inset this - do I just go into the visbasic
window, click in the leftmost column on the main form
which holds all of these subforms on tabs, click on the
INSERT MODULE button and type in your code, replacing
control/form names with my specific info?

Is that all I'll need to do??? I'm so willing to give
this a try - I desperately need to get this to work!
THANKS VERY MUCH!



-----Original Message-----
Hi Maureen,

Instead of setting/unsetting the recordsources, I generally
bind/unbind the suborm by setting/unsetting the Sourceobject of
the subforms. Create an OnChange event for the tabcontrol itself
that sets the SourceObject property of correct subform based on
the new value of the tabcontrol. Remember that the value of the
tab control is actually the page index of the current tab page
where 0 refers to the first tab page.

When I design a complex for this way, my onChange event also
clears the SourceObject of the subform that is being hidden.
Here's some sample code - note that on each of the cases instead
of using absolute page numbers I get the page number by getting
the pageindex of a named page. I do this because the page index
of a page changes if you reorder the pages but I tend to leave
the names alone once I've set them. This just reduces maintenance
and prevents weird errors when you reorder the pages but forget
to adjust the code.

To be honest, I'm not sure how to do this with macros (or whether
you'd want to because of no error handling). Here's how to do it
with VBA:

'Module level variable
Private mIntCurTabPage As Integer

Private Sub TabCtl8_Change()
'Clear the SourceObject of subform on tabpage we're leaving
Select Case mIntCurTabPage
Case Me.TabCtl8.Pages("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = vbNullString
Case Me.TabCtl8.Pages("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = vbNullString
End Select

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages ("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = "sfrmOrders"
Case Me.TabCtl8.Pages ("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = "sfrmOrders2"
Case Me.TabCtl8.Pages("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = "sfrmOrders3"
Case Me.TabCtl8.Pages ("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = "sfrmOrders4"
End Select
mIntCurTabPage = Me.TabCtl8
End Sub

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
I have an access 2002 DB - split frontend/backend -
devleoped on WinXP which needs to run on a WinNT server.

One of my forms has 5 tabbed controls (pages) each of
which contains a separate subform, all linked to the
mainform. The record source for each subform is a query -
all calc's have been removed from the queries.

Problem is - the form is too slow. I'm trying to follow
a suggestion to remove the recordsourse of each subform
and then only supply that info once that tab/page is
selected (clicked on).

I need to use macros since I do not know visual basic -
or I need to have some kind soul provide me with the
visbasic so I can modify the existing coding.

Can anyone offer some detailed assistance, please??

Thank you!


.


.

.

 

[Maureen]

THANKS SANDRA! I don't want to give up - I appreciate
your continued assistance! Please hang in there with me!!

The "CntT Qbs for current Inquiry subform" text was the
NAME of the subform. However, I have also tried it with
the Name of the Page "Quantity Break(s)" as displayed in
the Page properties dialog box and the procedure still
does not work.

The two lines of code are currently as follows:

Case Me.TabCtl48.Pages("Quantity Break(s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT QBs for
current Inquiry subform"

I added the first tab/page to the code also which is why
you see "sfrmSecond" in the above text.

I can't imagine what's missing or incorrect - I've
searched - PLEASE HELP!

-----Original Message-----
Hi Maureen,

Looking at the first case only as an example:

Is "CntT QBs for current Inquiry subform" the name of the subform or the tab
page? You should have the name of the tab page in the Case statement in
parentheses after "Pages".

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Forgive me - the vis basic code reads as follows:


Private Sub TabCtl48_Change()

Select Case Me.TabCtl48
Case Me.TTabCtl48.Pages("CntT QBs for current
Inquiry subform").PageIndex
Me.sfrmFirst.SourceObject = "CntT QBs for current
Inquiry subform"
Case Me.TTabCtl48.Pages("CntT ProdVendors for Current
Inquriy QBs subform").PageIndex
Me.sfrmSecond.SourceObject = "CntT
ProdVendors for Current Inquriy QBs subform"
Case Me.TabCtl48.Pages("Orders Current Inquiry
subform").PageIndex
Me.sfrmThird.SourceObject = "Orders Current
Inquiry subform"
End Select
End Sub


I saved the module, compacted & repaired the DB, and
still nothing happends when I click on the tabs - do I
need to force a recompile? Do I need to do this for ALL
tab/pages and not skip the first and last tab/pages?

-----Original Message-----
Continued Thanks!!

I did just as you suggested but I'm not getting the
subforms to display - I must be missing something -

Here is the entire code I have:

Private Sub TabCtl48_Change()

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages("Quantity Break (s)").PageIndex
Me.sfrmFirst.SourceObject = "CntT QBs for current
Inquiry subform"
Case Me.TabCtl8.Pages("Mill(s) / Vendor (s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT
ProdVendors for Current Inquriy QBs subform"
Case Me.TabCtl8.Pages("Order").PageIndex
Me.sfrmThird.SourceObject = "Orders Current
Inquiry subform"
End Select
End Sub


I have 5 tabs and I really only need to get this working
on tab/pages 2, 3 & 4. Tab/pages 1 and 5 can remain as
they are (I believe - you may tell me different!).

I definitely have this code associated with the tbct148
on the change procedure - it shows that was in VisBasic
as well as in the form/tab control properties dialog box
on the OnChange event row -

Nothing happens when I click on tab/pages 2-3-4. Can you
see where I may have gone wrong?
-----Original Message-----
Hi Maureen,

Omit the first Select . . . End Select structure.

To add this code select your tab control (the entire control not
just a page). Then open the property sheet (F4 or View-
Properties). In the
property sheet under the event tab find the Change event. Put your
cursor in the property text box then click the down arrow and
select "[Event Procedure]". Then click the Builder button ("...").
This will open the VB Editor in the Class module for your form. It
will also create the Event procedure itself (with the proper
procedure declaration and termination). Just copy the guts of the
code into the procedure.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
Thanks Sandra!

I want to try your suggestion - but I am totally
unfamiliar with vis basic coding so I have a few
questions for you first:

Since tab pages 1,2,3,4 are dependent on what is
displayed on the preceeding tab(s) in order, then I don't
think I want to include the CLEAR sourceobject part of
the code you so kindly supplied - WHAT LINES exactly to I
omit?

Also - to inset this - do I just go into the visbasic
window, click in the leftmost column on the main form
which holds all of these subforms on tabs, click on the
INSERT MODULE button and type in your code, replacing
control/form names with my specific info?

Is that all I'll need to do??? I'm so willing to give
this a try - I desperately need to get this to work!
THANKS VERY MUCH!



-----Original Message-----
Hi Maureen,

Instead of setting/unsetting the recordsources, I generally
bind/unbind the suborm by setting/unsetting the Sourceobject of
the subforms. Create an OnChange event for the tabcontrol itself
that sets the SourceObject property of correct subform based on
the new value of the tabcontrol. Remember that the value of the
tab control is actually the page index of the current tab page
where 0 refers to the first tab page.

When I design a complex for this way, my onChange event also
clears the SourceObject of the subform that is being hidden.
Here's some sample code - note that on each of the cases instead
of using absolute page numbers I get the page number by getting
the pageindex of a named page. I do this because the page index
of a page changes if you reorder the pages but I tend to leave
the names alone once I've set them. This just reduces maintenance
and prevents weird errors when you reorder the pages but forget
to adjust the code.

To be honest, I'm not sure how to do this with macros (or whether
you'd want to because of no error handling). Here's how to do it
with VBA:

'Module level variable
Private mIntCurTabPage As Integer

Private Sub TabCtl8_Change()
'Clear the SourceObject of subform on tabpage we're leaving
Select Case mIntCurTabPage
Case Me.TabCtl8.Pages ("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = vbNullString
End Select

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages ("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = "sfrmOrders"
Case Me.TabCtl8.Pages ("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = "sfrmOrders2"
Case Me.TabCtl8.Pages ("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = "sfrmOrders3"
Case Me.TabCtl8.Pages ("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = "sfrmOrders4"
End Select
mIntCurTabPage = Me.TabCtl8
End Sub

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
I have an access 2002 DB - split frontend/backend -
devleoped on WinXP which needs to run on a WinNT server.

One of my forms has 5 tabbed controls (pages) each of
which contains a separate subform, all linked to the
mainform. The record source for each subform is a query -
all calc's have been removed from the queries.

Problem is - the form is too slow. I'm trying to follow
a suggestion to remove the recordsourse of each subform
and then only supply that info once that tab/page is
selected (clicked on).

I need to use macros since I do not know visual basic -
or I need to have some kind soul provide me with the
visbasic so I can modify the existing coding.

Can anyone offer some detailed assistance, please??

Thank you!


.


.

.

.

 

[Maureen]

If it helps - here is another copy of the full procedure -
every line is exactly as shown below:

Private Sub TabCtl48_Change()
Private mIntCurTabPage As Integer
Select Case Me.TabCtl48
Case Me.TabCtl48.Pages("Product(s)").PageIndex
Me.sfrmFirst.SourceObject = "InqProd Form Query
subform1"
Case Me.TabCtl48.Pages("Quantity Break(s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT QBs for
current Inquiry subform"
Case Me.TabCtl48.Pages("Mill(s) / Vendor
(s)").PageIndex
Me.sfrmThird.SourceObject = "CntT ProdVendors
for Current Inquriy QBs subform"
Case Me.TabCtl48.Pages("Order").PageIndex
Me.sfrm.SourceObject = "Orders Current
Inquiry subform"
End Select
mIntCurTabPage = Me.TabCtl48
End Sub



Nothing happens when I click on the tab/pages. No
subforms display - nothing.

Please help!

-----Original Message-----
THANKS SANDRA! I don't want to give up - I appreciate
your continued assistance! Please hang in there with me!!

The "CntT Qbs for current Inquiry subform" text was the
NAME of the subform. However, I have also tried it with
the Name of the Page "Quantity Break(s)" as displayed in
the Page properties dialog box and the procedure still
does not work.

The two lines of code are currently as follows:

Case Me.TabCtl48.Pages("Quantity Break(s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT QBs for
current Inquiry subform"

I added the first tab/page to the code also which is why
you see "sfrmSecond" in the above text.

I can't imagine what's missing or incorrect - I've
searched - PLEASE HELP!

-----Original Message-----
Hi Maureen,

Looking at the first case only as an example:

Is "CntT QBs for current Inquiry subform" the name of the subform or the tab
page? You should have the name of the tab page in the Case statement in
parentheses after "Pages".

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Forgive me - the vis basic code reads as follows:


Private Sub TabCtl48_Change()

Select Case Me.TabCtl48
Case Me.TTabCtl48.Pages("CntT QBs for current
Inquiry subform").PageIndex
Me.sfrmFirst.SourceObject = "CntT QBs for current
Inquiry subform"
Case Me.TTabCtl48.Pages("CntT ProdVendors for Current
Inquriy QBs subform").PageIndex
Me.sfrmSecond.SourceObject = "CntT
ProdVendors for Current Inquriy QBs subform"
Case Me.TabCtl48.Pages("Orders Current Inquiry
subform").PageIndex
Me.sfrmThird.SourceObject = "Orders Current
Inquiry subform"
End Select
End Sub


I saved the module, compacted & repaired the DB, and
still nothing happends when I click on the tabs - do I
need to force a recompile? Do I need to do this for ALL
tab/pages and not skip the first and last tab/pages?


-----Original Message-----
Continued Thanks!!

I did just as you suggested but I'm not getting the
subforms to display - I must be missing something -

Here is the entire code I have:

Private Sub TabCtl48_Change()

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages("Quantity Break (s)").PageIndex
Me.sfrmFirst.SourceObject = "CntT QBs for current
Inquiry subform"
Case Me.TabCtl8.Pages("Mill(s) / Vendor (s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT
ProdVendors for Current Inquriy QBs subform"
Case Me.TabCtl8.Pages("Order").PageIndex
Me.sfrmThird.SourceObject = "Orders Current
Inquiry subform"
End Select
End Sub


I have 5 tabs and I really only need to get this working
on tab/pages 2, 3 & 4. Tab/pages 1 and 5 can remain as
they are (I believe - you may tell me different!).

I definitely have this code associated with the tbct148
on the change procedure - it shows that was in VisBasic
as well as in the form/tab control properties dialog box
on the OnChange event row -

Nothing happens when I click on tab/pages 2-3-4.

Can

you

entire
control not

just a page). Then open the property sheet (F4 or View-
Properties). In the
property sheet under the event tab find the Change event. Put your
cursor in the property text box then click the down arrow and
select "[Event Procedure]". Then click the Builder button ("...").
This will open the VB Editor in the Class module

for
your form. It

will also create the Event procedure itself (with the proper
procedure declaration and termination). Just copy the guts of the
code into the procedure.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
Thanks Sandra!

I want to try your suggestion - but I am totally
unfamiliar with vis basic coding so I have a few
questions for you first:

Since tab pages 1,2,3,4 are dependent on what is
displayed on the preceeding tab(s) in order, then

I

don't

part

of

on

the

the
value of the the
cases instead

of using absolute page numbers I get the page number by getting
the pageindex of a named page. I do this because the page index
of a page changes if you reorder the pages but I tend to leave
the names alone once I've set them. This just reduces maintenance
and prevents weird errors when you reorder the pages but forget
to adjust the code.

To be honest, I'm not sure how to do this with macros (or whether
you'd want to because of no error handling). Here's how to do it
with VBA:

'Module level variable
Private mIntCurTabPage As Integer

Private Sub TabCtl8_Change()
'Clear the SourceObject of subform on tabpage we're leaving
Select Case mIntCurTabPage
Case Me.TabCtl8.Pages ("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = vbNullString
End Select

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages ("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = "sfrmOrders"
Case Me.TabCtl8.Pages ("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = "sfrmOrders2"
Case Me.TabCtl8.Pages ("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = "sfrmOrders3"
Case Me.TabCtl8.Pages ("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = "sfrmOrders4"
End Select
mIntCurTabPage = Me.TabCtl8
End Sub

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
I have an access 2002 DB - split frontend/backend -
devleoped on WinXP which needs to run on a WinNT server.

One of my forms has 5 tabbed controls (pages) each of
which contains a separate subform, all linked to the
mainform. The record source for each subform is a query -
all calc's have been removed from the queries.

Problem is - the form is too slow. I'm trying

to

follow

tab/page

is


.

.

 

[Sandra Daigle]

Hi Maureen,

Are you sure that "Product(s)" is the name of the tab page? Select the tab
page by clicking on the tab, then look at the *Name* property under the
*Other* tab. Whatever is in this property should be in place of
"Product(s)".


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

If it helps - here is another copy of the full procedure -
every line is exactly as shown below:

Private Sub TabCtl48_Change()
Private mIntCurTabPage As Integer
Select Case Me.TabCtl48
Case Me.TabCtl48.Pages("Product(s)").PageIndex
Me.sfrmFirst.SourceObject = "InqProd Form Query
subform1"
Case Me.TabCtl48.Pages("Quantity Break(s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT QBs for
current Inquiry subform"
Case Me.TabCtl48.Pages("Mill(s) / Vendor
(s)").PageIndex
Me.sfrmThird.SourceObject = "CntT ProdVendors
for Current Inquriy QBs subform"
Case Me.TabCtl48.Pages("Order").PageIndex
Me.sfrm.SourceObject = "Orders Current
Inquiry subform"
End Select
mIntCurTabPage = Me.TabCtl48
End Sub



Nothing happens when I click on the tab/pages. No
subforms display - nothing.

Please help!

-----Original Message-----
THANKS SANDRA! I don't want to give up - I appreciate
your continued assistance! Please hang in there with me!!

The "CntT Qbs for current Inquiry subform" text was the
NAME of the subform. However, I have also tried it with
the Name of the Page "Quantity Break(s)" as displayed in
the Page properties dialog box and the procedure still
does not work.

The two lines of code are currently as follows:

Case Me.TabCtl48.Pages("Quantity Break(s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT QBs for
current Inquiry subform"

I added the first tab/page to the code also which is why
you see "sfrmSecond" in the above text.

I can't imagine what's missing or incorrect - I've
searched - PLEASE HELP!

-----Original Message-----
Hi Maureen,

Looking at the first case only as an example:

Is "CntT QBs for current Inquiry subform" the name of the subform
or the tab page? You should have the name of the tab page in the
Case statement in parentheses after "Pages".

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


(e-mail address removed) wrote:
Forgive me - the vis basic code reads as follows:


Private Sub TabCtl48_Change()

Select Case Me.TabCtl48
Case Me.TTabCtl48.Pages("CntT QBs for current
Inquiry subform").PageIndex
Me.sfrmFirst.SourceObject = "CntT QBs for current
Inquiry subform"
Case Me.TTabCtl48.Pages("CntT ProdVendors for Current
Inquriy QBs subform").PageIndex
Me.sfrmSecond.SourceObject = "CntT
ProdVendors for Current Inquriy QBs subform"
Case Me.TabCtl48.Pages("Orders Current Inquiry
subform").PageIndex
Me.sfrmThird.SourceObject = "Orders Current
Inquiry subform"
End Select
End Sub


I saved the module, compacted & repaired the DB, and
still nothing happends when I click on the tabs - do I
need to force a recompile? Do I need to do this for ALL
tab/pages and not skip the first and last tab/pages?


-----Original Message-----
Continued Thanks!!

I did just as you suggested but I'm not getting the
subforms to display - I must be missing something -

Here is the entire code I have:

Private Sub TabCtl48_Change()

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages("Quantity Break (s)").PageIndex
Me.sfrmFirst.SourceObject = "CntT QBs for current
Inquiry subform"
Case Me.TabCtl8.Pages("Mill(s) / Vendor (s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT
ProdVendors for Current Inquriy QBs subform"
Case Me.TabCtl8.Pages("Order").PageIndex
Me.sfrmThird.SourceObject = "Orders Current
Inquiry subform"
End Select
End Sub


I have 5 tabs and I really only need to get this working
on tab/pages 2, 3 & 4. Tab/pages 1 and 5 can remain as
they are (I believe - you may tell me different!).

I definitely have this code associated with the tbct148
on the change procedure - it shows that was in VisBasic
as well as in the form/tab control properties dialog box
on the OnChange event row -

Nothing happens when I click on tab/pages 2-3-4. Can you
see where I may have gone wrong?
-----Original Message-----
Hi Maureen,

Omit the first Select . . . End Select structure.

To add this code select your tab control (the entire control not
just a page). Then open the property sheet (F4 or View-
Properties). In the
property sheet under the event tab find the Change event. Put
your cursor in the property text box then click the down arrow
and select "[Event Procedure]". Then click the Builder button
("..."). This will open the VB Editor in the Class module for
your form. It will also create the Event procedure itself (with
the proper procedure declaration and termination). Just copy the
guts of the code into the procedure.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
Thanks Sandra!

I want to try your suggestion - but I am totally
unfamiliar with vis basic coding so I have a few
questions for you first:

Since tab pages 1,2,3,4 are dependent on what is
displayed on the preceeding tab(s) in order, then I don't
think I want to include the CLEAR sourceobject part of
the code you so kindly supplied - WHAT LINES exactly to I
omit?

Also - to inset this - do I just go into the visbasic
window, click in the leftmost column on the main form
which holds all of these subforms on tabs, click on the
INSERT MODULE button and type in your code, replacing
control/form names with my specific info?

Is that all I'll need to do??? I'm so willing to give
this a try - I desperately need to get this to work!
THANKS VERY MUCH!



-----Original Message-----
Hi Maureen,

Instead of setting/unsetting the recordsources, I generally
bind/unbind the suborm by setting/unsetting the Sourceobject of
the subforms. Create an OnChange event for the tabcontrol
itself that sets the SourceObject property of correct subform
based on the new value of the tabcontrol. Remember that the
value of the tab control is actually the page index of the
current tab page where 0 refers to the first tab page.

When I design a complex for this way, my onChange event also
clears the SourceObject of the subform that is being hidden.
Here's some sample code - note that on each of the cases
instead of using absolute page numbers I get the page number
by getting the pageindex of a named page. I do this because
the page index of a page changes if you reorder the pages but
I tend to leave the names alone once I've set them. This just
reduces maintenance and prevents weird errors when you reorder
the pages but forget to adjust the code.

To be honest, I'm not sure how to do this with macros (or
whether you'd want to because of no error handling). Here's
how to do it with VBA:

'Module level variable
Private mIntCurTabPage As Integer

Private Sub TabCtl8_Change()
'Clear the SourceObject of subform on tabpage we're leaving
Select Case mIntCurTabPage
Case Me.TabCtl8.Pages ("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = vbNullString
End Select

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages ("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = "sfrmOrders"
Case Me.TabCtl8.Pages ("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = "sfrmOrders2"
Case Me.TabCtl8.Pages ("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = "sfrmOrders3"
Case Me.TabCtl8.Pages ("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = "sfrmOrders4"
End Select
mIntCurTabPage = Me.TabCtl8
End Sub

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
I have an access 2002 DB - split frontend/backend -
devleoped on WinXP which needs to run on a WinNT server.

One of my forms has 5 tabbed controls (pages) each of
which contains a separate subform, all linked to the
mainform. The record source for each subform is a query -
all calc's have been removed from the queries.

Problem is - the form is too slow. I'm trying to follow
a suggestion to remove the recordsourse of each subform
and then only supply that info once that tab/page is
selected (clicked on).

I need to use macros since I do not know visual basic -
or I need to have some kind soul provide me with the
visbasic so I can modify the existing coding.

Can anyone offer some detailed assistance, please??

Thank you!


.


.

.


.

.

 

[Maureen]

Hi Sandra,

The Name is "Products(s)" - it's on the Name row in the
properties dialog box for the PAGE - Each page has a
name I game it which is what is in the code.

Can you think of anything else as to why nothing happens
when I click on any of the tab/pages?

-----Original Message-----
Hi Maureen,

Are you sure that "Product(s)" is the name of the tab page? Select the tab
page by clicking on the tab, then look at the *Name* property under the
*Other* tab. Whatever is in this property should be in place of
"Product(s)".


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

If it helps - here is another copy of the full procedure -
every line is exactly as shown below:

Private Sub TabCtl48_Change()
Private mIntCurTabPage As Integer
Select Case Me.TabCtl48
Case Me.TabCtl48.Pages("Product(s)").PageIndex
Me.sfrmFirst.SourceObject = "InqProd Form Query
subform1"
Case Me.TabCtl48.Pages("Quantity Break (s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT QBs for
current Inquiry subform"
Case Me.TabCtl48.Pages("Mill(s) / Vendor
(s)").PageIndex
Me.sfrmThird.SourceObject = "CntT ProdVendors
for Current Inquriy QBs subform"
Case Me.TabCtl48.Pages("Order").PageIndex
Me.sfrm.SourceObject = "Orders Current
Inquiry subform"
End Select
mIntCurTabPage = Me.TabCtl48
End Sub



Nothing happens when I click on the tab/pages. No
subforms display - nothing.

Please help!

-----Original Message-----
THANKS SANDRA! I don't want to give up - I appreciate
your continued assistance! Please hang in there with me!!

The "CntT Qbs for current Inquiry subform" text was the
NAME of the subform. However, I have also tried it with
the Name of the Page "Quantity Break(s)" as displayed in
the Page properties dialog box and the procedure still
does not work.

The two lines of code are currently as follows:

Case Me.TabCtl48.Pages("Quantity Break(s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT QBs for
current Inquiry subform"

I added the first tab/page to the code also which is why
you see "sfrmSecond" in the above text.

I can't imagine what's missing or incorrect - I've
searched - PLEASE HELP!

-----Original Message-----
Hi Maureen,

Looking at the first case only as an example:

Is "CntT QBs for current Inquiry subform" the name of the subform
or the tab page? You should have the name of the tab page in the
Case statement in parentheses after "Pages".

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


(e-mail address removed) wrote:
Forgive me - the vis basic code reads as follows:


Private Sub TabCtl48_Change()

Select Case Me.TabCtl48
Case Me.TTabCtl48.Pages("CntT QBs for current
Inquiry subform").PageIndex
Me.sfrmFirst.SourceObject = "CntT QBs for current
Inquiry subform"
Case Me.TTabCtl48.Pages("CntT ProdVendors for Current
Inquriy QBs subform").PageIndex
Me.sfrmSecond.SourceObject = "CntT
ProdVendors for Current Inquriy QBs subform"
Case Me.TabCtl48.Pages("Orders Current Inquiry
subform").PageIndex
Me.sfrmThird.SourceObject = "Orders Current
Inquiry subform"
End Select
End Sub


I saved the module, compacted & repaired the DB, and
still nothing happends when I click on the tabs - do I
need to force a recompile? Do I need to do this for ALL
tab/pages and not skip the first and last tab/pages?


-----Original Message-----
Continued Thanks!!

I did just as you suggested but I'm not getting the
subforms to display - I must be missing something -

Here is the entire code I have:

Private Sub TabCtl48_Change()

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages("Quantity Break (s)").PageIndex
Me.sfrmFirst.SourceObject = "CntT QBs for current
Inquiry subform"
Case Me.TabCtl8.Pages("Mill(s) / Vendor (s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT
ProdVendors for Current Inquriy QBs subform"
Case Me.TabCtl8.Pages("Order").PageIndex
Me.sfrmThird.SourceObject = "Orders Current
Inquiry subform"
End Select
End Sub


I have 5 tabs and I really only need to get this working
on tab/pages 2, 3 & 4. Tab/pages 1 and 5 can remain as
they are (I believe - you may tell me different!).

I definitely have this code associated with the tbct148
on the change procedure - it shows that was in VisBasic
as well as in the form/tab control properties dialog box
on the OnChange event row -

Nothing happens when I click on tab/pages 2-3-4. Can you
see where I may have gone wrong?
-----Original Message-----
Hi Maureen,

Omit the first Select . . . End Select structure.

To add this code select your tab control (the entire control not
just a page). Then open the property sheet (F4 or View-
Properties). In the
property sheet under the event tab find the Change event. Put
your cursor in the property text box then click the down arrow
and select "[Event Procedure]". Then click the Builder button
("..."). This will open the VB Editor in the Class module for
your form. It will also create the Event procedure itself (with
the proper procedure declaration and termination). Just copy the
guts of the code into the procedure.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
Thanks Sandra!

I want to try your suggestion - but I am totally
unfamiliar with vis basic coding so I have a few
questions for you first:

Since tab pages 1,2,3,4 are dependent on what is
displayed on the preceeding tab(s) in order, then I don't
think I want to include the CLEAR sourceobject part of
the code you so kindly supplied - WHAT LINES exactly to I
omit?

Also - to inset this - do I just go into the visbasic
window, click in the leftmost column on the main form
which holds all of these subforms on tabs, click on the
INSERT MODULE button and type in your code, replacing
control/form names with my specific info?

Is that all I'll need to do??? I'm so willing to give
this a try - I desperately need to get this to work!
THANKS VERY MUCH!



-----Original Message-----
Hi Maureen,

Instead of setting/unsetting the recordsources, I generally
bind/unbind the suborm by setting/unsetting the Sourceobject of
the subforms. Create an OnChange event for the tabcontrol
itself that sets the SourceObject property of correct subform
based on the new value of the tabcontrol. Remember that the
value of the tab control is actually the page index of the
current tab page where 0 refers to the first tab page.

When I design a complex for this way, my onChange event also
clears the SourceObject of the subform that is being hidden.
Here's some sample code - note that on each of the cases
instead of using absolute page numbers I get the page number
by getting the pageindex of a named page. I do this because
the page index of a page changes if you reorder the pages but
I tend to leave the names alone once I've set them. This just
reduces maintenance and prevents weird errors when you reorder
the pages but forget to adjust the code.

To be honest, I'm not sure how to do this with macros (or
whether you'd want to because of no error handling). Here's
how to do it with VBA:

'Module level variable
Private mIntCurTabPage As Integer

Private Sub TabCtl8_Change()
'Clear the SourceObject of subform on tabpage we're leaving
Select Case mIntCurTabPage
Case Me.TabCtl8.Pages ("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = vbNullString
End Select

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages ("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = "sfrmOrders"
Case Me.TabCtl8.Pages ("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject
= "sfrmOrders2"
Case Me.TabCtl8.Pages ("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject
= "sfrmOrders3"
Case Me.TabCtl8.Pages ("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject
= "sfrmOrders4"
End Select
mIntCurTabPage = Me.TabCtl8
End Sub

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
I have an access 2002 DB - split frontend/backend -
devleoped on WinXP which needs to run on a WinNT server.

One of my forms has 5 tabbed controls (pages) each of
which contains a separate subform, all linked to the
mainform. The record source for each subform is a query -
all calc's have been removed from the queries.

Problem is - the form is too slow. I'm trying to follow
a suggestion to remove the recordsourse of each subform
and then only supply that info once that tab/page is
selected (clicked on).

I need to use macros since I do not know visual basic -
or I need to have some kind soul provide me with the
visbasic so I can modify the existing coding.

Can anyone offer some detailed assistance, please??

Thank you!


.


.

.


.

.

.

 

[Sandra Daigle]

Is the visible property of the subform true? Also, you might try stepping
through the code - use the debugger to set a break point on the first
executable line of code, then step through line by line.

If all else fails you can make create a small mdb with just this form and
the subforms, then zip it and email it to me and I'll take a look. I might
be overlooking something. Just be sure to compact and zip it first - then
email to smdaigle this address is at mvps.org.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Hi Sandra,

The Name is "Products(s)" - it's on the Name row in the
properties dialog box for the PAGE - Each page has a
name I game it which is what is in the code.

Can you think of anything else as to why nothing happens
when I click on any of the tab/pages?

-----Original Message-----
Hi Maureen,

Are you sure that "Product(s)" is the name of the tab page? Select the
tab page by clicking on the tab, then look at the *Name* property under
the *Other* tab. Whatever is in this property should be in place of
"Product(s)".


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

If it helps - here is another copy of the full procedure -
every line is exactly as shown below:

Private Sub TabCtl48_Change()
Private mIntCurTabPage As Integer
Select Case Me.TabCtl48
Case Me.TabCtl48.Pages("Product(s)").PageIndex
Me.sfrmFirst.SourceObject = "InqProd Form Query
subform1"
Case Me.TabCtl48.Pages("Quantity Break (s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT QBs for
current Inquiry subform"
Case Me.TabCtl48.Pages("Mill(s) / Vendor
(s)").PageIndex
Me.sfrmThird.SourceObject = "CntT ProdVendors
for Current Inquriy QBs subform"
Case Me.TabCtl48.Pages("Order").PageIndex
Me.sfrm.SourceObject = "Orders Current
Inquiry subform"
End Select
mIntCurTabPage = Me.TabCtl48
End Sub



Nothing happens when I click on the tab/pages. No
subforms display - nothing.

Please help!
-----Original Message-----
THANKS SANDRA! I don't want to give up - I appreciate
your continued assistance! Please hang in there with me!!

The "CntT Qbs for current Inquiry subform" text was the
NAME of the subform. However, I have also tried it with
the Name of the Page "Quantity Break(s)" as displayed in
the Page properties dialog box and the procedure still
does not work.

The two lines of code are currently as follows:

Case Me.TabCtl48.Pages("Quantity Break(s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT QBs for
current Inquiry subform"

I added the first tab/page to the code also which is why
you see "sfrmSecond" in the above text.

I can't imagine what's missing or incorrect - I've
searched - PLEASE HELP!

-----Original Message-----
Hi Maureen,

Looking at the first case only as an example:

Is "CntT QBs for current Inquiry subform" the name of the subform
or the tab page? You should have the name of the tab page in the
Case statement in parentheses after "Pages".

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


(e-mail address removed) wrote:
Forgive me - the vis basic code reads as follows:


Private Sub TabCtl48_Change()

Select Case Me.TabCtl48
Case Me.TTabCtl48.Pages("CntT QBs for current
Inquiry subform").PageIndex
Me.sfrmFirst.SourceObject = "CntT QBs for current
Inquiry subform"
Case Me.TTabCtl48.Pages("CntT ProdVendors for Current
Inquriy QBs subform").PageIndex
Me.sfrmSecond.SourceObject = "CntT
ProdVendors for Current Inquriy QBs subform"
Case Me.TabCtl48.Pages("Orders Current Inquiry
subform").PageIndex
Me.sfrmThird.SourceObject = "Orders Current
Inquiry subform"
End Select
End Sub


I saved the module, compacted & repaired the DB, and
still nothing happends when I click on the tabs - do I
need to force a recompile? Do I need to do this for ALL
tab/pages and not skip the first and last tab/pages?


-----Original Message-----
Continued Thanks!!

I did just as you suggested but I'm not getting the
subforms to display - I must be missing something -

Here is the entire code I have:

Private Sub TabCtl48_Change()

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages("Quantity Break (s)").PageIndex
Me.sfrmFirst.SourceObject = "CntT QBs for current
Inquiry subform"
Case Me.TabCtl8.Pages("Mill(s) / Vendor (s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT
ProdVendors for Current Inquriy QBs subform"
Case Me.TabCtl8.Pages("Order").PageIndex
Me.sfrmThird.SourceObject = "Orders Current
Inquiry subform"
End Select
End Sub


I have 5 tabs and I really only need to get this working
on tab/pages 2, 3 & 4. Tab/pages 1 and 5 can remain as
they are (I believe - you may tell me different!).

I definitely have this code associated with the tbct148
on the change procedure - it shows that was in VisBasic
as well as in the form/tab control properties dialog box
on the OnChange event row -

Nothing happens when I click on tab/pages 2-3-4. Can you
see where I may have gone wrong?
-----Original Message-----
Hi Maureen,

Omit the first Select . . . End Select structure.

To add this code select your tab control (the entire control not
just a page). Then open the property sheet (F4 or View-
Properties). In the
property sheet under the event tab find the Change event. Put
your cursor in the property text box then click the down arrow
and select "[Event Procedure]". Then click the Builder button
("..."). This will open the VB Editor in the Class module for
your form. It will also create the Event procedure itself (with
the proper procedure declaration and termination). Just copy the
guts of the code into the procedure.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
Thanks Sandra!

I want to try your suggestion - but I am totally
unfamiliar with vis basic coding so I have a few
questions for you first:

Since tab pages 1,2,3,4 are dependent on what is
displayed on the preceeding tab(s) in order, then I don't
think I want to include the CLEAR sourceobject part of
the code you so kindly supplied - WHAT LINES exactly to I
omit?

Also - to inset this - do I just go into the visbasic
window, click in the leftmost column on the main form
which holds all of these subforms on tabs, click on the
INSERT MODULE button and type in your code, replacing
control/form names with my specific info?

Is that all I'll need to do??? I'm so willing to give
this a try - I desperately need to get this to work!
THANKS VERY MUCH!



-----Original Message-----
Hi Maureen,

Instead of setting/unsetting the recordsources, I generally
bind/unbind the suborm by setting/unsetting the Sourceobject of
the subforms. Create an OnChange event for the tabcontrol
itself that sets the SourceObject property of correct subform
based on the new value of the tabcontrol. Remember that the
value of the tab control is actually the page index of the
current tab page where 0 refers to the first tab page.

When I design a complex for this way, my onChange event also
clears the SourceObject of the subform that is being hidden.
Here's some sample code - note that on each of the cases
instead of using absolute page numbers I get the page number
by getting the pageindex of a named page. I do this because
the page index of a page changes if you reorder the pages but
I tend to leave the names alone once I've set them. This just
reduces maintenance and prevents weird errors when you reorder
the pages but forget to adjust the code.

To be honest, I'm not sure how to do this with macros (or
whether you'd want to because of no error handling). Here's
how to do it with VBA:

'Module level variable
Private mIntCurTabPage As Integer

Private Sub TabCtl8_Change()
'Clear the SourceObject of subform on tabpage we're leaving
Select Case mIntCurTabPage
Case Me.TabCtl8.Pages ("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = vbNullString
End Select

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages ("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = "sfrmOrders"
Case Me.TabCtl8.Pages ("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject
= "sfrmOrders2"
Case Me.TabCtl8.Pages ("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject
= "sfrmOrders3"
Case Me.TabCtl8.Pages ("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject
= "sfrmOrders4"
End Select
mIntCurTabPage = Me.TabCtl8
End Sub

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
I have an access 2002 DB - split frontend/backend -
devleoped on WinXP which needs to run on a WinNT server.

One of my forms has 5 tabbed controls (pages) each of
which contains a separate subform, all linked to the
mainform. The record source for each subform is a query -
all calc's have been removed from the queries.

Problem is - the form is too slow. I'm trying to follow
a suggestion to remove the recordsourse of each subform
and then only supply that info once that tab/page is
selected (clicked on).

I need to use macros since I do not know visual basic -
or I need to have some kind soul provide me with the
visbasic so I can modify the existing coding.

Can anyone offer some detailed assistance, please??

Thank you!


.


.

.


.

.

.

 

[Sandra Daigle]

Hi Maureen,

I received your file and here is what I found - first, there are some syntax
errors - the variable declaration statement

Private mIntCurTabPage As Integer

is only valid in the module header - before any procedure declarations. You
don't really need this statement since you are not unbinding any subforms.
Just remove it and the last line of the sub.

Next, you have some problems with names of objects. I didn't do a very good
job of explaining what each part of the following statements mean:


Me.sfrmFirst.SourceObject = "InqProd Form Query subform1"

'sfrmFirst' should be the name of the subform control on the first tab page.
If you click once on the subform you have selected the subform control.
Check the name property. This is the name that goes in place of
'sfrmFirst' above. It is very common to get confused about the difference
between the subform control and the form which is viewed through the subform
control. In any case, the above statement requires the name of the subform
control. You need to check the same thing for the references to the other
subforms.

While I'm thinking about it, I've noticed that your object names tend to be
long and complex. You will probably find that short and sweet is much
easier. I prefer object names that have no embedded spaces or special
characters. The complex names can be used in captions for the user but for
me, I use very terse names. Here's one reason - to refer to a subform
control that is named "Inquiry Subform 2(a)" I have to wrap the whole thing
in brackets:

me.[Inquiry Subform 2(a)].sourceobject="whatever"

This is not nearly as easy for me as "sfrmInquiry2a". There are tons of
articles and books on naming conventions. You might want to check out a few
to help you.

One other thing, at the top of every module you want to make sure that you
have "Option Explicit". In the VB Editor under options, check the box for
"require Variable Declaration". This is a good practice and will help
prevent many logic errors.

I think that was it - be sure to compile your project before you try to run
it.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Is the visible property of the subform true? Also, you might try stepping
through the code - use the debugger to set a break point on the first
executable line of code, then step through line by line.

If all else fails you can make create a small mdb with just this form and
the subforms, then zip it and email it to me and I'll take a look. I might
be overlooking something. Just be sure to compact and zip it first - then
email to smdaigle this address is at mvps.org.

Hi Sandra,

The Name is "Products(s)" - it's on the Name row in the
properties dialog box for the PAGE - Each page has a
name I game it which is what is in the code.

Can you think of anything else as to why nothing happens
when I click on any of the tab/pages?

-----Original Message-----
Hi Maureen,

Are you sure that "Product(s)" is the name of the tab page? Select the
tab page by clicking on the tab, then look at the *Name* property under
the *Other* tab. Whatever is in this property should be in place of
"Product(s)".


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
If it helps - here is another copy of the full procedure -
every line is exactly as shown below:

Private Sub TabCtl48_Change()
Private mIntCurTabPage As Integer
Select Case Me.TabCtl48
Case Me.TabCtl48.Pages("Product(s)").PageIndex
Me.sfrmFirst.SourceObject = "InqProd Form Query
subform1"
Case Me.TabCtl48.Pages("Quantity Break (s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT QBs for
current Inquiry subform"
Case Me.TabCtl48.Pages("Mill(s) / Vendor
(s)").PageIndex
Me.sfrmThird.SourceObject = "CntT ProdVendors
for Current Inquriy QBs subform"
Case Me.TabCtl48.Pages("Order").PageIndex
Me.sfrm.SourceObject = "Orders Current
Inquiry subform"
End Select
mIntCurTabPage = Me.TabCtl48
End Sub



Nothing happens when I click on the tab/pages. No
subforms display - nothing.

Please help!
-----Original Message-----
THANKS SANDRA! I don't want to give up - I appreciate
your continued assistance! Please hang in there with me!!

The "CntT Qbs for current Inquiry subform" text was the
NAME of the subform. However, I have also tried it with
the Name of the Page "Quantity Break(s)" as displayed in
the Page properties dialog box and the procedure still
does not work.

The two lines of code are currently as follows:

Case Me.TabCtl48.Pages("Quantity Break(s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT QBs for
current Inquiry subform"

I added the first tab/page to the code also which is why
you see "sfrmSecond" in the above text.

I can't imagine what's missing or incorrect - I've
searched - PLEASE HELP!

-----Original Message-----
Hi Maureen,

Looking at the first case only as an example:

Is "CntT QBs for current Inquiry subform" the name of the subform
or the tab page? You should have the name of the tab page in the
Case statement in parentheses after "Pages".

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


(e-mail address removed) wrote:
Forgive me - the vis basic code reads as follows:


Private Sub TabCtl48_Change()

Select Case Me.TabCtl48
Case Me.TTabCtl48.Pages("CntT QBs for current
Inquiry subform").PageIndex
Me.sfrmFirst.SourceObject = "CntT QBs for current
Inquiry subform"
Case Me.TTabCtl48.Pages("CntT ProdVendors for Current
Inquriy QBs subform").PageIndex
Me.sfrmSecond.SourceObject = "CntT
ProdVendors for Current Inquriy QBs subform"
Case Me.TabCtl48.Pages("Orders Current Inquiry
subform").PageIndex
Me.sfrmThird.SourceObject = "Orders Current
Inquiry subform"
End Select
End Sub


I saved the module, compacted & repaired the DB, and
still nothing happends when I click on the tabs - do I
need to force a recompile? Do I need to do this for ALL
tab/pages and not skip the first and last tab/pages?


-----Original Message-----
Continued Thanks!!

I did just as you suggested but I'm not getting the
subforms to display - I must be missing something -

Here is the entire code I have:

Private Sub TabCtl48_Change()

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages("Quantity Break (s)").PageIndex
Me.sfrmFirst.SourceObject = "CntT QBs for current
Inquiry subform"
Case Me.TabCtl8.Pages("Mill(s) / Vendor (s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT
ProdVendors for Current Inquriy QBs subform"
Case Me.TabCtl8.Pages("Order").PageIndex
Me.sfrmThird.SourceObject = "Orders Current
Inquiry subform"
End Select
End Sub


I have 5 tabs and I really only need to get this working
on tab/pages 2, 3 & 4. Tab/pages 1 and 5 can remain as
they are (I believe - you may tell me different!).

I definitely have this code associated with the tbct148
on the change procedure - it shows that was in VisBasic
as well as in the form/tab control properties dialog box
on the OnChange event row -

Nothing happens when I click on tab/pages 2-3-4. Can you
see where I may have gone wrong?
-----Original Message-----
Hi Maureen,

Omit the first Select . . . End Select structure.

To add this code select your tab control (the entire control not
just a page). Then open the property sheet (F4 or View-
Properties). In the
property sheet under the event tab find the Change event. Put
your cursor in the property text box then click the down arrow
and select "[Event Procedure]". Then click the Builder button
("..."). This will open the VB Editor in the Class module for
your form. It will also create the Event procedure itself (with
the proper procedure declaration and termination). Just copy the
guts of the code into the procedure.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
Thanks Sandra!

I want to try your suggestion - but I am totally
unfamiliar with vis basic coding so I have a few
questions for you first:

Since tab pages 1,2,3,4 are dependent on what is
displayed on the preceeding tab(s) in order, then I don't
think I want to include the CLEAR sourceobject part of
the code you so kindly supplied - WHAT LINES exactly to I
omit?

Also - to inset this - do I just go into the visbasic
window, click in the leftmost column on the main form
which holds all of these subforms on tabs, click on the
INSERT MODULE button and type in your code, replacing
control/form names with my specific info?

Is that all I'll need to do??? I'm so willing to give
this a try - I desperately need to get this to work!
THANKS VERY MUCH!



-----Original Message-----
Hi Maureen,

Instead of setting/unsetting the recordsources, I generally
bind/unbind the suborm by setting/unsetting the Sourceobject of
the subforms. Create an OnChange event for the tabcontrol
itself that sets the SourceObject property of correct subform
based on the new value of the tabcontrol. Remember that the
value of the tab control is actually the page index of the
current tab page where 0 refers to the first tab page.

When I design a complex for this way, my onChange event also
clears the SourceObject of the subform that is being hidden.
Here's some sample code - note that on each of the cases
instead of using absolute page numbers I get the page number
by getting the pageindex of a named page. I do this because
the page index of a page changes if you reorder the pages but
I tend to leave the names alone once I've set them. This just
reduces maintenance and prevents weird errors when you reorder
the pages but forget to adjust the code.

To be honest, I'm not sure how to do this with macros (or
whether you'd want to because of no error handling). Here's
how to do it with VBA:

'Module level variable
Private mIntCurTabPage As Integer

Private Sub TabCtl8_Change()
'Clear the SourceObject of subform on tabpage we're leaving
Select Case mIntCurTabPage
Case Me.TabCtl8.Pages ("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = vbNullString
End Select

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages ("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = "sfrmOrders"
Case Me.TabCtl8.Pages ("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject
= "sfrmOrders2"
Case Me.TabCtl8.Pages ("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject
= "sfrmOrders3"
Case Me.TabCtl8.Pages ("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject
= "sfrmOrders4"
End Select
mIntCurTabPage = Me.TabCtl8
End Sub

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
I have an access 2002 DB - split frontend/backend -
devleoped on WinXP which needs to run on a WinNT server.

One of my forms has 5 tabbed controls (pages) each of
which contains a separate subform, all linked to the
mainform. The record source for each subform is a query -
all calc's have been removed from the queries.

Problem is - the form is too slow. I'm trying to follow
a suggestion to remove the recordsourse of each subform
and then only supply that info once that tab/page is
selected (clicked on).

I need to use macros since I do not know visual basic -
or I need to have some kind soul provide me with the
visbasic so I can modify the existing coding.

Can anyone offer some detailed assistance, please??

Thank you!


.


.

.


.

.


.

 

[Maureen]

Sandra,

It worked! PHEW! I am most grateful for your
perserverance and continued assistance throughout my
effort to get this procedure to work. It was a learning
experience for me. THANK YOU!

-----Original Message-----
Hi Maureen,

I received your file and here is what I found - first, there are some syntax
errors - the variable declaration statement

Private mIntCurTabPage As Integer

is only valid in the module header - before any procedure declarations. You
don't really need this statement since you are not unbinding any subforms.
Just remove it and the last line of the sub.

Next, you have some problems with names of objects. I didn't do a very good
job of explaining what each part of the following statements mean:


Me.sfrmFirst.SourceObject = "InqProd Form Query subform1"

'sfrmFirst' should be the name of the subform control on the first tab page.
If you click once on the subform you have selected the subform control.
Check the name property. This is the name that goes in place of
'sfrmFirst' above. It is very common to get confused about the difference
between the subform control and the form which is viewed through the subform
control. In any case, the above statement requires the name of the subform
control. You need to check the same thing for the references to the other
subforms.

While I'm thinking about it, I've noticed that your object names tend to be
long and complex. You will probably find that short and sweet is much
easier. I prefer object names that have no embedded spaces or special
characters. The complex names can be used in captions for the user but for
me, I use very terse names. Here's one reason - to refer to a subform
control that is named "Inquiry Subform 2(a)" I have to wrap the whole thing
in brackets:

me.[Inquiry Subform 2(a)].sourceobject="whatever"

This is not nearly as easy for me as "sfrmInquiry2a". There are tons of
articles and books on naming conventions. You might want to check out a few
to help you.

One other thing, at the top of every module you want to make sure that you
have "Option Explicit". In the VB Editor under options, check the box for
"require Variable Declaration". This is a good practice and will help
prevent many logic errors.

I think that was it - be sure to compile your project before you try to run
it.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Is the visible property of the subform true? Also, you might try stepping
through the code - use the debugger to set a break point on the first
executable line of code, then step through line by line.

If all else fails you can make create a small mdb with just this form and
the subforms, then zip it and email it to me and I'll take a look. I might
be overlooking something. Just be sure to compact and zip it first - then
email to smdaigle this address is at mvps.org.

Hi Sandra,

The Name is "Products(s)" - it's on the Name row in the
properties dialog box for the PAGE - Each page has a
name I game it which is what is in the code.

Can you think of anything else as to why nothing happens
when I click on any of the tab/pages?
-----Original Message-----
Hi Maureen,

Are you sure that "Product(s)" is the name of the tab page? Select the
tab page by clicking on the tab, then look at the *Name* property under
the *Other* tab. Whatever is in this property should be in place of
"Product(s)".


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
If it helps - here is another copy of the full procedure -
every line is exactly as shown below:

Private Sub TabCtl48_Change()
Private mIntCurTabPage As Integer
Select Case Me.TabCtl48
Case Me.TabCtl48.Pages("Product(s)").PageIndex
Me.sfrmFirst.SourceObject = "InqProd Form Query
subform1"
Case Me.TabCtl48.Pages("Quantity Break (s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT QBs for
current Inquiry subform"
Case Me.TabCtl48.Pages("Mill(s) / Vendor
(s)").PageIndex
Me.sfrmThird.SourceObject = "CntT ProdVendors
for Current Inquriy QBs subform"
Case Me.TabCtl48.Pages("Order").PageIndex
Me.sfrm.SourceObject = "Orders Current
Inquiry subform"
End Select
mIntCurTabPage = Me.TabCtl48
End Sub



Nothing happens when I click on the tab/pages. No
subforms display - nothing.

Please help!
-----Original Message-----
THANKS SANDRA! I don't want to give up - I appreciate
your continued assistance! Please hang in there with me!!

The "CntT Qbs for current Inquiry subform" text was the
NAME of the subform. However, I have also tried it with
the Name of the Page "Quantity Break(s)" as displayed in
the Page properties dialog box and the procedure still
does not work.

The two lines of code are currently as follows:

Case Me.TabCtl48.Pages("Quantity Break (s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT QBs for
current Inquiry subform"

I added the first tab/page to the code also which is why
you see "sfrmSecond" in the above text.

I can't imagine what's missing or incorrect - I've
searched - PLEASE HELP!

-----Original Message-----
Hi Maureen,

Looking at the first case only as an example:

Is "CntT QBs for current Inquiry subform" the name
of the subform
or the tab page? You should have the name of the tab page in the
Case statement in parentheses after "Pages".

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


(e-mail address removed) wrote:
Forgive me - the vis basic code reads as follows:


Private Sub TabCtl48_Change()

Select Case Me.TabCtl48
Case Me.TTabCtl48.Pages("CntT QBs for current
Inquiry subform").PageIndex
Me.sfrmFirst.SourceObject = "CntT QBs for current
Inquiry subform"
Case Me.TTabCtl48.Pages("CntT ProdVendors for Current
Inquriy QBs subform").PageIndex
Me.sfrmSecond.SourceObject = "CntT
ProdVendors for Current Inquriy QBs subform"
Case Me.TabCtl48.Pages("Orders Current Inquiry
subform").PageIndex
Me.sfrmThird.SourceObject = "Orders Current
Inquiry subform"
End Select
End Sub


I saved the module, compacted & repaired the DB, and
still nothing happends when I click on the tabs -
do I
need to force a recompile? Do I need to do this
for ALL
tab/pages and not skip the first and last tab/pages?


-----Original Message-----
Continued Thanks!!

I did just as you suggested but I'm not getting the
subforms to display - I must be missing something -

Here is the entire code I have:

Private Sub TabCtl48_Change()

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages("Quantity Break (s)").PageIndex
Me.sfrmFirst.SourceObject = "CntT QBs for current
Inquiry subform"
Case Me.TabCtl8.Pages("Mill(s) / Vendor (s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT
ProdVendors for Current Inquriy QBs subform"
Case Me.TabCtl8.Pages("Order").PageIndex
Me.sfrmThird.SourceObject = "Orders Current
Inquiry subform"
End Select
End Sub


I have 5 tabs and I really only need to get this working
on tab/pages 2, 3 & 4. Tab/pages 1 and 5 can remain as
they are (I believe - you may tell me different!).

I definitely have this code associated with the tbct148
on the change procedure - it shows that was in VisBasic
as well as in the form/tab control properties dialog box
on the OnChange event row -

Nothing happens when I click on tab/pages 2-3- 4. Can you
see where I may have gone wrong?
-----Original Message-----
Hi Maureen,

Omit the first Select . . . End Select structure.

To add this code select your tab control (the entire control not
just a page). Then open the property sheet (F4 or View-
Properties). In the
property sheet under the event tab find the
Change event. Put
your cursor in the property text box then click the down arrow
and select "[Event Procedure]". Then click the Builder button
("..."). This will open the VB Editor in the
Class module for
your form. It will also create the Event
procedure itself (with
the proper procedure declaration and
termination). Just copy the
guts of the code into the procedure.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
Thanks Sandra!

I want to try your suggestion - but I am totally
unfamiliar with vis basic coding so I have a few
questions for you first:

Since tab pages 1,2,3,4 are dependent on what is
displayed on the preceeding tab(s) in order,
then I don't
think I want to include the CLEAR sourceobject part of
the code you so kindly supplied - WHAT LINES exactly to I
omit?

Also - to inset this - do I just go into the visbasic
window, click in the leftmost column on the main form
which holds all of these subforms on tabs, click on the
INSERT MODULE button and type in your code, replacing
control/form names with my specific info?

Is that all I'll need to do??? I'm so willing
to give
this a try - I desperately need to get this to work!
THANKS VERY MUCH!



-----Original Message-----
Hi Maureen,

Instead of setting/unsetting the recordsources, I generally
bind/unbind the suborm by setting/unsetting the Sourceobject of
the subforms. Create an OnChange event for the tabcontrol
itself that sets the SourceObject property of correct subform
based on the new value of the tabcontrol. Remember that the
value of the tab control is actually the page index of the
current tab page where 0 refers to the first
tab page.

When I design a complex for this way, my onChange event also
clears the SourceObject of the subform that is being hidden.
Here's some sample code - note that on each of the cases
instead of using absolute page numbers I get
the page number
by getting the pageindex of a named page. I do this because
the page index of a page changes if you reorder the pages but
I tend to leave the names alone once I've set them. This just
reduces maintenance and prevents weird errors when you reorder
the pages but forget to adjust the code.

To be honest, I'm not sure how to do this with macros (or
whether you'd want to because of no error handling). Here's
how to do it with VBA:

'Module level variable
Private mIntCurTabPage As Integer

Private Sub TabCtl8_Change()
'Clear the SourceObject of subform on tabpage we're leaving
Select Case mIntCurTabPage
Case Me.TabCtl8.Pages ("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = vbNullString
End Select

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages ("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = "sfrmOrders"
Case Me.TabCtl8.Pages ("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject
= "sfrmOrders2"
Case Me.TabCtl8.Pages ("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject
= "sfrmOrders3"
Case Me.TabCtl8.Pages ("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject
= "sfrmOrders4"
End Select
mIntCurTabPage = Me.TabCtl8
End Sub

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
I have an access 2002 DB - split frontend/backend -
devleoped on WinXP which needs to run on a WinNT server.

One of my forms has 5 tabbed controls (pages) each of
which contains a separate subform, all linked to the
mainform. The record source for each subform is a query -
all calc's have been removed from the queries.

Problem is - the form is too slow. I'm trying to follow
a suggestion to remove the recordsourse of
each subform
and then only supply that info once that tab/page is
selected (clicked on).

I need to use macros since I do not know
visual basic -
or I need to have some kind soul provide me with the
visbasic so I can modify the existing coding.

Can anyone offer some detailed assistance, please??

Thank you!


.


.

.


.

.


.

.

 

[Sandra Daigle]

Great - I'm glad to hear that you have it working and also learned a thing
or two in the process! Good luck with the rest of your project!

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Sandra,

It worked! PHEW! I am most grateful for your
perserverance and continued assistance throughout my
effort to get this procedure to work. It was a learning
experience for me. THANK YOU!

-----Original Message-----
Hi Maureen,

I received your file and here is what I found - first, there are
some syntax errors - the variable declaration statement

Private mIntCurTabPage As Integer

is only valid in the module header - before any procedure
declarations. You don't really need this statement since you are not
unbinding any subforms. Just remove it and the last line of the sub.

Next, you have some problems with names of objects. I didn't do a
very good job of explaining what each part of the following
statements mean:


Me.sfrmFirst.SourceObject = "InqProd Form Query subform1"

'sfrmFirst' should be the name of the subform control on the first
tab page. If you click once on the subform you have selected the
subform control. Check the name property. This is the name that
goes in place of 'sfrmFirst' above. It is very common to get
confused about the difference between the subform control and the
form which is viewed through the subform control. In any case, the
above statement requires the name of the subform control. You need
to check the same thing for the references to the other subforms.

While I'm thinking about it, I've noticed that your object names
tend to be long and complex. You will probably find that short and
sweet is much easier. I prefer object names that have no embedded
spaces or special characters. The complex names can be used in
captions for the user but for me, I use very terse names. Here's one
reason - to refer to a subform control that is named "Inquiry
Subform 2(a)" I have to wrap the whole thing in brackets:

me.[Inquiry Subform 2(a)].sourceobject="whatever"

This is not nearly as easy for me as "sfrmInquiry2a". There are tons
of articles and books on naming conventions. You might want to check
out a few to help you.

One other thing, at the top of every module you want to make sure
that you have "Option Explicit". In the VB Editor under options,
check the box for "require Variable Declaration". This is a good
practice and will help prevent many logic errors.

I think that was it - be sure to compile your project before you try
to run it.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Is the visible property of the subform true? Also, you might try
stepping through the code - use the debugger to set a break point
on the first executable line of code, then step through line by
line.

If all else fails you can make create a small mdb with just this
form and the subforms, then zip it and email it to me and I'll take
a look. I might be overlooking something. Just be sure to compact
and zip it first - then email to smdaigle this address is at
mvps.org.


Maureen wrote:
Hi Sandra,

The Name is "Products(s)" - it's on the Name row in the
properties dialog box for the PAGE - Each page has a
name I game it which is what is in the code.

Can you think of anything else as to why nothing happens
when I click on any of the tab/pages?
-----Original Message-----
Hi Maureen,

Are you sure that "Product(s)" is the name of the tab page?
Select the tab page by clicking on the tab, then look at the
*Name* property under the *Other* tab. Whatever is in this
property should be in place of "Product(s)".


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
If it helps - here is another copy of the full procedure -
every line is exactly as shown below:

Private Sub TabCtl48_Change()
Private mIntCurTabPage As Integer
Select Case Me.TabCtl48
Case Me.TabCtl48.Pages("Product(s)").PageIndex
Me.sfrmFirst.SourceObject = "InqProd Form Query
subform1"
Case Me.TabCtl48.Pages("Quantity Break (s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT QBs for
current Inquiry subform"
Case Me.TabCtl48.Pages("Mill(s) / Vendor
(s)").PageIndex
Me.sfrmThird.SourceObject = "CntT ProdVendors
for Current Inquriy QBs subform"
Case Me.TabCtl48.Pages("Order").PageIndex
Me.sfrm.SourceObject = "Orders Current
Inquiry subform"
End Select
mIntCurTabPage = Me.TabCtl48
End Sub



Nothing happens when I click on the tab/pages. No
subforms display - nothing.

Please help!
-----Original Message-----
THANKS SANDRA! I don't want to give up - I appreciate
your continued assistance! Please hang in there with me!!

The "CntT Qbs for current Inquiry subform" text was the
NAME of the subform. However, I have also tried it with
the Name of the Page "Quantity Break(s)" as displayed in
the Page properties dialog box and the procedure still
does not work.

The two lines of code are currently as follows:

Case Me.TabCtl48.Pages("Quantity Break (s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT QBs for
current Inquiry subform"

I added the first tab/page to the code also which is why
you see "sfrmSecond" in the above text.

I can't imagine what's missing or incorrect - I've
searched - PLEASE HELP!

-----Original Message-----
Hi Maureen,

Looking at the first case only as an example:

Is "CntT QBs for current Inquiry subform" the name of the
subform or the tab page? You should have the name of the tab
page in the Case statement in parentheses after "Pages".

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


(e-mail address removed) wrote:
Forgive me - the vis basic code reads as follows:


Private Sub TabCtl48_Change()

Select Case Me.TabCtl48
Case Me.TTabCtl48.Pages("CntT QBs for current
Inquiry subform").PageIndex
Me.sfrmFirst.SourceObject = "CntT QBs for current
Inquiry subform"
Case Me.TTabCtl48.Pages("CntT ProdVendors for Current
Inquriy QBs subform").PageIndex
Me.sfrmSecond.SourceObject = "CntT
ProdVendors for Current Inquriy QBs subform"
Case Me.TabCtl48.Pages("Orders Current Inquiry
subform").PageIndex
Me.sfrmThird.SourceObject = "Orders Current
Inquiry subform"
End Select
End Sub


I saved the module, compacted & repaired the DB, and
still nothing happends when I click on the tabs - do I
need to force a recompile? Do I need to do this for ALL
tab/pages and not skip the first and last tab/pages?


-----Original Message-----
Continued Thanks!!

I did just as you suggested but I'm not getting the
subforms to display - I must be missing something -

Here is the entire code I have:

Private Sub TabCtl48_Change()

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages("Quantity Break (s)").PageIndex
Me.sfrmFirst.SourceObject = "CntT QBs for current
Inquiry subform"
Case Me.TabCtl8.Pages("Mill(s) / Vendor (s)").PageIndex
Me.sfrmSecond.SourceObject = "CntT
ProdVendors for Current Inquriy QBs subform"
Case Me.TabCtl8.Pages("Order").PageIndex
Me.sfrmThird.SourceObject = "Orders Current
Inquiry subform"
End Select
End Sub


I have 5 tabs and I really only need to get this working
on tab/pages 2, 3 & 4. Tab/pages 1 and 5 can remain as
they are (I believe - you may tell me different!).

I definitely have this code associated with the tbct148
on the change procedure - it shows that was in VisBasic
as well as in the form/tab control properties dialog box
on the OnChange event row -

Nothing happens when I click on tab/pages 2-3- 4. Can you
see where I may have gone wrong?
-----Original Message-----
Hi Maureen,

Omit the first Select . . . End Select structure.

To add this code select your tab control (the entire
control not just a page). Then open the property sheet (F4
or View- Properties). In the
property sheet under the event tab find the Change event.
Put your cursor in the property text box then click the
down arrow and select "[Event Procedure]". Then click the
Builder button ("..."). This will open the VB Editor in the
Class module for your form. It will also create the Event
procedure itself (with the proper procedure declaration and
termination). Just copy the guts of the code into the
procedure.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
Thanks Sandra!

I want to try your suggestion - but I am totally
unfamiliar with vis basic coding so I have a few
questions for you first:

Since tab pages 1,2,3,4 are dependent on what is
displayed on the preceeding tab(s) in order, then I don't
think I want to include the CLEAR sourceobject part of
the code you so kindly supplied - WHAT LINES exactly to I
omit?

Also - to inset this - do I just go into the visbasic
window, click in the leftmost column on the main form
which holds all of these subforms on tabs, click on the
INSERT MODULE button and type in your code, replacing
control/form names with my specific info?

Is that all I'll need to do??? I'm so willing to give
this a try - I desperately need to get this to work!
THANKS VERY MUCH!



-----Original Message-----
Hi Maureen,

Instead of setting/unsetting the recordsources, I
generally bind/unbind the suborm by setting/unsetting the
Sourceobject of the subforms. Create an OnChange event
for the tabcontrol itself that sets the SourceObject
property of correct subform based on the new value of the
tabcontrol. Remember that the value of the tab control is
actually the page index of the current tab page where 0
refers to the first tab page.

When I design a complex for this way, my onChange event
also clears the SourceObject of the subform that is being
hidden. Here's some sample code - note that on each of
the cases instead of using absolute page numbers I get
the page number by getting the pageindex of a named page.
I do this because the page index of a page changes if you
reorder the pages but I tend to leave the names alone
once I've set them. This just reduces maintenance and
prevents weird errors when you reorder the pages but
forget to adjust the code.

To be honest, I'm not sure how to do this with macros (or
whether you'd want to because of no error handling).
Here's how to do it with VBA:

'Module level variable
Private mIntCurTabPage As Integer

Private Sub TabCtl8_Change()
'Clear the SourceObject of subform on tabpage we're
leaving Select Case mIntCurTabPage
Case Me.TabCtl8.Pages ("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject = vbNullString
Case Me.TabCtl8.Pages ("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject = vbNullString
End Select

Select Case Me.TabCtl8
Case Me.TabCtl8.Pages ("pgFirstPageName").PageIndex
Me.sfrmFirst.SourceObject = "sfrmOrders"
Case Me.TabCtl8.Pages ("pgSecondPageName").PageIndex
Me.sfrmSecond.SourceObject
= "sfrmOrders2"
Case Me.TabCtl8.Pages ("pgThirdPageName").PageIndex
Me.sfrmThird.SourceObject
= "sfrmOrders3"
Case Me.TabCtl8.Pages ("pgFourthPageName").PageIndex
Me.sfrmFourth.SourceObject
= "sfrmOrders4"
End Select
mIntCurTabPage = Me.TabCtl8
End Sub

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.


Maureen wrote:
I have an access 2002 DB - split frontend/backend -
devleoped on WinXP which needs to run on a WinNT server.

One of my forms has 5 tabbed controls (pages) each of
which contains a separate subform, all linked to the
mainform. The record source for each subform is a query
- all calc's have been removed from the queries.

Problem is - the form is too slow. I'm trying to follow
a suggestion to remove the recordsourse of each subform
and then only supply that info once that tab/page is
selected (clicked on).

I need to use macros since I do not know visual basic -
or I need to have some kind soul provide me with the
visbasic so I can modify the existing coding.

Can anyone offer some detailed assistance, please??

Thank you!


.


.

.


.

.


.

.