Building Microsoft Access Applications
This article is an excerpt from Building Microsoft Access Applications, from Microsoft Press (ISBN 0-7356-2039-3, copyright Microsoft Press 2005, all rights reserved). The author of the book is John Viescas.
John L. Viescas is the author of Microsoft Office Access 2003 Inside Out and the popular Running Microsoft Access books from Microsoft Press. He is president of Viescas Consulting, Inc., a respected provider of database application design and editorial consulting services. He has been recognized by Microsoft Product Support Services as a Most Valuable Professional (MVP) every year since 1993 for his contributions to the community of Access users.
No part of this chapter may be reproduced, stored in a retrieval system, or transmitted in any form or by any means—electronic;, electrostatic, mechanical, photocopying, recording, or otherwise—without; the prior written permission of the publisher, except in the case of brief quotations embodied in critical articles or reviews.
Chapter 7: Tracking Member Status
- Entering Dues Rates and Payments
- Detecting Dues Not Paid
- Reporting Dues Status
Every organization needs some level of funds to run, whether it be something as mundane as paying for postage stamps and stationery, or something as expensive as paying for meeting space. Many organizations acquire the funds they need by charging a membership fee or annual dues. Nonprofit organizations accept pledges and donations. So, it's important to not only accurately record dues or donations paid but also track payment status and report dues or pledges owed in a timely manner.
The Membership Tracking sample application assumes dues can be paid at any time, and dues can be paid to renew for various numbers of months, which you can define in the tblDuesRates table. The actual Pacific Northwest Access Developers Group, on which the sample is modeled, charges annual dues only and collects those dues each January. Perhaps after they see how easy it is to implement a rolling dues payment plan, they'll change how they collect their dues.
Entering Dues Rates and Payments
Whenever a user enters a dues payment, you could require that the user manually specify the current dues rate. You could also set a default value for the dues rate, but the user would have to correct that each time if the rate has changed, or you would need to modify your application. A simpler method is to provide a dues rates table that contains at least one row with the current rate. When you do that, the user can change the current rate at any time, and you can look up that value to supply the current value as the default in any new payment record.
But if you're going to provide a dues rates table, you might as well allow the user to keep a history of rate changes over time. Your table could also allow the user to specify different renewal intervals. It's a bit more complex to find and load the latest rates, but doing so provides an additional level of integrity for the dues amounts charged.
The Membership application includes a tblDuesRates table. You can examine the contents of this table by opening the frmDuesRates form, as shown in Figure 7-1. Note that when running the application, the user can access this form by clicking the Code Tables button on the main switchboard, and then clicking the Dues Rates button on the Code Tables switchboard.
.gif)
Figure 7-1. The frmDuesRates form allows you to view and edit the contents of the tblDuesRates table in the Membership application
As you can see, the annual dues were $25.00 starting in January 1992, increased to $30.00 in January 1995, and an option for a six month membership was added in 1997. Now all you need to know is how to pick up the most recent dues rates from this list and make them available to the user.
In the Member Activity report that is shown in Figure 6-4, you can see several members at the top of the list who haven't been active for a while. Let's assume you edited those member records and set their status to Inactive. A few days later, you receive a dues payment from Peter Waxman, so you need to enter the payment in the database and renew his membership. To see how this works, perform the following steps:
- Open the Membership application (Membership.mdb) and open the frmMembers form.
- Find the record for Peter Waxman and set his status to Inactive.
- Click on the Dues History tab and select the All Records option.
- Go to the last record—the; new record row—enter; a date a few days earlier than the current date (perhaps the date you received the payment) in the Date Paid column, and then open the Paid Thru list.
You should see a result similar to that shown in Figure 7-2.
.gif)
Figure 7-2. The Membership application calculates available renewal dates using the information from the tblDuesRates table (Click picture to view larger image)
Because it's clear from the activity report that this user hasn't attended any meetings since his dues last expired, you'll probably want to renew his membership from the payment date rather than extend his old membership. Go ahead and save his dues payment (press Shift+Enter or move to another record)—we;'ll see how that affects signing in to a meeting later in this chapter.
The list that you see is generated by code running in the fsubMemberDues subform. The code depends on a query that returns the current rate in each different renewal interval, and then it generates the combo box row source as a value list for each rate. The qryCurrentDuesRates query fetches the current rates, and the SQL for that query is as follows:
SELECT tblDuesRates.DuesEffective, tblDuesRates.DuesAmt, tblDuesRates.DuesInterval
FROM tblDuesRates
WHERE tblDuesRates.DuesEffective =
(Select Max([DuesEffective])
FROM tblDuesRates As DR2
WHERE DR2.[DuesEffective] <= Date()
And DR2.[DuesInterval] = tblDuesRates.DuesInterval)
ORDER BY tblDuesRates.DuesInterval;
The list is created by the SetupPaidThru function in the form's module that is called each time you move to a new row (from the Current event) and each time you change the value in the DatePaid field (from that control's AfterUpdate event). The code begins by setting the heading information in the combo box Row Source property (which also clears any previous entries) and then verifying that a valid MemberID exists in the outer form, as follows:
Private Function SetupPaidThru()
Dim db As DAO.Database, rst As DAO.Recordset, rstDues As DAO.Recordset
Dim datMemberPaidThru As Date, strRowSource As String
' Called by other procedures to set up the Row Source for cmbPaidThru
' Procedure fetches the current dues intervals and rates from
' qryCurrentDuesRates and sets up the following valid "Paid Thru"
' choices:
' 1) Renewal extension from this members last paid thru date
' 2) Renewal extension from the date paid
' 3) Renewal extension from today
'
' The combo box has four columns:
' 1) New Expire date
' 2) Dues amount
' 3) Length of renewal in months
' 4) Comment indicating reason for choice
' Set an error trap
On Error GoTo SetupPaidThru_Error
' Clear the current Row Source - headings only
Me.cmbPaidThru.RowSource = "'New Expire';'Dues';'Months';'Comment'"
' If there's not a valid member ID
If IsNothing(Me.Parent.MemberID) Then
' Bail
Exit Function
End If
Next, the function opens the qryCurrentDuesRates query and verifies that it returns at least one row.
' Point to this database
Set db = DBEngine(0)(0)
' Open a recordset on the current dues rates
Set rstDues = db.OpenRecordset("qryCurrentDuesRates")
' If no records, close and bail
If rstDues.RecordCount = 0 Then
' Close
rstDues.Close
Set rstDues = Nothing
Set db = Nothing
Exit Function
End If
The function fetches the most recent dues payment record for the current member, and if any payment exists, generates one row in the combo box for each current renewal rate. These are the first two data rows you see in Figure 7-2.
' Get the last dues renewal
Set rst = db.OpenRecordset("SELECT Top 1 * FROM tblDues " & _
"WHERE MemberID = " & Me.Parent.MemberID & _
" ORDER BY PaidThru DESC")
' If there's a record
If Not rst.EOF Then
' Save the member's last paid thru date
datMemberPaidThru = rst!PaidThru
' Loop through dues rates to generate combo box rows
Do Until rstDues.EOF
' Set up the next row of data
strRowSource = strRowSource & ";'" & _
Format(DateAdd("m", rstDues![DuesInterval], datMemberPaidThru), _
"dd mmm yyyy") & _
"';'" & Format(rstDues!DuesAmt, "Currency") & "';'" & _
rstDues!DuesInterval & "';" & _
"'Extend from previous expiration date.'"
rstDues.MoveNext
Loop
End If
' Close the member recordset
rst.Close
Set rst = Nothing
The next piece of code repositions back to the beginning of the current dues rates recordset and creates the renewal options for the date paid that you entered (the third and fourth rows in Figure 7-2).
' Move back to start of current duesrecordset
rstDues.MoveFirst
' If there's a date paid
If Not IsNothing(Me.DatePaid) Then
' Create rows for "Extend from date paid"
Do Until rstDues.EOF
' Set up the next row of data
strRowSource = strRowSource & ";'" & _
Format(DateAdd("m", rstDues![DuesInterval], Me.DatePaid), _
"dd mmm yyyy") & _
"';'" & Format(rstDues!DuesAmt, "Currency") & "';'" & _
rstDues!DuesInterval & "';" & _
"'Extend from date paid.'"
rstDues.MoveNext
Loop
End If
Finally, if the date paid is different from today's date, the code creates rows for renewal options from today (the last two rows in Figure 7-2).
' Move back to start of current dues rates recordset
rstDues.MoveFirst
' Create today rows only if DatePaid <> Today
If Me.DatePaid <> Date Then
' Create "today" rows
Do Until rstDues.EOF
' Set up the next row of data
strRowSource = strRowSource & ";'" & _
Format(DateAdd("m", rstDues![DuesInterval], Date), "dd mmm yyyy") & _
"';'" & Format(rstDues!DuesAmt, "Currency") & "';'" & _
rstDues!DuesInterval & "';" & _
"'Extend from today.'"
rstDues.MoveNext
Loop
End If
' Close out
rstDues.Close
Set rstDues = Nothing
Set db = Nothing
' Set the rest of the row source
Me.cmbPaidThru.RowSource = Me.cmbPaidThru.RowSource & strRowSource
' Indicate success
SetupPaidThru = True _Exit:
' Done
Exit Function _Error:
' Tell user there was an error
MsgBox "Unexpected error setting up Paid Thru list: " & _
Err & "," & Error, _
vbCritical, gstrAppTitle
' Log it
ErrorLog Me.Name & "_SetupPaidThru", Err, Error
' Bail
Resume SetupPaidThru_Exit Function
End Function
An experienced programmer might ask why I didn't load the renewal options from the qryCurrentDuesRates query into an array in memory rather than loop through the recordset three times. To use an array, the code would have to dynamically allocate the memory using the ReDim statement and then read through the recordset at least once to load the array. Notice that the code doesn't close and reopen the recordset—it; merely repositions to the beginning of the recordset each time. Unless you have dozens of renewal options, Access will load the entire recordset into memory, so moving through the recordset in this way is more efficient than allocating and loading additional memory.
Detecting Dues Not Paid
In the previous section, you entered a dues payment for Peter Waxman, but there are other members in the database whose dues are not current. To see what happens when one of these members signs in to a meeting, open the frmMeetings form. Go to the last meeting—a; general meeting in January 2005—and; click the Attendees button. In the Attendee Options dialog box, select the Open the meeting sign-in form option and click Go.
If you haven't changed the sample data, you should see 19 members already signed in to this meeting. In the Sign In, Please box, type the letters wax (the first three letters of Peter Waxman's last name), and press Enter. If you entered a dues payment as instructed in the previous section, the application should create an attendance record and issue no warnings, as shown in Figure 7-3.
Next, try to sign in Douglas Hite. Press Alt+S to return to the Sign In, Please box, type the letters hit, and press Enter. The application creates an attendance record, but warns you that dues are not paid, as shown in Figure 7-4.
.gif)
Figure 7-3. Recording the attendance of a member whose dues are paid (Click picture to view larger image)
.gif)
Figure 7-4. The Membership application warns you when a member whose dues are unpaid signs in to a meeting (Click picture to view larger image)
After you click OK, the application code changes the Status field to Guest as promised, and puts the focus on the Dues tab. You can ask the member if he'd like to renew his dues and immediately enter the renewal if he chooses to do so.
The code that makes this happen is in the KeyDown event of the Sign In, Please text box. After the procedure detects that you have pressed Tab or Enter, it adds the new member attendance record. It then performs a check to see if dues are paid through the current meeting date. The code is as follows:
' See if dues are paid - or Complimentary
If (IsNull(DLookup("MemberID", "tblDues", _
"MemberID = " & lngNewMember & _
" And PaidThru >= #" & frmMtg.MeetingDate & "#"))) And _
(Me.MembershipStatus.Column(1) = "0") Then
' Issue message
MsgBox "Dues are not current for this member." & _
vbCrLf & vbCrLf & "Status will be changed to 'Guest.'", _
vbInformation, gstrAppTitle
' Make sure Dues are clear
Me.DuesPaid = False
' Save the change
Me.Dirty = False
' Change the status
DBEngine(0)(0).Execute "UPDATE tblMembers " & _
"SET MembershipStatus = 'Guest' " & _
"WHERE MemberID = " & lngNewMember, dbFailOnError
' Put focus on the Dues tab
Me.TabCtl0.Value = 2
Else
' Set dues paid
Me.DuesPaid = True
' Save the change
Me.Dirty = False
' Put focus on Title
Me.Title.SetFocus
End If
The MembershipStatus combo box control has a Row Source property that displays the status (the displayed text), and also fetches the DuesComp field (not displayed) in the second column. The DuesComp field will be True (-1) for a status that is complimentary (Honorary and Speaker members), but False (0) for a member status that should pay dues. The DLookup function attempts to find a dues payment row for the current member that has a PaidThru date greater than or equal to the meeting date. If no record is found and the member's current status is one that should pay dues, the code issues the warning message, updates the record in tblMembers to Guest, and puts the focus on the Dues tab. If dues have been paid, the code sets the DuesPaid field in the attendance record to True and saves the change.
Tip The Access help topic on the Dirty property notes that the property is read/write—which; means you can update the property from code. However, the help topic doesn't tell you what happens when you change the property. If the Dirty property is True (meaning the current record has been changed but not saved), you can set the property to False, and Access saves the changed record. Using this technique is not only more efficient but also safer than using RunCommand acCmdSaveRecord. When you use the RunCommand method, the command applies to whatever has the focus, which in a complex form with several subforms might not save what you intended. Setting the Dirty property of a specific form to False always saves the record being edited by that form
If Doug decides to pay his dues, code in the AfterUpdate event of the Dues subform runs to verify that the dues are current. If the dues are paid, the code sets the record in tblMemberAttend to paid and updates the members status to Active.
The code is as follows:
Private Sub Form_AfterUpdate()
Dim db As DAO.Database, strSQL As String
' Set an error trap
On Error GoTo AfterUpdate_Err
' See if member is paid now
If Not IsNull(DLookup("MemberID", "tblDues", _
"MemberID = " & Me.MemberID & _
" And PaidThru >= #" & Form_frmMeetings.MeetingDate & "#")) Then
' Dues table says they've paid, set it - using a query
Set db = DBEngine(0)(0)
' Set up the query
strSQL = "UPDATE tblMemberAttend Set DuesPaid = -1 " & _
"WHERE MemberID = " & Me.MemberID & _
" AND MeetingID = " & Me.Parent.MeetingID
' Execute it
db.Execute strSQL, dbFailOnError
' If current status is not Active
If (Me.Parent.MembershipStatus <> "Active") Then
' Change the status to Active
strSQL = "UPDATE tblMembers SET MembershipStatus = 'Active' " & _
"WHERE MemberID = " & Me.MemberID
' Execute it
db.Execute strSQL, dbFailOnError
End If
' Refresh the parent
Me.Parent.Refresh
' Clear the object
Set db = Nothing
End If _Exit:
Exit Sub _Err:
' Tell user about error
MsgBox "Unexpected error: " & Err & ", " & Error, _
vbCritical, gstrAppTitle
' Log it
ErrorLog Me.Name & "_Form_AfterUpdate", Err, Error
' Bail
Resume AfterUpdate_Exit Sub
End Sub
Reporting Dues Status
You can also find out which members will owe dues as of a certain date by creating a report that uses a parameter query. First, you need a query that finds the last expiration date for each member. You can find qryDuesExpire in the sample database, as shown in Figure 7-5, that returns the Max PaidThru date for each MemberID.
.gif)
Figure 7-5. A query that finds the latest dues expiration date for each member (Click picture to view larger image)
You can use this query in a parameter query that fetches member data to determine whose dues will expire as of a specific date. You can find the qryRptDuesExpire query in the sample database, and the SQL for the query is as follows:
PARAMETERS [Forms]![fdlgDuesExpireParm]![txtExpireDate] DateTime;
SELECT ([Title]+" ") & [FirstName] & " " & ([MiddleName]+" ") & [LastName] & (", "+[Suffix])
AS MemberName, tblMembers.LastName, tblMembers.FirstName, tblMembers.MembershipStatus,
tlkpMembershipStatus.DuesComp,Choose([DefaultAddress],[WorkAddress], [HomeAddress])
AS StreetAddr, Choose([DefaultAddress],[WorkCity] & ", " & [WorkStateOrProvince] & " " &
[WorkPostalCode],[HomeCity] & ", " & [HomeStateOrProvince] & " " & [HomePostalCode]) AS CSZ,
tblMembers.WorkPhone, tblMembers.WorkExtension, tblMembers.WorkFax, tblMembers.HomePhone,
tblMembers.MobilePhone, Replace(Mid([EmailName],InStr([EmailName], "MailTo:")+7),"#","") AS
Email, qryDuesExpire.PaidUntil, [Forms]![fdlgDuesExpireParm]![txtExpireDate] AS ExpireBy
FROM tlkpMembershipStatus INNER JOIN (tblMembers LEFT JOIN qryDuesExpire ON
tblMembers.MemberID = qryDuesExpire.MemberID) ON tlkpMembershipStatus.MemberShipStatus =
tblMembers.MembershipStatus
WHERE (((tlkpMembershipStatus.DuesComp)=False) AND ((qryDuesExpire.PaidUntil) Is Null Or
(qryDuesExpire.PaidUntil)<[Forms]![fdlgDuesExpireParm]![txtExpireDate]));
In addition to assembling the parts of the member name into one field, the query uses the Choose function to display the correct default address and city, state, and postal code for each member. The query uses an outer join from tblMembers to qryDuesExpire because some members might not have any dues payment records. You want to look at all member records regardless of whether the member has ever paid dues. Finally, the WHERE clause eliminates any members that have a complimentary dues status and includes members with no dues record or whose latest dues record has a PaidUntil date that is earlier than the date you entered in the parameter.
The fdlgDuesExpireParm form is a simple dialog box form that lets you enter the date you want to test, as shown in Figure 7-6.
.gif)
Figure 7-6. The parameter form referenced by the query that returns members whose dues have expired by the date entered
To run the report that displays the members whose dues will expire by the date you select, open the frmReports form (or click Reports on the main switchboard form), and click the Upcoming Dues button. The report opens the fdlgDuesExpireParm form, and the report runs when you click the Print button on that form. If you run the report with a date in February 2005, you should see the result shown in Figure 7-7.
.gif)
Figure 7-7. The Dues Expiration report (Click picture to view larger image)
The real key to the report is the query that is its record source, the qryRptDuesExpire query shown earlier. However, the report also shows you an interesting way to resolve parameters for the report with a user-friendly form. This technique depends on the fact that a report doesn't start to open its record source until after the report Open event completes. So, you can open a dialog box form in that event and hide the form after the user enters the parameter. Hiding the form allows the Open event to complete, but you must leave the form open so that the parameter in the record source can be resolved without further prompting the user. Here is the code in this report's Open event:
Private Sub Report_Open(Cancel As Integer)
' Open the parameter dialog box
DoCmd.OpenForm "fdlgDuesExpireParm", WindowMode:=acDialog
' If the user closed the form (canceled)
If Not IsFormLoaded("fdlgDuesExpireParm") Then
' Cancel this report
Cancel = True
End If
End Sub
Notice that the code checks to be sure the form is still open. If you click the Cancel button on the form, it closes. When the form is no longer open, the Open event code discovers this by calling the IsFormLoaded function that you can find in the modUtility module. When the form is no longer open, the code cancels the Open event, and the report never appears. If you execute a DoCmd.OpenReport for this report in code elsewhere (this command is executed in the Click event procedure for the cmdDues command button on the frmReports form), you'll also need an error trap in that code to trap the 2501 cancel error or ignore errors. If you don't do that, the code that issued the DoCmd.OpenReport will cause Access to display an error to the user if the user cancels the Dues Expiration dialog box.