Results 1 to 8 of 8
  1. #1
    bal is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    4

    Updating Subform in multipage tab control

    Hi,



    I have a tab control in my main form with 8 tabs/pages. Each tab has the same sub form (copy and pasted from first tab to others). I want the subform to requery and show the results based on the tab selected.

    In the onchange event in the events for the tab control itself, I am using the below code and the funny thing is it works fine but only for the first tab.

    The subform for some reason does not requery when I click on the other tabs. I put a message box showing the selected tab as a test to see if it picks up me changing tabs and these popped up and displayed the correct tab index as I cycled the different tabs but the subform would only requery for the tab index 1, this is after clicking other tabs and returning to it works every time. When I click away the other tabs change the results in the subform but to list all items not the correct items as per the requery. I dont want tab index 0 to do anything which also works fine.

    I also tested the sql statements in the query design and they all work correctly. The tab control on the main form is called tabctl_menu and subform is called subfrm_menu_main_items

    Code:
     Private Sub tabctl_menu_Change()
    'Show only the starters
    'Starter, Soup, Salad, Main Course, Sundry, Dessert, Extras, Drink
    Dim x As Variant
    x = Me.tabctl_menu.Value
    
    Select Case Me.tabctl_menu
        Case 0
            MsgBox x
            GoTo endprog
        Case 1
           Me.subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Starter'"
        Case 2
            MsgBox x
            Me.subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Soup'"
        Case 3
            MsgBox x
            Me.subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Salad'"
        Case 4
            MsgBox x
            Me.subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Main Course'"
        Case 5
            MsgBox x
            Me.subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Sundry'"
        Case 6
            MsgBox x
            Me.subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Dessert'"
        Case 7
            MsgBox x
            Me.subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Extras'"
        Case 8
            MsgBox x
            Me.subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Drink'"
    End Select
    
    endprog:
    
    End Sub
    Not sure what I am doing wrong as the other lines are exactly the same as the one for the tab index 1 only the value is different. I even copied and pasted the line for the tab index 1 that works to the others and still no luck. It is driving me crazy.

    Many thanks in advance for your kind assistance.

    Kind regards,
    B

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Me!subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_me....

    Try an exclamation instead of period.

  3. #3
    bal is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    4
    Hi davegri,

    Thanks for taking the time to look at this and for your suggestion. I tried the below and still get the same issue:

    Me!subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Starter'"

    It is really odd because tab index1 works fine. When I change from tab index 1 to the others the list does not update but shows all entries. When I go back to tab index 1 it works fine and changes the record source of the subform based on the selection. It is really strange. Not sure if copying and pasting the subform to the other tabs has an impact where by it is active or something in only one tab.

    It is really strange, I am still searching the net for why this behavior is occurring. I compacted and repaired the database as well and still same result. What is also odd is that I have included a message box with each case above the code like below and this triggers fine across each tab as the selection changes. The only thing that seems to work for only one tab is the record source part.

    Also oddly, tab index 1 is the original tab where I first input the subform. It was then copy and pasted to the other forms. I wonder if this impacts.

    Code:
    Private Sub tabctl_menu_Change()
    'Show only the starters
    'Starter, Soup, Salad, Main Course, Sundry, Dessert, Extras, Drink
    Dim X As Variant
    X = Me.tabctl_menu.Value
    
    Select Case Me.tabctl_menu
        Case 0
            MsgBox X
            GoTo endprog
        Case 1
            MsgBox X
            Me!subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Starter'"
        Case 2
            MsgBox X
            Me!subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Soup'"
        Case 3
            MsgBox X
            Me!subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Salad'"
        Case 4
            MsgBox X
            Me.subfrm_menu_main_items.Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Main Course'"
        Case 5
            MsgBox X
            Me.subfrm_menu_main_items!Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Sundry'"
        Case 6
            MsgBox X
            Me.subfrm_menu_main_items!Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Dessert'"
        Case 7
            MsgBox X
            Me.subfrm_menu_main_items!Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Extras'"
        Case 8
            MsgBox X
            Me.subfrm_menu_main_items!Form.RecordSource = "SELECT tbl_menu.* FROM tbl_menu WHERE tbl_menu.[Item Category]= 'Drink'"
    End Select
    
    endprog:
    End Sub
    Thanks for everyones time and kind assistance in looking at this odd issue.

    Kind regards
    B

  4. #4
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Could we see a copy of your database? It would be easier?


    Sent from my iPhone using Tapatalk

  5. #5
    bal is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    4
    Just an update:

    I did a brief test and found something. What I did:

    1) I changed the onchange event in the tab control so that instead of updating the subform it would record the current tab index to a text box. I also removed the select case statement that updated the subform so that as you change tabs only the text box is updated witht eh current tab index. This worked fine:
    Code:
    Dim X As Variant
    X = Me.tabctl_menu.Value
    Me.bx_tab_index = X
    2) I created a button and for the onclick event I placed the select case statement that would take the number from the text box and update the subform as per the previous posts.

    Outcome:

    I tested by clicking on a tab index and then pressing the button (the subform update was now controlled by pressing the button). I still received the same issue where by only tab index1 would update. When I would click on tab index 2 say and then pressed the button nothing appeared to happen. However if I returned back to tab index 1 after running the procedure from another tab, the subform would show correctly updated in tabindex1 for the respective other tabs. I hope that makes sense.

    i.e. if I go to tab3 and run the code, the subform wont show as updated in tab3 but if i go back to tab1 without running the code via the button, tab index 1 would show the correctly updated subform but with tab 3's results as that was the last time I ran the code.

    So it appears that although I copied and pasted the subform to the other tabs, they are being treated and separate instances, potentially. The update to record source only shows in tab index1 which is where I originally inserted the subform. Which is odd as I was under the impression that if you use the same subform in a tabcontrol it is actually treated as the same item irrespective of what tab you are on.

    When updating the subform in the tab control do I also need to activate the subform or some such in the relevant tab index so it updates on the correct tab?

    Just need to pop out will try uploading database for reference when I get a chance. It looks like what I am experiencing is correct behaviour by design and I am missing something in terms of sequence of events or activating/referencing something.

    Thanks again!

  6. #6
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    I think the problem is that you have 8 instances of the same subform, one in each tab. When you adjust the recordsource it doesn't get applied to the instance you expect. If the contents of each tab is exactly the same, there's a way to do what you want with only one instance of the subform. The idea is not to put the subform on any tab, but to put it on the Detail section with the transparent tabs overlaying it.
    Last edited by davegri; 01-14-2017 at 08:41 AM. Reason: clarity

  7. #7
    bal is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jan 2017
    Posts
    4
    Hi davegri,

    Thank you so much for your reply.

    I misunderstood how subforms in tab controls work which is the cause of the issue. When I looked closer at each subform on the various tabs, although the same form was copied and pasted each instance of the subform was given a different name, only the original instance on tab1 had the correct name which is why that was the only one that updated.

    I renamed each of the subform copies on the tab controls differently and updated the code to reflect and it all started working.

    Reading your update it all made clear sense. Subforms in tab controls are like any other control and an instance of that control. So it is important to reference them by looking at each instance name, obvious I know when you know what you are looking for. However, I was under the mistaken assumption that when you use the same subform across each tab it is actually one form. While this is true, Access creates a container for each one and in essence it is a separate instance so needs to be referenced accordingly. (I think that is right anyways).

    Thank you all for your kind help and time.

    Davegri big thanks for your kind suggestions. I am using your method as far more elegant and cleaner to work with!

    Thanks again all.

  8. #8
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Bal, glad to help. Sometimes the simple is not so evident.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 06-19-2015, 02:19 AM
  2. dsiplay multipage pdf in an Access control
    By chuck555 in forum Forms
    Replies: 5
    Last Post: 03-10-2014, 03:36 PM
  3. Replies: 3
    Last Post: 03-29-2012, 12:40 PM
  4. Updating Default Value of a Subform Control
    By eww in forum Programming
    Replies: 4
    Last Post: 04-11-2011, 12:57 PM
  5. How to add multipage after using form wizard?
    By robertrobert905 in forum Access
    Replies: 1
    Last Post: 09-21-2010, 02:37 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums