ACC: How to Create a Dot Leader Between Fields

• Microsoft Access versions 2.0, 7.0, 97

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article demonstrates a sample user-defined Visual Basic function that you can use to create lists of fields with dot leaders between the items. For example, given the items "John" and "Doe," the function would create:

   John.....................................Doe

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access version 2.0. For more information about Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

To create a dot leader (or any other leader) between fields, you must use a function to make sure that the field columns line up. The Dots() function demonstrates how to create dot leaders between fields.

NOTE: In order for multiple lines of characters to line up correctly, you must use a fixed-width font (such as Courier) in the text boxes.

The Dots() Function

1. Start Microsoft Access and open any database.

2. Create a new, blank form.

3. Add three text box controls with the following properties to the form:

         Text Box 1:
            Name: Field0
            DefaultValue: "John"
         Text Box 2:
            Name: Field2
            DefaultValue: "Doe"
         Text Box 3:
            Name: Field4
            ControlSource: =[Field0] & Dots([Field2]) & [Field2]
   
   

4. Create a module and type the following line in the Declarations section:

         Option Explicit
   

5. Type the following procedure:

         Function Dots (ByVal Title)
            ' Set LineLen to the gap you want (in characters) between the
            ' Field0 data and the beginning of the Field2 data.
            Const LineLen = 40
   
            ' Set FillChar to the leader character you want to use between the
            ' fields.
            Const FillChar = "."
   
            Dots = String$(LineLen - Len(Title), ".")
         End Function
   
   

6. View the form you created in step 2 in Form view. Note that Field4 contains:

         John.....................................Doe