Loop in Report Detail Section

D

datamerc

Hi,

I'm struggling trying to get this to work. I am building report
(Access 2003) to generate invoices for storage spaces. There are 2
tables (customers, storage_spaces) that it pulls from; each customer
can have multiple storage spaces that are paid once a year.

In the detail section of the report, I need to have a line generated
for each storage space for each year that it may be overdue up to the
current year (along with the corresponding storage fee). For example,
a customer with 2 storage spaces may have an invoice that looks like
this:
Space #101 for 2005... $300.00
Space #101 for 2006... $300.00
Space #305 for 2004... $500.00
Space #305 for 2005... $500.00
Space #305 for 2006... $500.00

The following code that I have will print out a line (into a label
field), but I have had to include the "vbCrLf" if I want to see all the
lines (rather than just the last record). Because there could be many
years that a storage space is overdue, a label field doesn't work well
because it has a defined height. For some reason though, the fee for a
single space will only print once. Here is the all the code I have for
the report:


Code: 
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim curr_year
Dim years_overdue
Dim x               'counter for do loop
Dim yr_print        'year printed on the invoice line item
Dim annual_fee As Currency
curr_year = Format(Date, "yyyy")
x = 1

'Calculate the number of years overdue
years_overdue = curr_year - Me.txt_last_year_paid

'Loop through all previous unpaid years
Do While x < years_overdue + 1

yr_print = Me.txt_last_year_paid + x
Me.lbl_space.Caption = Me.lbl_space.Caption & "Space #" &
Me.txt_space.Value & " for " & yr_print & vbCrLf
Me.lbl_fee.Caption = Me.lbl_fee.Caption & Me.txt_fee.Value
& vbCrLf
x = x + 1

Loop

End Sub
[END CODE]


Can someone please help me figure out how to get this report to print
out a line for every year that a storage space is overdue up to the
current year (this can be x number of lines) and have this be in a
field with no predefined height.  Thank you in advance.
 
M

Marshall Barton

I'm struggling trying to get this to work. I am building report
(Access 2003) to generate invoices for storage spaces. There are 2
tables (customers, storage_spaces) that it pulls from; each customer
can have multiple storage spaces that are paid once a year.

In the detail section of the report, I need to have a line generated
for each storage space for each year that it may be overdue up to the
current year (along with the corresponding storage fee). For example,
a customer with 2 storage spaces may have an invoice that looks like
this:
Space #101 for 2005... $300.00
Space #101 for 2006... $300.00
Space #305 for 2004... $500.00
Space #305 for 2005... $500.00
Space #305 for 2006... $500.00

The following code that I have will print out a line (into a label
field), but I have had to include the "vbCrLf" if I want to see all the
lines (rather than just the last record). Because there could be many
years that a storage space is overdue, a label field doesn't work well
because it has a defined height. For some reason though, the fee for a
single space will only print once. Here is the all the code I have for
the report:


Code: 
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim curr_year
Dim years_overdue
Dim x               'counter for do loop
Dim yr_print        'year printed on the invoice line item
Dim annual_fee As Currency
curr_year = Format(Date, "yyyy")
x = 1

'Calculate the number of years overdue
years_overdue = curr_year - Me.txt_last_year_paid

'Loop through all previous unpaid years
Do While x < years_overdue + 1

yr_print = Me.txt_last_year_paid + x
Me.lbl_space.Caption = Me.lbl_space.Caption & "Space #" &
Me.txt_space.Value & " for " & yr_print & vbCrLf
Me.lbl_fee.Caption = Me.lbl_fee.Caption & Me.txt_fee.Value
& vbCrLf
x = x + 1

Loop

End Sub
[END CODE]


Can someone please help me figure out how to get this report to print
out a line for every year that a storage space is overdue up to the
current year (this can be x number of lines) and have this be in a
field with no predefined height.[/QUOTE]


I don't understand why this is so complicated.  Can't you
just use a line control (name it linOverDue) at the bottom
of the detail section and make it visible as needed using
code like this in the detail section's Format event:

Me.linOverDue.Visible = (Me.[yeartextbox] = Year(Date))
Click to expand...
 
D

datamerc

I'm not sure how that would work. The last_year_paid is the only field
in the storage_spaces table that the report query looks for. If a
space is not paid up to the current year, the report must calculate the
number of years that have not been paid and loop through this in order
to print a line for each year.

The reason I think the text or label field needs to be an unrestricted
height is because the number of years overdue (the number of lines that
needs to be printed) can vary widely.

Thanks again!
 
M

Marshall Barton

I'm not sure how that would work. The last_year_paid is the only field
in the storage_spaces table that the report query looks for. If a
space is not paid up to the current year, the report must calculate the
number of years that have not been paid and loop through this in order
to print a line for each year.

The reason I think the text or label field needs to be an unrestricted
height is because the number of years overdue (the number of lines that
needs to be printed) can vary widely.
Click to expand...


If you do not have a record for each year, how are you
generating the report information? I guess I need more
details about the data records and how you are manipulating
the data to display the information on the report. I can't
see from the code you posted earlier how the multiple detail
lines are generated from a single record.

Of particular concern is how you can deal with a rate change
during an account's overdue period??
 
D

datamerc

Thanks for getting back so quickly. You are correct, there is only one
record per storage space and within that record is a field
(last_year_paid) that tells us the last year that they paid on that
storage space. Right now the query for the report gathers all the
records that are not paid to the current year. The report then
calculates the number of years the space is overdue in this code:

Code: 
curr_year = Format(Date, "yyyy")
'Calculate the number of years overdue
years_overdue = curr_year - Me.txt_last_year_paid
[END CODE]

Then the code can loop through the calculated number of overdue years
and print a line for each year:

[CODE]
'Loop through all previous unpaid years
Do While x < years_overdue + 1
yr_print = Me.txt_last_year_paid + x
Me.lbl_space.Caption = Me.lbl_space.Caption & "Space #" &
Me.txt_space.Value & " for " & yr_print & vbCrLf
Me.lbl_fee.Caption = Me.lbl_fee.Caption & Me.txt_fee.Value
& vbCrLf
x = x + 1
Loop
[END CODE]


Also, rate changes that occur during an overdue period are not a
concern for us.

Thanks,
David
 
M

Marshall Barton

Thanks for getting back so quickly. You are correct, there is only one
record per storage space and within that record is a field
(last_year_paid) that tells us the last year that they paid on that
storage space. Right now the query for the report gathers all the
records that are not paid to the current year. The report then
calculates the number of years the space is overdue in this code:

Code: 
curr_year = Format(Date, "yyyy")
'Calculate the number of years overdue
years_overdue = curr_year - Me.txt_last_year_paid
[END CODE]

Then the code can loop through the calculated number of overdue years
and print a line for each year:

[CODE]
'Loop through all previous unpaid years
Do While x < years_overdue + 1
yr_print = Me.txt_last_year_paid + x
Me.lbl_space.Caption = Me.lbl_space.Caption & "Space #" &
Me.txt_space.Value & " for " & yr_print & vbCrLf
Me.lbl_fee.Caption = Me.lbl_fee.Caption & Me.txt_fee.Value
& vbCrLf
x = x + 1
Loop
[END CODE][/QUOTE]


I don't think that approach can be used to underline some of
the data.

Adifferent approach would be to use a bound text box for the
space# and amount (the same way you would if you had one
record for each year).  Make the bound text box for the year
invisible and add an unbound, visible text box (named
txtBillYear) to display the calculated year. Label controls
should be used for the fixed text.

As I suggested before, add a line control just below the
other controls and make it visible as needed.

To simulate the normal situation with a separate record for
each year, use the report's NextRecord property.  The code
would be along these lines:

If IsNull(Me.txtBillYear) Or Me.txtBillYear=Year(Date) Then
Me.txtBillYear = Me.txt_last_year_paid
Else
Me.txtBillYear = Me.txtBillYear + 1
End If

If Me.txtBillYear = Year(Date) Then
Me.linOverDue.Visible = False
Else
Me.linOverDue.Visible = True
Me.NextRecord = False
End If
Click to expand...
 
D

datamerc

Thanks Marshall! Please forgive me but I am pretty new to Access.

When I used the term "line" in my previous paragraphs, I was referring
to a line item (such as "Space #305 for 2006... $500.00") and not to a
black line on the paper (such as a 4pt line, black in color).

The report (to be used as an invoice) needs print a new line item for
every year that is overdue. The database just contains a single record
for every storage space (and each of these records contains the year it
was last paid) and the corresponding customer who rents that space:

storage_spaces Table
- storage_id
- customer_id
- space_number
- annual_fee
- last_year_paid

customers Table
- customer_id (linked to customer_id in storage_spaces table)
- first_name
- last_name
- address

For example if John Smith has two spaces (101 and 102) and each are
overdue (last_year_paid = 2004 and 2003 repectively).
His records in the storage_spaces table would look like this:
[storage_id...customer_id...space_number...annual_fee...last_year_paid]
101...234...101...500...2004
102...234...102...400...2003

His invoice for these records should look like this:
Space #101 for 2005... $500.00
Space #101 for 2006... $500.00
Space #102 for 2004... $400.00
Space #102 for 2005... $400.00
Space #102 for 2006... $400.00

I think the code to make these lines would be something like:
txtLineItem = "Space #" & me.space_number & " for " & calculated_year &
"... " & me.annual_fee

Thanks for any help!
 
M

Marshall Barton

Thanks Marshall! Please forgive me but I am pretty new to Access.

When I used the term "line" in my previous paragraphs, I was referring
to a line item (such as "Space #305 for 2006... $500.00") and not to a
black line on the paper (such as a 4pt line, black in color).

The report (to be used as an invoice) needs print a new line item for
every year that is overdue. The database just contains a single record
for every storage space (and each of these records contains the year it
was last paid) and the corresponding customer who rents that space:

storage_spaces Table
- storage_id
- customer_id
- space_number
- annual_fee
- last_year_paid

customers Table
- customer_id (linked to customer_id in storage_spaces table)
- first_name
- last_name
- address

For example if John Smith has two spaces (101 and 102) and each are
overdue (last_year_paid = 2004 and 2003 repectively).
His records in the storage_spaces table would look like this:
[storage_id...customer_id...space_number...annual_fee...last_year_paid]
101...234...101...500...2004
102...234...102...400...2003

His invoice for these records should look like this:
Space #101 for 2005... $500.00
Space #101 for 2006... $500.00
Space #102 for 2004... $400.00
Space #102 for 2005... $400.00
Space #102 for 2006... $400.00

I think the code to make these lines would be something like:
txtLineItem = "Space #" & me.space_number & " for " & calculated_year &
"... " & me.annual_fee
Click to expand...


Ahh, I see now. Yes, that will work fairly well. Use a
loop sort of like you had before. Just make sure that
txtLineItem is a CanGrow text box.

I think this air code is close to all you need:

Private Sub Detail_Print(...
Me.txtLineItem = Null
For yr_print = Me.txt_last_year_paid +1 To Year(Date)
Me.txtLineItem = (Me.txtLineItem + vbCrLf) & "Space #" _
& Me.space_number & " for " & yr_print _
&"... " & Me.txt_fee
Next
End Sub
 
D

datamerc

This is wonderful Marshall - I almost got it! Here is all my code:

Code: 
Dim f As Double   'Counter for total fee due

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

'Initialize values
Me.txtLineItem = Null
Me.txt_total_fee.Value = 0

'Loop to print line items
For yr_print = Me.txt_last_year_paid + 1 To Year(Date)
Me.txtLineItem = (Me.txtLineItem + vbCrLf) & "Space " &
Me.txt_space_number.Value & " for " & yr_print & " .... " &
Me.txt_fee.Value
f = f + Me.txt_fee.Value
Next

'Calculate total amount due
Me.txt_total_fee.Value = Me.txt_total_fee.Value + f
f = 0

End Sub
[END CODE]


There are just two things I can't get right:

1.  I set the CanGrow and CanShrink values (tried on/off, off/on,
on/on) for txtLineItem, but for some reason the height of the text box
never stays the same.  Sometimes the height is perfect for the amount
of content, sometimes it is too tall, and sometimes it is too short and
cuts off the content.  And the strange thing about this is that the
height is never the same on my three test records (I do one print
preview after another and it changes each time).  Any ideas???

2.  I need to calculate the total fee due.  The current code will get
me the total fee for one storage space, but if a customer has more than
one space, it won't add the remaining fees.  For example:
Space #101 for 2005... $500.00
Space #101 for 2006... $500.00
Space #102 for 2004... $400.00
Space #102 for 2005... $400.00
Space #102 for 2006... $400.00
The total fee for this should be $2,200.00, but right now the code only
says the total is $1,000.00.  I must be doing something wrong with my
variable "f", which is supposed to add these values.  Any ideas on
this?

Thank you again in advance.
David
 
M

Marshall Barton

Ahh ha, I just noticed that we're doing this in the Print
event, but CanGrow takes place after the Format event and
before the Print event. Just move all the code to the
Detail section's Format event procedure.

As for the total, this gets more complicated because you
have two records (i.e. two details) for two spaces. The
code can not calculate a total across more than a single
detail. The trick here is to put the txt_total_fee text box
in the detail section and set its RunningSum property to
Over Group. Then add a text box to the customer group
footer section and use the expression =txt_total_fee
 
D

datamerc

CanGrow is working perfectly now Marshall - Thank you!

The subtotal is working better now, but I can't get it to add the
subtotals together. Here's my code now:
Code: 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

'Initialize values
Me.txtLineItem = Null
'Me.txt_subtotal.Value = 0

'Loop to print line items
For yr_print = Me.txt_last_year_paid + 1 To Year(Date)
'Print a line item
Me.txtLineItem = (Me.txtLineItem + vbCrLf) & "Space " &
Me.txt_space_number.Value & " for " & yr_print & " .... " &
Me.txt_fee.Value
'Keep running total for the subtotal
Me.txt_subtotal.Value = Me.txt_subtotal.Value + Me.txt_fee.Value
Next

End Sub
[END CODE]

- In the "Detail" section, I've got "txt_subtotal" with the Running Sum
property set to "Over Group".  This is used to calculate the subtotal
for a single storage space.
- In the "salutation Footer" section, I've got "txt_total_fee" with the
Control Source set to "=[txt_subtotal]" and the Running Sum set to
"No".  This is supposed to display the invoice total for all the
storage spaces of a single customer (basically just adding up the
txt_subtotal amounts).  It is this part that doesn't work - it just
displays the last txt_subtotal fee.

Any ideas (This is finally the last problem on this report!)?  Thank
you very much.  David
 
M

Marshall Barton

CanGrow is working perfectly now Marshall - Thank you!

The subtotal is working better now, but I can't get it to add the
subtotals together. Here's my code now:
Code: 
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

'Initialize values
Me.txtLineItem = Null
'Me.txt_subtotal.Value = 0

'Loop to print line items
For yr_print = Me.txt_last_year_paid + 1 To Year(Date)
'Print a line item
Me.txtLineItem = (Me.txtLineItem + vbCrLf) & "Space " &
Me.txt_space_number.Value & " for " & yr_print & " .... " &
Me.txt_fee.Value
'Keep running total for the subtotal
Me.txt_subtotal.Value = Me.txt_subtotal.Value + Me.txt_fee.Value
Next

End Sub
[END CODE]

- In the "Detail" section, I've got "txt_subtotal" with the Running Sum
property set to "Over Group".  This is used to calculate the subtotal
for a single storage space.
- In the "salutation Footer" section, I've got "txt_total_fee" with the
Control Source set to "=[txt_subtotal]" and the Running Sum set to
"No".  This is supposed to display the invoice total for all the
storage spaces of a single customer (basically just adding up the
txt_subtotal amounts).  It is this part that doesn't work - it just
displays the last txt_subtotal fee.[/QUOTE]


Aarrrggghhhh, we can't overwrite the running sum with each
value!   We need another text box named txtAmtDue for you to
place your calculated value.  Then change the subtotal
running sum text box to use the expression  =txtAmtDue.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Do While Loop in Detail Section of Report 5
Spacing in detail section 2
Subform in Report is blank 1
Import Complex info from Excel via VBA 6
Loop through filter and output report with filter in filename 4
Macro running slow 7
Help! Explorer.exe crashing due to fault in shell32.dll. 9
Dump of perfection Test 1

Top