Create a copy of existing Tab(s) with all applicable forms & display next record

I received an interesting problem, I am pretty sure the answer will be not possible.  So I put forth to the experts.  

Is there a way to create a new tab(s) on the fly - with a copy of existing subforms to display a 2nd, 3rd, 4th record, etc. per tab.

I know this is really stretching, but I told them that I would research the possiblities.  From all my research creating tabs on the fly consist of creating multiple tabs and hiding them when not needed - very messy way to do this.
Karen SchaeferBI ANALYSTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Well depending on what you're showing in the tab, you may be able to simulate multiple tabs without really using a tab control.  In the image below, all I do is requery the subform when I select a different tab.  In this particular setup I have 6 images that look like tabs, each one colored grey.  I also have another tab that's colored yellow to show the current tab.  When I select a grey tab, I just position the yellow tab right on top of it.  I then update whatever I need to in the form and it looks like I moved to another tab.
Gustav BrockCIOCommented:
I would create, say, ten tabs hiding the nine. Then, as the user demands, unhide a tab and set the masterlink value for its subform. And so on.

This will, of course, enforce a limit for the number of tabs, but I guess that would be acceptable.

Jeffrey CoachmanMIS LiasonCommented:
I am still confused on the need for this...?

Can you elaborate?, ...perhaps there is an alternate approach...
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Karen SchaeferBI ANALYSTAuthor Commented:
My client wants to be able to display an unknown quantity of data, each record on a separate tab.  They are not aware of the limitations of Access.  I am trying to convince them that a continuous form will return the results better, but for some reasons they do not want to have to scroll a lot, but tabs seem to look cleaner.   From my research I have seen the option of creating and hiding the unused tabs, however, with not knowing the quantity of tabs needed, this is not an option.  # tabs can change drastically from 1-10 or more per each Main record.

Ron,  Do you have a working sample or can I see the code behind the form you suggest?

Thanks for the input.
Jeffrey CoachmanMIS LiasonCommented:
I am trying to convince them that a continuous form will return the results better, but for some reasons they do not want to have to scroll a lot, but tabs seem to look cleaner.

Anytime you create this type of non-standard user interface, are setting your self up for trouble...
Things like this would be better suited for unbound forms, where you will have to write code to handle/bypass/override, all of the Access built-in events and functionality...

All of this seems un-neccesarily over complex, ...then when it does not work the way they expect, ...who will help you?

Up to four record/tabs may be ok, ...but anymore than that may become a nightmare to manage.

Because you will have to create code to hide show a differing amount of records for each situation.
Then what about situations where the data changes and you need to requery the data?

Just my thoughts...

Karen SchaeferBI ANALYSTAuthor Commented:

I agree with you wholeheartedly, I told them I would research the issue and you confirmed my suspicions.  About to go into a meeting to let them know that the best approach is to use the continuous form is the best approach, a lot less coding and lot less chances to break.

Thanks for your input.

Jeffrey CoachmanMIS LiasonCommented:
OK, ..feel free to work through this with Ron.
You don't need to use a continuous form.  You can use a single record form which is what they seem to be asking for.  It just won't have tabs.  You can put forward and backward buttons on the form if you don't want to use the Access navigation control.
Here's a section of the code behind the form I posted.  I removed some  portions that were only applicable for our use so what's left is pretty much generic code you can use.  In my code, double-clicking on fgridPickupList is what adds a new tab (or goes to the tab if it's already open).  I created the grey and yellow tabs by inserting a Microsoft Word Picture Object.  I just used a shape that resembled a tab and colored it in.

Private Sub fgridPickupList_DblClick()
    Dim i As Integer
    Dim sCustCd As String
    Dim sCustomer As String
    Dim ctl As Control
    Dim rs As Recordset
    sCustCd = Me.fgridPickupList.TextMatrix(Me.fgridPickupList.Row, 4)
    sCustomer = Me.fgridPickupList.TextMatrix(Me.fgridPickupList.Row, 3)

    'add tabs or set focus to a tab as applicable
    For i = 1 To 6
        Set ctl = Controls("lblCustomer" & i)
        If ctl.Caption = sCustomer Then
            'go to tab that has this customer
            Exit For
        ElseIf ctl.Caption = "" Then
            'add new tab for this customer
            Me("imgTab" & i).Visible = True
            Me("lblCloseTab" & i).Visible = True
            ctl.Caption = sCustomer
            ctl.Tag = sCustCd
            Exit For
        End If
    'grab customer information and display in a tab
    Call UpdateCurrentTab(sCustCd, i)
End Sub

Private Sub lblCloseTab1_Click()
    Call CloseCurrentTab(1)
End Sub
Private Sub lblCloseTab2_Click()
    Call CloseCurrentTab(2)
End Sub
Private Sub lblCloseTab3_Click()
    Call CloseCurrentTab(3)
End Sub
Private Sub lblCloseTab4_Click()
    Call CloseCurrentTab(4)
End Sub
Private Sub lblCloseTab5_Click()
    Call CloseCurrentTab(5)
End Sub
Private Sub lblCloseTab6_Click()
    Call CloseCurrentTab(6)
End Sub

Private Sub CloseCurrentTab(Optional tabNumber As Integer)
'   closes the current tab
    Dim iTabNum As Integer
    If tabNumber = 0 Then
        iTabNum = val(Me.imgTabCurrent.Tag)     'closes current tab
        iTabNum = tabNumber                     'closes specified tab
    End If

    'shift customer names to the left
    Do While Me("lblCustomer" & iTabNum + 1).Caption <> ""
        Me("lblCustomer" & iTabNum).Caption = Me("lblCustomer" & iTabNum + 1).Caption
        Me("lblCustomer" & iTabNum).Tag = Me("lblCustomer" & iTabNum + 1).Tag
        iTabNum = iTabNum + 1
        If iTabNum = 6 Then Exit Do
    'hide last tab
    Me("lblCustomer" & iTabNum).Caption = ""
    Me("lblCustomer" & iTabNum).Tag = ""
    Me("imgTab" & iTabNum).Visible = False
    Me("lblCloseTab" & iTabNum).Visible = False
    If iTabNum = 1 Then
        'no more customers, hide tab
        Me.fgridPickupList.Height = HeightTall
        'go to first tab
        Me.imgTabCurrent.Left = Me.imgTab1.Left
        Call UpdateCurrentTab(Me.lblCustomer1.Tag, 1)
    End If
End Sub

Private Sub lblCustomer1_Click()
    Call UpdateCurrentTab(Me.lblCustomer1.Tag, 1)
End Sub
Private Sub lblCustomer2_Click()
    Call UpdateCurrentTab(Me.lblCustomer2.Tag, 2)
End Sub
Private Sub lblCustomer3_Click()
    Call UpdateCurrentTab(Me.lblCustomer3.Tag, 3)
End Sub
Private Sub lblCustomer4_Click()
    Call UpdateCurrentTab(Me.lblCustomer4.Tag, 4)
End Sub
Private Sub lblCustomer5_Click()
    Call UpdateCurrentTab(Me.lblCustomer5.Tag, 5)
End Sub
Private Sub lblCustomer6_Click()
    Call UpdateCurrentTab(Me.lblCustomer6.Tag, 6)
End Sub

Private Sub UpdateCurrentTab(sCustCd As String, iTabNum As Integer)
    Dim i As Integer
    Dim myQry As QueryDef
    Dim rs As Recordset
    If sCustCd <> "" Then
        DoCmd.HourGlass True
        If Me.tabCustomerInformation.Value = 1 Then
            Me.tabCustomerInformation.Value = 0         'show customer info tab if hidden
            Me.fgridPickupList.Height = HeightShort     'shrink list
        End If
        'highlight current tab
        Me.imgTabCurrent.Left = Me("imgTab" & iTabNum).Left
        Me.imgTabCurrent.Visible = True
        'update tab
        Me.lbxPickupList = sCustCd
        Me.imgTabCurrent.Tag = iTabNum
        gloCustCd = sCustCd
    End If
End Sub

Open in new window

Compared to just using the built in tabs, there is more work involved here.  I did this on Access 97 but if it was on a newer version of Access with the nicer looking Tabs, I would probably have used the built in Tab control and just use code to make the tab pages visible or invisible.  Then whatever control you requery for each tab should not be inside a tab page but just on top of the whole tab control so that you would see it no matter which tab you were on.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks for the input, but I convinced them to go with a continuous form after all, lot less coding.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.