Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2010

    Unanswered: Can Access send me "Past Due" Alerts?

    Hi all,

    I'm new to Access (2010) and hoping to set it up to handle all my small business financials.

    I'm studying how to use the program right now, but one question I would like to know right away is if Access can be easily setup to alert me of past due accounts. Ideally I would punch in the invoice date, and if the invoice was not marked "paid" within, say 15 days, Access would notify me via popup or email that the invoice had not been marked as paid yet. A 2nd delinquent notice at 30 days would be extra nice.

    Is this something that Access can do without advanced programming?


  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    This will require some programming, but it's rather easily doable.

    You'll need to select the invoice, compare the date of payment (you'll need one, probably in the Invoice table) with the current date (using DateDiff() function, for instance) and see if the result lies within a certain range (15 days in your question). According to the result, you can call a function to send an email, display a message box or perform any action you see fit.
    Have a nice day!

  3. #3
    Join Date
    Oct 2010
    Thanks, is there any way to have fully automated alerts? If I am required to select the invoice, then a manual scan for payment would actually be faster than the alert.

  4. #4
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    assuming you can structure your requirement as a question, then converting that into a query should be possible. often the hardest part is clearly and unambiguosly structuring the question.

    depending on how that question is converted into a query depends on how easy it is to automate.

    automate when... every time you view a customer record, or do you want a list of invoices which are at or beyond credit terms?. personally i would have thought that a form or report displaying all invoices out of terms would be more useful. granted i could see a warning on a customer form indicating if there is money outstanding would be useful (say in a order handling form, warning that the customer is in default)

    so Id suggest you have a look at designing the query

    It would be impossible for anyone here to design it for you as we don't know your table design

    open the query designer, place the invoice table (andany others required) onto the workspace
    select the columns you want
    then you need to set a filter to remove invoices which have been paid / settled, that depends entirely on how your system is designed
    set that by placing a condition in the boxes below the column(s)
    say you had a a column indicating AmountOutstanding, this filter would be >0 in the AmountOutstanding

    then you need to deifne you overdue criteria
    say its 15 days
    the filter is datediff("d",date()-invoicedate,15)
    then save your query
    then run it
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Oct 2006
    I dont mean to hijack the thead, but I am doing something similar.. where a ticket is referred to a certain user. This is currently being executed on the on open event for the switchboard form. I am using a query and a hidden listbox on the form. Feel free to use anything that can help.

    Can I bypass the query/listbox and use something quicker?

    If Environ("username") = Me.Msg_Notice_to_Referred_User.Column(4, 0) Then
    MsgBox ("Ticket" & " " & Me.Msg_Notice_to_Referred_User.Column(5, 0) & " " & "has been referred to you by" & " " & Me.Msg_Notice_to_Referred_User.Column(6, 0) & "."), vbOKOnly, "Ticket Referred"
    Dim XstrSQL As String
    Dim XstDocName As String
    Dim XstLinkCriteria As String

    XstrSQL = "select * form helpdesk where"
    XstDocName = "helpdesk"
    XstLinkCriteria = "[ID]=" & Me.Msg_Notice_to_Referred_User.Column(5, 0)
    DoCmd.OpenForm XstDocName, acWindowNormal, acFormEdit, XstLinkCriteria, AutoCenter

    DoCmd.SetWarnings False
    DoCmd****nSQL "UPDATE helpdesk SET [chk_notice]=false WHERE [ID]= ID"
    DoCmd.SetWarnings True
    End If

  6. #6
    Join Date
    May 2010
    Here is an example:

    Boyd Trimmell aka HiTechCoach (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts