I keep the records for a governmental retired employee association, and each year, if dues have not been paid for the past three consecutive years, we inactivate the member (and still keep them in the database as inactive). I used the following code in a query only, which worked but didn't give me any information. I would still have to go through and verify that each record was inactivated. The code for the query was as follows (FYI: Prospective members and Beneficiaries don't owe dues; however if you didn't pay 2021, 2022 or 2023 dues, you will be inactivated):
{ 'NAME OF QUERY: "qryDeactivate Unpaid Members" -- Main Database is MEMBERSHIP_LIST"}
Code:
UPDATE Membership_List SET Dont_Show_At_All = -1, Active = 0, Newsletter = "N", Directory_Preference = "N"
WHERE (MEMBERSHIP_LIST.Paid_Thru = "2020") AND (Active = -1) AND (Department <> "Beneficiary") AND (Prospect <> "P");
I tried to improve the functionality by incorporating it into an Access VBA module where I warn the user to be careful since he is changing files and then use the following code in Access VBA to provide some of the necessary items to make it ultimately a PDF file - here is that code which included the query from above:
Code:
Option Compare Database
Option Explicit
'------------------------------------------------------------
' modDeactivateUnpaidMembers
'------------------------------------------------------------
Function modDeactivateUnpaidMembers()
Dim strFileName As String
Dim strPath As String
Dim rptName As String
Dim MyDate
Dim mystr As String
MyDate = Date
mystr = Format(MyDate, "mm-dd-yyyy")
strPath = CurrentProject.path & "\"
rptName = "Deactivated_Members_Report"
strFileName = "FCREA Deactivated Members List as of " & mystr & ".pdf"
On Error GoTo DeactivateUnpaidMembers_Err
If MsgBox("Are You Sure You Want to Run This Program?" & vbCrLf & _
" Records Will Be Changed" & vbCrLf & _
" Press [OK] to Run the Program" & vbCrLf & _
" ***** Back Up Database First! *****", vbCritical Or vbOKCancel, "Proceed CAREFULLY!") = vbOK Then
DoCmd.SetWarnings True
DoCmd.OpenQuery "qryDeactivate Unpaid Members", acViewNormal, acEdit
Else
MsgBox "You have pressed Esc Key or Clicked the Cancel button" & vbCrLf & _
" Program Will NOT Run"
End If
DeactivateUnpaidMembers_Exit:
Exit Function
DeactivateUnpaidMembers_Err:
MsgBox Error$
Resume DeactivateUnpaidMembers_Exit
End Function
Where I need help is basically to keep an audit trail of those members whose records I have inactivated, listing out membNo, Whole Name, Department, years owed, amount owed, no newsletter, no membership directory, no emails (from Association), etc. in a printed-out report The report would be an audit trail for me and the organizatrion. I'm at a loss as to how tomake this into a report and the a QDF (which I do know how to do. Any other questions, hints or solutions would be greatly apprerciated. Thanks.