Ok Kelly, here we go.
Follow the steps below. There are countless ways to do this,
but we'll follow Arvin's lead since you have already been
studying this.
1. Make a backup of your database.
2. Repeat step 1 please.
3. Open your own database and go to the Queries area.
4. Create a new query. When the dialog box that comes up prompting
for table and field names just hit cancel. You should have a blank
query design grid.
5. Switch to SQL view by going to View | SQL View on the main menu bar.
6. Now just copy and paste this SQL right into the SQL Editor window:
SELECT Passport.*
FROM Passport
WHERE (((Passport.[Expiry Date])<=Date()+7));
7. Save and close the query. Name the query: qryPassportsAboutToExpire
8. Create a new blank form with the following properties:
Caption - Passport Expiration Information
Allow Edits - Yes
Allow Deletions - Yes
Allow Additions - Yes
Data Entry - No
Scroll Bars - Neither
Record Selectors - No
Navigation Buttons - No
Dividing Lines - Yes
Auto Center - Yes
Pop Up - No
Modal - Yes
9. Create a new command button on the form, but do not put any
code attached to it just yet (just hit Cancel if the wizard comes up).
Change the name of the command button to cmdOpenQuery and
set the caption of the command button to Open the Query.
10. Now go to the Code window for the form (View | Code on the
main Access menu bar).
11. All you should see at the moment is this:
Option Compare Database
Option Explicit
12. Now copy and paste ALL of this code right **below** the top two
lines shown above on Step 11:
'**********Code Start**********
Private Sub cmdOpenQuery_Click()
On Error GoTo ErrorPoint
DoCmd.OpenQuery "qryPassportsAboutToExpire"
ExitPoint:
Exit Sub
ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint
End Sub
Private Sub Form_Open(Cancel As Integer)
On Error GoTo ErrorPoint
'************************************
' Name: Form_Open
' Purpose: Check Passport Expirations
'
' Author: Jeff Conrad - Access Junkie
' Date: May 4, 2005
' Comment: For Kelly
'************************************
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngCount As Long
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("qryPassportsAboutToExpire", dbOpenSnapshot)
lngCount = rst.RecordCount
If lngCount > 0 Then
MsgBox "There are Passports about to expire. Please " _
& "review the list to start the paperwork.", vbExclamation _
, "Passport Information"
End If
ExitPoint:
' Cleanup code
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Sub
ErrorPoint:
MsgBox "The following error has occurred:" _
& vbNewLine & "Error Number: " & Err.Number _
& vbNewLine & "Error Description: " & Err.Description _
, vbExclamation, "Unexpected Error"
Resume ExitPoint
End Sub
'**********Code End**********
13. Compile the code by going to Debug | Compile on the main menu bar.
14. Save and close the form. Name the form frmPassportExpiration
15. Now test opening the form. If there are passport expiration dates
within the next week you will get a message box just like Arvin's sample.
Once the form is open you can press the command button to see a list
of the upcoming passport expiration records.
16. Change any formatting on the form to suit your needs.
Will this work for you?
--
Jeff Conrad
Access Junkie
Bend, Oregon
in message:
