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.

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.

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.

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.

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.

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.

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.

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.

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