Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Collections' (VBA Access 2002)

    Can someone clarify for me the reason to use object collections in code, as opposed to referencing the specific control of the object? Maybe a short example, if time permits.

    Thanks

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: 'Collections' (VBA Access 2002)

    Can you explain your question in more detail? There are collections of objects in the object model, but you also can create new collections. Just not sure what you're getting at here. Is this about looping??

  3. #3
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Collections' (VBA Access 2002)

    Sorry...Within the object model. For example:

    Form_Form1.Label1.Caption = "Gabrielle Fonseca's Form"

    Why not just assign the caption property of the label, or the label on the form & not reference the collection?

  4. #4
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: 'Collections' (VBA Access 2002)

    Gabi,

    In the code example you cite, in most cases the "Form_Form1." part of it isn't really necessary: in particular, if you have this code in Form_Form1's own private code module, then you can get along fine without mentioning Form_Form1 explicitly.

    However, if you ever needed to refer to "Label1" in some code that is not located in Form_Form1's code module, then it may be better to fully qualify it by including the name of the userform which Label1 belongs to. For an example, suppose your project contained a Form_Form1, as well as a Form_Form2, and both of these forms contain a Label1. If you refer to Label1 in code which is outside either of these two form's private modules, then you would need to qualify the reference with the name of the form to which it belongs.

    Also, even if you are working within the form's code module, there is one slight advantage to fully qualifying the reference to Label1 with the name of the form, which is that you then have intellisense available to you - in other words, once you type "Form_Form1.", you should get a popup list which will include the names of all of the members of Form1, including the names of the custom controls. As a shorthand, when working in a form's code module, you can use the "Me" keyword i.e. Me.Label1 etc.

    Gary

  5. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: 'Collections' (VBA Access 2002)

    <P ID="edit" class=small>(Edited by jscher2000 on 10-Jul-03 11:59. Oooopsss... Access can be different.)</P>There are times when you want to act on a lot of objects or controls and looping through the collection is more efficient than hardcoding each reference. For example:

    Dim shp as Shape
    For Each shp in ActiveDocument.Shapes
    'do something to every Shape object in the document's Shapes collection
    Next

    Otherwise, you don't need to. Certainly if you're assigning a single Label Caption, you don't need to access it through a collection. And in your example, you're not, you're simply giving a "fully qualified" reference to the property. This avoids ambiguity about what Label1 you're dealing with.

    If the code is in the form's code module, I typically would use:

    Me.Label1.Caption = "Gabrielle Fonseca's Form"

    You don't need the Me in there because the VBA host will assume Label1 is local to the form the code is associated with, but if you type me. you get a helpful pop-up list, so it speeds up the process and reduces typing errors to use it.

    If the code is in another module outside the form, you do need to let the VBA host know exactly where Label1 is.

    Note: The above is for VBA UserForms, and may not apply to Access forms. Ask about Access forms on the Access forum.

  6. #6
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Collections' (VBA Access 2002)

    Gary, could you give me a little bit more explanation on this? I'm BRAND NEW <img src=/S/brainwash.gif border=0 alt=brainwash width=15 height=15> at coding and I'm not sure why one would refer to an object or control outside of it's own code module. Just a small example.

    <<However, if you ever needed to refer to "Label1" in some code that is not located in Form_Form1's code module, then it may be better to fully qualify it by including the name of the userform which Label1 belongs to. For an example, suppose your project contained a Form_Form1, as well as a Form_Form2, and both of these forms contain a Label1. If you refer to Label1 in code which is outside either of these two form's private modules, then you would need to qualify the reference with the name of the form to which it belongs.>>

  7. #7
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: 'Collections' (VBA Access 2002)

    A common example might be putting information on a form before you display it, for exampe

    <font face="Georgia">
    frmUserInput.lblInfo.Caption = "Here is some information for you"
    frmUserInput.Show
    </font face=georgia>
    StuartR

  8. #8
    Super Moderator
    Join Date
    Dec 2000
    Location
    New York, NY
    Posts
    2,970
    Thanks
    3
    Thanked 29 Times in 27 Posts

    Re: 'Collections' (VBA Access 2002)

    Gabi,

    I think Jefferson and I were both guilty of jumping in with non Access-centric replies. I'm really a beginner at Access coding so can't provide a good example. It may be there that in Access there is less occasion to refer to a form's controls, outside of the form's own code module, so in that respect my reply may have been off target.

    Since it may not be relevant to Access, here's just a general example of why you might refer to a userform's control in a module outside of the userform. An example might be a userform for a custom Letter template in Word, let's say for a firm which has offices in many different cities.
    There needs to be extensive code in the template to insert the appropriate content (such as logo, firm name, address, telephone, fax etc), specific to each individual city.

    When creating a new document, the user indicates on the userform, which city they are located in. When they click OK on the userform to finalize creating the document, a procedure needs to run to do the city-specific formatting.

    The procedure required to do the city-specific formatting might be very extensive and involve a great many lines of code, and to help keep all of the code organized, it might be better to split this procedure into its own separate module. The code in this procedure, even though it is no longer located in the userform's code module, still needs to refer to some of the controls in the userform, for example:

    strCity = UserForm1.cboCity.Value

    Select Case strCity
    Case "New York" 'do NY formatting
    Case "Washington" 'do Washington formatting
    End Select

    Don't know whether the above would ever be relevant to an Access project....

    Gary

  9. #9
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: 'Collections' (VBA Access 2002)

    To wrap a bit more context around Stuart's example, not all applications are "Form-centric." In Word, for example, I might want to gather some data in the middle of a process and interact with the user the rest of the time in other ways (if at all). In that case you might have this in a regular code module:

    ' we now need to gather information from the user
    UserForm1.Show
    ' code resumes after the UserForm is hidden
    ' now we can read information out of the form
    MsgBox "Your name is: " & UserForm1.TextBox1.Value
    ' do useful things with the data
    ' now destroy the form in memory, its data will no longer be accessible
    Unload UserForm1
    Set UserForm1 = Nothing

  10. #10
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: 'Collections' (VBA Access 2002)

    I'm going to lunch now, so you can handle this from here on out. <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'Collections' (VBA Access 2002)

    Gabi,

    As others in this thread have pointed out, Access VBA is different from other dialects of VBA, even if they all share a common basis. In particular, Access forms and controls are a lot different from the forms and controls you would use in Word or Excel, or in VB6. So it might be better to post new questions in the Access forum.

    Collections are very useful if you want to do something with all members. For example, all open forms in an Access database are members of the Forms collection. Here is a stupid example: say that you want to hide all forms that are currently open - today, there might be four forms open, tomorrow one and next week, three. So you can't specify them explicitly. Instead, you loop through the members of the Forms collection:

    ' Declare object variable
    Dim frm As Form
    ' Loop through all open forms
    For Each frm In Forms
    ' Hide it
    frm.Visible = False
    Next frm
    ' Release object memory
    Set frm = Nothing

    Another example is when you want to refer to the name of a control with a variable. Let's say that you have labels Label1, Label2, ..., Label32 on a form. You want to make them all bold. You could do it as follows:

    Me.Label1.FontBold = True
    Me.Label2.FontBold = True

    etc., through Me.Label32.FontBold = True, but this becomes tedious very soon. Instead of this, you can use the Controls collection:

    ' Declare loop index
    Dim i As Integer
    ' Loop through labels
    For i = 1 To 32
    ' Make it bold
    Me.Controls("Label" & i).FontBold = True
    Next i

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 'Collections' (VBA Access 2002)

    Actually, except for the objects involved, your example is equally applicable in Access, which is where this thread should have been posted. Since there are multiple replies, I won't move it now but you have quite clearly demonstrated the main reason to keep Access VBA questions in the Access forum.

    The Access equivalent would be something like this:

    Dim ctl As Control

    For Each ctl In Me.Controls
    'do something
    Next ctl

    So the short answer to Gabi's question is that you use a collection when you need to iterate through the controls, perhaps without knowing the name of the control ahead of time (such as changing the color of all the labels under certain conditions). You use a direct reference to a specific control when you want to act only on that control.
    Charlotte

  13. #13
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Collections' (VBA Access 2002)

    Thanks for all the help, I get it now!

    I'll know for next time to post in Access any VBA Access specific questions.

Posting Permissions

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