By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,009 Members | 2,866 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,009 IT Pros & Developers. It's quick & easy.

adding blank lines in an Access 2010 Report

P: 16
I am trying very hard to make this work - i found a question posed a few years ago to do the same thing and tried the solution but it doesn't work...

I have a standardized form that I am trying to duplicate and populate w/data from my database.

the form has 29 lines for data entry - with 10 columns of data.

I want all 29 lines to show wether there is a record or not to put in the line.

I have one field that is only on the form - it counts the records that are pulled in from the query and gives me a line #. The rest of the fields are populated by the query.

I have the following report sections:

Page Header
Detail
Page Footer

I have no grouping on the report - the query that is used to populate requires the user to provide a date for which to pull records.

There has to be an easy way to print a line w/out having a record...

I am attaching a skeleton of the report to aid in figuring out what I am asking for, as well as a picture of the standardized form I am trying to duplicate.

Please help.
Attached Files
File Type: docx catalog report layout.docx (418.8 KB, 601 views)
Sep 4 '12 #1
Share this Question
Share on Google+
7 Replies


zmbd
Expert Mod 5K+
P: 5,397
You need what I call an expansion table.
This table holds all of your tests (I'm a chemist so tests...)
This is then crossed with the actual data tables via a query so that the expansion table returns all of its records and the data that has the same id's return their data...

Before I can post the DB to the net, I'd have to really strip it down which will take a day or two. In the meantime, try a search in google or this website using "ms access report expansion table"

{{{Edit}}}
Ok, this should be fairly generic...
There are two tables, one with IDs, Names, department ids:
Z_XL_SampleIDWSpecs
and the other with the results
et_revised (actually this is a query that filters for departement and date etc.... and there are a whole lot of tables and relationships - but that's not the point)

Note the Left joint... it returns all of the Rows from Z_XL_SampleIDWSpecs and I have a constraint on that for an area... and the related fields

Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.    Z_XL_SampleIDWSpecs.XL_OldSampleID, 
  3.    Z_XL_SampleIDWSpecs.XL_SampleDescription, 
  4.    et_revised.ResultsM, 
  5.    et_revised.ResultsYAM, 
  6.    et_revised.ResultsA, 
  7.    et_revised.AO1, 
  8.    et_revised.Comments
  9. FROM Z_XL_SampleIDWSpecs 
  10.    LEFT JOIN et_revised 
  11. ON Z_XL_SampleIDWSpecs.XL_OldSampleID = et_revised.OldSampleNumber
  12. WHERE (((Z_XL_SampleIDWSpecs.XL_Area)="ET"));
SO, if the Z_XL_SampleIDWSpecs had ten record related to area "ET" all ten of those records show even if there are no results so... I get something like:

(showing just the first result column and (null) is a blank result)
Expand|Select|Wrap|Line Numbers
  1. XL_OldSampleID, XL_SampleDescription, ResultsM, (rest of columns....>) 
  2. 1                 Name 1              (null)
  3. 2                 Name 2              Pass
  4. 3                 Name 3              Pass
  5. 4                 Name 4              (null)
  6. (etc... to name 10)
You can then format the report as needed.
-z
Sep 4 '12 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
In your form, have you done anything to limit the lines to only 29? or is 29 simply the amount of rows that fit your screen? If so, please remember that on a users screen (I am assuming you have users, and this is not just for yourself), that they might have more or less lines on their screen while in form view.

My approach would probably be to use a temporary table. First use a query to empty the table, then add new records, and then use a bit of VBA to add numbers 1-29 to the records.
Sep 5 '12 #3

zmbd
Expert Mod 5K+
P: 5,397
Smiley brings up a good point and adds a bit of detail I did not as to the number of lines.
Something else I should have inquired about, are you dealing with a pre-printed form or trying to duplicate such a format?

-z
Sep 5 '12 #4

P: 16
I am trying to duplicate a pre printed form - i think I may have gotten it - though it would be nice to have the alternating grey lines. Here is my code:

Expand|Select|Wrap|Line Numbers
  1. Sub Report_Page()
  2. Dim intRows As Integer
  3. Dim intLoop As Integer
  4. Dim intTopMargin As Integer
  5. Dim intDetailHeight As Integer
  6. intRows = 23
  7. intDetailHeight = Me.Section(0).Height
  8. intTopMargin = 2150
  9. Me.FontSize = 14
  10.  
  11.  
  12.  
  13. For intLoop = 0 To intRows
  14. Me.CurrentX = 20
  15. Me.CurrentY = intLoop * intDetailHeight + intTopMargin
  16. 'Me.Print intLoop + 1
  17. Me.Line (3, intLoop * intDetailHeight + intTopMargin)-Step(Me.Width, intDetailHeight), , B
  18. Next
  19.  
  20. End Sub
  21.  
can some one tell me how to change the line color? I thought the spot where the 3 was was it... but its not - I don't know what that does actually - no matter what I change it to - i see no change in the report.

I have to adjust the font and line #'s still - but this does give me the basic look I was searching for. would be nice to have a ticker border around the worksheet too - but havn't figured that out either - since I technically have a growing form - i cannot just put vertical lines in the detail section, so if you can point out which of the me.line sections controls the width of the boarder... that would be helpful too. right now its a hairline border.
Sep 5 '12 #5

P: 16
I do have code to control the # of lines for the page. and I have code to do the line#'s of the lines that contain data, so I am good there. My user will never actually see this on a screen... only print it out. The user enters items one at a time in a collection form - the report itself - is based on a query that pulls a date range selected by the user and select data.
Sep 5 '12 #6

zmbd
Expert Mod 5K+
P: 5,397
I'll answer this here as this is still dealing with the lines... obliquely; however, you need to start a new thread regarding the additional formating questions
.

Alternating row colours:
For Access 2003:
http://office.microsoft.com/en-us/ac...001219146.aspx is one such method.

For Access 2010:
http://msdn.microsoft.com/en-us/libr.../ff836091.aspx
Also, report design view, if you view the properties of the detail section group header you can directly set the alternating shading.
{{Edit}} This should also be an option in 2007

-z
Sep 5 '12 #7

P: 3
Sophia, where did you put that code? in the On Load event?
Apr 17 '13 #8

Post your reply

Sign in to post your reply or Sign up for a free account.