Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2013
    Posts
    6

    Unanswered: Report blank boxes

    So i have a report that needs to have 25 rows broken down into 20 columns (see attached). I have built the report in access and have the data populate based on the number of entries. Now the problem is if i have one entry there needs to be 24 blank rows and columns or if there are 27 entries; page 2 need to have 23 blank entries. i have tried this code i found:

    Private Sub Report_Page()
    Dim intRows As Integer
    Dim intLoop As Integer
    Dim intTopMargin As Integer
    Dim intDetailHeight As Integer
    intRows = 24
    intDetailHeight = Me.Section(0).Height
    intTopMargin = 2500 'top height'
    Me.FontSize = 0
    For intLoop = 0 To intRows
    Me.CurrentX = 20
    Me.CurrentY = intLoop * intDetailHeight + intTopMargin
    Me.Print intLoop + 1
    Me.Line (5, intLoop * intDetailHeight + intTopMargin)- _
    Step(Me.Width, intDetailHeight), , B
    Next

    Thanks in advance.
    Attached Thumbnails Attached Thumbnails 2051.png  

  2. #2
    Join Date
    Jan 2005
    Posts
    146
    Have you considered using a Union query as the record source for your report? In the On Open event for your report you can count the number of rows in the table, do the math and then add the blanks in the Union query.

    Code:
    Dim x As Integer
    Me.RecordSource = "SELECT fldX,fldY FROM Table1"
    For x = 1 To 3 'add 3 blank lines to recordsource
    Me.RecordSource = Me.RecordSource & " UNION ALL SELECT Null,Null FROM Table1"
    Next x
    Me.RecordSource = Me.RecordSource & " ORDER BY fldX desc"
    You could then use a Dcount() to know how many true records you have then use mod statement to determine how many blanks to include. Place it all in the On Open event of the report and you should be able to make your blank records.
    Last edited by billmeye; 06-19-13 at 17:34.

  3. #3
    Join Date
    Mar 2013
    Posts
    6
    So the report source would need to be switched to the union query? If so can I still code it like the normal query that uses drop down boxes, on a form, to filter the query for the desired data to show on the report?

    Thanks for your help.

  4. #4
    Join Date
    Jan 2005
    Posts
    146
    Never mind, I just tried it and it didn't work, sorry.

  5. #5
    Join Date
    Jan 2005
    Posts
    146
    Blaze, I gave it more thought and attached a DB showing how I was able to add the blank rows. I am using the Union query but this time I created a temp table that's only purpose is to have the blank records depending on how many records there are in the actual table. The tmpTable has all the same fields as the actual table except the primary key ID.

    If you have trouble adjusting your DB and want to attach it I can take a look and give you a hand.
    Attached Files Attached Files

  6. #6
    Join Date
    Mar 2013
    Posts
    6
    Quote Originally Posted by billmeye View Post
    Blaze, I gave it more thought and attached a DB showing how I was able to add the blank rows. I am using the Union query but this time I created a temp table that's only purpose is to have the blank records depending on how many records there are in the actual table. The tmpTable has all the same fields as the actual table except the primary key ID.

    If you have trouble adjusting your DB and want to attach it I can take a look and give you a hand.
    Thanks but i can open the file.

  7. #7
    Join Date
    Jan 2005
    Posts
    146
    Sorry Blaze, I am using 2010. Here it is as 2003.
    Attached Files Attached Files

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ...or just draw lines in the vertical and horizontal plane to fake a grid layout
    you can pad additional rows in the group footer, assuming you know how many rows have been printed (start the count at each group header)
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2013
    Posts
    6
    Quote Originally Posted by billmeye View Post
    Sorry Blaze, I am using 2010. Here it is as 2003.
    Thanks but my work computer says the Zip file is invalid. I'll try it at home.


    Quote Originally Posted by healdem View Post
    ...or just draw lines in the vertical and horizontal plane to fake a grid layout
    you can pad additional rows in the group footer, assuming you know how many rows have been printed (start the count at each group header)
    i understand how to do Group Footer but don't get how to "start the count at each group header"

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK so you want a grid but you dont' knwo how many rows of data you will be printing
    so you need to know how many have been rpitned

    define a variable at the top of the reports code module
    in the reports group header set that variable to 0
    in the detail on format ass one to that variable
    ..so now you know how many rows you have pritned


    then in the groups footer of format event draw as many lines as required to get to th number of empty rows as you need
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Mar 2013
    Posts
    6
    Quote Originally Posted by healdem View Post
    OK so you want a grid but you dont' knwo how many rows of data you will be printing
    so you need to know how many have been rpitned

    define a variable at the top of the reports code module
    in the reports group header set that variable to 0
    in the detail on format ass one to that variable
    ..so now you know how many rows you have pritned


    then in the groups footer of format event draw as many lines as required to get to th number of empty rows as you need
    Thanks but think of me as someone that just started using Access when it comes to coding.


    So i go to Report Property---> Event---> On Current---> Code builder

    Private Sub Report_Current()
    Dim Detailcount As Integer
    Detailcount = 0

    After that(even if thats right) i'm like

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •