Use VBA to add, delete, rename, reorder pages in tab control

[Lisa M. Fida]

Windows XP Pro, Access 2003

I have a form with a tab control on it. I would like the user to be able to
add, delete, reorder, etc. pages on the tab control at runtime without having
the user go into design view. What is the best approach to do this? I'd
like it to function similar to adding, editing, ordering sheets in Excel.

 

[Allen Browne]

You can't add new pages to a tab control at runtime. (Well, you can if you
open in design view but that's going to block other things you probably
want, such as creating an MDE.)

So, add the maximum number of pages you will ever need. Then use code in the
form's Open event to hide the ones you don't need.

 

[Lisa M. Fida]

Thank you Allen for your response.

Is there another kind of Tab Control that has the functionality I'm looking
for? I've looked at the TabStrip Control but not sure if I'll run into the
same types of issues.

 

[Allen Browne]

There's only one tab control built into Access.

I can't comment on whether anyone anywhere else makes an add-in tab control
you could buy.

 

[Tony Toews [MVP]]

I have a form with a tab control on it. I would like the user to be able to
add, delete, reorder, etc. pages on the tab control at runtime without having
the user go into design view. What is the best approach to do this? I'd
like it to function similar to adding, editing, ordering sheets in Excel.

I would wonder why you'd want to add or delete pages on a tab control.
Hiding pages if they don't apply to the current parent record I can
understand. Same with reordering.

Tony

 

[Afro54]

Wonder no more - I have a tab control that produces a reconciliation report,
each month.

I want to create a 'Rec Results' tab for each calendar month and so, I would
like to add a new tab as a month's data is processed.

It's a perfectly valid requirement, set against an imperfect tab control.
But, if creating each tab in advance and hiding it is the (messy) way, sobeit.

 

[Afro54]

Wonder no more - I have a tab control that produces a reconciliation report,
each month.

I want to create a 'Rec Results' tab for each calendar month and so, I would
like to add a new tab as a month's data is processed.

It's a perfectly valid requirement, set against an imperfect tab control.
But, if creating each tab in advance and hiding it is the (messy) way, sobeit.

 

[Armen Stein]

Wonder no more - I have a tab control that produces a reconciliation report,
each month.

I want to create a 'Rec Results' tab for each calendar month and so, I would
like to add a new tab as a month's data is processed.

It's a perfectly valid requirement, set against an imperfect tab control.
But, if creating each tab in advance and hiding it is the (messy) way, sobeit.

Instead of using tabs, we use "panels". They're in a listbox on the
left side of the form, and their friendly names and subform object
names are in a table. Each panel is a separate subform that gets
loaded when needed.

To see a visual example, see page 12 on in the Best of Both Worlds
slide deck at www.JStreetTech.com/Downloads.

You could dynamically show the appropriate months in the panel list,
along with any other general panels you might need. I think this
would be easier for your users to navigate (espcially when you need to
show a lot of months) and may also be a performance improvement, since
the panels are only loaded when they're clicked, while tabs are
normally loaded when the form first opens.

The technique in a nutshell: when the panel is clicked in the
listbox, replace the subform control's sourceobject with the one from
the panel table. If you have Master/Child properties, you should also
reset those too, as some versions of Access drop those when the
sourceobject changes.

Much less messy than creating tabs on the fly.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Armen Stein]

Wonder no more - I have a tab control that produces a reconciliation report,
each month.

I want to create a 'Rec Results' tab for each calendar month and so, I would
like to add a new tab as a month's data is processed.

It's a perfectly valid requirement, set against an imperfect tab control.
But, if creating each tab in advance and hiding it is the (messy) way, sobeit.

Instead of using tabs, we use "panels". They're in a listbox on the
left side of the form, and their friendly names and subform object
names are in a table. Each panel is a separate subform that gets
loaded when needed.

To see a visual example, see page 12 on in the Best of Both Worlds
slide deck at www.JStreetTech.com/Downloads.

You could dynamically show the appropriate months in the panel list,
along with any other general panels you might need. I think this
would be easier for your users to navigate (espcially when you need to
show a lot of months) and may also be a performance improvement, since
the panels are only loaded when they're clicked, while tabs are
normally loaded when the form first opens.

The technique in a nutshell: when the panel is clicked in the
listbox, replace the subform control's sourceobject with the one from
the panel table. If you have Master/Child properties, you should also
reset those too, as some versions of Access drop those when the
sourceobject changes.

Much less messy than creating tabs on the fly.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Tony Toews [MVP]]

Wonder no more - I have a tab control that produces a reconciliation report,
each month.

I want to create a 'Rec Results' tab for each calendar month and so, I would
like to add a new tab as a month's data is processed.

It's a perfectly valid requirement, set against an imperfect tab control.

I respectfully disagree with creating a new tab. Let the user select
the year and month in a combo box and produce the report based on
that.

Tony

 

[Tony Toews [MVP]]

Wonder no more - I have a tab control that produces a reconciliation report,
each month.

I want to create a 'Rec Results' tab for each calendar month and so, I would
like to add a new tab as a month's data is processed.

It's a perfectly valid requirement, set against an imperfect tab control.

I respectfully disagree with creating a new tab. Let the user select
the year and month in a combo box and produce the report based on
that.

Tony