Share this Page URL

Chapter 5. Using Visual Basic to Automat... > Using Control Structures - Pg. 161

Using Visual Basic to Automate Your Database For intCounter = 0 to rs.Fields(i).Count -1 Debug.Print rs.Fields(intCounter).Name Next intCounter End Sub 161 The variable intCounter is used as a counter. The Count property of the Fields collection indicates how many items are in the collection. (Counting starts with 0 and ends with the number of items less 1 because the fields are indexed starting with 0 rather than 1.) The names of the fields are displayed in the Immediate window one by one. The For Each...Next Statement When you work with the items in a collection or elements in an array, you can use For Each... Next to move through the items, using a variable to represent a member of the collection. The following example uses For Each...Next to display the name of each field in the CampaignExpenses table. This is the same operation we performed with the For...Next statement in the previous section, but using For Each...Next is more efficient. This example uses the Recordset object and the Field object, which you'll learn more about later in this chapter. Public Sub ForEachNext() Dim db As Database Dim rs As Recordset Dim fld as Field Set db = CurrentDb Set rs = db.OpenRecordset("CampaignExpenses") For Each fld In rs.Fields Debug.Print fld.Name Next fld End Sub The Do While...Loop Statement A Do While...Loop statement repeats a set of statements as long as a condition is true. Do While... Loop structures are useful for moving through the records in a recordset, as you can see in this example and in other examples later in this book. To see a Do While...Loop example, follow these steps. Use a Do While...Loop statement 1. 2. Switch to the Database window in Access, and then open the form named Demonstrations in Form view. Click the Display Countries button. You should see the names of the countries from the Mar- ketingCampaigns table in the list box to the right of the button, as shown here: