By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,762 Members | 1,838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,762 IT Pros & Developers. It's quick & easy.

Reminder pop-up in Microsoft Access 2013

P: 3
Hello everyone I'm new to Access and have been putting together an Assets Database for the company i work for but i have no idea how to create a reminder form or report. I would like the reminder to reference information from 2 tables. I have a Registration Due in one table and a Next Service Due in another. I would like to have a 21 day reminder for both to display upon opening and i don't mind if it is separate reminders or one joined one.
If anyone could help me on this it would be very much appreciated.
Thank You
Jun 4 '17 #1

✓ answered by NeoPa

Hi Bryan.

It's actually not necessary to create an AutoExec macro simply to fire off a form on opening the database. You can specify a form in the Startup Options which will open automatically when the database is opened as follows :
File | Options | Current Database | Display Form

Unless you already have another use for the form you could then design it to show the details of your due items.

At this point it's hard to be too specific as we don't know the layout of your tables but if your form had a Detail Section and each table had an [AssetID] linked to a separate table with asset info like an [AssetName] then the form's .RecordSource could be a QueryDef or some SQL with the following SQL (or something similar) :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [tblAsset].[AssetName]
  2.        , [qDue].[Registration Due]
  3.        , [qDue].[Next Service Due]
  4. FROM     [tblAsset]
  5.          INNER JOIN
  6.          (SELECT [AssetID]
  7.                , [Registration Due]
  8.                , Null AS [Next Service Due]
  9.           FROM   [tblRegistration]
  10.           WHERE  ([Registration Due]>=DateAdd('d',21,Date()))
  11.           UNION ALL
  12.           SELECT [AssetID]
  13.                , Null AS [Registration Due]
  14.                , [Next Service Due]
  15.           FROM   [tblService]
  16.           WHERE  ([Next Service Due]>=DateAdd('d',21,Date()))) AS [qDue]
  17.   ON     [tblAsset].[AssetID]=[qDue].[AssetID]
  18. ORDER BY [tblAsset].[AssetName]
  19.        , [qDue].[Registration Due]
Please note that this SQL code is untested as I don't have a database to test it on.

If the recordset is empty once loaded then the form can be closed. If that's how you prefer to handle it. I suggest you get that up and working before moving on to such worries though. Let us know when you have or if you have difficulty managing that from these instructions.

I would just add, in case it isn't obvious, and that may be possible for newer developers, that to show the data you'll need to create Form Controls for each of the fields returned by this SQL or QueryDef.

Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,489
You need a form that's set to open when the database opens and a Form_Open() Event Procedure that runs your checking code.

When you have that up and working we can proceed further.
Jun 4 '17 #2

P: 3
Thank you for the reply! I have created a reminder form with an autoexec which opens it upon start up. I am quite new to all of this and have no idea what the checking code is.
Jun 5 '17 #3

NeoPa
Expert Mod 15k+
P: 31,489
Hi Bryan.

It's actually not necessary to create an AutoExec macro simply to fire off a form on opening the database. You can specify a form in the Startup Options which will open automatically when the database is opened as follows :
File | Options | Current Database | Display Form

Unless you already have another use for the form you could then design it to show the details of your due items.

At this point it's hard to be too specific as we don't know the layout of your tables but if your form had a Detail Section and each table had an [AssetID] linked to a separate table with asset info like an [AssetName] then the form's .RecordSource could be a QueryDef or some SQL with the following SQL (or something similar) :
Expand|Select|Wrap|Line Numbers
  1. SELECT   [tblAsset].[AssetName]
  2.        , [qDue].[Registration Due]
  3.        , [qDue].[Next Service Due]
  4. FROM     [tblAsset]
  5.          INNER JOIN
  6.          (SELECT [AssetID]
  7.                , [Registration Due]
  8.                , Null AS [Next Service Due]
  9.           FROM   [tblRegistration]
  10.           WHERE  ([Registration Due]>=DateAdd('d',21,Date()))
  11.           UNION ALL
  12.           SELECT [AssetID]
  13.                , Null AS [Registration Due]
  14.                , [Next Service Due]
  15.           FROM   [tblService]
  16.           WHERE  ([Next Service Due]>=DateAdd('d',21,Date()))) AS [qDue]
  17.   ON     [tblAsset].[AssetID]=[qDue].[AssetID]
  18. ORDER BY [tblAsset].[AssetName]
  19.        , [qDue].[Registration Due]
Please note that this SQL code is untested as I don't have a database to test it on.

If the recordset is empty once loaded then the form can be closed. If that's how you prefer to handle it. I suggest you get that up and working before moving on to such worries though. Let us know when you have or if you have difficulty managing that from these instructions.

I would just add, in case it isn't obvious, and that may be possible for newer developers, that to show the data you'll need to create Form Controls for each of the fields returned by this SQL or QueryDef.
Jun 5 '17 #4

P: 3
You are a legend! I managed to get it all working finally, Thanks so much for your help!
Jun 7 '17 #5

NeoPa
Expert Mod 15k+
P: 31,489
Very happy to help Bryan. It's what we try to do here :-)
Jun 7 '17 #6

Post your reply

Sign in to post your reply or Sign up for a free account.