Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Aug 2002
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    for each .. next ? (Access 2000)

    In one of my forms i have to set the values of all controls to 0. For example
    Me![cartons].Value = 0
    Me![Productid].Value = 0
    etc etc
    I want to make all the controls in the form equal to 0.

    I know there is a way to use a command to place for each control in Form control = 0 but i do not know how to do it.
    May i askj for help?

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: for each .. next ? (Access 2000)

    Not exactly sure what you're trying to do....But in the Help, look up Do Loop.

    Here's what it says under For...Each

    Syntax

    For Each element In group
    [statements]
    [Exit For]
    [statements]
    Next [element]

    Example
    Dim Found, MyObject, MyCollection
    Found = False ' Initialize variable.
    For Each MyObject In MyCollection ' Iterate through each element.
    If MyObject.Text = "Hello" Then ' If Text equals "Hello".
    Found = True ' Set Found to True.
    Exit For ' Exit loop.
    End If
    Next

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: for each .. next ? (Access 2000)

    You can use something like this for an event procedure attached to a command button or some other control:

    <pre> Dim ctl As Control
    For Each ctl In Controls
    Select Case ctl.ControlType
    Case 109, 110, 111 'acTextBox, acListbox, acComboBox respectively
    ctl.Value = 0
    Case Else
    'Do nothing - labels, etc do not have values!
    End Select
    Next ctl</pre>

    This may need to be modified as necessary depending on what type of controls you are using. To look up the numerical values and named constants that correspond to each type of Access control, open Object Browser in VBE, select "Access" in Project/Library box and browse for AcControlType enum in the Classes list.

    HTH

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

    Re: for each .. next ? (Access 2000)

    Why lookup or use the numerical values at all? Using the built in constants is easier and you don't have to look them up, just use acTextBox, etc. in the Case statement.
    Charlotte

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: for each .. next ? (Access 2000)

    Suppose you don't know all the named constants off the top of your head? Not everybody does. I was 99% sure it was "acTextBox", etc but looked it up just to be sure. Or if you're lazy like me, type CTRL + SPACE in code window and select correct constant from "global" list.

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

    Re: for each .. next ? (Access 2000)

    Well, if you don't know the named constants, it's entirely unlikely that you'll know their numeric equivalents. <img src=/S/yikes.gif border=0 alt=yikes width=15 height=15> Actually, the named constants for object are the same as the name of the object with the addition of the "ac" prefix, so that shouldn't be too hard to recall.
    Charlotte

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Royal Oak, Michigan, USA
    Posts
    255
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: for each .. next ? (Access 2000)

    Hi Mark,
    I just want you to know how much time you saved me. I searched for the proper code to select all controls in a form and I ran across your reply to someone else in early September. The code saved me hours and I figured you'd want to know that it came in handy for someone else.
    Thanks!!

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: for each .. next ? (Access 2000)

    Glad you found this information to be useful - never did hear back from person who posted original question....

Posting Permissions

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