How to bring Empty Table rows in an A4 page sized Report? (1 Viewer)

[prabha_friend]

Hi All,
I and My friend are building an A4 size report. The problem is: Our customer wants a table spread upto the page length (A4) despite of empty rows. How to do this?

For Example:
Assume there are 25 rows in that table and only 5 records. Still he want that 20 more extra table rows has to be present.

Please reply. Thanks.

With Hope
Prabhakaran

 

[strive4peace]

hi @prabha_friend

a way that immediately comes to mind is using a Union query, where the second query uses a table with numbers to create empty records, and how many records that query get is controlled by referencing a function or TempVar or database property with the number of extra records needed. Beforehand, code figures out how many records will be on the report and then how many extra are needed. There may be a more elegant way to do this, and it's not easy to explain.

 

[prabha_friend]

I am proceeding with Word VBA. Is there any other way to achieve the same in Access?

 

[arnelgp]

look at "Similar threads" below.
if you are lucky you will find what you asked for.

 

[strive4peace]

hi @prabha_friend

I made a sample for you

attached is numbers_makeReportRows_s4p__ACCDB.zip
(The database also has a few other objects to show other uses for a Numberz table)

It has a table called Numberz for making blank rows. It also has a table called Colorz for the sample data.
The query with report data is called qColors
The query for the extra rows is called qColors_ExtraRows based on the Numberz table with criteria Between 1 And GetNumberOfExtraRows(25)
The query that combines them is qUnion
The report is rColor_Extra_Rows and is based on qUnion
(in addition to showing extra rows, it also shows how to color a control different on every row using the Detail_Format event -- code only runs for Preview, Print, or OutputTo)

Function GetNumberOfExtraRows(pnNumberOfRowsPrint As Long)
'220519 strive4peace
  
   Dim db As DAO.Database _
      ,rs As DAO.Recordset
     
   Dim sSQL As String _
      ,sQuery As String

   Dim nNumberOfRowsThere As Long

   sQuery =  "qColors"
  
   sSQL =  "SELECT Count(*) as NumberOfRows FROM [" & sQuery &  "];"
  
   Set db = CurrentDb
   Set rs = db.OpenRecordset(sSQL,dbOpenDynaset)
  
   'get number of records in query
   With rs
      nNumberOfRowsThere = !NumberOfRows
      .Close
   End With
   Set rs = Nothing
   Set db = Nothing
  
   GetNumberOfExtraRows = pnNumberOfRowsPrint - nNumberOfRowsThere
  
End Function

Here is what the rColor_Extra_Rows report looks like:

I chose to "blank out" the cells showing color for the empty rows that were created using the Detail_Format event that colors the samples depending on the color code.