Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160

    Unanswered: Using arrays and page tabs

    hi there,

    I would like to address 4 fields in a table.

    I have 4 Page tabs in a form called main

    When I click on tab 1 in main i would like to copy data from field(1) to an unbound text box
    When I click on tab 2 in main I would like to copy data to field(2) to the same unbound text box as above.

    basically, would like to copy the data in Fields 1 to 4 to the same unbound text box. The data I copy depends on the tab I have selected.


    At the moment I have code which runs when I click on a page tab. for example Tab page 4

    Forms![main].Heading = Forms![main]![Main_sub1].Form![Tab4]

    I would like to array this somehow. So when I click on tab 4 the following code runs

    Forms![main].Heading = Forms![main]![Main_sub1].Form![Tab(4)]

    Tab4 is a field in a table. Do I have to declare this as an array somehow?

    thanks
    M

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Contrarily to VB, Access does not allow the creation of control arrays. There are two easy solutions to your problem:
    1. Compose the name of the control you want to address:
    Code:
    Private Sub TabPageChanged(ByVal TabIndex As Long)
        '
        ' TabIndex contains the Page index number
        '
        Me.Heading.Value = Me.Main_sub1.Form.Controls("Tab" & TabIndex).Value
    
    End Sub
    2. When the form opens, add the relevant controls to a collection and use this collection to retrieve the proper value :
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_colCtl As Collection
    
    Private Sub Form_Open(Cancel As Integer)
    
        Dim ctl As Control
        
        Set m_colCtl = New Collection
        '
        ' The Tag property of each page contains its index number
        '
        For Each ctl In Me.Main_sub1.Form.Controls
            If ctl.Name Like "Tab?" Then m_colCtl.Add ctl, Right(ctl.Name, 1)   ' only works for 'Tab0' to 'Tab9'
        Next ctl
        
    End Sub
    
    Private Sub TabPageChanged(ByVal TabIndex As Long)
        '
        ' TabIndex contains the Page index number
        '
        Me.Heading.Value = m_colCtl(TabIndex).Value
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    Hi Sinndho

    thanks for your reply.

    You gave me the idea to append the values together to create the field I would like to changing
    I created a Global integer in a module. I called it Gbl_Tabpointer

    When i click on a tab i update the Gbl_Tabpointer so I know which field to change.

    It's not quite working

    For the example below I want to change a field called Tab4

    But I get an error saying can't find Field.

    I think my syntax is wrong

    strTab = "Tab" & Gbl_TabPointer
    Forms![main]![Main_sub1].Form!['strTab'] = Forms.main.Heading

    I want to access a field called Tab4.

    string strTab = 'Tab4' but when i step through the code an error says it can't find the field.

    thanks
    Marcus

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you want to build the name of a control to address it, the proper syntax is:
    Code:
    Me.Controls("Name")
    So in your case:
    Code:
    Forms![main]![Main_sub1].Form.Controls(strTab)
    Have a nice day!

  5. #5
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    Hi Sinndho,

    thanks for looking at my problem.

    I tried the code below but an error appeared saying object doesn't support this property or method.


    Forms![main]![Main_sub1].Form.Controls(strTab) = Forms.main.Heading


    So strTab holds a the name Tab4

    so i'm trying to make the Field in Main_sub1 called Tab4 equal to the value held in an unbound text box called Heading.

    thanks
    Marcus

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In your first post, you wrote:
    When I click on tab 1 in main i would like to copy data from field(1) to an unbound text box
    When I click on tab 2 in main I would like to copy data to field(2) to the same unbound text box as above.
    In my first answer the Tabindex property I used is not directly related to the Tab Control of the form, it's the name of a variable that is received as argument by the sub TabPageChanged. The value of TabIndex indicates the number of the tab that was just clicked before the function is called.

    Moreover, the value (hence the control you want to reference) is a textbox (Field1 in your example) not Tab1, Tab2 whatsoever. Tab controls and their pages are not containers, i.e. the controls that appear on them still pertain to the form and are included in the Controls collection of this form.

    For addressing a control, Access Help (and MSDN: Controls Collection [Access 2003 VBA Language Reference]) explains:
    The Controls collection contains all of the controls on a form, report, or subform, within another control, or attached to another control. The Controls collection is a member of a Form , Report , and SubForm objects.

    Using the Controls Collection

    You can enumerate individual controls, count them, and set their properties in the Controls collection. For example, you can enumerate the Controls collection of a particular form and set the Height property of each control to a specified value.

    Tip
    It is faster to refer to the Controls collection implicitly, as in the following examples, which refer to a control called NewData on a form named OrderForm. Of the following syntax examples, Me!NewData is the fastest way to refer to the control.

    Me!NewData ' Or Forms!OrderForm!NewData.

    Me![New Data] ' Use if control name contains space.

    Me("NewData") ' Performance is slightly slower.

    You can also refer to an individual control by referring explicitly to the Controls collection.

    Me.Controls!NewData ' Or Forms!OrderForm.Controls!NewData.

    Me.Controls![New Data]

    Me.Controls("NewData")

    Additionally, you can refer to a control by its index in the collection. The Controls collection is indexed beginning with zero.

    Me(0) ' Refer to first item in collection.

    Me.Controls(0)
    Have a nice day!

  7. #7
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    hi Sinndho,

    My first posting was the wrong way round sorry for the confusion.

    Can we start again with the case below?


    This code runs when i click on a command button

    Private Sub Update_Button_Click()
    Dim strTab As String
    If Gbl_TabPointer = 4 Then
    ' Gbl_Tabpointer is an integer holding the page tab number I have assigned to it - in this case 4. I have field names of Tab4,Tab5 & Tab6 which I am trying to update

    strTab = "Tab" & Gbl_TabPointer ' now strTab = Tab4 which is the field I would like to change in the subform

    Forms![main]![Main_sub1].Form.(strTab) = Forms.main.Heading
    ' I'm trying to say make the Field called Tab4 = to the field called Heading

    Else

    Hope this makes sense?
    thanks
    Marcus


    Is this possible?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No problem! I was confused a little bit and feared you were too.

    Instead of:
    Code:
    Forms![main]![Main_sub1].Form.(strTab) = Forms.main.Heading
    It should be (see MSDN info):
    Code:
    Forms![main]![Main_sub1].Form(strTab) = Forms.main.Heading
    or
    Code:
    Forms![main]![Main_sub1].Form.Controls(strTab) = Forms.main.Heading
    This is because when you write:
    Code:
    Forms.main.Heading
    Heading is a litteral (it's actually the name of the control), while in the case:
    Code:
    Forms![main]![Main_sub1].Form(strTab)
    strTab is a variable containing the name of the control and you must reference it through the Controls collection of the form. Since this Controls collection is the default collection for the form you do not need to specify it and can use the shortcut Form(Index) instead of Form.Controls(Index).

    You'll find a more detailed explanation at:
    Also notice that if this code runs from the main form module (Forms!main in your code) you can simply write:
    Code:
    Me.Main_sub1.Form(strTab) = Me.Heading
    Have a nice day!

  9. #9
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    Hi Sinndho,

    I've been away for a few days but tried your suggestion and it worked a treat. thanks for the other links and advice, it's good to get an answer but even better to get a bit of training too.
    thanks again
    marucs

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    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
  •