Sales Invoice Problem

[AccessJunkie33]

To begin:

I don't know what I'm doing.

I'm currently volunteering at a nonprofit and am using MS Access to track customers to pledges, cash receipts, and seats and tables.

The gala (the database was designed for) has passed and I need to print and send out invoices for our sponsors who dined on credit.

I've been messing around with Access to create an invoice by using forms and subforms (and even that I'm iffy with).

The problem I have is that I have to print out around 150 invoices and I would like to do it at the same time.

How would I be able to do this without changing the form inputs one at a time?

Any input or general advice would be highly appreciated.

 

[the_net_2.0]

if you're going to print batches, I would seriously look at using a word mail merge. that may be your best bet.

for example, the function below will automate the mail merge by using data in the open database:

Function MailMerge(mDoc As String, _
                   strSQL As String)

On Error GoTo Err_Handle

'******************************************************************************
'_____________________________________________________________________________*
'                                                                             |
'THIS FUNCTION USES THE CURRENT DATABASE AS THE MAIL MERGE SOURCE.  IT ALSO   |
'USES LETTERS AS THE MERGE FORMAT.  THERE IS NO ARGUMENT TO CHANGE THIS.      |
'_____________________________________________________________________________|
'                                                                             *
'Arguments:                                                                   *
'mDoc > Full path of the Merge document.                                      *
'strSQL> The query to run the Merge with.                                     *
'                                                                             *
'******************************************************************************

Dim oApp As New Word.Application
Dim oMainDoc As Word.Document
Dim sData As String

   oApp.Visible = True
      sData = CurrentProject.Path & "" & CurrentProject.Name

   Set oMainDoc = oApp.Documents.Open(mDoc)
   
      With oMainDoc.MailMerge
          .MainDocumentType = wdFormLetters
          .OpenDataSource Name:=sData, _
                          SQLStatement:=strSQL
      End With
   
   With oMainDoc
       .MailMerge.Destination = wdSendToNewDocument
       .MailMerge.Execute
   End With
   
   oApp.Activate
   oApp.Documents.Parent.Visible = True
   oApp.Application.WindowState = 1
   oApp.ActiveWindow.WindowState = 1

Set oApp = Nothing
Set oMainDoc = Nothing

Exit Function

Err_Handle:
   Set oApp = Nothing
   Set oMainDoc = Nothing
      MsgBox "An error occurred..." & vbCrLf & vbCrLf & Err.Description

End Function

the only thing you need to do to alter that function is add the data fields to your invoice document...

the database also must be shared, as i believe using it as exclusive will throw an error once the code tries to open the source.

 

[AccessJunkie33]

Thank you! Was this using Visual Basic code? And, since I don't know any, could you recommend a guide?

I've used mail merge before, but I don't know where to insert the function in word.

If it is indeed VB, then I'll study up a bit and look through the code. It's kind of scary to just take code and copy and paste! LOL


Thanks for the response!

 

[the_net_2.0]

Thank you! Was this using Visual Basic code? And, since I don't know any, could you recommend a guide?

I've used mail merge before, but I don't know where to insert the function in word.

If it is indeed VB, then I'll study up a bit and look through the code. It's kind of scary to just take code and copy and paste! LOL


Thanks for the response!

This code can be copied and pasted. it goes in access, not word. the arguments are explained as well. And the code is VBA, not VB.

googling ''vba mail merge'' will give you more than a days worth of info to sift through. the function I posted does everything except populate the merge fields, which you have to insert yourself. you can do this in the mail merge wizard after selecting the data source.

 

[AccessJunkie33]

Thanks again!