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) :
- SELECT [tblAsset].[AssetName]
-
, [qDue].[Registration Due]
-
, [qDue].[Next Service Due]
-
FROM [tblAsset]
-
INNER JOIN
-
(SELECT [AssetID]
-
, [Registration Due]
-
, Null AS [Next Service Due]
-
FROM [tblRegistration]
-
WHERE ([Registration Due]>=DateAdd('d',21,Date()))
-
UNION ALL
-
SELECT [AssetID]
-
, Null AS [Registration Due]
-
, [Next Service Due]
-
FROM [tblService]
-
WHERE ([Next Service Due]>=DateAdd('d',21,Date()))) AS [qDue]
-
ON [tblAsset].[AssetID]=[qDue].[AssetID]
-
ORDER BY [tblAsset].[AssetName]
-
, [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.
3
