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

[MunroeM]

Hello I currently have a table with 175 records. all records have a nunmber (001 to 175) in field "Tag #" and the rest of the info is blank accross the record. once the use enters data in for a certian tag number (0-175) that record becomes complete.

Now in my Report i only want the complete records to be included and then i want the rest of the page to have 100% blank rows (no tag number) and i want the formatting of the blank rows to match the complete ones. If i could i would also like the ability to add 2 more pages of full blank rows after the last page with the complete records on them. As you can see in photo 1 this is what my current print preview looks like. In the second photo you can see my report before i filter out the non complete records. the non complete records are not blank as they have the Tag Number in them.



Basicly what i do with this is print off everything and then they have a complete list of all current tags and the blank rows are there so they can be filled in with Pen untill they can be entered in the database.

Thanks

[Micron]

If I understand the goal, I think I'd use a temporary table. First append the records that are complete. However, you do not say how they are identified as such, and I see no "Completed" field. I have to presume that you enforce all fields that comprise a completed record, except Tag, to be filled in by using form code since you can't enforce it by table design (because there is a Tag value in each incomplete record). To move these records to the temp table, you'd have to pick one of the 'required' fields and use it as criteria in the first append query. The second step would be to append the rest of the records that have a Tag value but nothing else. Then you'd open the report using a query based on the temp table. As to whether or not this would fill two pages would depend on report design. Obviously, temp tables have to be set up and utilized in a way that would not affect anyone else in a shared db setup.

[MunroeM]

If I understand the goal, I think I'd use a temporary table. First append the records that are complete. However, you do not say how they are identified as such, and I see no "Completed" field. I have to presume that you enforce all fields that comprise a completed record, except Tag, to be filled in by using form code since you can't enforce it by table design (because there is a Tag value in each incomplete record). To move these records to the temp table, you'd have to pick one of the 'required' fields and use it as criteria in the first append query. The second step would be to append the rest of the records that have a Tag value but nothing else. Then you'd open the report using a query based on the temp table. As to whether or not this would fill two pages would depend on report design. Obviously, temp tables have to be set up and utilized in a way that would not affect anyone else in a shared db setup.

I use a yes/no checkbox field to determine if it's a complete record. It gets set to yes once they enter all the required data and click save on a form I made.

on the print out I only want the completed records. The records with just the tag # I don't want them included at all in the print out. I created all the tag #'s so they can pick what tag they are using from a drop down box.

Would there be be a way to export the completed records to a pre built excel file? Maybe that's another route I could try.

[June7]

Prebuilt Excel file could have a link to table or query in Access.


Options for 'blank' rows on report:

1. table of blank records

2. complex VBA code http://www.tek-tips.com/viewthread.cfm?qid=1695691


Advise not to use spaces and special characters/punctuation (underscore is exception) in naming convention.

[CJ_London]

If I understand you correctly, you have 175 records, some are complete, some are not and you want to see all 175 listed - reporting all the details for completed records (indicated by a Boolean field) and no details except the tag number for the incomplete ones

If this is correct try something like

SELECT T.Tag, R.*
FROM myTable AS T LEFT JOIN (SELECT * FROM myTable WHERE Completed=True) AS R On T.Tag=R.Tag
ORDER BY T.Tag

[MunroeM]

If I understand you correctly, you have 175 records, some are complete, some are not and you want to see all 175 listed - reporting all the details for completed records (indicated by a Boolean field) and no details except the tag number for the incomplete ones

If this is correct try something like

SELECT T.Tag, R.*
FROM myTable AS T LEFT JOIN (SELECT * FROM myTable WHERE Completed=True) AS R On T.Tag=R.Tag
ORDER BY T.Tag

For the incomplete records I Don't want the tag numbers to show up.

Basicly I want it so if there are 54 records complete then they will all be listed first in the report then I want about 2 sheets of blank rows without tag numbers after them. (Each of my sheets is set up to have 25 records printed on each) now if I have 54 records complete when I print the report I will have 3 pages with completed records ( third page will have 4 completed and 21 blank)and 2 extra pages of blank records.

[June7]

Did you see post 5?

[CJ_London]

For the incomplete records I Don't want the tag numbers to show up.

You really need to read and understand what people are suggesting and learn from it - I've provided a query - you don't have to display all the resulting fields - just ignore T.Tag and use R.tag. Also it helps if you answer questions that are asked otherwise we continue to guess what it is you require.

good luck with your project, but I don't believe I can help you any further

[MunroeM]

You really need to read and understand what people are suggesting and learn from it - I've provided a query - you don't have to display all the resulting fields - just ignore T.Tag and use R.tag. Also it helps if you answer questions that are asked otherwise we continue to guess what it is you require.

good luck with your project, but I don't believe I can help you any further

Since I seem to have a hard time explaining what I want to actually happen I'v included a picture that I edited in paint to show what i would like to happen. As you can see It will include all the records that are "complete". It will not show all the non complete rectords (Tag# only records as showen in photo 2 in orginal post) and it will fill the rest of the page with blank "records" ( i call them records but they are not actually records. I just dont know what to call them).

Now you can see if I print this and Timmy comes by and takes tag # 99 he can write it on the sheet below tag # 11. the reason i dont just print off all the tag numbers is because timmy could be 5 weeks with that tag or just 5 minutes. I just use blank lines so the tag can be signed out and in multiple times in a shift if required without having to print each time a tag is returned.

[MunroeM]

Did you see post 5?

I did see post 5 and i have actually come accross that post before when i was doing my googles search of this problem. I just cant seem to figure out the code they are posting there its a bit out of my league in VBA

[June7]

Yes, I found this to be quite a challenge the first time I encountered it.

The 2 options indicated are the only ways I know to 'fill' a page with blank rows.

Provide your attempted code or the db for analysis, otherwise wish you luck.

[MunroeM]

Here is a cut down copy of my database with all the required items for my report. thanks for any help you can provide.

[June7]

Set the FilterOnLoad property to Yes or change the RecordSource to:
SELECT TemmisTagT.* FROM TemmisTagT WHERE (((TemmisTagT.SignedOut)=True));

This code almost works:

Private Sub Report_Page()
Dim intRows As Integer
Dim intLoop As Integer
Dim intTopMargin As Integer
Dim intDetailHeight As Integer
intRows = 25
intDetailHeight = Me.Section(0).Height
intTopMargin = 2150
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

I am not able to figure out how to get the correct record count. When I try:
intRows = 25 - Me.Count
the count is 32, not 3

Wish I could find the reference I used long time ago to get this working with my db. Unfortunately, I discarded the code because my report became too complicated and I decided the blank rows were not important.

A working example can be downloaded from http://www.access-programmers.co.uk/...d.php?t=183782

[MunroeM]

With that code i get 1 long box for each line.(PIC 1)
Is there a way to make it look like PIC 2?

[June7]

Did you try the example db at the link?