OL2000: Creating Sequentially-Numbered Items in a Folder (201093)

MORE INFORMATION

Many database-oriented programs, such as Microsoft Access, provide a way to create uniquely numbered items (or records). In database terminology, this is typically referred to as a primary key.

There are several situations where you might want Outlook to sequentially number items. A form that a helpdesk uses to track task requests is one such example. Each task in a folder would have a unique task number for tracking purposes.

Outlook does not provide a way of manually indexing fields in folders like most database management systems (DBMS). Outlook stores items in folders, and because these folders are designed to be flexible in terms of what can be placed in them, they are "less structured" than a typical DBMS. The folders are "self-indexing" based on various criteria, such as how often a folder is accessed, and therefore do not have a primary key that you can manually set.

Implementation Strategies

In all of the following scenarios, you have to create Microsoft Visual Basic Scripting Edition (VBScript) code with event procedures for the form, typically Item_Open(). VBScript code in the Item_Open() event keeps track of the auto-incrementing number, or ID, for the items. You then store the number assigned to the last form item in some designated location. Choosing the storage location is the main difference between the following strategies.

Examples below auto-increment the ID number in the Mileage field, because the Mileage field is available in all types of Outlook forms. In general, the code placed in the Item_Open() event needs to determine if the form is a new form or whether the form is an existing form. If it is a new form, VBScript code needs to retrieve, increment, and store the ID in the Mileage field. Then the code needs to update the number wherever the "last used" ID is stored so that it is ready for the next new form. If the form is an existing form, the VBScript code typically does nothing related to the ID number.

Increment Number and Republishing the Form

This approach uses a published form to keep track of the ID number. Because the Item_Open event executes when a new item is created, or when an existing item is opened, the VBScript code has to test to see whether the index needs to be incremented.

If you use this approach in a public folder, be aware that all the users who use the custom form must have owner rights to the folder (to enable them to re-publish the form). This does pose a security problem, and in this case, you may want to try another method.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site: For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

The following is sample code to provide a starting point for implementing this type of solution. Because VBScript cannot use Outlook constants, the constants are explicitly defined at the beginning of the code.

To implement this solution, follow these steps:

1. Open a new contact form and enter the following code in the VBScript editor. Then publish the form to the Contact folder, being sure to use the name in the third line of VBScript code below (myFormName = "FormName").

   olFolderRegistry = 3
   olFolderContacts = 10
   myFormName = "FormName"
   
   Sub Item_Open()
   
      ' Set the Outlook NameSpace.
      Set olns = Application.GetNameSpace("MAPI")
   
      ' Set the folder to the Contacts folder
      Set myFolder = olns.GetDefaultFolder(olFolderContacts)
   
      ' #1/1/4501# is the internal representation of an
      ' empty data value in Outlook.
      If Item.LastModificationTime = #1/1/4501# Then
   
         ' Item is a brand new item.
         MsgBox "New Item"
         If Item.Mileage = "" Then
   
            ' Create the first item in the folder.
            Item.Mileage = 1
   
         Else
            ' Increment the index in the Mileage field.
            Item.Mileage = CStr(CInt(Item.Mileage) + 1)
   
         End If
   
         ' The following three lines republish the form to
         ' the Contacts folder with the new index number
         ' in the Mileage field.
         Set myForm = Item.FormDescription
         myForm.Name = myFormName
         myForm.PublishForm olFolderRegistry, myFolder
   
      Else
   
         ' Item is an existing item.
         MsgBox "Existing item, doing nothing."
   
      End If
   
   End Sub
   						

2. Right-click the Contact folder. On the shortcut menu, click Properties.
3. In the "When posting to this folder, use" list, click the name of the published form so that the form is the default form for the folder.
4. Set the view to one of the "table" views, and then using the Field Chooser, drag the Mileage field to the column heading so you can easily see the values in that field.

Use Microsoft Access to Generate the Unique ID

You can use this method as part of a much larger data-synchronizing solution between Outlook and Microsoft Access. For example, you store a master copy of contact data in Microsoft Access. In this case, you match the Mileage field to a primary key field in Microsoft Access.

A custom Outlook contact form can have an Item_Open event in VBScript that checks the Mileage field and then uses Data Access Objects (DAO) to retrieve the information for the contact. If the Mileage field is empty, this means the user created a new contact item and DAO code is used to create a new contact record in the Microsoft Access database. This automatically generates a unique ID for the record, and code can retrieve and store this ID in the Outlook Mileage field.

Use an Item in a Folder

Instead of using a published form, an Outlook item can store the last-used ID number. If you place the item in the same folder as the other numbered items, one issue to consider is that someone might accidentally delete the item storing the last-used number. To safeguard this, you can store the item in a different folder.

Text File

Store the last-used number in a separate text file on the hard disk using the FileSystemObject that is available with VBScript versions later than 1.0.