Results 1 to 5 of 5

Help with Report Design & VBA (Bulleted List)

  1. #1
    ohmydatabase is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2017
    Posts
    38

    Help with Report Design & VBA (Bulleted List)

    Hey All,



    I'm new to reports and I am trying to wrap my head around designing a report. I am currently able to export data in the form I truly want using VBA to control MS Word. However, the users wants the ability to see the report from a form within the Access Database. The word document I export takes the form of a bulleted list. I was having problems replicating the same organization using a report. From what I've seen so far, a report adds rows of information consecutively until it reaches the end of the record source query. I can query the information just fine, but arranging a report in an intelligent manner is escaping me...

    The form I'm after resembles: (NOTE: underscores were added because the post would delete spaces, _ = a space)

    Header 1
    ___- Info 1.1
    ___- Info 1.2
    ______- SubInfo 1.2.1
    ______- SubInfo 1.2.2
    ___- Info 1.3
    ___SubHeader 1.1
    ______- Info 1.1.1
    ______- Info 1.1.2
    ______- Info 1.1.3
    _________-SubInfo 1.1.3.1
    _________-SubInfo 1.1.3.2

    Header 2
    ___SubHeader 2.1
    ______- Info 2.1.1
    Last edited by ohmydatabase; 04-19-2017 at 04:47 PM. Reason: Fix Spacing

  2. #2
    ohmydatabase is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2017
    Posts
    38
    And it didnt space it right.... Awesome
    Hope you guys still get the idea!!

  3. #3
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    4,638
    to maintain spacing surround the text block with the code tags (highlight required text and click the # button)

    first thing to try is use the grouping and sorting features of a report. Note that sorting a query to be used in a report has no effect, you need to use the reports sorting functionality.

    Second thing is you may need a special sort column. If you are sorting on your headings, since these are text, a text sort will be used. Which may be OK, but not clear if you are prefixing with spaces - issue may be that 'subinfo' sorts after 'info', but can perhaps be resolved with grouping

    to demonstrate

    number sort
    1
    2
    3
    10
    20

    text sort
    1
    10
    2
    20
    3

  4. #4
    ohmydatabase is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Feb 2017
    Posts
    38
    @Ajax

    Thank you Francis.

    This gives me a good place to start with this. There are actually two different "Sub-Headers" that can fall under a given Header. But a given "info" may have any combination of Header-SubHeader1-Subheader2 the only required field is the "Header" itself.

    Is it wise to pursue controlling/constructing reports VBA or is using the Acces interface a better approach?

  5. #5
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    4,638
    one way or another you need to be able to sort the data to get it into the right order. This requires a logic to be applied to the sorting data which provides the right order, so the use of VBA is not really relevant.

    if your sorting data was simply

    1
    1.1
    1.1.1
    1.2
    1.2.1
    1.3

    etc then you take out the problem of 'Header', 'SubHeader' etc

    but if your numbers get to

    1
    1.1
    ...
    ...
    9
    9.1
    10
    10.1

    they will sort as

    1
    1.1
    10
    10.1
    ...
    ...
    9
    9.1

    so perhaps your sorting data needs to be

    01
    01.01
    ....
    09
    09.01
    10
    10.01

    which is fine until you get to header 100

    So you'll need to experiment and understand where the maximum parameters are (number of levels, number of headers/subheaders within each level)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 07-13-2016, 07:28 AM
  2. Need Grocery List Databse Design form help
    By Zipster1967 in forum Database Design
    Replies: 0
    Last Post: 05-18-2015, 03:32 PM
  3. Replies: 15
    Last Post: 11-20-2013, 03:30 PM
  4. Replies: 13
    Last Post: 11-03-2013, 09:25 PM
  5. Report Design
    By cbrsix in forum Reports
    Replies: 2
    Last Post: 06-27-2012, 06:10 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums