Adding blank lines in a report

  • Thread starter Thread starter Steve Hart via AccessMonster.com
  • Start date Start date
S

Steve Hart via AccessMonster.com

I'm creating a report that is basically a listing of all of the Old Time
Radio programs I have of a certain series. The basic report is working
fine, but I'm getting fancy and that's where I'm getting over my head.

Each episode has a number and they are sorted in ascending order. What I
would like to do is add a blank line anywhere in the report that an episode
is missing from my collection. For example:

#1
#2
#3

#5
#6

#10
#11


Is there a way to do this?

Steve
 
The extra lines have to come from somewhere. The simplest source is another
table that tells Access about the missing numbers.

1. Create a new table, containing just one field named CountID, of type
Number (Long Integer). Mark the field as the primary key (toolbar icon).
Save the table as tblCount.

2. Enter the records into this table manually, or use the function below to
enter 1000 records instantly.

3. Create a query that contains both this table and the table containing
your data.

4. In the upper pane of the query window, drag the number field of your
table onto the CountID field in tblCount, and release the mouse. Access will
create a line joining the two tables.

5. Double-click the line you just created. Access pops up a dialog offering
3 options. Choose the one that says:
All fields from "MyMainTable", and any matches from "tblCount".

6. Drag tblCount.CountID into the output grid. In the Criteria row beneath
this field, specify less than or equal to the highest number in your
collection. For example, if you collection goes up to 80, specify:
<= 80

7. Drag all the fields from your main table into the query grid as well.

8. Save the query, and use it as the RecordSource for your report.

Function MakeData()
'Purpose: Create the records for a counter table.
Dim db As Database 'Current database.
Dim lng As Long 'Loop controller.
Dim rs As DAO.Recordset 'Table to append to.
Const conMaxRecords As Long = 1000 'Number of records you want.

Set db = DBEngine(0)(0)
Set rs = db.OpenRecordset("tblCount", dbOpenDynaset, dbAppendOnly)
With rs
For lng = 1 To conMaxRecords
.AddNew
!CountID = lng
.Update
Next
End With
rs.Close
Set rs = Nothing
Set db = Nothing
MakeData = "Records created."
End Function
 
Back
Top