Results 1 to 2 of 2
  1. #1
    jaeapla is offline Novice
    Windows 11 Office 365
    Join Date
    Aug 2013
    Atlanta, GA

    Lightbulb Need help printing out a report showing members who have been deactivated for unpaid dues

    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"}

    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:

    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
        MsgBox "You have pressed Esc Key or Clicked the Cancel button" & vbCrLf & _
        "                      Program Will NOT Run"
    End If
        Exit Function
        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.

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Ontario, Canada
    Not sure I follow. A yes/no T/F -1/0 field doesn't tell you much. Hard to be definitive when we can't see your tables but I'm left wondering why you don't have a InactiveDate field instead. Where it's null, the member is still active. When it's not, they're inactive and the rest of the member details should come from wherever the membership details come from. If you also had an ActiveDate field, you could have the duration of the membership as well (InactiveDate - ActiveDate), even if it cycles from one status to another and back again. As far as years and amounts owing, impossible to say without a grasp of the tables. That might be as simple as using Totals queries. If you can create a query that gives you the records you need, then you can make a report out of it. If not, then likely cannot create a report.

    A pic of your relationships (if you have created them) might help. Quickest might be a zipped copy of your db in a post.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 09-19-2017, 01:06 AM
  2. Printing Daily Report for Existing Members
    By Rabihk in forum Access
    Replies: 3
    Last Post: 08-12-2015, 07:54 AM
  3. dues paid on a month with no future dues paid
    By survivo01 in forum Queries
    Replies: 3
    Last Post: 04-13-2015, 11:36 AM
  4. Replies: 3
    Last Post: 12-29-2014, 01:04 PM
  5. Replies: 4
    Last Post: 11-18-2013, 03:23 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums