Subreport Problem

[Erick C]

Hi everybody.
I am hoping someone may be able to help me out with a problem that I
am having. I am creating some new receipts via an access report. I
made a report that is pulling customer information from a select
query. Inside the report deail section I have added a subreport to
list the various acconuts that a customer may have (created in a
separate report). The amount of space that I can use is limited,
since I am fitting 3 receipts per page, but I can still list 8
accounts in the small detail section to the right of the customer name
and address.
The problem comes in when there is a customer that has more than 8
accounts. I tested the report out by adding 15 accounts to one
customer to see what would happen. Unfortunately, the report resized
itself to fit all 15 accounts. I need to figure out a way to stop the
number of accounts in the subreport at 8, and if a customer has more
than 8 accounts to create a new receipt for the customer, rather than
trying to fit everything in on one and messing up the size of the
report.
Can anybody help me out? Am I making any sense at all?

 

[Allen Browne]

In report design view, select the subreport control and make it the right
height to show only 8 rows. Then set its Can Grow property to No.

You probably want to ensure the Can Grow property of the (Detail?) section
is No as well.

 

[Erick C]

Allen -
Thank you for the information. I will try it out in the morning. One
question for you, will this just prevent more than 8 rows from being
viewed, or will changing the height and setting can grow to no make
Access make a new report for any rows over 8? In other words, if
there are 15 rows for one customer and I make these changes, will I
only see one report with 8 rows of data and the rest just missing? Or
will this make Access create another report to display the next 7
rows?

 

[Allen Browne]

Because the control cannot grow, it simply prints whatever fits in the
height you gave it. Any that don't fit, just don't get printed.

It is possible to have controls that can grow but the section that can't.

 

[Erick C]

Hi Allen -
I have made the change to the control, so now my report stays the
proper size. Unfortunately, I still have an issue that I cannot seem
to find an answer to.
If my subreport has more than 8 rows of data for a customer how can I
get access to put the first 8 records into receipt number one and then
the remaining subreport rows into receipt number two?
I have been kicking around some thoughts, like updating the source
data to only give me a maximum of 8 records in the table that makes
the report, and then dump customers with more than 8 records into
another table to make a new receipt. This could work, but it it
pretty tedious and there are some customers who can have up to 50
records. This would be a lot of extra tables to create.
Ideally, I am trying to find a way to get the receipts to print one
after another so when they are mailed out they can be put together in
the same envelope.
Any ideas that maybe could help me out?

 

[Allen Browne]

I'm not clear what you are seeking to achieve here. Sounds like you want to
combine several records into one long string, but only the records that
don't fit within the 8 rows you have available?

Perhaps you want to try to concatenate records together. It won't be easy,
but here's an example of how to do that kind of thing in code:
http://allenbrowne.com/func-concat.html

 

[Erick C]

Hi Allen -
I am sorry for the confusion, let me try to explain my situation a
little bit better. Hopefully it will help you with better clarity to
answer.
I have essentially made a report for a 3 equal-part-per-page receipt
that will be mailed to customers. They will just receive a single
slip of paper in the envelope, 1/3rd of a sheet of paper. The main
"receipt" report i made pulls customer information, including the
customer name and address (so it can be seen from the little envelope
window). I have a subreport in the detail section of the "receipt"
report (basically another report that I created) that is tied to the
customer's account number from the "receipt" report. This subreport
lists any accounts belonging to the customer, one row per account.
Given the fact that I need 3 equal reports per page in order to line
up with the perforations in the paper, my subreport can only provide 8
rows of customer account detail. Anything more than that will mess up
my margins.
So, if you opened up the envelope you would have a slip with customer
information in the header, and detail information to the right of the
name and address. I wish that I could draw on here, a picture would
make it so much easier!
So, here is my problem. Say I have a customer that is supposed to be
getting one of my receipts but the customer has 12 accounts. My
account detail subreport is listing all 12 accounts with their
detail. When the "receipt" report tries to add the subreport
information it can only fit 8 rows of account information on one
receipt and stay within the margins. As it is made right now, the
other 4 rows of data are not displayed. How can I get my report to
find the customer information to populate the customer information on
receipt number one with all 8 rows of account information that can
fit, and then make receipt number two for the customer with the same
customer information populated and the remaining 4 rows of account
information? This will give the customer two receipts, one with 8
accounts listed, and another receipt with 4 accounts listed.
I tried making one simple report, all pulling from one table
containing all of the information together. I ran into problems
because the same customer has one row of data for each account that
they have, therefore the report waould try to make multiple "receipts"
for one customer with the same data. If they had 3 accounts it would
give me 3 receipts.
Do I need to re-think my format? Is there a better way to make this
report?
I hope this makes a bit more sense now? Please let me know if I can
provide some more information that would further clarify.

 

[Allen Browne]

Okay, there's a few way to address that problem.

Solution A: Cartesian Product to generate extra records
====================================
Under any customer, how do you decide the order in which to print the items
on their receipt? Is there any chance of adding a (hidden) field to that
table to determine the sort order of the items?

If you could add such a field, it may be possible to populate it
automatically in as the items are entered. Use Form_BeforeInsert (or
Form_BeforeUpdate testing if Me.NewRecord if you have multiple users
entering data.) The first account in a receipt will be 0, the next one is 1,
and so on.

Now you can add a calculated field to a query to indicate if some items are
spilling over onto another record. The expression will be:
ReceiptGroup: [SortOrder] \ 8
This yields zero for the first 8 records (0-7), 1 for the next 8, and so on.

Now create another table with just one field of type Number named (say)
CountID. Mark it as primary key. Save the table. Enter 3 records (0-3), or
more if you think it's possible a customer could have more than 24 accounts.

Add this table to the query, without any line joining the 2 tables in the
upper pane of query design. This (Cartesian Product) gives every possible
combination of records (i.e. 3 rows of every receipt.) In the Criteria row
under the CountID, enter:
<= [SortOrder] \ 8
This yields just one row for a receipt if there's 8 or fewer accounts, but
generates a second record for the remaining ones.

Solution B: Programmatically generate extra records
==================================
Another alternative would be to write some code to OpenRecordset on the
source data, and another one on the receipt table also. Loop through the
source data. If you find more than 8 records, create another record in the
receipt table (same customer and date, different receipt number), and then
Edit (and Update) the remaining items in the original receipt with this new
receipt number. By assigning a new receipt number like this, there are no
cases where more than 8 items all share the same receipt number.

There'll be other solutions too, and no doubt you'll have to modify this to
suit, but hopefully it sets you on a useful track.

 

[Erick C]

Hi Allen -
Thank you for your response. I think that I am following your
response accurately, but I may have some things a bit wrong. I am
still learning a lot of the vba stuff in Access, so I am not really
all that good at it. I just have the most experience in my group with
building databases, I just do not have all of the knowledge that many
of the poeple on here have.
Currently, I have no way of deciding the order that things are printed
in my report. The select query that is the record source for the
report is sorting the account numbers. But basically all I have is a
giant report with all customers that would receive a receipt. Since
they are going 3 to a page and being separated this is no big deal.
I am still building this database from scratch, so I am able to
modify, remove, or add anything that I need to. If a new field is
required then it is no big deal at all. I am kind of scratching my
head with the BeforeInsert command. It is something that I have never
seen before, and as I have been messing around with it, it seems that
it is driven by something on my form? Right now, my form does not
have any information that is actually entered into it. The form
simply has buttons that are used to import data into my tables and run
some simple macros that I made to combine all of the data together. I
don't know if I can still use the BeforeInsert or not. So basically
all of the data is being imported from excel or csv files, nothing is
manually entered in Access right now. If there is a way to get a
field to populate with a record count when the data is imported that
would be great. Even if it gets populated after the import, that is
great too! Please keep in mind, the source data that is being
imported had an account number attached so there are no other tables
or queries necessary in order for me to count the number of records
belonging to an account number. Right now I just have everything
coming together in one big make table. I then have select queries
that separate out data further. This is nothing set in stone, I can
modify to anything that you believe would be more beneficial.
Here is an example of the code that I am using to bring in the
accounts. Please keep in mind, I cannot take the credit for all of
the cool code, I had a lot of help from some of your counterparts in
the access.externaldata group:
Dim strPathFile As String
Dim strTable As String, strBrowseMsg As String
Dim strFilter As String
Dim blnHasFieldNames As Boolean

blnHasFieldNames = True
strBrowseMsg = " Please select the Collateral Detail file to import…"

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.csv)",
"*.csv")
strPathFile = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=False, _
DialogTitle:=strBrowseMsg, _
Flags:=ahtOFN_HIDEREADONLY)
If strPathFile = "" Then
MsgBox "No file was selected.", vbOK, "No Selection"
Exit Sub
End If

strTable = "Collateral_Details "

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
strTable, strPathFile, blnHasFieldNames

MsgBox "Collateral Details Import Complete.", vbOK, "Status"
Exit Sub

So is there a way to integrate the BeforeInsert into the code that I
already have? Or is there a way to modify my current code so that I
get a record count for each record based on account number?
I hope this message gets you at a better hour. I am going to try to
work on this from home so I can make my Chicago time a bit closer to
your Australia time! As always, thank you for all of your
assistance. It is greatly appreciated.

Okay, there's a few way to address that problem.

Solution A: Cartesian Product to generate extra records
====================================
Under any customer, how do you decide the order in which to print the items
on their receipt? Is there any chance of adding a (hidden) field to that
table to determine the sort order of the items?

If you could add such a field, it may be possible to populate it
automatically in as the items are entered. Use Form_BeforeInsert (or
Form_BeforeUpdate testing if Me.NewRecord if you have multiple users
entering data.) The first account in a receipt will be 0, the next one is1,
and so on.

Now you can add a calculated field to a query to indicate if some items are
spilling over onto another record. The expression will be:
    ReceiptGroup: [SortOrder] \ 8
This yields zero for the first 8 records (0-7), 1 for the next 8, and so on.

Now create another table with just one field of type Number named (say)
CountID. Mark it as primary key. Save the table. Enter 3 records (0-3), or
more if you think it's possible a customer could have more than 24 accounts.

Add this table to the query, without any line joining the 2 tables in the
upper pane of query design. This (Cartesian Product) gives every possible
combination of records (i.e. 3 rows of every receipt.) In the Criteria row
under the CountID, enter:
    <= [SortOrder] \ 8
This yields just one row for a receipt if there's 8 or fewer accounts, but
generates a second record for the remaining ones.

Solution B: Programmatically generate extra records
==================================
Another alternative would be to write some code to OpenRecordset on the
source data, and another one on the receipt table also. Loop through the
source data. If you find more than 8 records, create another record in the
receipt table (same customer and date, different receipt number), and then
Edit (and Update) the remaining items in the original receipt with this new
receipt number. By assigning a new receipt number like this, there are no
cases where more than 8 items all share the same receipt number.

There'll be other solutions too, and no doubt you'll have to modify this to
suit, but hopefully it sets you on a useful track.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Hi Allen -
I am sorry for the confusion, let me try to explain my situation a
little bit better.  Hopefully it will help you with better clarity to
answer.
I have essentially made a report for a 3 equal-part-per-page receipt
that will be mailed to customers.  They will just receive a single
slip of paper in the envelope, 1/3rd of a sheet of paper.  The main
"receipt" report i made pulls customer information, including the
customer name and address (so it can be seen from the little envelope
window).  I have a subreport in the detail section of the "receipt"
report (basically another report that I created) that is tied to the
customer's account number from the "receipt" report.  This subreport
lists any accounts belonging to the customer, one row per account.
Given the fact that I need 3 equal reports per page in order to line
up with the perforations in the paper, my subreport can only provide 8
rows of customer account detail.  Anything more than that will mess up
my margins.
So, if you opened up the envelope you would have a slip with customer
information in the header, and detail information to the right of the
name and address.  I wish that I could draw on here, a picture would
make it so much easier!
So, here is my problem.  Say I have a customer that is supposed to be
getting one of my receipts but the customer has 12 accounts.  My
account detail subreport is listing all 12 accounts with their
detail.  When the "receipt" report tries to add the subreport
information it can only fit 8 rows of account information on one
receipt and stay within the margins.  As it is made right now, the
other 4 rows of data are not displayed.  How can I get my report to
find the customer information to populate the customer information on
receipt number one with all 8 rows of account information that can
fit, and then make receipt number two for the customer with the same
customer information populated and the remaining 4 rows of account
information?  This will give the customer two receipts, one with 8
accounts listed, and another receipt with 4 accounts listed.
I tried making one simple report, all pulling from one table
containing all of the information together.  I ran into problems
because the same customer has one row of data for each account that
they have, therefore the report waould try to make multiple "receipts"
for one customer with the same data.  If they had 3 accounts it would
give me 3 receipts.
Do I need to re-think my format?  Is there a better way to make this
report?
I hope this makes a bit more sense now?  Please let me know if I can
provide some more information that would further clarify.

...

read more »- Hide quoted text -

- Show quoted text -

 

[Allen Browne]

Use the Sorting And Grouping dialog (in report design view) to specify the
sort order.

Form_BeforeInsert is triggered at the point when you start adding a new
record in the form. You don't have to code this: it's just a way of getting
Access to insert the next number instead of having to do it manually.

If this is imported data, Form_BeforeInsert doesn't apply. You may be able
to use an Update query to populate the new field, using a DMax() expression.

Hope you understand we can't do it for you: just suggest ideas for you to
follow up.

 

[Erick C]

Hi Allen -
Thank you for the info. I definitely understand that you guys cannot
do the whole thing for me, man if it was only that easy! But I do
appreciate all of the guidance that has been provided. I am working
on some queries to update a new field with a running count of records
for each account number. I can then follow your "Solution A"
suggestion and try the Cartesian Product. I am much more comfortable
building queries than I am writing SQL or working in VBA, so this will
probably be my best bet.
If I run into any questions once I get everything finished I will ask.

Thanks.
Erick