Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Location
    Oakland, CA
    Posts
    126

    Unanswered: Notify About Contract Expiration

    Hello,
    I would like to have a msgbox notification when a form opens that goes through a table that has Date field for ContractExpiration. When the date in that field is older than today I want to list the Vendor name.

    So, I have a table with two fields - VendorName, ContractExpiration

    The situation when the form opens would be a msgbox saying (supposing that HP and DELL have older than today dates in the ContractExpiration field):

    HP, DELL have pass due contracts.

    I'm not sure how simple it's to write, I don't want to use reports for this. Thanks all of you

  2. #2
    Join Date
    Feb 2004
    Posts
    142
    In the form load event you can open a query to detect past due accounts
    Either create a query or you can use the string below as a record source

    dim db as database
    dim rs as dao.recordset
    dim strMsg as string

    set db = currentdb()
    set rs = db.openrecordset("SELECT VendorName From YourTableName WHERE ContractExpiration < #" & Date() & "#")

    if rs.recordcount <> 0 then
    'cache recordset (important for ODBC datasources)
    rs.movelast
    rs.movefirst
    else'no one past due - exit sub
    rs.close
    set rs = nothing
    exit sub
    end if

    do
    strMsg = rs!VendorName
    rs.movenext
    if not rs.eof then
    strMsg = strMsg & ", "
    end if
    loop until rs.eof
    rs.close
    set rs = nothing
    strMsg = strMsg & " are past due."
    msgbox strmsg
    KC

Posting Permissions

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