Creat a report that will add blank "records" in for printing

[MunroeM]

I tried but i get a pop up saying " Cannot open a databse created with a previous version of your application"



At home im on Windows 10 running access 2016. At work i run windows 7 access 2010

[June7]

I opened it and allowed Access to convert it. Works just fine.

Here is another link that uses the same technique https://support.microsoft.com/en-us/kb/210350

That code is designed to run from a general module but I modified to run it behind report. I put textbox named TotRec in your report footer, it can be set not visible.

Option Compare Database 'Use database order for string comparisons.
Option Explicit
Public TotCount As Integer

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
With Me
   TotCount = TotCount + 1
   If TotCount = .TotRec Then
        .NextRecord = False
   ElseIf TotCount > .TotRec And TotCount < 25 Then
        .NextRecord = False
        .TemmisTagNum.ForeColor = vbWhite
        .TemmisNum.ForeColor = vbWhite
   End If
End With
End Sub

You will have to add code to set the ForeColor for every textbox or to not be visible. Notice that the white font will show through the alternate record grey color. Handling this with code gets tricky. Or don't use alternate color.

Now if you expect the records to exceed 1 page, probably need some code modification but I have no idea what.

Will the table always have 175 records? Alternatively, you could sort records so the ones without a TemmisNum follow the ones with then use an expression in textbox to force the TemmisTagNum to not display if TemmisNum Is Null.

=IIf([TemmisNum] Is Null, Null, [TemmisTagNum])

This way I suppose will always have some blank rows but without any code to fill up last page.

I add records (total 44) and tested with a sort on expression: =Nz([TemmisNum],"z") and secondary sort on TemmisTagNum. For some reason record 057 sorts to the top. I had to delete and re-enter the record. Get a 7 page report with about 5 pages of blank rows (the last page has lot of white space because it is not 'filled').

[MunroeM]

I opened it and allowed Access to convert it. Works just fine.

Here is another link that uses the same technique https://support.microsoft.com/en-us/kb/210350

That code is designed to run from a general module but I modified to run it behind report. I put textbox named TotRec in your report footer, it can be set not visible.

Option Compare Database 'Use database order for string comparisons.
Option Explicit
Public TotCount As Integer

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
With Me
   TotCount = TotCount + 1
   If TotCount = .TotRec Then
        .NextRecord = False
   ElseIf TotCount > .TotRec And TotCount < 25 Then
        .NextRecord = False
        .TemmisTagNum.ForeColor = vbWhite
        .TemmisNum.ForeColor = vbWhite
   End If
End With
End Sub

You will have to add code to set the ForeColor for every textbox or to not be visible. Notice that the white font will show through the alternate record grey color. Handling this with code gets tricky. Or don't use alternate color.

Now if you expect the records to exceed 1 page, probably need some code modification but I have no idea what.

Will the table always have 175 records? Alternatively, you could sort records so the ones without a TemmisNum follow the ones with then use an expression in textbox to force the TemmisTagNum to not display if TemmisNum Is Null.

=IIf([TemmisNum] Is Null, Null, [TemmisTagNum])

This way I suppose will always have some blank rows but without any code to fill up last page.

I tested this with a sort on expression: =Nz([TemmisNum],"z") and secondary sort on TemmisTagNum. For some reason record 057 sorts to the top. I had to delete and re-enter the record. Get a 7 page report with 5.5 pages of blank rows.

I actually want all the lines to have the same background colour so that wont be an issue.

The Table always has 175 records as there are 175 tags numbered 001 to 175. How many pages for the report depends on the amount of tags currently signed out at the time of printing. if 78 tags are currently signed out then there should be 4 pages to print. 3 complete pages and 1 with 3 lines complete.

Can that IIf statement use the "SignedOut" field in the table to set the TemmisTagNum to null? But if i use that does it actually change the record value to null?

[June7]

Yes, the IIf expression can use the SignedOut field as criteria for display of TemmisTagNum and it can be in query. No, it does not actually change value in table.

Now if you don't want all 175 records to print, only the signed out tags and enough extra records to fill up the last page, that might be possible but won't be easy.

[MunroeM]

Now if you don't want all 175 records to print, only the signed out tags and enough extra records to fill up the last page, that might be possible but won't be easy.

That is exactly what i want.

[June7]

Review options - all involve VBA:

1. temp table that is populated with just enough records to fill out report - method described by micron in post 2

2. the code in post 17 somehow modified to handle multiple pages

3. some way of restricting a query of the 175 records to just enough to fill out report - method and code not yet determined

Right now I see option 1 as the easiest.

[MunroeM]

I can get a temp table set up with the completed records using a append querry.

I just dont know how i would make it so it would add all the "blank records" i would need to fill the final page

[June7]

Something like:

CurrentDb.Execute "DELETE FROM temptable"
CurrentDb.Execute "INSERT INTO temptable SELECT * FROM TemmisTagT WHERE SignedOut = True"
intFillCount = 25 - DCount("*", "temptable") Mod 25
For x = 1 To intFillCount
    CurrentDb.Execute "INSERT INTO Temptable(TemmisTagNum) VALUES(Null)"
Next

or this variation

CurrentDb.Execute "DELETE FROM temptable"
intFillCount = 25 - DCount("*", "TemmisTagT", "SignedOut=True") Mod 25
For x = 1 To intFillCount
    CurrentDb.Execute "INSERT INTO Temptable(TemmisTagNum) VALUES(Null)"
Next

In this case the report RecordSource would be a UNION query:
SELECT TemmisTagNum, TemmisTag, ... FROM TemmisTagT WHERE SignedOut=True
UN ION SELECT TemmisTagNum, Null, ... FROM temptable;

Is this a multi-user split db? Temp table must be in the frontend.

[Micron]

Am I wrong or should this CurrentDb.Execute "DELETE FROM temptable" be CurrentDb.Execute "DELETE * FROM temptable"

[June7]

Since the entire record is deleted there is no need to reference field(s), neither explicitly nor with wildcard.

[Micron]

OK. It seems to be the default syntax for a query object, as in
DELETE tblRawData.*
FROM tblRawData;
It's also how it's written at the MS Developer site, but I've never tried it without the wildcard. I always thought it was needed.

[June7]

Works either way.