adding blank lines in an Access 2010 Report

**zmbd** · Sep 4 '12, 07:56 PM

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_SampleIDWS pecs
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_SampleIDWS pecs and I have a constraint on that for an area... and the related fields

Code:

SELECT 
   Z_XL_SampleIDWSpecs.XL_OldSampleID, 
   Z_XL_SampleIDWSpecs.XL_SampleDescription, 
   et_revised.ResultsM, 
   et_revised.ResultsYAM, 
   et_revised.ResultsA, 
   et_revised.AO1, 
   et_revised.Comments
FROM Z_XL_SampleIDWSpecs 
   LEFT JOIN et_revised 
ON Z_XL_SampleIDWSpecs.XL_OldSampleID = et_revised.OldSampleNumber
WHERE (((Z_XL_SampleIDWSpecs.XL_Area)="ET"));

SO, if the Z_XL_SampleIDWS pecs 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)

Code:

XL_OldSampleID, XL_SampleDescription, ResultsM, (rest of columns....>) 
1                 Name 1              (null)
2                 Name 2              Pass
3                 Name 3              Pass
4                 Name 4              (null)
(etc... to name 10)

You can then format the report as needed.
-z

**TheSmileyCoder** · Sep 5 '12, 05:13 AM

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.

**zmbd** · Sep 5 '12, 10:40 AM

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

**Sophia Olivas** · Sep 5 '12, 02:33 PM

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:

Code:

Sub Report_Page()
Dim intRows As Integer
Dim intLoop As Integer
Dim intTopMargin As Integer
Dim intDetailHeight As Integer
intRows = 23
intDetailHeight = Me.Section(0).Height
intTopMargin = 2150
Me.FontSize = 14



For intLoop = 0 To intRows
Me.CurrentX = 20
Me.CurrentY = intLoop * intDetailHeight + intTopMargin
'Me.Print intLoop + 1
Me.Line (3, intLoop * intDetailHeight + intTopMargin)-Step(Me.Width, intDetailHeight), , B
Next

End Sub

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.

**Sophia Olivas** · Sep 5 '12, 02:38 PM

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.

**zmbd** · Sep 5 '12, 04:47 PM

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:

Create alternating row colors on a report

http://msdn.microsoft.com/en-us/library/office/ff836091.aspx

Access VBA conceptual documentation

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

**KarlHalterman** · Apr 17 '13, 01:23 AM

Sophia, where did you put that code? in the On Load event?