Results 1 to 10 of 10
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919

    Smile User specified a block formatting -

    An end user (read bureaucrat) has specified that records be printed in a blocked format like you see in the sample below. Because of some other information that appears only once in the detail section of the report, the records from the RecordSource would necessarily be within a sub-form.



    How do I design the sub-form so that the records get formatted in groups of 5 like you see in the example below?

    Also, I want the RecordSource query to automatically generate the numbers you see down the left side of the blocks. I.e., an expression added to the query that increments with each record. (I know how to do that in code, but Jet is better at that kind of stuff than I am)

    Click image for larger version. 

Name:	000.jpg 
Views:	14 
Size:	54.3 KB 
ID:	24598

  2. #2
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    If I design a single line with the lines and rectangles with the bound controls within, can I run a counter in the OnFormat event that can be examine to insert a blank line after every 5th record? If so, then my question becomes a bit simpler as to how to insert a blank line.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it may be possible - think you would need an additional column from another table which provides actual line numbers (i.e. record 6 is actually row 7) and have some sort of algorithm on the join

    something like

    Code:
    SELECT *
    FROM lineTable left join myQuery
        ON myQuery.recnum + (myQuery.recnum \ 5)=lineTable.Linenum OR myQuery.recnum is null
    note you wont be able to see this in the query builder

    alternatively have 3 (or however many required) subform controls, each woth the same form object, each sized for 5 records then in vba form event (maybe current, maybe load, depends on what you are displaying) use code to modify the recordsource or filter for each subform control form

    first would be 'select top 5'
    second would be 'select top 10 when record not in top 5'
    third would be 'select top 15 when record not in top 10'

    etc

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What about code that sets the height of the detail section in the format event of the detail section, checking that the record number is divisible by 5? I would think that would give you a little blank space for that record.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    It's late and I'm just getting back to this. I try your suggestion in the AM.

  6. #6
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    Paul, is this what you had in mind?

    Code:
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Select Case tbRecCntr Mod 5
        Case 0
            Me.Detail.Height = 2 * 0.2083
        Case Else
            Me.Detail.Height = 0.2083
    End Select
        
    End Sub
    Just taking a small sample of the report's RecordSource: (As you can see there's no added space after lines 5, 10, 15 or 20)
    Seems like a good idea, but I can't think of how to insert something that would essentially force a blank line.

    Click image for larger version. 

Name:	000.jpg 
Views:	9 
Size:	28.0 KB 
ID:	24606



    With the above code, I don't get any additional space after lines where the record counter is a multiple of 5. I ran the sub in debug to insure the code worked.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    That's what I had in mind, but I'm pretty sure in code you have to use twips, not inches.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    This worked in a brief test. I didn't have a record number, so just used a value in the report. That line was taller:

    Code:
        Select Case Me.RFQItemNum
            Case 68364
                Me.Detail.Height = 2 * 400
            Case Else
                Me.Detail.Height = 400
        End Select
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 7 64bit Access 2003
    Join Date
    Feb 2011
    Posts
    1,919
    I wrote the code in the middle of the night and when I went back to bed the thought occurred to me before I went back to sleep that measurements in code are always in twips. I guess there are just some things we do that should never be done unless we're entirely awake.................. not like the "all night'ers" I used to do when I was a lot younger

    Click image for larger version. 

Name:	000.jpg 
Views:	6 
Size:	27.4 KB 
ID:	24607

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad it worked for you!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Block Dates (Help)
    By jglayden in forum Queries
    Replies: 12
    Last Post: 02-12-2015, 12:32 PM
  2. New kid on the block
    By ldodge429 in forum Access
    Replies: 5
    Last Post: 07-17-2014, 08:45 PM
  3. Block a user to login
    By rcoreejes in forum Access
    Replies: 3
    Last Post: 07-03-2013, 11:20 AM
  4. End While block problem
    By cbh35711 in forum Programming
    Replies: 4
    Last Post: 05-07-2012, 03:05 PM
  5. Block IF End IF Confusion
    By Phred in forum Programming
    Replies: 11
    Last Post: 03-10-2012, 02:18 PM

Posting Permissions

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