none
Multi-Level Numbering in MS Access

    Question

  • I am trying to build a database that contains specific text information (inputted using forms) that I can later merge together to create one master document.  The problem I am running into is formatting.  Some of information I a entering in the form contains multi-level numbering and I need to maintain that format when I output to a master document.  However it appears the text box that I am using on the forms does not support multi-level numbering.  Specifically, I need the following type of format:

    1.   dddkdkdk

          a.  sllslkdkdkdk

          b.  dldldldkk

    What I am trying to do is input my information in the format above.  Create a report using that data (while still maintaing the format) and then output it to a Word document (formatting still in tact).   I tried to use Rich Text but when I output it, it just shows a bunch of html characters.

    It there a way to accomplish this programmatically?

    Tuesday, August 24, 2010 10:13 PM

Answers

  • To automate word:

    first tools->References

    add the reference to Microsoft Word (whatever version) - UNLESS you will be distributing this code to run on PCs that may have differing versions of office installed.

    If so, then w'll have to do this via late binding instead.

    Sub OpenWordDoc(strPathName As String)

    Dim oWD As Word.Application
    Dim oDoc As Word.Document

    Set oWD = New Word.Application
    oWD.Visible = False
    oWD.ScreenUpdating = False
    'if opening an existing document file, then...
    Set oDoc = oWD.Documents.Open(strPathName)
    'if opening a new document then
    Set oDoc = oWD.NewDocument
    oDoc.SaveAs strPathName

    oDoc.Range = "whatever text you want to enter into the document here"
     'there are a LOT of methods and options you can use here to manipulate, select, add, & edit text, graphics and other objects into your word document available from here, including a Paragraphs collection, a characters collection, etc.,.
     
     'to show the word app window when ready...
     oWD.Visible = True
     oWD.ScreenUpdating = True
     oWD.ScreenRefresh
     
     'when done
     oDoc.Save
     'to print
     oDoc.PrintOut 'lots of options for this operation too
     'or
     oDoc.PrintPreview
     
     oDoc.Close
     oWD.Quit
     Set oDoc = Nothing
     Set oWD = Nothing
     
    End Sub

     

    Wednesday, August 25, 2010 5:14 AM
  • Hi,

    in addition to Mark's message. As always, the best way to understand how should you make VBA code is to record a macro, then open VBA editor and see how this macro looks like.

    txt = "mytext"
    
    With oDoc
      .Selection.TypeText Text:=txt
      .Selection.MoveLeft Unit:=wdCharacter, Count:=Len(txt), Extend:=wdExtend
      With .ListGalleries(wdNumberGallery).ListTemplates(1).ListLevels(1)
        .NumberFormat = "%1."
        .TrailingCharacter = wdTrailingTab
        .NumberStyle = wdListNumberStyleArabic
        .NumberPosition = CentimetersToPoints(0.63)
        .Alignment = wdListLevelAlignLeft
        .TextPosition = CentimetersToPoints(1.27)
        .TabPosition = CentimetersToPoints(1.27)
        .ResetOnHigher = 0
        .StartAt = 1
      End With
    Selection.TypeParagraph
    
    End With
    
    

    And about numbering. I think it's better to keep a structure in your db rather than just "formatted" records. For example, you can add one field called NumbLevel and add a line

    oDoc.Selection.Range.ListFormat.ListIndent
    

    if NumbLevel = 2.

    There is a wide area for trying. If you have some time you can try and you'll see the great result in the end.

    P.S. I haven't tried this code. There could be some problems with .Selection (according to my Excel automation experience it doesn't work out of native app, i.e. out of Word - from Access), but there is always at least one way how to realize it.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Wednesday, August 25, 2010 6:01 AM

All replies

  • You can make the field that holds the text of type memo and change its text format property to rich text, and then do the same for the text box that is bound to it on the form. On the form you will also need to change the Textbox property Enter Key Behavior from Default to New Line in Field. This property is found in the Other tab of the property sheet.

    Your other option is that since you will eventually be merging these records into a master document, give the record a field that lets the user describe the desired formatting and then during the merging process programatically format that text accordingly.

    Tuesday, August 24, 2010 10:28 PM
  • Raignfilms,

    I'm wondering if you're not confusing the roles of formatting -vs- data here.

    Understand, I know that formatting itself can be data as well, but when designing something it frequently pays well to be sure you're breaking the things down into their most appropriate lowest-common-denominator chunks before relegating them into a database form.

    The second thing to consider is why you're using a database to begin with...would it be better to store some selected pieces of data in Access and then store a pointer to a Word Document file, and use a bit of code to automate Word from your Access forms, perhaps?

    If you really do want the text in Access, the RTF control really is the way to go here as it is the only entirely-in-access sort of presentation-oriented control in the toolbelt. How exactly did you try and output the RTF data field? copy/paste, or just by printing it, or...?

     

    Tuesday, August 24, 2010 10:31 PM
  • Thanks for your assistance.  I do have the Enter Key Behavior sent to New Line in Field.

     

    The second thing to consider is why you're using a database to begin with...would it be better to store some selected pieces of data in Access and then store a pointer to a Word Document file, and use a bit of code to automate Word from your Access forms, perhaps?

    The above sounds like a good idea but I  a not sure how I would automate Word from my access form using code.

    I currently have the memo field in the table designated as Rich text as well as the text box in the form and the text box in the report also designated as such.  So what I am doing is entering data (i.e. paragraphs of information) into the text box of my form and then creating a report where I have everything arranged like a regular document (again the appropriate fields designated as rich text) and then I use the export feature to export my report to a MS Word document.  The result looks similar to this:

    </ol>                  

                            <blockquote>              

                            <blockquote>                       

                            <blockquote>                       

                            <div><font face="Arial Narrow">All letter fee(s)- $125</font></div>                       

                            <div><font face="Arial Narrow">Mailing per recipient fee(s)- $9</font></div>                       

                            <div><font face="Arial Narrow">Register Mailing-$20--This fee will generated for foreign

                            addresses</font></div>

                           

    Also, if I manually input data into the form and use the numbering feature button, it will not do alpha numbering only numerical like below:

    1. abcdefg

        1. hijklmnop

        2. qrstuvwx

    It will not allow me to do:

    1. abcdefg

        a. hijklmnop

        b. qrstuvwx

    Tuesday, August 24, 2010 11:26 PM
  • To automate word:

    first tools->References

    add the reference to Microsoft Word (whatever version) - UNLESS you will be distributing this code to run on PCs that may have differing versions of office installed.

    If so, then w'll have to do this via late binding instead.

    Sub OpenWordDoc(strPathName As String)

    Dim oWD As Word.Application
    Dim oDoc As Word.Document

    Set oWD = New Word.Application
    oWD.Visible = False
    oWD.ScreenUpdating = False
    'if opening an existing document file, then...
    Set oDoc = oWD.Documents.Open(strPathName)
    'if opening a new document then
    Set oDoc = oWD.NewDocument
    oDoc.SaveAs strPathName

    oDoc.Range = "whatever text you want to enter into the document here"
     'there are a LOT of methods and options you can use here to manipulate, select, add, & edit text, graphics and other objects into your word document available from here, including a Paragraphs collection, a characters collection, etc.,.
     
     'to show the word app window when ready...
     oWD.Visible = True
     oWD.ScreenUpdating = True
     oWD.ScreenRefresh
     
     'when done
     oDoc.Save
     'to print
     oDoc.PrintOut 'lots of options for this operation too
     'or
     oDoc.PrintPreview
     
     oDoc.Close
     oWD.Quit
     Set oDoc = Nothing
     Set oWD = Nothing
     
    End Sub

     

    Wednesday, August 25, 2010 5:14 AM
  • Hi,

    in addition to Mark's message. As always, the best way to understand how should you make VBA code is to record a macro, then open VBA editor and see how this macro looks like.

    txt = "mytext"
    
    With oDoc
      .Selection.TypeText Text:=txt
      .Selection.MoveLeft Unit:=wdCharacter, Count:=Len(txt), Extend:=wdExtend
      With .ListGalleries(wdNumberGallery).ListTemplates(1).ListLevels(1)
        .NumberFormat = "%1."
        .TrailingCharacter = wdTrailingTab
        .NumberStyle = wdListNumberStyleArabic
        .NumberPosition = CentimetersToPoints(0.63)
        .Alignment = wdListLevelAlignLeft
        .TextPosition = CentimetersToPoints(1.27)
        .TabPosition = CentimetersToPoints(1.27)
        .ResetOnHigher = 0
        .StartAt = 1
      End With
    Selection.TypeParagraph
    
    End With
    
    

    And about numbering. I think it's better to keep a structure in your db rather than just "formatted" records. For example, you can add one field called NumbLevel and add a line

    oDoc.Selection.Range.ListFormat.ListIndent
    

    if NumbLevel = 2.

    There is a wide area for trying. If you have some time you can try and you'll see the great result in the end.

    P.S. I haven't tried this code. There could be some problems with .Selection (according to my Excel automation experience it doesn't work out of native app, i.e. out of Word - from Access), but there is always at least one way how to realize it.


    Andrey V Artemyev | Saint-Petersburg, Russia
    Wednesday, August 25, 2010 6:01 AM