Among the top four objects most searched for on the Microsoft Developer Network by Excel programmers are the Sheets collection and the Worksheets collection (the other two are the Range object and the Application object). As an aside, I've written new articles on programming the Range and Application objects which will be published on the Office Developer Center in January 2008.

For new Excel programmers, there might be some confusion as to the difference between the Sheets and Worksheets collections. The Worksheets collection contains the items you typically think of on an Excel worksheet: rows, columns, cells, and formulas. The Sheets collection, on the other hand, consist of not only a collection of worksheets but also other types of sheets to include Chart sheets, Excel 4.0 macro sheets (also known as XLM files) and Excel 5.0 dialog sheets (allows you to create custom dialog boxes). Chart sheets are charts that take up an entire worksheet, but not charts that are inserted as part of a worksheet. The Excel 4 and Excel 5 sheets are legacy items used to maintain backwards compatibility and ease the transition from older versions of Excel to new. And just to further muddy the waters, there is also a Charts collection that is made up of chart sheets.

The Count property of the Worksheets collection contains the number of worksheets in a workbook. The Count property of the Sheets collection contains the number of all sheets in a workbook to include chart sheets and worksheets. For example, you can add a specific number of worksheets to a workbook with the following:

Do While Worksheets.Count < 5
   ThisWorkbook.Sheets.Add
Loop

The worksheets you add may then become either a chart sheet or left as a worksheet, at which time, they become part of the Charts or Worksheets collection, respectively, or collectively become members of the Sheets collection. In the following example, you change the name of the last sheet in a workbook:

Dim wrkSheetName As String
wrkSheetName = "Projected Sales Chart"
Sheets(Sheets.Count).Name = wrkSheetName

Note that because you are using the Sheets collection, the last sheet in this workbook could be either a worksheet or a chart sheet. Given the name, it is likely a chart sheet.

As you might expect, the Sheets collection contains more methods than the Worksheets collection since it is home to more types of sheets. However, in both collections, there are a variety of methods to do such things as add, delete, copy, and move a sheet. There is ample documentation on these and other properties, methods, and events of the Worksheets and Sheets collections from the Excel portal on Office Developer Center on MSDN (http://msdn.microsoft.com/office).

One of the coolest features in Office that I think many people (well, at least me) overlook is the translation capability. You can type in a word and get a translation immediately in any language you select. I'm currently trying to teach myself Spanish and find this feature really useful.

http://frice.officeisp.net/Miscellaneous%20pictures/OfficeTranslationFeature.gif

In most Office 2007 applications, you can find the translation feature on the Research pane in the Proofing group on the Review tab. In Outlook, right-click anywhere in the message body of an e-mail, and then click Translate on the shortcut menu.

Just a quick note to say that there is new developer content on the Office Developer Center (http://msdn2.microsoft.com/en-us/office/default.aspx). These include over one hundred Ribbon code snippets that can be dropped into Visual Studio solutions. About half have been written in Visual Basic .NET and the other half werer written in C#. There are samples that target Excel, PowerPoint, and Word so check them out. We are also creating a ton of how-to videos on the Office Developer Center to include videos on the Ribbon by your truly, Open XML Format files by Ken Getz, and on Content Controls by Erika Ehrli Cabral. New video how-to are coming out almost every day so you should check the site frequently.

In this and the next installment in the series of topics on customizing the Ribbon UI, we'll look at different scenarios for customizing the Ribbon at the application-level by using COM add-ins. These modifications could just as easily be applied to a specific document with document-level customizations using Office 2007 Open XML Formats files as described in Part 3 of this series.

First, you'll create the project in Visual Studio using C#. Next, you'll create different customization XML markup files and then embed those into the project. As some of the customizations involve using bitmap icons, you'll need to include three of your favorite 16 x 16 pixel icons. Custom icons for add-ins must be 16 x 16 pixels and be either 16-color or True Color. To get started:

First, create the Visual C# project:
1. Start Visual Studio .NET 2005.
2. On the File menu, click New Project.
3. In the New Project dialog box under Project Types, expand Other Projects, click Extensibility Projects, and then double-click Shared  Addin.
4. Type a name for the project. In this sample, type RibbonUISamplesCS.
5. In the first screen of the Shared Add-in Wizard, click Next.
6. In the next screen, select "Create an Add-in using Visual C#", and then click Next.
7. In the next screen, clear all of the selections except Microsoft Word and then click Next.
8. Type a Name and Description for the add-in, and then click Next.
9. In the Choose Add-in Options screen, select "I would like my Add-in to load when the host application loads" and then click Next.
10. Click Finish to complete the wizard.

Now add a reference to Word:
1. In the Solution Explorer, right-click References, and then click Add Reference.

Note: If you don't see the References folder, click the Project menu and then Show All Files.
 
2. Scroll downward on the .NET tab, press the Ctrl key, and then select Microsoft.Office.Interop.Word.
3. On the COM tab, scroll downward, select Microsoft Office 12.0 Object Library, and then click OK.
4. Next add the following namespace references to the project if they don't already exist. Do this just below the namespace line:

using System;
using Extensibility;
using System.Runtime.InteropServices;
using Microsoft.Office.Core;
using Microsoft.Office.Interop;
using System.Drawing;
using System.Windows.Forms;

Implement the IRibbonExtensibility interface.
1. In the Solution Explorer, right-click Connect.cs, and click View Code.
2. At the end of the public class Connect statement, add a comma, and then type IRibbonExtensibility.

Tip: You can use Microsoft IntelliSense to insert interface methods for you. For example, at the end of the public class Connect : statement, type IRibbonExtensibility, right-click, point to Implement Interface, and then click Implement Interface Explicitly. This adds a stub for the only IRibbonExtensibility interface member: GetCustomUI. The implemented method looks like this:

string IRibbonExtensibility.GetCustomUI(string RibbonID)
{
}

3. Insert the following statement into the GetCustomUI method, overwriting the existing code:

return resource.GetString("customui-1.xml");

4. Above the GetCustomUI method, add this statement that gives you access to the XML markup files which you'll add later:

private ResourceManager resource = new ResourceManager();

5. Add the callback methods that will be used in the different customization files:

#region callbacks-1
       
public stdole.IPictureDisp ButtonImage(IRibbonControl control)
{
   return resource.GetImage("Icon2.bmp");
}

public void ButtonAction(IRibbonControl control)
{
   Random r = new Random();
   string s = r.Next(100000, 200000).ToString();

   Microsoft.Office.Interop.Word.Application wordApp = (Microsoft.Office.Interop.Word.Application)(applicationObject);
   wordApp.Selection.InsertAfter("\nContoso Case ID " + s);
}

#endregion callbacks-1

#region callbacks-2

private Boolean randomize = false;
private IRibbonUI myRibbon;

public void ribbonLoaded(IRibbonUI ribbon)
{
   myRibbon = ribbon;
}

public stdole.IPictureDisp getSButton(IRibbonControl control)
{
   return resource.GetImage("Icon3.bmp");
}

public int bankTellers_getItemCount(IRibbonControl control)
{
   return 6;
}

public stdole.IPictureDisp bankTellers_getItemImage(IRibbonControl control, int index)
{
   Random r = new Random();

   if (!randomize)
   {
      return resource.GetImage(index + ".bmp");
   }
   else
   {
      return resource.GetImage(r.Next(0, 7).ToString() + ".bmp");
   }
}

public void bankTellers_OnAction(IRibbonControl control, int index)
{
   myRibbon.Invalidate();
   Microsoft.Office.Interop.Word.Application wordApp = (Microsoft.Office.Interop.Word.Application)(applicationObject);
   wordApp.Selection.InsertAfter("Officer #" + (index+1) + " ");
   //  MessageBox.Show("You just clicked on Bank Teller #" + (index + 1) + "!");
}

public void toggle_randomize(IRibbonControl control, bool press)
{
   randomize = press;
   myRibbon.Invalidate();
}

public string getRandomizeLabel(IRibbonControl control)
{
   if (randomize)
   {
      return ("Randomize ON");
   }
   else
   {
      return ("Randomize OFF");
   }
}

#endregion callbacks-2

#region callbacks-3
public stdole.IPictureDisp dinoImage(IRibbonControl control)
{
   return resource.GetImage("Icon1.bmp");
}

public stdole.IPictureDisp OnLoadImage(string image)
{
   return resource.GetImage(image);
}

#endregion callbacks-3

Now, we'll start creating different customization files, embedding them as resources in the project, and then observing how they impact the Ribbon UI:

1. In a text editor, add the following XML markup:

<?xml version="1.0" encoding="utf-8" ?>
<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>
      <tab idMso="TabInsert">
        <group id="MyContosoGroup" label="Contoso!" insertAfterMso="GroupHeaderFooter">
          <button id="CInsertCaseID"
                 size="large"
                  label="Case ID Number"
                  screentip="Insert Contoso Case ID Number."
                  onAction="ButtonAction"
                  getImage="ButtonImage" />
        <!-- onAction, getImage callbacks -->
      </group>
      </tab>
    </tabs>
  </ribbon>
</customUI>

2. Close and save the file as customUI-1.xml.
3. In the Solution Explorer in the project, right-click RibbonUISamplesCS, point to Add, and then click Existing Item.
4. Navigate to the customUI-1.xml file you created, select the file, and then click Add.

For each of the customization files you create, you will repeat these steps. After adding each file and before building and installing the project, you'll need to ensure that the GetCustomUI method points to the correct file. So in this instance, the statement in the GetCustomUI method will read:

return resource.GetString("customui-1.xml");

And finally, use the same procedures to add each of the three 16 x 16 bitmaps as Icon1.bmp. Icon2.bmp, and Icon3.bmp, respectively.

Compile both the add-in and its setup project. Before beginning, make sure that Word is closed.
1. In the Project menu, click Build Solution. You will see a message when the build is complete in the system tray in the lower left corner of the window.
2. In the Solution Explorer, right-click RibbonSamplesCSSetup and click Build.
3. Right-click RibbonSamplesCSSetup and click Install. This launches the RibbonSamplesCSSetup Setup Wizard screen.
4. Click Next on all of the screens and then Close on the final screen.
5. Start Word. You should see the "My Tab" tab to the right of the other tabs.

If you have problems with any of this, recheck that the procedures in the project match those in the steps or go back to part 4 in this series of articles for additional troubleshooting steps. 

In this installment in the series on customizing the Ribbon UI, we'll look at adding application-level customization. In the previous blog, we detailed the steps to create document-level Ribbon by modifying an Open XML Formats macro-enabled file in Word 2007. In this blog, we will create application-level customizations using a managed COM add-in which we'll create in Microsoft Visual Studio 2005 using C#. For consistency, we'll use the same customization that we used in the previous blog; namely adding a custom tab, a custom group, and button that will insert a company name into a Word 2007 document at the beginning of the document. This is the procedure:

1. In a text editor, add the following XML markup:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon>
    <tabs>
      <tab id="CustomTab" label="My Tab">
        <group id="SampleGroup" label="Sample Group">
          <button id="Button" label="Insert Company Name" size="large" onAction="InsertCompanyName" />
        </group >
      </tab>
    </tabs>
  </ribbon>
</customUI>

2. Close and save the file as customUI.xml.

Now create the Visual C# project to modify the Ribbon UI:
1. Start Visual Studio .NET 2005.
2. On the File menu, click New Project.
3. In the New Project dialog box under Project Types, expand Other Projects, click Extensibility Projects, and then double-click Shared Addin.
4. Type a name for the project. In this sample, type RibbonXSampleCS.
5. In the first screen of the Shared Add-in Wizard, click Next.
6. In the next screen, select "Create an Add-in using Visual C#", and then click Next.
7. In the next screen, clear all of the selections except Microsoft Word and then click Next.
8. Type a Name and Description for the add-in, and then click Next.
9. In the Choose Add-in Options screen, select "I would like my Add-in to load when the host application loads" and then click Next.
10. Click Finish to complete the wizard.

Now add a reference to Word:
1. In the Solution Explorer, right-click References, and then click Add Reference.

Note: If you don't see the References folder, click the Project menu and then Show All Files.
 
2. Scroll downward on the .NET tab, press the Ctrl key, and then select Microsoft.Office.Interop.Word.
3. On the COM tab, scroll downward, select Microsoft Office 12.0 Object Library, and then click OK.
4. Next add the following namespace references to the project if they don't already exist. Do this just below the namespace line:

using System.Reflection;
using Microsoft.Office.Core;
using System.IO;
using System.Xml;
using Extensibility;
using System.Runtime.InteropServices;
using MSword = Microsoft.Office.Interop.Word;

Add the XML customization file as an embedded resource in the project by following these steps:
1. In the Solution Explorer, right-click RibbonXSampleCS, point to Add, and click Existing Item.
2. Navigate to the customUI.xml file you created, select the file, and then click Add.
3. In the Solution Explorer, right-click customUI.xml, and then select Properties.
4. In the properties window select Build Action, and then scroll down to Embedded Resource.

Next, to get access to its members, you need to implement the IRibbonExtensibility interface.
1. In the Solution Explorer, right-click Connect.cs, and click View Code.
2. Just below the Connect method, add the following declaration which creates a reference to the Word application object:

private MSword.Application applicationObject;

3. Add the following line to the OnConnection method. This statement creates an instance of the Application object:

applicationObject =(MSword.Application)application;
 
4. At the end of the public class Connect statement, add a comma, and then type IRibbonExtensibility.

Tip: You can use Microsoft IntelliSense to insert interface methods for you. For example, at the end of the public class Connect : statement, type IRibbonExtensibility, right-click, point to Implement Interface, and then click Implement Interface Explicitly. This adds a stub for the only IRibbonExtensibility interface member: GetCustomUI. The implemented method looks like this:

string IRibbonExtensibility.GetCustomUI(string RibbonID)
{
}

5. Insert the following statement into the GetCustomUI method, overwriting the existing code:
            return GetResource("customUI.xml");

6. Insert the following method below the GetCustommUI method:

        private string GetResource(string resourceName)
        {
            Assembly asm = Assembly.GetExecutingAssembly();
            foreach (string name in asm.GetManifestResourceNames())
            {
                if (name.EndsWith(resourceName))
                {
                    System.IO.TextReader tr = new System.IO.StreamReader(asm.GetManifestResourceStream(name));
                    //Debug.Assert(tr != null);
                    string resource = tr.ReadToEnd();

                    tr.Close();
                    return resource;
                }
            }
            return null;
        }

Note: The GetCustomUI method calls the GetResource method. The GetResource method sets a reference to this assembly during runtime and then loops through the embedded resource until it finds the one named "customUI.xml." It then creates an instance of the StreamReader object that reads the embedded file containing the XML markup. The procedure passes the XML back to the GetCustomUI method which returns the XML to the Ribbon. Alternately, you can also construct a string that contains the XML markup and read it directly in the GetCustomUI method.
 
7. Following the GetResource method, add this method. This method inserts the company name into the document at the beginning of the page:

        public void InsertCompanyName(IRibbonControl control)
        {
        // Inserts the specified text at the beginning of a range.
            string MyText;
            MyText = "Microsoft Corporation";

            MSword.Document doc = applicationObject.ActiveDocument;

            //Inserts text at the beginning of the active document.
            object startPosition = 0;
            object endPosition = 0;
            MSword.Range r = (MSword.Range)doc.Range(
                   ref startPosition, ref endPosition);
            r.InsertAfter(MyText);
        }

Compile both the add-in and its setup project. Before beginning, make sure that Word is closed.
1. In the Project menu, click Build Solution. You will see a message when the build is complete in the system tray in the lower left corner of the window.
2. In the Solution Explorer, right-click RibbonXSampleCSSetup and click Build.
3. Right-click RibbonXSampleCSSetup and click Install. This launches the RibbonXSampleCSSetup Setup Wizard screen.
4. Click Next on all of the screens and then Close on the final screen.
5. Start Word. You should see the "My Tab" tab to the right of the other tabs.

To test the project:
Click the "My tab" tab and then click the "Insert Company Name" button. The company name is inserted into the document at the cursor location.

If you don’t see the customized Ribbon UI, you may need to add an entry to the registry. To do this, perform the following steps:

Caution: The next few steps contain information about modifying the registry. Before you modify the registry, be sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, search for the following article in the Microsoft Knowledge Base: 256986 Description of the Microsoft Windows Registry.

1. In the Solution Explorer, right click on the setup project, RibbonXSampleCSSetup, point to View, and then click Registry.
2. From the Registry tab, navigate to the following registry key for the add-in:

HKCU\Software\Microsoft\Office\Word\AddIns\RibbonXSampleCS.Connect

Note: If the RibbonXSampleCS.Connect key does not exist, you can create it. To do so, right-click the Addins folder, point to New, and then click Key. Name the key RibbonXSampleCS.Connect. Add a LoadBehavior DWord and set its value to 3.

And that's all there is to creating a COM Add-in to customize the Ribbon UI. In the next blog in this series, we'll look at customizing the Ribbon UI by using a COM add-in created in Visual Basic.

 

 

In the previous blog, I discussed reasons for the new Office Ribbon UI, ways to customize the Ribbon, parts of the Ribbon, and gave an example of an XML customization file. In this blog, I'll talk some about callback procedures that give the Ribbon's controls and components functionality.
A callback is a way of telling one procedure, say procA, where to send messages when procA has completed a task. It's just like giving someone a job to do and also giving them a contact number where they can reach you once the job has been completed. Consider the case of the onAction callback procedure for a button. When you click the button, the callback procedure specified for the onAction event is called and executed. When the task has completed, the procedure notifies and passes control back to the Ribbon.
In the example I gave in the previous blog, the Launcher1 button specified an onAction callback procedure named "AdditionalWidgetOptions." When you click the button, the callback procedure, which resides in your document, is called. The procedure performs whatever task it has been given, for example setting text to bold, and then notifies the Ribbon that the task has been completed and returns control back to the Ribbon.
Now lets briefly look at sample code that supports the XML markup. This particular sample is a portion of an add-in class created as managed C# code in Visual Studio but could just as easily be created in Visual Basic, Visual C++, any of the .NET languages, or VBA. The IDTExtensibility2 implementations have been left out in order to make the example less cluttered:

public class Connect : Object, Extensibility.IDTExtensibility2, IRibbonExtensibility
{
   public string GetCustomUI()
   {
      StreamReader customUIReader = new System.IO.StreamReader("C:\\Visual Studio Projects\\RibbonXSampleCS\\customUI.xml");
      string customUIData = customUIReader.ReadToEnd();
      return customUIData;

   #region Ribbon callbacks

   public void ButtonOnAction(IRibbonControl control)
   {
      MessageBox.Show("Button clicked: " + control.Id);
   }

   public void ToggleButtonOnAction(IRibbonControl control, bool pressed)
   {
      if (pressed)
         MessageBox.Show("ToggleButton pressed.");
      else
         MessageBox.Show("ToggleButton un-pressed.");
   }

   public void EditBoxOnChange(IRibbonControl control, string text)
   {
      ///Do something with 'text' here.
   }

   #endregion
}

First, the class must implement the Extensibility.IDTExtensibility2 and IRibbonExtensibility interfaces. Anyone who has created add-ins is already familiar with implementing the IDTExtensibility2 interface. The IRibbonExtensibility interface exposes the GetCustomUI method. The GetCustomUI method is the only method defined in the IRibbonExtensibility interface. In this example, it creates an instance of the System.IO.StreamReader that reads a file containing the XML markup. The method stores the XML customization markup in a variable named customUIData. You could also construct a string containing the XML markup or open an embedded resource (a file contained in the project itself) to supply the XML markup. The sample also includes three callback procedures. The first, ButtonOnAction, receives an object representing the button pressed and displays a message showing the id of the control. The next procedure, ToggleButtonOnAction, receives the toggleButton object and a boolean indicating whether the togglebutton was pressed and then displays a message in a dialog box. The third procedure is a stub procedure that is called when the contents of an editBox control changes. An object representing the clicked control is passed in as well as the text in the editBox. You can see that the process is easily understood and easy to implement based on your needs.

In this topic, I've discussed the basic of implementing callbacks to add functionality to the Ribbon UI. In the next topic, I will dive into customizing the Ribbon in Word by adding a button control with a simple callback procedure.

 

One of the views associated with creating queries in the Query designer in Access is the SQL window. The SQL window displays SQL statements that are created behind the scenes when you create a query. However, the SQL window doesn't display just SQL statements. Once you see how it works with the Access Query Design window, you'll find that this window can help you in other ways. For example, you can use the SQL window to create SQL statements that you can then paste into Visual Basic for Applications (VBA) modules. Likewise, you can take SQL statements from VBA procedures and use the SQL window to troubleshoot them for you. Because the SQL windows is designed to work with SQL statements, it provides much more meaningful error messages than the generic messages that come from the VBE.

The query design window is a great visual way to create SQL queries that you can then copy and paste into your VBA procedures. You can use the visual aspect of the query design window to easily create your SQL statement. For example, you add the tables to the design surface of the query design window, drag the fields from the table(s) to the query grid, specify any filtering and sort criteria, execute the query, perform any troubleshooting needed, open the SQL window, and there you have a working SQL statement.

Just as a refresher, SQL statements are made up of the following components:

  |Keyword - Instructs the query to retrieve data|
         |*************Field List**************|
  SELECT Employees.[FirstName], Employees.[LastName]
  FROM Employees |**Source of the data**|
  WHERE Employees.Title = "Sales Representative" |**Filter Criteria**|
  ORDER BY Employees.LastName; (**semicolon is required**|
  |ORDERBY clause specifies the sort order|

#Access uses parentheses to enclose the various parts of the WHERE clause but these are optional.
#Whenever a field is specified you have the option to append the table name, separating the two with a dot.If your query refers to more than one table you must include the table name along with the field name.
#When supplying values in a SQL statement, such as in the query criteria, the data type must be correctly defined by a "qualifier". This is done by enclosing the value between a pair of appropriate characters. For example, single (') or double quotes (") for Strings, pound sign (#) for dates, and no qualifier for numbers.
#Square brackets are only required around field names when they contain spaces but it's good practice to include them.
#Dates in SQL must be written in the US date format (month/day/year).
#It is a good practice to write SQL statement in uppercase so you can distinguish SQL Statement from VBA statements. It's also good practice to write each SQL clause on a separate line.

Once you have a working SQL statement, you can then execute it in VBA. VBA and SQL are totally two different types of languages. VBA is a programming language that you can use to get Access (and other Microsoft Office programs) to do what you want. SQL is a language used exclusively to manipulate the data and structure of a database.
VBA has available a large number of objects, properties, methods, functions and constants to construct the sometimes complex statements used to control the program. SQL uses a limited range of "keywords" combined with information you supply, such as table names, field names, criteria, and sort order.

There are different ways to execute SQL in VBA code. The easiest way is by using the DoCmd object's RunSQL method. Something like this:
 Dim strSQL As String
 strSQL = "... THE SQL STATEMENT GOES HERE ...”
 DoCmd.RunSQL strSQL

The RunSQL method takes two arguments, the SQL Statement itself which must be supplied as a string (i.e. enclosed in quotes) and Use Transaction which is optional and assumes True if you omit it. When transaction processing is applied to a query, Access first performs a "dry run" of the query during which it writes all the changes to a temporary log file but does not make any permanent changes to the database. If the query finishes its task without any problems, the changes noted in the log file are applied and the job (the transaction) is completed. If, however, Access encounters problems while executing the query, the transaction terminates and the log file is discarded without any changes being made. Transaction processing is a very useful safeguard for your data and should always be applied, unless you have a particular reason not to do so.

The SQL window in the Access Query Designer is also the easiest way to debug SQL statements. Most SQL errors are caused by missing keywords. SQL statements in Access are interpreted by the Jet database engine that runs in the background while Access is running. A missing keyword might generate a generic error:
"Syntax error (missing operator) in query expression SELECT...."
Or a more specific error as you narrow down to the faulty part:
"Syntax error in the ORDER BY clause."
If you misspell the name of a field or refer to one that doesn't exist:
"The Microsoft Jet database engine does not recognize LastNme as a valid field name."
Or you might get an 'Enter Parameter Value' dialog box asking you to entry a value for the misspelled name. This type of error almost always refers to a typo in a field name. General steps to troubleshoot this and most types of errors include:
* Open the query in design view and make sure that all references to fields are spelled correctly,
* Check any fields that you recently renamed in the table. This can cause a problem in criteria expressions.
* If you still can't find the problem, open the data source (usually one or more tables or other queries), and check all of the field names, and then recheck your query.

Errors generated by faulty SQL statements in VBA are usually not as helpful or specific as those generated in the Query Designer which is why it's best to create and troubleshoot statements in the Query Designer.

If you want to play around with creating and troubleshooting SQL statements in Access, a good way to do this is to use the Northwind sample database that comes with Access.

One of the subjects that made me nervous when taking computer science classes in college was recursive programming. It was easy to accidentally write a recursive (i.e. never-ending} program but it was sometimes hard for me to determine when I should write one on purpose. Like any other tool, it is important to know which situations call for recursive programming and which don't. It seemed that as the assignments became more complicated, the more I had to rely on recursive programming.
A sure-fire way to accidentally end up with an endless loop is to forget to provide some way to terminate a process. A never-ending program eventually runs out of memory to store intermediate results which ends up with an "Out of stack space" error message. Fortunately, this error message in and of itself is a clear sign that you've created an endless loop because it's relatively hard to run out of stack space. Typically, stack space is used to store the arguments of a function when it is called as well as the state of the calling procedure. To run out of stack space, you'd need to have to call a large number of procedures, each within the other. This is kind of what recursion does, except that you can do the same thing with only one procedure if you don't have a way to exit the loop.
So when is recursion appropriate? Knowing isn't always obvious.If you have a procedure that is carrying out task A and needs to stop and carryout identical task B, then recursion might help. A classic example, is scanning files in folders and sub-folders. The procedure first iterates through the first folder and when it encounters another folder, has to repeat itself. Another example is sorting arrays. You compare two items and then swap one with the other And then you repeat this recursively. Another flag that recursion may help is if you find yourself nesting several loops within themselves.
One VB.NET program that I currently maintain iterates through the nodes in an XML schema (XSD) and creates a help topic for elements and complex types. The program uses recursion to look for parent elements for elements inside complex types. So the algorithm goes something like this: read through the nodes until you encounter complexType node A. Then continue reading through the nodes inside the type until you find element X. Now read each parent node of element X until you find an element with type A. This element is a parent of element X. If the element is not of type A, continue to read until you find another element and compare its type with type A. Without recursion. imagine how many Do..Until loops and If Then Else loops you would need to move though the element nodes comparing the element's types and then moving to the next element node and so on. With recursion, the amount of code to do this is relatively small. A simplified version is shown here:
    Private Function GetNamedParent(ByVal itemNode As XmlNode, itemType) As XmlNode
        Dim tempParent As XmlNode
        Dim tempTypeNode As XmlNode

        Try
            tempParent = itemNode.ParentNode
            tempTypeNode = tempParent.Attributes("type").Value

            ' Move up the chain until you get to a node with
            ' a name attribute, such as an element or type.
            If tempTypeNode = itemNode.Attributes("type").Value Then
                Return tempParent
            Else
                Return GetNamedParent(tempParent)
            End If
        Catch excpt As System.Exception
            '    MessageBox.Show("Error in GetNamedParent function: " & excpt.Message)
        End Try
    End Function 'GetNamedParent

The function receives the element in the complex type as an argument. The element's parent node is found and assigned to a temporary node variable. Then the parent node's type is compared to the type of the input element. If they match, the temporary element node must be a parent of the input element and its value is returned to the calling procedure. Otherwise the function is called again until a parent is found. Hopefully this explanation has given you a better understanding of recursion and when to use it in your own programs.