MS-Access / Getting Started

---

For Each...Next Statement

Use a For Each...Next statement to execute a series of statements for each item in a collection or an array.

Syntax

For Each item In group
    [<procedure statements>]
    [Exit For]
    [<procedure statements>]
Next [item]

Notes

The item must be a variable that represents an object in a collection or an element of an array. The group must be the name of a collection or an array. Visual Basic executes the loop so long as at least one item remains in the collection or the array. All the statements in the loop are executed for each item in the collection or the array. You can place one or more Exit For statements anywhere within the loop to exit the loop before reaching the Next statement. Generally you'll use the Exit For statement as part of some other evaluation statement structure, such as an If...Then...Else statement.

You can nest one For Each loop inside another. When you do, you must choose a different item name for each loop.

Example

To list in the Immediate window the names of all the queries in the Conrad Systems Contacts database, enter the following in a function or sub:

Dim dbContacts As DAO.Database
Dim qdf As DAO.QueryDef
Set dbContacts = CurrentDb
For Each qdf In dbContacts.QueryDefs
    Debug.Print qdf.Name
Next qdf

Note

If you execute code within the For Each loop that modifies the members of the group, then you might not process all the members. For example, if you attempt to close all open forms using the following code, you will skip some open forms because you are eliminating members from the group (the Forms collection) inside the loop:

Dim frm As Form
For Each frm In Forms
  DoCmd.Close acForm, frm.Name
Next frm

The correct way to close all open forms is as follows:

Dim intI As Integer
For intI = Forms.Count - 1 To 0 Step - 1
  DoCmd.Close acForm, Forms(intI).Name
Next intI

GoTo Statement

Use a GoTo statement to jump unconditionally to another statement in your procedure.

Syntax

GoTo {label | linenumber}

Notes

You can label a statement line by starting the line with a string of no more than 40 characters that starts with an alphabetic character and ends with a colon (:). A line label cannot be a Visual Basic or Access reserved word. If you want, you can also number the statement lines in your procedure. Each line number must contain only numbers, must be different from all other line numbers in the procedure, must be the first nonblank characters in a line, and must contain 40 characters or less. To jump to a line number or a labeled line, use the GoTo statement and the appropriate label or linenumber.

Example

To jump to the statement line labeled SkipOver, enter the following:

GoTo SkipOver