brahm neufeld

ALT TEXT

Access/Visual Basic/Adobe InDesign Receipting

One of the areas at SCI-FI that needed significant retouching was our receipting and confirmation system. Much like many of our old processes, we had hacked together a working solution, but it was in need of attention. The old system used an odd combination of plaintext email reecipts, and "fancy" mail-merged post receipts.

The new system is much better, as it has the ability to easily generate professional-looking receipts at the click of a button. The basic program flow is as follows:

  1. A trio of views on SQL server monitor three payment scenarios:
    • Registrations who have not received any confirmation (we have not mailed/emailed them anything).
    • Registrations who received a confirmation of registration, but not a receipt for payment.
    • Registrations wh have received both their confirmation of enrollment and a final receipt.
  2. A report in Access displays registrations who need a confirmation or a receipt. A pair of buttons (see above image) call the Confirmation script (batch mode) in Visual Basic. Receipts can also be called individually using a pair of buttons on the camper's info screen.
  3. The script queries the views with pending confirmations (or, a single camper's record) and adds them to a large array.
  4. Visual Basic fires up Adobe InDesign and does some document layout.
  5. A PDF file of the registration is saved on our network.
    • If the confirmation method is post, the receipt is automatically spooled to our networked printer.
    • If the confirmation method is email, the receipt is automatically added to a new Outlook email message and sent through our main email account.
  6. All variables are cleared, and each camper's record is marked as "confirmation sent" or "final receipt sent".

The learning curve was pretty steep on this project. Neither myself or Kurt (who spearheaded the InDesign scripting) had much prior knowledge about Visual Basic or the InDesign scripting langugage, which is extremely dense and online resources are SPARSE.

Because online resources are so limited, I'm posting a modified version of our confirmation script below, in the hopes that it will help out some other people. To summarize, it tackles the following:

  • Using ADODB connections to connect to a database.
  • Adobe InDesign CS3 scripting in Visual Basic.
  • PDF generation (through InDesign).
  • Looping through a large recordset (list of campers), each with their own subrecord sets (transaction table or registered extras table), with with several linked tables for extra information.
  • Efficient batch receipting (note: I am using "efficient" from an office administrator's perspective, not a programmer's perspective!)

You can look a pretty HTML richtext-formatted version, or download the plain script for your own reference or experimentation!

Last Modified March 7, 2009