If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Making a report that can leave records blank based on user input.
I am making a database of a large video archive. In this database I need to
be able to print out the records on small MiniDV labels. This is easy now since I print out 15 at a time on a single sheet. But after all the existing tapes are labeled, new tapes will need to be entered inot the database and eventually labeled. This poses a problem since if they only need to print out 5 labels, I will have 10 labels on the page left blank. Is there anyway I could solve this. I was thinking about some way to prompt the user how many spaces on the particular previously used label sheet has been printed and then the report would put in that amount of blank records before starting the labels that are desired. Any information would be helpful since this is my very first database I've ever created/worked with. |
#2
|
|||
|
|||
Here are two ways which not only prints any amount of
labels but also lets you skip the labels already used. One I got from a previous post by Fred G. Method 1 See article: Skip Used Mailing Labels and Print Duplicates in Access 2000 at: http://support.microsoft.com/default...roduct=acc2000 Printing Multiple Copies of the Same Label When you click Print on the File menu, you can choose to print multiple copies of the same report. But when you try to print a single mailing label 20 times, Access prints one label on each of 20 pages. On a dot matrix printer, using single column labels, you can work around this behavior by defining each label as a separate page. However, you cannot use this method for laser printers or multiple-column labels. To work around this behavior, use the step-by-step procedure described below. back to the top Using Labels That Would Otherwise Be Wasted After printing labels, you usually end up with a partially used last page. There is no built-in mechanism in Access to use the remaining labels on a partially used page. Access always starts on a new page. On a dot matrix printer, you can adjust the top of form manually. But you cannot do that on laser printers. To solve this problem, use the step-by-step procedure described below. back to the top Step-by-Step Procedure to Solve Both Problems The Access report generator provides powerful hooks that allow control over the finished product. By calling a function from the OnFormat property of the report's detail section, you can alter the MoveLayout, NextRecord, and PrintSection properties to leave blank spaces or print multiple copies on the same page. The following code is generic. You can attach it to any Mailing Label report to print multiple copies and to skip used labels if needed. To use the example, you need to have a mailing label report called MyLabels. 1. Create a new module, and place the following lines in the Declarations section: '************************************************* ******** 'Declarations section of the module. '************************************************* ********* Option Compare Database Option Explicit Dim LabelBlanks& Dim LabelCopies& Dim BlankCount& Dim CopyCount& 2. Type the following functions: '================================================= ========= ' The following function will cause an input box to ' display when the report is run that prompts the user ' for the number of used labels to skip and how many ' copies of each label should be printed. '================================================= ========== Function LabelSetup () LabelBlanks& = Val(InputBox$("Enter Number of blank labels to skip")) LabelCopies& = Val(InputBox$("Enter Number of Copies to Print")) If LabelBlanks& 0 Then LabelBlanks& = 0 If LabelCopies& 1 Then LabelCopies& = 1 End Function '================================================= ========== ' The following function sets the variables to a zero '================================================= ========== Function LabelInitialize () BlankCount& = 0 CopyCount& = 0 End Function '================================================= ========== ' The following function is the main part of this code ' that allows the labels to print as the user desires. '================================================= ========== Function LabelLayout (R As Report) If BlankCount& LabelBlanks& Then R.NextRecord = False R.PrintSection = False BlankCount& = BlankCount& + 1 Else If CopyCount& (LabelCopies& - 1) Then R.NextRecord = False CopyCount& = CopyCount& + 1 Else CopyCount& = 0 End If End If End Function 3. Open the report named MyLabels in Design view and add the following line to the OnPrint property of the detail section: =LabelLayout(Reports![MyLabels]) 4. Add the following line to the OnOpen property of the MyLabels report: =LabelSetup() 5. Although typically labels do not have a report header, add a report header and footer to the report by clicking Report Header/Footer on the View menu. Then, add the following line to the OnFormat property of the report header: =LabelInitialize() 6. Set the Height property for both the report header and report footer to 0. When you print the report, the report calls the LabelSetup() function, which first asks you to enter the number of used labels to skip on the first page (BlankCount) and then asks how many of each label you want printed (CopyCount). When the report header is formatted, it calls the LabelInitialize() function, so when you switch from preview to print, the BlankCount and CopyCount fields are set to zero. As each label is formatted, the LabelLayout() function adjusts the NextRecord and MoveLayout properties to skip used labels and to print the desired duplicates. ************************************************** ****************************************** Method 2 This code comes from FredG: Try this code. I know it works both in preview and printing. If this is what you are already using, or if this doesn't work, then your problem lies elsewhere. ==== First make sure your label report is properly printing a full sheet of labels. Then add a Report Header to your label report. Add 2 text boxes to the Header. 1) Name one SkipControl Leave it's control source unbound 2) Name the other SkipCounter Set it control Source to =[Skip How Many?] Now code the Report Header Format event as below: Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer) [SkipControl] = "Skip" Cancel = True End Sub ========== Next code the Detail OnPrint event: Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) If PrintCount = [SkipCounter] And [SkipControl] = "Skip" Then Me.NextRecord = False Me.PrintSection = False Else [SkipControl] = "No" Me.PrintSection = True Me.NextRecord = True End If End Sub Chris "Billy" wrote: I am making a database of a large video archive. In this database I need to be able to print out the records on small MiniDV labels. This is easy now since I print out 15 at a time on a single sheet. But after all the existing tapes are labeled, new tapes will need to be entered inot the database and eventually labeled. This poses a problem since if they only need to print out 5 labels, I will have 10 labels on the page left blank. Is there anyway I could solve this. I was thinking about some way to prompt the user how many spaces on the particular previously used label sheet has been printed and then the report would put in that amount of blank records before starting the labels that are desired. Any information would be helpful since this is my very first database I've ever created/worked with. |
#3
|
|||
|
|||
Wow, thanks!
I didn't expect to get that much info! I guess I should have found the previiously posted article in the first place, but now i have 2 methods to try out and hopefully I'll get it to work. Thanks again. Billy "Eric Blitzer" wrote: Here are two ways which not only prints any amount of labels but also lets you skip the labels already used. One I got from a previous post by Fred G. Method 1 See article: Skip Used Mailing Labels and Print Duplicates in Access 2000 at: http://support.microsoft.com/default...roduct=acc2000 Printing Multiple Copies of the Same Label When you click Print on the File menu, you can choose to print multiple copies of the same report. But when you try to print a single mailing label 20 times, Access prints one label on each of 20 pages. On a dot matrix printer, using single column labels, you can work around this behavior by defining each label as a separate page. However, you cannot use this method for laser printers or multiple-column labels. To work around this behavior, use the step-by-step procedure described below. back to the top Using Labels That Would Otherwise Be Wasted After printing labels, you usually end up with a partially used last page. There is no built-in mechanism in Access to use the remaining labels on a partially used page. Access always starts on a new page. On a dot matrix printer, you can adjust the top of form manually. But you cannot do that on laser printers. To solve this problem, use the step-by-step procedure described below. back to the top Step-by-Step Procedure to Solve Both Problems The Access report generator provides powerful hooks that allow control over the finished product. By calling a function from the OnFormat property of the report's detail section, you can alter the MoveLayout, NextRecord, and PrintSection properties to leave blank spaces or print multiple copies on the same page. The following code is generic. You can attach it to any Mailing Label report to print multiple copies and to skip used labels if needed. To use the example, you need to have a mailing label report called MyLabels. 1. Create a new module, and place the following lines in the Declarations section: '************************************************* ******** 'Declarations section of the module. '************************************************* ********* Option Compare Database Option Explicit Dim LabelBlanks& Dim LabelCopies& Dim BlankCount& Dim CopyCount& 2. Type the following functions: '================================================= ========= ' The following function will cause an input box to ' display when the report is run that prompts the user ' for the number of used labels to skip and how many ' copies of each label should be printed. '================================================= ========== Function LabelSetup () LabelBlanks& = Val(InputBox$("Enter Number of blank labels to skip")) LabelCopies& = Val(InputBox$("Enter Number of Copies to Print")) If LabelBlanks& 0 Then LabelBlanks& = 0 If LabelCopies& 1 Then LabelCopies& = 1 End Function '================================================= ========== ' The following function sets the variables to a zero '================================================= ========== Function LabelInitialize () BlankCount& = 0 CopyCount& = 0 End Function '================================================= ========== ' The following function is the main part of this code ' that allows the labels to print as the user desires. '================================================= ========== Function LabelLayout (R As Report) If BlankCount& LabelBlanks& Then R.NextRecord = False R.PrintSection = False BlankCount& = BlankCount& + 1 Else If CopyCount& (LabelCopies& - 1) Then R.NextRecord = False CopyCount& = CopyCount& + 1 Else CopyCount& = 0 End If End If End Function 3. Open the report named MyLabels in Design view and add the following line to the OnPrint property of the detail section: =LabelLayout(Reports![MyLabels]) 4. Add the following line to the OnOpen property of the MyLabels report: =LabelSetup() 5. Although typically labels do not have a report header, add a report header and footer to the report by clicking Report Header/Footer on the View menu. Then, add the following line to the OnFormat property of the report header: =LabelInitialize() 6. Set the Height property for both the report header and report footer to 0. When you print the report, the report calls the LabelSetup() function, which first asks you to enter the number of used labels to skip on the first page (BlankCount) and then asks how many of each label you want printed (CopyCount). When the report header is formatted, it calls the LabelInitialize() function, so when you switch from preview to print, the BlankCount and CopyCount fields are set to zero. As each label is formatted, the LabelLayout() function adjusts the NextRecord and MoveLayout properties to skip used labels and to print the desired duplicates. ************************************************** ****************************************** Method 2 This code comes from FredG: Try this code. I know it works both in preview and printing. If this is what you are already using, or if this doesn't work, then your problem lies elsewhere. ==== First make sure your label report is properly printing a full sheet of labels. Then add a Report Header to your label report. Add 2 text boxes to the Header. 1) Name one SkipControl Leave it's control source unbound 2) Name the other SkipCounter Set it control Source to =[Skip How Many?] Now code the Report Header Format event as below: Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer) [SkipControl] = "Skip" Cancel = True End Sub ========== Next code the Detail OnPrint event: Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) If PrintCount = [SkipCounter] And [SkipControl] = "Skip" Then Me.NextRecord = False Me.PrintSection = False Else [SkipControl] = "No" Me.PrintSection = True Me.NextRecord = True End If End Sub Chris "Billy" wrote: I am making a database of a large video archive. In this database I need to be able to print out the records on small MiniDV labels. This is easy now since I print out 15 at a time on a single sheet. But after all the existing tapes are labeled, new tapes will need to be entered inot the database and eventually labeled. This poses a problem since if they only need to print out 5 labels, I will have 10 labels on the page left blank. Is there anyway I could solve this. I was thinking about some way to prompt the user how many spaces on the particular previously used label sheet has been printed and then the report would put in that amount of blank records before starting the labels that are desired. Any information would be helpful since this is my very first database I've ever created/worked with. |
#4
|
|||
|
|||
Billy,
I also have a DVD database that I designed in ms Access. It is a little crude but effective. I would be interested in seeing yours and I could send you mine. If you are intersted send me an email at g a n d ha w k @ g m a i l . com Remove the spaces -- Have a great Day Chris "Billy" wrote: Wow, thanks! I didn't expect to get that much info! I guess I should have found the previiously posted article in the first place, but now i have 2 methods to try out and hopefully I'll get it to work. Thanks again. Billy "Eric Blitzer" wrote: Here are two ways which not only prints any amount of labels but also lets you skip the labels already used. One I got from a previous post by Fred G. Method 1 See article: Skip Used Mailing Labels and Print Duplicates in Access 2000 at: http://support.microsoft.com/default...roduct=acc2000 Printing Multiple Copies of the Same Label When you click Print on the File menu, you can choose to print multiple copies of the same report. But when you try to print a single mailing label 20 times, Access prints one label on each of 20 pages. On a dot matrix printer, using single column labels, you can work around this behavior by defining each label as a separate page. However, you cannot use this method for laser printers or multiple-column labels. To work around this behavior, use the step-by-step procedure described below. back to the top Using Labels That Would Otherwise Be Wasted After printing labels, you usually end up with a partially used last page. There is no built-in mechanism in Access to use the remaining labels on a partially used page. Access always starts on a new page. On a dot matrix printer, you can adjust the top of form manually. But you cannot do that on laser printers. To solve this problem, use the step-by-step procedure described below. back to the top Step-by-Step Procedure to Solve Both Problems The Access report generator provides powerful hooks that allow control over the finished product. By calling a function from the OnFormat property of the report's detail section, you can alter the MoveLayout, NextRecord, and PrintSection properties to leave blank spaces or print multiple copies on the same page. The following code is generic. You can attach it to any Mailing Label report to print multiple copies and to skip used labels if needed. To use the example, you need to have a mailing label report called MyLabels. 1. Create a new module, and place the following lines in the Declarations section: '************************************************* ******** 'Declarations section of the module. '************************************************* ********* Option Compare Database Option Explicit Dim LabelBlanks& Dim LabelCopies& Dim BlankCount& Dim CopyCount& 2. Type the following functions: '================================================= ========= ' The following function will cause an input box to ' display when the report is run that prompts the user ' for the number of used labels to skip and how many ' copies of each label should be printed. '================================================= ========== Function LabelSetup () LabelBlanks& = Val(InputBox$("Enter Number of blank labels to skip")) LabelCopies& = Val(InputBox$("Enter Number of Copies to Print")) If LabelBlanks& 0 Then LabelBlanks& = 0 If LabelCopies& 1 Then LabelCopies& = 1 End Function '================================================= ========== ' The following function sets the variables to a zero '================================================= ========== Function LabelInitialize () BlankCount& = 0 CopyCount& = 0 End Function '================================================= ========== ' The following function is the main part of this code ' that allows the labels to print as the user desires. '================================================= ========== Function LabelLayout (R As Report) If BlankCount& LabelBlanks& Then R.NextRecord = False R.PrintSection = False BlankCount& = BlankCount& + 1 Else If CopyCount& (LabelCopies& - 1) Then R.NextRecord = False CopyCount& = CopyCount& + 1 Else CopyCount& = 0 End If End If End Function 3. Open the report named MyLabels in Design view and add the following line to the OnPrint property of the detail section: =LabelLayout(Reports![MyLabels]) 4. Add the following line to the OnOpen property of the MyLabels report: =LabelSetup() 5. Although typically labels do not have a report header, add a report header and footer to the report by clicking Report Header/Footer on the View menu. Then, add the following line to the OnFormat property of the report header: =LabelInitialize() 6. Set the Height property for both the report header and report footer to 0. When you print the report, the report calls the LabelSetup() function, which first asks you to enter the number of used labels to skip on the first page (BlankCount) and then asks how many of each label you want printed (CopyCount). When the report header is formatted, it calls the LabelInitialize() function, so when you switch from preview to print, the BlankCount and CopyCount fields are set to zero. As each label is formatted, the LabelLayout() function adjusts the NextRecord and MoveLayout properties to skip used labels and to print the desired duplicates. ************************************************** ****************************************** Method 2 This code comes from FredG: Try this code. I know it works both in preview and printing. If this is what you are already using, or if this doesn't work, then your problem lies elsewhere. ==== First make sure your label report is properly printing a full sheet of labels. Then add a Report Header to your label report. Add 2 text boxes to the Header. 1) Name one SkipControl Leave it's control source unbound 2) Name the other SkipCounter Set it control Source to =[Skip How Many?] Now code the Report Header Format event as below: Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer) [SkipControl] = "Skip" Cancel = True End Sub ========== Next code the Detail OnPrint event: Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) If PrintCount = [SkipCounter] And [SkipControl] = "Skip" Then Me.NextRecord = False Me.PrintSection = False Else [SkipControl] = "No" Me.PrintSection = True Me.NextRecord = True End If End Sub Chris "Billy" wrote: I am making a database of a large video archive. In this database I need to be able to print out the records on small MiniDV labels. This is easy now since I print out 15 at a time on a single sheet. But after all the existing tapes are labeled, new tapes will need to be entered inot the database and eventually labeled. This poses a problem since if they only need to print out 5 labels, I will have 10 labels on the page left blank. Is there anyway I could solve this. I was thinking about some way to prompt the user how many spaces on the particular previously used label sheet has been printed and then the report would put in that amount of blank records before starting the labels that are desired. Any information would be helpful since this is my very first database I've ever created/worked with. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conditional Adding of Blank Records in a Report | Randy via AccessMonster.com | Setting Up & Running Reports | 5 | April 13th, 2005 04:29 AM |
Prevent Blank Records being written. Need Help. | Robert Nusz @ DPS | Using Forms | 4 | December 29th, 2004 05:15 PM |
Oddly duplicated records in a report, based on information from a | dhender09 | Setting Up & Running Reports | 2 | December 28th, 2004 07:37 PM |
Still Hoping for help with a Query problem | Don Sealer | Using Forms | 15 | November 13th, 2004 06:24 AM |
Save Report With CreateReport Coding Issue | Jeff Conrad | Setting Up & Running Reports | 8 | July 12th, 2004 08:39 AM |