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

[gutenberg]

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



The Table I am using is labeled: T_Package

The column (in the T_Package tbl) is labeled ExpirationDateand 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 2days 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 toaccomplish this?

[aytee111]

The VBA code would be MsgBox "message".

Where to make it happen is - when do you want it to happen? When the database opens? When the form opens? When the user makes a selection?

[NTC]

Well there are several steps to put this together - and some of the design will depend on how you prefer things to occur.

As you say it is a table column with multiple records then it would seem one could have multiple records all within the 2 day expiration. So first you need a query that will result in the correct records (based on the current date) AND you'll have to have some method to deal with old records so they do not perpetually return to this query. Call this ExpirationQ.

Then ExpirationQ can be the record set by which you can make a form or report from. Lets just call that ExpirationScreen. Forms do have a pop up property but I don't think Reports do - but if it opens first it will be seen so either might work.

Then decide when & how to launch that. You suggest an event which is the opening of a form which is fine.

You don't want to open a blank Expiration Screen so you want to do a DCount to see if ExpirationQ has records along with the If/then logic to open the ExpirationScreen if there are records.

[gutenberg]

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?

[jwhite]

The simplest approach would be to create a subform on your form with a recordsource to return records where dateadd(expirationdate,-2,date()) <= date(), and set the subform property to not allow adding or updating records.