Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2016
    Posts
    25

    Answered: Creating a Pop Up reminder when an expiration date is approaching on a table

    This just concerns one column in a table and the dates listed in that column.

    The Table I am using is labeled: T_Package

    The column (in the T_Package tbl) is labeled ExpirationDate and there are a number of dates listed in that column.

    The primary key field name is labeled PackageID.

    I am trying to create a reminder or alert pop up box 2 days from when the expiration date is about to arrive.

    I want the Pop up to appear on my form which is labeled Frm_ProgramSelect.

    What event would I use and what VBA code would I use to accomplish this?

  2. Best Answer
    Posted by weejas

    "You don't need to use an event for this - a calculated text box will do:
    Code:
    txtExpiryClose:
    =IIf(DateDiff("d", Now(), [ExpirationDate]) < 2, "This product is about to expire!", "")
    You can set the background of txtExpiryClose (or whatever you want to call it) to be invisible and the borders to zero width. Set the font colour to be something that really stands out against the form's background colour."


  3. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    Provided Answers: 11
    I would Create A Query that shows the reminders

    then you could Use the Dcount to count the number of rows it would show

    then in a onLoad event

    if Dcount("*","2dayReminder")>0 then
    'Do what you want to do
    End if
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  4. #3
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    You don't need to use an event for this - a calculated text box will do:
    Code:
    txtExpiryClose:
    =IIf(DateDiff("d", Now(), [ExpirationDate]) < 2, "This product is about to expire!", "")
    You can set the background of txtExpiryClose (or whatever you want to call it) to be invisible and the borders to zero width. Set the font colour to be something that really stands out against the form's background colour.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  5. #4
    Join Date
    Jun 2016
    Posts
    25
    Can the table (T_Package) be input into this code somehow? Because I don't think the code references back to the table.

  6. #5
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    My code snippet was based on the (sparse) information that you provided.

    Provided that your form is either bound to T_Package, or has a control called ExpirationDate that contains the values from that column in the table, it'll work.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  7. #6
    Join Date
    Jun 2016
    Posts
    25
    How would I create a calculated text box or list box (with a scroll function) that can include (show) multiple records from the results of the query?

    I don't think DLookup would work because it only returns one record.

    Do you know what code or formula I would use instead of DLookup in the a textbox or listbox?

  8. #7
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    1,101
    Provided Answers: 17
    This is a new question, and you should ideally start a new thread for it.

    A list box returns one or more columns from one or more rows from a table or query. What do you want it to show? Do you want its contents to be linked in any way to the current record being displayed on the form?
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

Posting Permissions

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