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

    Unanswered: address a field through a variable

    hi all,

    quick question, I have fields called Tab1, Tab2, Tab3 in a form.
    I am trying to address a field in a form using a variable which holds the field name. last line throws up an object defined error.

    could someone help please?

    ' gbl_CommandPointer is a integer of value 1


    strA = "Tab" & [gbl_CommandPointer]

    ' strA now = Tab1

    strTabA = Forms![main].Fields(strA) ' does not work

  2. #2
    Join Date
    Aug 2011
    Location
    Indianapolis, IN, USA
    Posts
    41
    I don't think a form has a fields collection. A good explanation of the fields collection is at Field Object*[Access 2007 Developer Reference]

    You might have to cut and paste the URL. It doesn't look like a link in the input box where I'm typing this.

    In your question, I assume the VBA is in the Class Module for the form named Main and Tab1 is the name of a text box on the form. If so, I'd use:

    strTabA = Me!Tab1
    Off hand, I'm not sure how you'd reference the text box using a string variable but I'm sure there's a way.

    If you're trying to pull something out of the field named Tab1 in a table, then you will probably have to open a recordset and get the value from there.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    A form has a Controls collection. It also has a RecordSet member that has a Fields collection.

    If you want to address the Control:
    1)
    Code:
    strTabA = Forms![main].Controls(strA).Value
    If the code is run from the class module of the form, you can simplify:
    2)
    Code:
    strTabA = Me.Controls(strA).Value
    Be aware that this will cause an error if the value is Null. You should use:
    3)
    Code:
    strTabA = Nz(Me.Controls(strA).Value, "")
    If you want to address the Field:
    Code:
    strTabA = Forms![main].Recordset.Fields(strA).Value
    Then:
    2)
    Code:
    strTabA = Me.Recordset.Fields(strA).Value
    And:
    3)
    Code:
    strTabA = Nz(Me.Recordset.Fields(strA).Value, "")
    Have a nice day!

  4. #4
    Join Date
    Aug 2011
    Location
    Indianapolis, IN, USA
    Posts
    41
    Thanks Sinndho. I wasn't aware you could address a form's recordset that way.

    I'll put your reply into my "How To" book.

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  6. #6
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    thanks for your help.
    this is the code i got to work....

    strA = "Tab" & [gbl_CommandPointer]

    'capture current order
    strTabA = Forms![main].Form(strA)

    'which point to a field called Tab1


    so basically, depending on which command buttom i press, will give me a number (gbl_CommandPointer) which is used to recall the information held in field Tab1 or Tab2 or Tab3 etc

    Running on the same lines I'm trying to change the caption on a control indirectly. PS. Is there somewhere i can look up addressing fields , controls etc? I've tried the code below but it throws run-time error 2465:-

    strTabA = Forms![main].Command(strA).Caption

    'strA hold a integer value. I have several buttons called Command1 Command2 etc. which i would like to change the caption to the string held in strTabA.

    Any ideas very welcome:-)

    thanks again
    Marcus

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You cannot create an array of controls in VBA, contrarily to what can be done in VB, so: Command(strA) cannot work. If the names of the controls are Command1, Command2, etc., you can recompose the name from the value of strA like this:
    Code:
    ControlName = "Command" & strA
    In such a case, you can address the control from the Controls collection only:
    Code:
    Forms!Main.Controls("Command" & strA)
    Finally, if you want to change the caption (of a command button) to the string held in strTabA, it should be the other way around:
    Code:
    Forms!Main.Controls("Command" & strA).Caption = strTabA
    Personally I would not work like that. With your method you cannot change the captions several times, except if you memorize each previous value. I would store the command buttons into a Collection object and address them from there. Here's an example:

    One form has 6 command buttons named Command1, Command1, ... etc. to Command6. The form also has 2 textboxes named Text_ButtonIndex and Text_ButtonCaption.

    When you type something in Text_ButtonCaption, it changes the caption of the command button corresponding to the numeric value contained in Text_ButtonIndex, and when you change the numeric value of Text_ButtonIndex (between 1 and 6) it changes the caption of the corresponding button to what's contained in Text_ButtonCaption. Here's the code of the form module:
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_colCommandButtons As Collection
    
    Private Sub Form_Open(Cancel As Integer)
    
        Dim i As Integer
        
        ' Store the command buttons into the collection.
        '
        Set m_colCommandButtons = New Collection
        
        ' The form has 6 command buttons named "Command1" to "Command6"
        '
        For i = 1 To 6
            m_colCommandButtons.Add Me.Controls("Command" & CStr(i)), CStr(i)
        Next i
        
    End Sub
    
    Private Sub Text_ButtonCaption_AfterUpdate()
    
        ChangeCaption
        
    End Sub
    
    Private Sub Text_ButtonIndex_AfterUpdate()
    
        ChangeCaption
        
    End Sub
    
    Private Sub ChangeCaption()
    
        Dim strIndex As String
        
        strIndex = Nz(Me.Text_ButtonIndex.Value, 0)
        Select Case Val(strIndex)
            Case 1 To 6:    m_colCommandButtons.Item(strIndex).Caption = Nz(Me.Text_ButtonCaption.Value, "")
            Case Else:      MsgBox "The index must be between 1 and 6", vbInformation
                            Me.Text_ButtonIndex.Value = Null
        End Select
        
    End Sub
    Have a nice day!

  8. #8
    Join Date
    Sep 2010
    Location
    UK
    Posts
    160
    that's hit the nail on the head :-) thanks again Sinndho, i took your advice on the collect object.
    cheers
    marcus

  9. #9
    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
  •