Generate a receipt number during mail merge

My database holds payment details for donations made by donors to our school.  Once per year I need to send them an official receipt showing the total donated for the year, for tax purposes.  This receipt is currently done via mail merge.  I use a word template, laid out appropriately, and sourced to the table in access97.  This works ok.

Now to the nitty gritty.  I need to print a unique Receipt Number on the receipt.  I assume this will need to be generated by the computer (access) at the time of mail merge, no dups etc.  

How to go about this.  I am still thinking it thru, but maybe the number assigned to the receipt needs to be captured in the database for reference.

And I am wondering how to go about this.

The solution will need to include the appropriate code.  

Any suggestions...
thanks


mickwillAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

brewdogCommented:
One question first:

Do you keep all year's donations in the same table, or start with a fresh table each year?

If you start fresh each year, here's what I'd say:

1. Design a table to hold archived receipt information. Structure might look like . . .

ReceiptNo   Donor   DonationTotal

Make ReceiptNo an AutoNumber field.

2. Design an append query that utilizes Group By to add summarized data into the table we created in step 1. something like:

Insert into tblArchiveDonations99
select Donor, sum(DonationAmount)
from tblDonations
Group by donor

3. Base your mail merge on either this summarized table or, since you probably need addresses, etc., a query that links donor information with our summarized table.

If you have any questions about the process, let me know. Hope this helps . . .

brewdog
0
mickwillAuthor Commented:
From memory, I'm at home now, the table structure is like this.

tblDonor contains donorID and various other details like addresses etc.

tblPayment contains donorID, paymentID, datePaid, AmountPaid ...

A relationship exists between both tables linked on donorID.

The payments table is not started fresh each year.  Payments are added to the table and will cover multiple years.

When we run the reports, and receipts, we use qbf, and the range of dates is based on datePaid.  ie donations made between 1 apr 98 and 31 mar 99.  etc.

The focus of the question lies on the receipt number.  how to generate the number in the word document.  Perhaps how to capture it in the database and where to save it.

Maybe we need another table, tblRecipts, containing donorID, ReceiptDate, receiptNumber.  This table to be related to tblDonor ???


It's this latter bit that I need to sort out, and hopefully draw on your experience in this scenario.

thanks

0
BrianWrenCommented:
I would make a table carrying the fields [recieptNo]-(AutoIncrement), [DonorID]-(Long to match with the DonorID in the table of donors), [Year]-(either String or Integer: you will be manipulating this yourself, so using a date field is less than helpful).

At the time you archive the receipts for a year I would add a record to this table, fill in the Donor Id and the year.

Since this would be set up with a 1-to-many relationship with the Donor table, you would have unique Receipt numbers for each year.  If you additionally had a field in the table of donations for associating with this process, you could put into that field the receipt number that had been associated with the archiving of the record.  Records having a reciept number in that field would be identified as having been archived by the presence of data in that field, and a 1-to-many relationship between that field in the donations table as the many and the receipts  table as the '1'--without enforcing referential integrity--would allow you to look at records by receipt number, by donor, by donor when not archived, by donor WHEN archived, etc.

When it is time to archive you would add a record to the recepts table for the current donor.  This would generate a receipt number.

If you were using a recordset for the donors, (rd), and for the receipts, (rc):

strYear = "1999"
rc.AddNew
rc!DonorID = rd!DonorID
rc!Year = strYear
intReceipt = rc!recieptNo
rc.Update

The AutoIncrement field has the automatically generated number in that field as soon as the AddNew method has been executed, and you will need it later.  So store it in a variable.

Next you would open a recordset of the donations being archived, rDs.

Set rDs = db.OpenRecordset("SELECT * FROM [Donations] " & _
          "WHERE DonorID = " & rd!DonorID & " " & _
          "AND CStr(Year(Donations.GiveDate)) = '" & strYear & "'")

Then loop through the records filling in the receipt number:

Do Until rDs.Eof
    rDs.Edit
    rDs![ReceiptNo] = intReceipt
    rDs.UpDate
    rDs.MoveNext
Loop

(This would be better done with an update query, but I'm too tired to work out the SQL right now.  Something like:
strSQL = "Update [Donations] Set ReciptNo = " & intReceipt & " Where DonorID = " & rd!DonorID & " AND CStr(Year(Donations.GiveDate)) = '" & strYear & "'"
db.Execute strSQL

When this is done, the records are marked as archived, they are related to the reciepts table which tells the archive year into which they were archived, and the reciepts table is related to the Donors table.

So you would have:
Bob Smith      127364, 1998
      127698, 1999
Jane Smith      127365, 1998
      127715, 1999
etc.

Does that help?

Brian
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

mickwillAuthor Commented:
Maybe you are missing the point of the question - I probably haven't explained it too well.

None of the records are archived.  The receipt is not issued per record.  Once per year, a word document is created summarising the total of all donations made by the donor for the year.

So, if he has made five donations of $100, then the "Receipt" as I am calling it will say something like:

Receipt No: 244

Dear Mr Smith
His address

For the 1998/99 FY you have donated $500.00.

On behalf of the school board, thankyou

etc


On the receipt I want to put a unique number, ie receipt number.  This number to be generated at the time the receipt is printed.

As an aside to that, I think it would be good to add that particular receipt number in another table so it can be referred to later if necessary.
0
JimMorganCommented:
If all you want to do is generate a unique receipt number, I would suggest that you have a system table which keeps track of the last number used.

Then you can use a report to generate the number is you want it printed by Access or sent to Word for fancier printing.

The key in the report would be to get the value from the table when the report is loaded and store this in a variable inside the report code.  As each report is printed, increment the number by one.  During close of the report, put the new number back in the system table.

We do this all the time to keep track of purchase order, sales receipts, and other system numbers in our applications.

Is there a particular reason to generate the receipt in Word?  It seems like using a report to summarize and print all the information would be ideal.

There is not much to the code involved this way.  The system table has only one record with the various fields assigned.  Assuming that you only have the receipt field at this time, the field could be gotten from the table with a simple DLookup:

In report load:

   Dim intRecp as Integer
   intRecp = DLookup("ReceiptNo", "tblSysValues")
   
In the header for the report record:

   intRecp = intRecp + 1

In the report close:

   CurrentDB.Execute "Update tblSysValues Set Receipt = " & intRecp

If you need help with the report itself, I can assist you.

Jim
0
mickwillAuthor Commented:
Word is the way we want to go.  The users, who are not access trained and therefore know nothing about reports, are familiar with word templates.  They will be quite comfortable editing the documents.

0
brewdogCommented:
I suggested some kind of summary table for three reasons:

1. With the ReceiptNumber field as an AutoNumber, you won't have to create the receipt numbers yourself

2. By using a Group By query to append records to this summary table, you'll get one line per donor number for the date range you're generating.

3. By changing the Word template to draw information from this summary table (linked to the donor table) you can get address, etc., information and still have just one figure to give donors.

I'm assuming, of course, that you're only going to be printing/sending these letters once per year, not sending them out on a daily or hourly basis. If that is the case, then I don't see the need for JimMorgan's suggestion about managing the receipt number -- let Access do the work for you, as Jim always says. :o)

Does my suggestion not work? If not, please let me know what I'm missing, because it seems perfectly suited for what you're looking for.

brewdog
0
JimMorganCommented:
mickwill:  I had to ask.  Since brewdog was here first with his comments, I didn't want to parrot what he was saying so I came up with an alternate solution.

Although you can have the output of the report directed to a Word so that the users can make any changes or customize for a great donor.  The users don't have to know anything about Access.  You just push the button once a year and the report will run and put the receipts out to Word for the final look see and verification.

I understand that you probably want to stay with what you are most familiar so I will have to admit that brewdog's suggestion would have been the one that I would have used, had I been here first.  At least this way, you have records of the total contributions by year for each person.  You can do a lot more with that table then just store the records.  It can be sorted by the contributions and you can have a list of who is providing the most support...

Jim
0
EdouardCommented:
I am having a problem submitting my comment (maybe it os too long?)
I'll try to submit it in two parts.
0
EdouardCommented:
I understand Mickwill's desire not to archive. The payments have been entered once and should not be entered again (not even as totals), not manually and not through code.
The nice part about Access is that we can do anything by using SELECT queries which don't alter or add any data, they just look for it and bring it to us in any form we want.

Mickwill you are on the right track by saying you want a receipts table. If you were writing your receipts manually you would keep a book with the receipt stubs right?

(to get a better view you might want to copy and paste this text into Word)

For this to work you need the following objects in your db (I tried it and it looks fine):

Tables:
---------
tblDonor
tblPayment
tblReceipt
tlkpYear

Queries:
----------
qryPayment
qryPaymentSum (used in frmReceipt to show the total yearly donations for the current receipt)
qryReceipt (this will be merged with your Word doc)

Forms:
---------
frmReceipt (use it to enter and print new receipts and to view old ones)


STRUCTURE OF OBJECTS:
=====================

1.
tblDonor: the way you have it

2.
tblPayment
field name: - data type:
   PaymentID - AutoNumber (Primary key)
   PaymentDate - Date/Time
   PaymentAmnt - Currency
   DonorID - Number (related to tblDonor)

3.
tblReceipt
field name: - data type:
   ReceiptNo - Number (Primary key, Long Integer)
   (No autonumber because if a user enters a new receipt and cancels it the numbers won't   be consecutive anymore. Instead with the code below
   DonorID - Number (related to tblDonor)
   ReceiptDate - Date/Time
   ReceiptYear - Number (related to tlkpYear)
   ReceiptComment - Text (optional)
   ReceiptUser - Number (optional, if used relate it to tblUser if you have one)
Create a unique index on fields DonorID and ReceiptYear to avoid accidentally  issuing dubble receipts for the same donor for same year.

4.
tlkpYear
field name: - data type:
   Year - Number (Primary key, Long Integer)
   YearBegin - Date/Time
   YearEnd - Date/Time

The data in this table needs to be entered only ones so it looks like:
  1997 | 01-04-1997 | 31-03-1998
  1998 | 01-04-1998 | 31-03-1999
  1999 | 01-04-1999 | 31-03-2000
  etc.

5.
qryPayment (paste directly as is in SQL view):
SELECT [tblPayment].[PaymentAmnt], [tblPayment].[DonorID], [tblPayment].[PaymentDate]
FROM tblPayment
WHERE ((([tblPayment].[DonorID])=[Forms]![frmReceipt]![cboDonorID]) And (([tblPayment].[PaymentDate]) Between [Forms]![frmReceipt]![txtYearBegin] And [Forms]![frmReceipt]![txtYearEnd]));

6.
qryPaymentSum (paste directly as is in SQL view):
SELECT Sum([qryPayment].[PaymentAmnt]) AS SumOfPaymentAmnt
FROM qryPayment;

7.
qryReceipt (paste directly as is in SQL view):
SELECT tblDonor.DonorName, Format([ReceiptNo],"000") AS ReceiptNumber, tblReceipt.ReceiptDate, tblReceipt.ReceiptYear, tblReceipt.DonorID, Format(DLookUp("SumOfPaymentAmnt","qryPaymentSum"),"Currency") AS Amount
FROM tblDonor INNER JOIN tblReceipt ON tblDonor.DonorID = tblReceipt.DonorID
WHERE (((tblReceipt.ReceiptYear)=[Forms]![frmReceipt]![cboReceiptYear]) AND ((tblReceipt.DonorID)=[Forms]![frmReceipt]![cboDonorID]));

8.
frmReceipt
  record source = tblReceipt
Controls:
- txtReceiptNo
  control source = ReceiptNo

- cboDonorID
  control source = DonorID
  row source = SELECT [tblDonor].[DonorID], [tblDonor].[DonorName] FROM tblDonor;
  On_AfterUpdate event: Me.Recalc (to show the right amount in txtAmnt)

- txtReceiptDate
  control source = ReceiptDate

- cboReceiptYear
  (control source = ReceiptYear)
  (row source = SELECT [tlkpYear].[Year], [tlkpYear].[YearBegin], [tlkpYear].[YearEnd] FROM tlkpYear;)
  bound column = 1
  column count = 3
  column widths = 2cm;0cm;0cm
  On_AfterUpdate event: Me.Recalc (to show the right amount in txtAmnt)

- txtYearBegin
  record source = =[cboReceiptYear].column(1)
  visible = No (optional)

- txtYearEnd
  record source = =[cboReceiptYear].column(2)
  visible = No (optional)

- txtAmnt
  record source = =DLookUp("SumOfPaymentAmnt","qryPaymentSum")

- cmdNewReceipt
  On_Click event:
   DoCmd.GoToRecord , , acNewRec
   Me!txtReceiptNo = DMax("ReceiptNo", "tblReceipt") + 1
  'This makes the receipt numbers consecutive

- cmdMerge (your merge button)

One comment:
If you use this method you will be outputting your receipts to Word just one at a time, which will be the current receipt in your frmReceipt. If this is sufficient it should work for you.
Also:
Personally I would not use merging with Word either since you can make a report as the others suggested above. You could add a comment and a user field to your receipts and include it as well. However I respect your wish to use Word as the final step and think you should go ahead with it.
Also:
You probably have issued receipts already before without saving them in a table. For the first receipt saved to this table you could enter any number you want manually in tblReceipts so that you don't start at no 001. From there on it will count up.

Good luck

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EdouardCommented:
I managed to submit the comment in one part. I wrote the comment in Word and I guess it contained some formatting (those autoformats!) that could not be accepted. I pasted the whole thing in notepad and copied it from there. It works!
0
JimMorganCommented:
Edouard:

Word is notorious for that.  What kind of error message did you get when you tried to submit the comment?  Several of us have gotten difference messages and I'm trying to piece together all the errors so that EE can fix the problem.  They should be able to strip out unwarranted formatting codes.

I tried pasting mine into notepad and still a problem.  I finally had to put the whole thing in a DOS editor, save the file, then open in notepad to get a clean copy.  Even saving Word to a text file doesn't give you a clean copy.

Jim
0
EdouardCommented:
Jim:

After trying to submit it gave me the page where it says something like: Cannot open the requested page - Click refresh etc. The usual page when you can't connect. I didn't write it down though.
It will be great if you manage to help EE to fix the problem.

Thanks from all of us.

Edouard
0
JimMorganCommented:
Mickwill:

Sorry to be using your question to test this EE server but this needs to be cleared up.

Edouard:

EE claims that the problem is resolved.  I'm going to try to paste in a few lines of formatted Word document to test the theory.

2) Create an icon on your desktop that points to the Access executable and your workgroup, something like: “C:\Program Files\Microsoft Office\Office\msaccess.exe” /wrkgrp “c:\my database\my system.mdw”
a) I put paths in quotes because of the spaces in the file names
b) The /wrkgrp switch functions like the ini file did in Access 2, redirecting Access immediately to verify the security system involved before looking to anything else
3) Open Access through your new icon
0
JimMorganCommented:
Edouard:

It looks like they have fixed the problem.  Hooray!!
0
mickwillAuthor Commented:
Hi chaps.

Just to let you know I am working on this at the moment and will come back in the next couple of days.

thanks for all your input..

appreciated.
0
mickwillAuthor Commented:
Thanks Edouard - I think your answer gave me a good push in the right direction.  

Brewdog.  I decided against using the autonumber type field for generating the receipt number.  Unfortunatley, if someone starts to enter a receipt and then cancels, the receipt number is lost.

thanks to all who helped out...
0
JimMorganCommented:
Since that only leaves you and me, Brian, I guess we fall into the category of "all who helped out".  :-)
0
JimMorganCommented:
Edouard:  That was a nice bit of points for the first question that you have answered.  Congradulations.  Don't be so long joining in on the comments next time.

Jim
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.