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
|
|||
|
|||
access - how do i print detail lines multiple times?
For a scheduling program, I have a detail record with a date field (TDate),
Amount, and integer field (TNumber). On a report I need to print the detail Tnumber times, incrementing the TDate by one each time: [Detail Section] Tdate Amount Tdate+1 Amount Tdate+2 Amount etc I tried using undefined variables and a For-Next Loop, but it only prints the last value it calculates. Private Sub Detail_Format(...) Dim i as integer for i=1 to me.tnumber me.PrintDate=me.Tdate-1+i me.PrintAmount=me.amount me.print next i I'm open to suggestions, short of making a temporary table. thanks. |
#2
|
|||
|
|||
access - how do i print detail lines multiple times?
I would create a table of numbers [tblNumbers] with a single, numeric field
[Num] and enter values 0,1,2,3,...x. You can then add this table to your report's record source query and set the criteria under the [Num] column to =TNumber. Create another column in the query like: TheDates: TDate + [Num] This should create multiple records with the correct dates. -- Duane Hookom Microsoft Access MVP "gregcdss" wrote: For a scheduling program, I have a detail record with a date field (TDate), Amount, and integer field (TNumber). On a report I need to print the detail Tnumber times, incrementing the TDate by one each time: [Detail Section] Tdate Amount Tdate+1 Amount Tdate+2 Amount etc I tried using undefined variables and a For-Next Loop, but it only prints the last value it calculates. Private Sub Detail_Format(...) Dim i as integer for i=1 to me.tnumber me.PrintDate=me.Tdate-1+i me.PrintAmount=me.amount me.print next i I'm open to suggestions, short of making a temporary table. thanks. |
#3
|
|||
|
|||
access - how do i print detail lines multiple times?
Thanks Duane, but if you read my original question, you'd see that the last
thing I specified was to NOT have to create a temporary table, which is exactly what you are telling me to do. What I'm looking for is a VB command such as me.print that will print the detail line, let me change it, and then print it again with the changes as many times as needed. I'm already doing it using a form and a button to call a VB script that deletes the data from the temp table and then adds the new records and finally calls the report that uses the data from the temp table. What I was looking for was a way to do it in one step within the report itself. "Duane Hookom" wrote: I would create a table of numbers [tblNumbers] with a single, numeric field [Num] and enter values 0,1,2,3,...x. You can then add this table to your report's record source query and set the criteria under the [Num] column to =TNumber. Create another column in the query like: TheDates: TDate + [Num] This should create multiple records with the correct dates. -- Duane Hookom Microsoft Access MVP "gregcdss" wrote: For a scheduling program, I have a detail record with a date field (TDate), Amount, and integer field (TNumber). On a report I need to print the detail Tnumber times, incrementing the TDate by one each time: [Detail Section] Tdate Amount Tdate+1 Amount Tdate+2 Amount etc I tried using undefined variables and a For-Next Loop, but it only prints the last value it calculates. Private Sub Detail_Format(...) Dim i as integer for i=1 to me.tnumber me.PrintDate=me.Tdate-1+i me.PrintAmount=me.amount me.print next i I'm open to suggestions, short of making a temporary table. thanks. |
#4
|
|||
|
|||
access - how do i print detail lines multiple times?
What makes you think I am asking you to create a temporary table? The
tblNumbers is a permanent table with just a single field and some records. You don't need to write any code for this to work. If you still don't understand, take a look at Allen Browne's page http://www.allenbrowne.com/ser-39.html. He provides more complete documentation on this solution. -- Duane Hookom Microsoft Access MVP "gregcdss" wrote: Thanks Duane, but if you read my original question, you'd see that the last thing I specified was to NOT have to create a temporary table, which is exactly what you are telling me to do. What I'm looking for is a VB command such as me.print that will print the detail line, let me change it, and then print it again with the changes as many times as needed. I'm already doing it using a form and a button to call a VB script that deletes the data from the temp table and then adds the new records and finally calls the report that uses the data from the temp table. What I was looking for was a way to do it in one step within the report itself. "Duane Hookom" wrote: I would create a table of numbers [tblNumbers] with a single, numeric field [Num] and enter values 0,1,2,3,...x. You can then add this table to your report's record source query and set the criteria under the [Num] column to =TNumber. Create another column in the query like: TheDates: TDate + [Num] This should create multiple records with the correct dates. -- Duane Hookom Microsoft Access MVP "gregcdss" wrote: For a scheduling program, I have a detail record with a date field (TDate), Amount, and integer field (TNumber). On a report I need to print the detail Tnumber times, incrementing the TDate by one each time: [Detail Section] Tdate Amount Tdate+1 Amount Tdate+2 Amount etc I tried using undefined variables and a For-Next Loop, but it only prints the last value it calculates. Private Sub Detail_Format(...) Dim i as integer for i=1 to me.tnumber me.PrintDate=me.Tdate-1+i me.PrintAmount=me.amount me.print next i I'm open to suggestions, short of making a temporary table. thanks. |
#5
|
|||
|
|||
access - how do i print detail lines multiple times?
Sorry, I tried to boil the program down to the simplest terms in order to get
my point across about what I am trying to accomplish. I guess that I boiled it too long. Each time I go to do a report, the Date and Number-of-Times will vary, so that is why I am referring to your table (tblNumbers) as a temporary table. It will have to be erased and recreated each time. Thanks for the pointer to AB, I'll check there. "Duane Hookom" wrote: What makes you think I am asking you to create a temporary table? The tblNumbers is a permanent table with just a single field and some records. You don't need to write any code for this to work. If you still don't understand, take a look at Allen Browne's page http://www.allenbrowne.com/ser-39.html. He provides more complete documentation on this solution. -- Duane Hookom Microsoft Access MVP "gregcdss" wrote: Thanks Duane, but if you read my original question, you'd see that the last thing I specified was to NOT have to create a temporary table, which is exactly what you are telling me to do. What I'm looking for is a VB command such as me.print that will print the detail line, let me change it, and then print it again with the changes as many times as needed. I'm already doing it using a form and a button to call a VB script that deletes the data from the temp table and then adds the new records and finally calls the report that uses the data from the temp table. What I was looking for was a way to do it in one step within the report itself. "Duane Hookom" wrote: I would create a table of numbers [tblNumbers] with a single, numeric field [Num] and enter values 0,1,2,3,...x. You can then add this table to your report's record source query and set the criteria under the [Num] column to =TNumber. Create another column in the query like: TheDates: TDate + [Num] This should create multiple records with the correct dates. -- Duane Hookom Microsoft Access MVP "gregcdss" wrote: For a scheduling program, I have a detail record with a date field (TDate), Amount, and integer field (TNumber). On a report I need to print the detail Tnumber times, incrementing the TDate by one each time: [Detail Section] Tdate Amount Tdate+1 Amount Tdate+2 Amount etc I tried using undefined variables and a For-Next Loop, but it only prints the last value it calculates. Private Sub Detail_Format(...) Dim i as integer for i=1 to me.tnumber me.PrintDate=me.Tdate-1+i me.PrintAmount=me.amount me.print next i I'm open to suggestions, short of making a temporary table. thanks. |
#6
|
|||
|
|||
access - how do i print detail lines multiple times?
You stated in your first post "I have a detail record with a date field
(TDate), Amount, and integer field (TNumber)" so you should need to create the table only once and use it many times in your report's record source. Reply back after checking out Allen's site. -- Duane Hookom Microsoft Access MVP "gregcdss" wrote: Sorry, I tried to boil the program down to the simplest terms in order to get my point across about what I am trying to accomplish. I guess that I boiled it too long. Each time I go to do a report, the Date and Number-of-Times will vary, so that is why I am referring to your table (tblNumbers) as a temporary table. It will have to be erased and recreated each time. Thanks for the pointer to AB, I'll check there. "Duane Hookom" wrote: What makes you think I am asking you to create a temporary table? The tblNumbers is a permanent table with just a single field and some records. You don't need to write any code for this to work. If you still don't understand, take a look at Allen Browne's page http://www.allenbrowne.com/ser-39.html. He provides more complete documentation on this solution. -- Duane Hookom Microsoft Access MVP "gregcdss" wrote: Thanks Duane, but if you read my original question, you'd see that the last thing I specified was to NOT have to create a temporary table, which is exactly what you are telling me to do. What I'm looking for is a VB command such as me.print that will print the detail line, let me change it, and then print it again with the changes as many times as needed. I'm already doing it using a form and a button to call a VB script that deletes the data from the temp table and then adds the new records and finally calls the report that uses the data from the temp table. What I was looking for was a way to do it in one step within the report itself. "Duane Hookom" wrote: I would create a table of numbers [tblNumbers] with a single, numeric field [Num] and enter values 0,1,2,3,...x. You can then add this table to your report's record source query and set the criteria under the [Num] column to =TNumber. Create another column in the query like: TheDates: TDate + [Num] This should create multiple records with the correct dates. -- Duane Hookom Microsoft Access MVP "gregcdss" wrote: For a scheduling program, I have a detail record with a date field (TDate), Amount, and integer field (TNumber). On a report I need to print the detail Tnumber times, incrementing the TDate by one each time: [Detail Section] Tdate Amount Tdate+1 Amount Tdate+2 Amount etc I tried using undefined variables and a For-Next Loop, but it only prints the last value it calculates. Private Sub Detail_Format(...) Dim i as integer for i=1 to me.tnumber me.PrintDate=me.Tdate-1+i me.PrintAmount=me.amount me.print next i I'm open to suggestions, short of making a temporary table. thanks. |
#7
|
|||
|
|||
access - how do i print detail lines multiple times?
what my original intentions we
"in a report, for each record selected, print one or more detail lines that contain a starting date and a number of days to follow." i.e. record 1: date=7/6/7, times=3 would print three detail lines from one record: date 7/6/7 date 7/7/7 date 7/8/7 record 2: date 1/1/96, 8 times would print eight detail lines from one record: date 1/1/96 date 1/2/96 ...(1/3/96 through 1/7/96) date 1/8/96 record 3 may be in 2005, and record 4 may be in some future year. does this make my goal clearer? Even if we are talking about just one of the above records, the table you suggested would have to either be erased each time and then recreated (temporary) or contain every possible date throughout eternity. Obviously, there isn't any elegant VB solution to the question I thought I was posting, i.e. is there a way to tell the report to stop and print the detail line, then let me modify it and print it again, ad infinitum. So back to the 'brute force' method. But thanks for the help. "Duane Hookom" wrote: You stated in your first post "I have a detail record with a date field (TDate), Amount, and integer field (TNumber)" so you should need to create the table only once and use it many times in your report's record source. Reply back after checking out Allen's site. -- Duane Hookom Microsoft Access MVP "gregcdss" wrote: Sorry, I tried to boil the program down to the simplest terms in order to get my point across about what I am trying to accomplish. I guess that I boiled it too long. Each time I go to do a report, the Date and Number-of-Times will vary, so that is why I am referring to your table (tblNumbers) as a temporary table. It will have to be erased and recreated each time. Thanks for the pointer to AB, I'll check there. "Duane Hookom" wrote: What makes you think I am asking you to create a temporary table? The tblNumbers is a permanent table with just a single field and some records. You don't need to write any code for this to work. If you still don't understand, take a look at Allen Browne's page http://www.allenbrowne.com/ser-39.html. He provides more complete documentation on this solution. -- Duane Hookom Microsoft Access MVP "gregcdss" wrote: Thanks Duane, but if you read my original question, you'd see that the last thing I specified was to NOT have to create a temporary table, which is exactly what you are telling me to do. What I'm looking for is a VB command such as me.print that will print the detail line, let me change it, and then print it again with the changes as many times as needed. I'm already doing it using a form and a button to call a VB script that deletes the data from the temp table and then adds the new records and finally calls the report that uses the data from the temp table. What I was looking for was a way to do it in one step within the report itself. "Duane Hookom" wrote: I would create a table of numbers [tblNumbers] with a single, numeric field [Num] and enter values 0,1,2,3,...x. You can then add this table to your report's record source query and set the criteria under the [Num] column to =TNumber. Create another column in the query like: TheDates: TDate + [Num] This should create multiple records with the correct dates. -- Duane Hookom Microsoft Access MVP "gregcdss" wrote: For a scheduling program, I have a detail record with a date field (TDate), Amount, and integer field (TNumber). On a report I need to print the detail Tnumber times, incrementing the TDate by one each time: [Detail Section] Tdate Amount Tdate+1 Amount Tdate+2 Amount etc I tried using undefined variables and a For-Next Loop, but it only prints the last value it calculates. Private Sub Detail_Format(...) Dim i as integer for i=1 to me.tnumber me.PrintDate=me.Tdate-1+i me.PrintAmount=me.amount me.print next i I'm open to suggestions, short of making a temporary table. thanks. |
#8
|
|||
|
|||
access - how do i print detail lines multiple times?
Aren't the starting date and the number of times to repeat stored in your
report's record source? Isn't there some real limit to "the number of times to repeat"? If you have a starting date and number of repetitions then your statement "contain every possible date throughout eternity" makes no sense. You are simply storing numbers in tblNumbers and you only need the number of records as the maximum value in your Number of Times to repeat field. Why do you not want to try this simple solution that should work unless I am making a wrong assumption about your report's record source? I love coding in reports but don't use it when SQL is much more efficient and easy. -- Duane Hookom Microsoft Access MVP "gregcdss" wrote: what my original intentions we "in a report, for each record selected, print one or more detail lines that contain a starting date and a number of days to follow." i.e. record 1: date=7/6/7, times=3 would print three detail lines from one record: date 7/6/7 date 7/7/7 date 7/8/7 record 2: date 1/1/96, 8 times would print eight detail lines from one record: date 1/1/96 date 1/2/96 ...(1/3/96 through 1/7/96) date 1/8/96 record 3 may be in 2005, and record 4 may be in some future year. does this make my goal clearer? Even if we are talking about just one of the above records, the table you suggested would have to either be erased each time and then recreated (temporary) or contain every possible date throughout eternity. Obviously, there isn't any elegant VB solution to the question I thought I was posting, i.e. is there a way to tell the report to stop and print the detail line, then let me modify it and print it again, ad infinitum. So back to the 'brute force' method. But thanks for the help. "Duane Hookom" wrote: You stated in your first post "I have a detail record with a date field (TDate), Amount, and integer field (TNumber)" so you should need to create the table only once and use it many times in your report's record source. Reply back after checking out Allen's site. -- Duane Hookom Microsoft Access MVP "gregcdss" wrote: Sorry, I tried to boil the program down to the simplest terms in order to get my point across about what I am trying to accomplish. I guess that I boiled it too long. Each time I go to do a report, the Date and Number-of-Times will vary, so that is why I am referring to your table (tblNumbers) as a temporary table. It will have to be erased and recreated each time. Thanks for the pointer to AB, I'll check there. "Duane Hookom" wrote: What makes you think I am asking you to create a temporary table? The tblNumbers is a permanent table with just a single field and some records. You don't need to write any code for this to work. If you still don't understand, take a look at Allen Browne's page http://www.allenbrowne.com/ser-39.html. He provides more complete documentation on this solution. -- Duane Hookom Microsoft Access MVP "gregcdss" wrote: Thanks Duane, but if you read my original question, you'd see that the last thing I specified was to NOT have to create a temporary table, which is exactly what you are telling me to do. What I'm looking for is a VB command such as me.print that will print the detail line, let me change it, and then print it again with the changes as many times as needed. I'm already doing it using a form and a button to call a VB script that deletes the data from the temp table and then adds the new records and finally calls the report that uses the data from the temp table. What I was looking for was a way to do it in one step within the report itself. "Duane Hookom" wrote: I would create a table of numbers [tblNumbers] with a single, numeric field [Num] and enter values 0,1,2,3,...x. You can then add this table to your report's record source query and set the criteria under the [Num] column to =TNumber. Create another column in the query like: TheDates: TDate + [Num] This should create multiple records with the correct dates. -- Duane Hookom Microsoft Access MVP "gregcdss" wrote: For a scheduling program, I have a detail record with a date field (TDate), Amount, and integer field (TNumber). On a report I need to print the detail Tnumber times, incrementing the TDate by one each time: [Detail Section] Tdate Amount Tdate+1 Amount Tdate+2 Amount etc I tried using undefined variables and a For-Next Loop, but it only prints the last value it calculates. Private Sub Detail_Format(...) Dim i as integer for i=1 to me.tnumber me.PrintDate=me.Tdate-1+i me.PrintAmount=me.amount me.print next i I'm open to suggestions, short of making a temporary table. thanks. |
#9
|
|||
|
|||
access - how do i print detail lines multiple times?
Thanks for the redirection to Allen. I had to search his KB and fortunately
found an article about skipping a blank line every 5th record, which got me headed in the right direction using NextRecord. Since my input table had multiple records that contained different dates and numbers of times to print, then your tblNumbers would have had to cover every possible date, therefore the comment about 'eternity'. Here's what works, for those interested in oddball program examples: Option Compare Database Dim first as boolean Dim fint as integer Private Sub Report_Open(Cancel As Integer) fdone = False first = True fint = 0 Me.NextRecord = False End Sub Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) If first Then first = False Me.NextRecord = False Me.MedDate = Me.Tdate Me.Reason = "Here" Me.Amount = Me.THere Else Me.MedDate = Me.Tdate + fint + 1 Me.Reason = "Take" Me.Amount = Me.TTake fint = fint + 1 If Not (Me.TTimes fint) Then first = True fint = 0 Me.NextRecord = True Else Me.NextRecord = False End If End If End Sub The fields from the record are invisible and can shrink, and the printable undefined fields are reused for each time through the loop. When a record is read, the report prints the Date, the String "Here", and the amount. Then, if there are Take Home Dosages, they are printed x Times with the Date automatically incremented, and the Take Home Amount. Setting NextRecord to true or false controls whether the record pointer advances or not, looping until it has printed ALL of the dates and Dosages. Now do you understand where I was going? 'preciate the help and the jog towards the right direction. gregcdss "Duane Hookom" wrote: Aren't the starting date and the number of times to repeat stored in your report's record source? Isn't there some real limit to "the number of times to repeat"? If you have a starting date and number of repetitions then your statement "contain every possible date throughout eternity" makes no sense. You are simply storing numbers in tblNumbers and you only need the number of records as the maximum value in your Number of Times to repeat field. Why do you not want to try this simple solution that should work unless I am making a wrong assumption about your report's record source? I love coding in reports but don't use it when SQL is much more efficient and easy. -- Duane Hookom Microsoft Access MVP |
Thread Tools | |
Display Modes | |
|
|