Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2012
    Posts
    9

    Unanswered: Linking combo box to tab

    Hi,

    I am trying to link a combo box to a tab (in Access 2007), so that when a certain field is selected on the record, the relevant tab is opened. I have looked at the previous threads on this and have tried to follow the code.

    What I have done so far -

    1. Created combo box
    2. Created tabs
    3. In the combo box properties - After Update > Event Procedure > code
    4. After Update code is -

    Private Sub Combo666_AfterUpdate()

    Me.TabCtl28.Pages("Audit" & Right(Me.Combo_SelectTab.Value, 1)).SetFocus

    End Sub

    5. I am not sure what the value is meant to be so left it as 1. Is it meant to be the tab caption, page index or page number?

    6. When I go to compile the code I get an error - "compile error - method or data member not found". It highlights this part of the code - .Combo_SelectTab

    Please help!! I am totally clueless so any replies in step-by-step laymans terms please!
    Last edited by libbiker; 01-03-13 at 08:34.

  2. #2
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    You can name your tabs and then set focus on them just like you would a control as follows:

    Me("Audit" & Right(Me.Combo_SelectTab.Value, 1)).SetFocus

    NB: This assumes you have names your tab "Audit" + the charatcer from the combobox.
    Regards
    JD

    Software-Matters

  3. #3
    Join Date
    Dec 2012
    Posts
    9
    Thank you for replying so quickly!

    Does that code need to go in the combo box event?

    I have renamed the tabs and it still comes up with the same error message. Do I need to do anything to the events on each tab properties?

  4. #4
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    Try this:

    Me("Audit" & Right(Me![ComboName], 1)).SetFocus

    NB: "ComboName" should be replaced with the actual name of the combo and this code should be on the AfterUpdate of that combo.
    Regards
    JD

    Software-Matters

  5. #5
    Join Date
    Dec 2012
    Posts
    9
    Thanks. I think I'm being totally dense as I still can't get it to work :-(

    If I want a combo box selection to display a set of options specific to the combo box selections can I use something other than tabs to do this? Perhaps theres an easier option? Will hidden subforms work?

  6. #6
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    Yes you can use hidden subforms to show/hide data depending on a selection but tabs should work just as well or you could just have the fields on the main form and show and hide them. It really depends how many fields you have and the layout you require. All of this is quite simple so you're probably very close.

    Upload the db and I will take a look if you want.
    Regards
    JD

    Software-Matters

  7. #7
    Join Date
    Dec 2012
    Posts
    9
    Thanks, that would be great!
    Attached Files Attached Files
    Last edited by libbiker; 01-04-13 at 12:19.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Change 2 properties of the Combobox (Combo93):
    ColumnCount = 2
    Rowsource = "'Audit';0;'Fac UK';1;'Lab';2;'Lib';3;'NHS REC';4;'Obs Overseas';5;'Obs UK';6;'Serv Eval Notes';7;'Serv Eval QIS';8;'Other';9"
    You can do it in the Form_Open event handler:
    Code:
    Private Sub Form_Open(Cancel As Integer)
    
        Me.Combo93.RowSource = "'Audit';0;'Fac UK';1;'Lab';2;'Lib';3;'NHS REC';4;'Obs Overseas';5;'Obs UK';6;'Serv Eval Notes';7;'Serv Eval QIS';8;'Other';9"
        Me.Combo93.ColumnCount = 2
        
    End Sub
    2. Correct the Combo93_AfterUpdate event handler, which becomes:
    Code:
    Private Sub Combo93_AfterUpdate()
    
        Me.TabCtl28.Pages(Val(Me.Combo93.Column(1))).SetFocus
    
    End Sub
    Notes:
    a) The original Combo93_AfterUpdate event handler made a reference to Combo_SelectTab which does not exist:
    Code:
    Private Sub Combo93_AfterUpdate()
    
    Me.TabCtl28.Pages("Audit" & Right(Me.Combo_SelectTab.Value, 1)).SetFocus
    
    End Sub
    b) You project would be easier to manage and maintain if you would give meaningful names to tho objects (i.e. Combo_ProjectType instead of Combo93, TabCtl_Projects instead of TabCtl28, etc.).
    Have a nice day!

  9. #9
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    I wrote afunction I used a long time ago to handle which tabs to view or make visible for certain criteria - It has served me well since Office 97 so I have placed it here for you guys

    if you think it helps let me know.

    The only thing is you have to Name the tabs starting with P0 , P1, P2 , etc.

    Public Function fnPages(intpage As Integer)

    Dim x
    On Error Resume Next
    x = 0
    Do Until x = 10 ' this just tells me how many pages on the tab control there are

    Me("P" & x).Visible = False
    x = x + 1
    Loop
    Me.MyTabControl.Visible = True 'not needed I use this to make the entire Tab visible if
    'it is hidden on a form - say at start up or something
    Me("P" & intpage).Visible = True

    End Function


    so based on certain criteria or events - if i want to show a certain tab only and no ther tabs - say tab 2
    fnPages(2)
    what is nice is if you set the entire tab control's Style property to NONE then you can create the illusion of conrols appearing/disappearing like a mini subforms.

    Any way this has served me well - and what is nice - if you are not setting the visibility of the tab control to yes or no then you never have to deal with the referencing of the tab control - the function handles the tabs for visible, etc.
    Dale Houston, TX

  10. #10
    Join Date
    Dec 2012
    Posts
    9
    Thanks everyone!

    Sinndho - thank you. I did as you advised, and it worked once I created a separate table with the values (it didn't like the row source for some reason). However, the combo selection doesn't save once I create a new record or close and reopen the database.

    Have a missed out a step somewhere?

  11. #11
    Join Date
    Dec 2012
    Posts
    9
    Heres the database again with the changes.
    Attached Files Attached Files

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm not sure to understand what you mean. How could the combo "remember" it's former value, is it linked to another control?
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •