Do While Loop in Detail Section of Report

[datamerc]

Hi,

I am creating a report in Access 2003 that will be used for invoicing
storage spaces. The format of the report in the "design view" is as
follows:
"Salutation Header" - customer address
"Detail" - line item for each storage space and fee due for that
storage space (there may be anywhere from 1 to 100 storage individual
spaces for a single customer).
"Salutation Footer" - total amount due

Each line for a storage space in the "Detail" section is formatted as:
"2006 Storage fees for space 367" "$300.00"

The trouble I run into is if a customer is overdue on a storage space,
multiple lines will need to be displayed in the "Detail" section in
order to invoice for prior years. For example, if the last year paid
for space 367 was 2003 and space 253 was paid in 2005, this is what it
should be displayed on this customer's report:
"2006 Storage fees for space 253" "$400.00"
"2004 Storage fees for space 367" "$300.00"
"2005 Storage fees for space 367" "$300.00"
"2006 Storage fees for space 367" "$300.00"

The recordset for this report is a query run on the "customers" and
"storage" tables. The "storage" table contains a field called
"last_year_paid" and this is how the report should determine how many
lines are required for a given storage space.

It is not a problem to display all the storage spaces for a single
customer, but I don't have a clue as to how I can get multiple lines
for a single space to be displayed. Can anyone help me figure out how
to get multiple lines to display (as necessary) for a single storage
space, as shown in the above example. Thank you in advance!

David

 

[Guest]

1) You can do this by coding the MoveLayout, NextRecord, and
PrintSection Properties, which allow you to print another detail
section for the same record.

or

2) by adding an (unjoined) Year table to query, which will give you
every year for every record, then restricting the result to only years
that after the last paid year (and before or equal to the next due year).

Method 2 is the normal method, Method 1 was more for people who
came to Access 1 and 2 from a coding background, but AFAIK it
works.

(david)

 

[datamerc]

Thanks for the quick reply. I believe that method #1 may work better,
because what I was hoping to be able to do was something like this
rough code (in the "Detail" section):

If(last_year_paid < 2006) then
'Calculate the number of years (lines to print) past due
years_overdue = 2006 - last_year_paid

'Print a line for every year that is past due
for(x=1, x<years_overdue+1, x++) {
print(last_year_paid+x." Storage fees for space
".space_number." $".storage_fee
}
end if

I think something like this should work, but I just don't know the
following:
1) where to put the code should be put (i.e. in the "Private Sub
Detail_Print" section)
2) how to reference the fields from the query (last_year_paid,
space_number, etc)
3) how to reference a text box for the line item(s) to print in the
detail section.

Best regards,
David

 

[david epsom dot com dot au]

1) You can't use a text box, you will have to use a label.

2) labels can be referenced like this:

me.lbl_amount.caption = dblAmount

3) fields in your query can be referenced like this:

me!MyField

but you may find that you have to put a hidden
text box in your detail section, so that you can
reference that instead:

me.mytextbox.value


4) Code should go in the format event of the section.

(david)

 

[datamerc]

I believe I have most everything correct in the following code:

Private Sub Detail_Format(Cancel As Integer, FormatCount 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
annual_fee = Me.txt_fee.Value


If last_yr_pd < curr_year Then
'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
x = x + 1
yr_print = Me.txt_last_year_paid + x
Me.lbl_grave.Caption = "Grave " & Me.txt_grave.Value & ",
Block " & Me.txt_block.Value & " for " & yr_print
Me.lbl_fee.Caption = annual_fee

'Me.MoveLayout = True
'Me.NextRecord = False
'Me.PrintSection = True

Loop

End If
'Me.MoveLayout = False
'Me.NextRecord = True
'Me.PrintSection = False
End Sub
[END CODE]


This is supposed to print one line on the invoice for every year that a
particular space is overdue.  I currently have the MoveLayout,
NextRecord, and PrintSection commented out and it will print just one
line.  With those, no lines will print.

Any ideas?  Thanks!

 

[david epsom dot com dot au]

Never tried it, like I said, most people now would use the other
approach, but I think it is more complex than that.

Let Access do the 'loop' - you just need to keep track and tell
Access what do to each time it formats the detail section.

1st time: print first year. don't move to the next record. print section.
next time: print next year. don't move to the next record. print section
next time: last year. print year, move to next, print section

etc.

You probably need to track the format count as well, to be sure
that you don't write the next year into the same line.

(david)