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 -
