Automatic Invoicing

[anoob]

Hello all - I posted this at the end of another thread, but it's something (well, two things) I am very curious about, so I figured it was worth it's own thread.



1. Have any of you ever done anything like this:

I have a very small web development company, and we do hosting for a handful of clients. On the 25th of the third month of every quarter, I would like Access to generate new invoice records for each client signed up for this service, so all I have to do is print and mail.

I'm not looking for an answer so much as just a general idea - is this something that is possible? Is it practical or far too involved? (I know it will save me time and allow me to quit using post-its for my hosting billing reminders...) Any online tutorials or projects or forum posts related to something similar to this that you have seen?


2. Are several of you employed in a place that you essentially manage data all day, be it with Access or otherwise? I'm just curious if knowing Access can land you a job.

I mean no offense, I'm just ignorant to the field and wondering if I should have paid more attention in my MS Office 2000 classes...

Thanks all.

[ajetrumpet]

it is, but it's still duplicated so i reported it. don't ask things in more than one thread.

[pbaldy]

1) Not too difficult, presuming you have a way of identifying the data. You'd create a query/report for the invoices. I'd create a stand-alone database that printed that report when it opened, then closes itself. Then I'd call that db from Windows Scheduled Tasks on the appropriate schedule.

2) I'm IT Manager, but I'd guess 75% of my time is spend creating and maintaining Access databases. I know of a few people that make their living with Access, either as employees of a company or as consultants.

[NoellaG]

Hi,

question1:
-----------
I did a similar thing for a sports federation for the yearly renewal of the memberships. Each year I generate an invitation for payment (same layout of an invoice, but other title and no rigid invoice numbering) for each member : I use a query there and a routine that loops through every record and creates a report for that query, then I export it as a PDF and create in Outlook an e-mail with the pdf as attachment. It works like a marvel.

Question 2:
-------------
it does pay of, a lot of organizations are stuck with poorly made Access databases that badly need some cleaning up. But be sure you're really good and not only understand the mechanics of Access and VBA, but know the do's and don'ts of relational database design, know how to make a good analysis (UML is a great tool for that), and how to write the documentation. It helps if you know how to use Access together with Excel, Word, SAP and other databases like MySQL, Oracle and SQL server.
I started with Access some 20 years ago and stepped up to SQL Server and Oracle after that, but Access still makes out about 30 percent of my work.

succes
NG

[anoob]

it is, but it's still duplicated so i reported it. don't ask things in more than one thread.

You gotta be kidding me.

Either way, thanks for your reply in the other thread, and thank you two for the replies in this thread, it has been very helpful to me. If any of you know the Access Forum Police please tell them it was an honest mistake and they do not need to take me to the gulag. I will not re-post again.

[anoob]

I'd create a stand-alone database that printed that report when it opened, then closes itself. Then I'd call that db from Windows Scheduled Tasks on the appropriate schedule.

I think this is a great idea - although I probably wouldn't have it close itself so that I could add more hosting clients for the next round - yes? Thoughts?

I'm really pleased that is seems like between the answers on this page it will be do-able. Awesome.

[anoob]

then I export it as a PDF

Awesome response - is it possible to set up a macro for a button like "Export Current Record" that would save as a PDF?

Would it then be possible to define the naming structure? For example:

ClientName_InvoiceNumber45.PDF

Very helpful - thank you.

[NoellaG]

Hi,

if you have Access 2007, you'll need to install the add-in to publish pdf's, free download at https://www.microsoft.com/downloads/...displaylang=en

This is a piece of the code I use to create the mail with pdf's:

    With rst
        If Not (.BOF And .EOF) Then
            'get PDF locations
            strR = Nz(Me.OpenArgs, "rptClubExamenlijst")
            If strR = "rptClubExamenlijst" Then
                strS = "Examenlijst " & Format(Date, "dd-mm-yyyy")
            ElseIf strR = "rptClubAdminLijst" Then
                strS = "Administratieve lijst " & Format(Date, "dd-mm-yyyy")
            ElseIf strR = "rptClubFiche" Then
                strS = "Clubfiche " & Format(Date, "dd-mm-yyyy")
            Else
                strS = "Administratie - secretariaat VKF"
            End If
            objMailing.LoadMailingPars (strR)
            .MoveFirst
            intC = 0
            While Not .EOF
                objCurSel.EmptyCurSel
                Call objCurSel.InsertCurSel(!wafEntiteitsID, "")
                strClubnummer = CStr(Nz(!wafClubNummer, "nn"))
                'test: creer een gewoon accass rapport
                'DoCmd.OpenReport strR & "_pdf", acViewPreview
                'creer de pDF
                strFile = objMailing.MailLoc & "\Club" & strClubnummer & strR & Format(Now, "YYYYMMDD_hms") & ".pdf"
                DoCmd.OutputTo acOutputReport, strR & "_PDF", acFormatPDF, strFile, False
                'if exisyting e-mail adress: create mail
                strMailadres = Nz(DLookup("clubEmail", "tblClubs", "clubID = " & Nz(!wafEntiteitsID, 0)))
                If Len(strMailadres) > 0 Then
                    fMailCreated = objMailing.SendMailMessage(strMailadres, strS, objMailing.MailTekst, strFile)
                    If fMailCreated Then ' creeer een record in tabel mailing
                        lngMailID = objMailing.CreateMailRecord("C", !wafEntiteitsID, strMailadres, False, strR)
                        'delete pdf of send mails
                        fFileDeleted = objMailing.DeleteStoredFile(strFile)
                        intC = intC + 1
                    End If
                End If
                
                .MoveNext
            Wend
            MsgBox  intC & " E-mails created"
        End If
        .Close
    End With

The objMailing is an object created from the Mailing class I wrote to handle all interactions with Outlook, the createmail function in that class is written as follows:

Public Function SendMailMessage(strTo As String, strSubject As String, strBody As String, strFile As String) As Boolean
On Error GoTo Err_SendMailMessage

    Dim appOutlook As New Outlook.Application
    Dim msg As Outlook.MailItem
    Dim intpos As Integer
    
    Set msg = appOutlook.CreateItem(olMailItem)
    msg.To = strTo
    msg.Subject = strSubject
    msg.Body = strBody
    msg.Attachments.Add (strFile)
SendMessageNow:
    msg.Send
    
    SendMailMessage = True

Exit_SendMailMessage:
    Exit Function
    
Err_SendMailMessage:
    'MsgBox Err.Number & ": " & Err.Description
    If Err.Number = -2147467259 Then 'Outlook doesn't recognize the adress
        intpos = InStr(1, strTo, "#")
        If intpos > 1 Then
            strTo = Left(strTo, intpos - 1)
            msg.To = strTo
            Resume SendMessageNow
        End If
    End If
    SendMailMessage = False
    Resume Exit_SendMailMessage

End Function

[pbaldy]

I think this is a great idea - although I probably wouldn't have it close itself so that I could add more hosting clients for the next round - yes? Thoughts?

Keep in mind this would be a "utility" db whose sole purpose would be to generate the invoices. I'd have a separate "full" db that I did all the day-to-day stuff in. Thus this one would open, create invoices and close. Adding new clients would be done in the full db (both would be linked to the same data). You can do as you're suggesting, it's just not the direction I would likely go. You know your needs better than any of us does though.