Solved Button to Add Tab to Tab Control (1 Viewer)

[tmyers]

I have a form (QuoteForm) that has a tab control within it.
It currently has 2 tabs, but what I am trying to do (and not succeeding very well) is to add a button that when clicked, will add a new tab to the control. Currently the button opens another form that will allow you to enter the desired name for the tab you are trying to create. The button on that form (named simply "add sheet") is what should add the new tab with the name you just entered.

The problem I have run into, is it keeps adding tabs to the sheet over and over with every click. I know this is where some will go "But that is what you wanted isn't it?" Yes, but the new problem this creates is my form keeps getting more and more tabs even when I want to open a "fresh" form. It also causes problems due to the tabs number increasing with every addition, so in my testing the code eventually loses track of the tab number (say if I deleted all the previous tabs to "start over") and then throws an error.

The code I am currently using is:
Option Compare Database

Private Sub Command2_Click()
On Error GoTo ErrorHandler
DoCmd.Close acForm, "QuoteForm", acSaveYes
DoCmd.OpenForm "QuoteForm", acDesign, , , , acHidden
Forms("QuoteForm")("TabCtl43").Pages.Add

Dim pageNumber As Integer
pageNumber = Forms("QuoteForm")("TabCtl43").Pages.Count


Forms("QuoteForm")("TabCtl43").Pages.Item(pageNumber - 1).Name = "Some Name" & pageNumber
Forms("QuoteForm")("TabCtl43").Pages.Item(pageNumber - 1).Caption = "Some Caption"

DoCmd.Close acForm, "QuoteForm", acSaveYes
DoCmd.OpenForm "QuoteForm", acNormal

DoCmd.Close acForm, Me.Name, acSaveNo
Exit_Here:
Exit Sub


ErrorHandler:
MsgBox "Error: " & Err.Description
Resume Exit_Here
End Sub

I also dont like the fact that the screen flickers when executing this command (because it has to go into design view and add the tab real quick then back to form view), but from what I have researched, that isnt really avoidable.

 

[theDBguy]

Hi. Even if the form doesn't lose track of the tabs you deleted and created, Access will keep a record of how many you have created thus far, and eventually, you will hit the limit of around 750 and won't be able to create any more.

We usually don't recommend designing a form where you dynamically create controls on it. Maybe what you can consider doing is simply create as many tabs as you or your users would ever need and simply hide and unhide them as you need to use each tab.

Just a thought...

 

[Minty]

Design changes should not be driven by data changes, this would tend to indicate a poor design.

You can change the caption of the tab page on the fly, as well as the record source but surely not the form design.

 

[tmyers]

@theDBguy would I be able to dynamically name them as needed? How would that work as far as being able to open the form to "new"?

My end goal was to have tabs named after construction plan sheet names and each tab have a subform within it that has data entered in a previous tab that you then add counts to for that specific page of the plans. Thinking back over this, maybe that is not the best way to do it.

 

[theDBguy]

@theDBguy would I be able to dynamically name them as needed? How would that work as far as being able to open the form to "new"?

My end goal was to have tabs named after construction plan sheet names and each tab have a subform within it that has data entered in a previous tab that you then add counts to for that specific page of the plans. Thinking back over this, maybe that is not the best way to do it.

Hi. Any labels on the form, you can always change what it says using code. As far as assigning subforms to each tab, you can do that dynamically too. What I am suggesting you don't do is "create" objects on the fly. Instead, you can rename or reassign any existing objects to repurpose them, as much as you like.

 

[MajP]

If you had to "add" pages dynamically you would start with hidden pages instead of adding pages. Then set them to visible and configure the caption.

 

[tmyers]

Got it. Thanks for the insight! I will go that route instead of what I was doing. Thanks everyone!