Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2005
    Posts
    144

    Unanswered: expiration date Issue

    Hello All,

    I have designed a membership database with various functions. Currently I am working with forms in which I ran across an issue that requires expert advice.

    I have member types (1year, 3year, life time) that are all active as long as the combo box(with options: “active” or “expired”) is set to “active”. After the membership term expires, their status should be switched to “expired”, and only re-activated after they pay the membership fees. But manually clicking on the “expired” option 400 members is very time consuming, not to mention inefficient. Yearly and 3year members are the only types that suppose to expire.
    How can I set an expiry date for the members, so that they get deactivated automatically, and I can manually activate them when necessary?
    Thanks
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    You can run a manual or automated process to do what you require ... At what point (or granularity) do you want the expiration to occur? The day after the expiration date? At the end of the month (grace period)? It's a simple query to set your expiration status dependent on if the user is paid up or not ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jul 2005
    Posts
    144
    I would go by the automated process; I want the yearly members and 3year members only to have their membership expire on the day after the expiration date, but the lifetime members should not be effected, since they do not have an expiration date.
    Once the person has paid, I want to manually activate the member individually, by using the combo box(active/expired).

    Thanks

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Here is your sample Database back to you with some modifications. Pay special attention to the Form's OnCurrent event and the OnClick events for the added buttons.

    I have utilized VBA's DateDiff and DateAdd functions for Date comparisons. The Records are sorted out via Filters in this returned sample. Usage of the two above mentioned functions should provide some assistance to you for when you decide to go with full automation.

    This should give you a start of some sort I hope...

    .
    Attached Files Attached Files
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  5. #5
    Join Date
    Jul 2005
    Posts
    144
    Cyberlynx,
    Wow that is some impressive code work there, it looks very professional now. I like the option where it turns expired and highlights it as well, however, I am not sure why 3year expires if the membership date is 1-1-2005??

  6. #6
    Join Date
    Nov 2003
    Posts
    1,487
    The 3 year expires don't work properly because......being as dumb as I am, I forgot to implement anything to cover the Three Year membership

    In any case.....now fixed (see the additional code).

    I also took the liberty this time to activate E-Mail and Auto Dialer buttons. I also cleaned the code a little by utilizing the ApplyFilter method. It just shows you that there's more than one way to skin a cat in Access.

    Reworked Sample Attached:

    .
    Attached Files Attached Files
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  7. #7
    Join Date
    Jul 2005
    Posts
    144
    Thats ok CyberLynx, LOL I really do appriciate all your hard work. I wonder if you ever plan to teach class?

    There is one tiny little thing I am trying to fix which is driving me crazy: I have this query setup for the total members which displays total members on top

    SELECT Count([Address1ID]) AS TOT
    FROM Yearly
    WHERE (((Yearly.Status)="active"));

    Problem is, for some reason it doesn't count the number of members that it pulls up in the report, instead, it counts all the members in the database.

    Any suggestions?

  8. #8
    Join Date
    Nov 2003
    Posts
    1,487
    Here (again) is your sample DB updated. I added another Tab page to the Tab Control named Database Details. To accomplish this task I simply used the DCount function. Search for it in the code to see how I did this.

    I also added a couple of ListBoxes to the page. One lists all the ACTIVE members and the other lists all the EXPIRED members. If you double-click on any one of the names in either list, it will display that member within the General Information Tab. Look at the code carefully to see how all this is done. You may find it usefull (for a start).
    Attached Files Attached Files
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  9. #9
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by CyberLynx
    The 3 year expires don't work properly because......being as dumb as I am, I forgot to implement anything to cover the Three Year membership

    In any case.....now fixed (see the additional code).

    I also took the liberty this time to activate E-Mail and Auto Dialer buttons. I also cleaned the code a little by utilizing the ApplyFilter method. It just shows you that there's more than one way to skin a cat in Access.

    Reworked Sample Attached:

    .
    What version of Access did you use? I can't open in A2K OR A2K3? Just wanted to check out what you did.

    BUD

  10. #10
    Join Date
    Nov 2003
    Posts
    1,487
    The application was worked over using MS-Access in MS-OfficeXP package in the Access 2000 format (not the 2002 format).
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  11. #11
    Join Date
    Jul 2005
    Posts
    144
    You continue to impress me CyberLynx, Very nice coding job here.

    I have to appologise here for not being clear on my problem.
    I have two reports i run every so often in those reports i have a criteria set which asks me to type yearly, lifetime or three year and then after report prints out it also has total member quantity query on top of the page which counts whatever membertype i am typing it in, however right now its only counting yearly members.
    I was wondering is there an easy way to just count the yearly members, lifetime and three year separetly on top of my reports?

    Member types total count is....

    yearly 400
    three year 25
    lifetime 70

  12. #12
    Join Date
    Nov 2003
    Posts
    1,487
    Oh...OK. Then simply place these lines into the Reports appropriate TextBox Control Source property:

    For 'Yearly':
    =DCount("*","[Yearly]","[Status] = 'Active' AND [MemberType] = 1")

    For 'Three Year':
    =DCount("*","[Yearly]","[Status] = 'Active' AND [MemberType] = 3")

    For 'Life Time':
    =DCount("*","[Yearly]","[Status] = 'Active' AND [MemberType] = 2")

    Because of the way you have set up your MemberType Field in Table, allthough it appears as a Text Type, it's actually a Number Type and therefore we need to supply a number to MemberType within our DCount WHERE clause rather than the suspected Text of either "Yearly", "Three Year", or "Life Time".

    Hence, according to your table:

    Yearly is 1
    Three Year is 3
    Life Time is 2

    If you want to also display TextBoxes within your Report that will indicate the number of Expired Members, you would then supply this line into the Control Source for your TextBox (i.e.: Expires for Yearly Members):

    For 'Yearly':
    =DCount("*","[Yearly]","[Status] = 'Expired' AND [MemberType] = 1")

    If you want to display overall Total for Yearly Members regardless of whether or not they are Active or Expired then you would use (for Yearly Members):

    For 'Yearly':
    =DCount("*","[Yearly]","[MemberType] = 1")
    Last edited by CyberLynx; 03-12-06 at 16:45.
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  13. #13
    Join Date
    Jul 2005
    Posts
    144
    Sorry about the confusion here. I went ahead and renamed my table to members instead of yearly. Let me explain a little better: I have only two reports that I run using this query, members by MemberID and by LastName.

    SELECT Members.*, MemberTypes.MemberType AS MemberTypeDesc
    FROM MemberTypes INNER JOIN Members ON MemberTypes.MemberTypeID = Members.MemberType
    WHERE (((MemberTypes.MemberType)=[Yearly, Three Year OR Lifetime]) AND ((Members.Status)="Active"));

    As I open report it prompts for yearly, lifetime or three year and whichever one I type I get the necessary records accurately. On top of that report I have a text box that counts the total number of members of whatever membertype I retrieve (lifetime, yearly or three year). All I want to know is the total number of members active in yearly, lifetime or three year, depending upon what I type.

    I hope I was clear enough this time

  14. #14
    Join Date
    Jul 2005
    Posts
    144
    Anyone wants to take a shot at it, i would really appreciate it !!

  15. #15
    Join Date
    Nov 2003
    Posts
    1,487
    SELECT Members.*, MemberTypes.MemberType AS MemberTypeDesc
    FROM MemberTypes INNER JOIN Members ON MemberTypes.MemberTypeID = Members.MemberType
    WHERE ((MemberTypes.MemberType='Yearly' OR MemberTypes.MemberType='Three Year' OR MemberTypes.MemberType='Lifetime') AND (Members.Status="Active"));


    OR

    SELECT Members.*, MemberTypes.MemberType AS MemberTypeDesc
    FROM MemberTypes INNER JOIN Members ON MemberTypes.MemberTypeID = Members.MemberType
    WHERE ((MemberTypes.MemberType=1 OR MemberTypes.MemberType=3 OR MemberTypes.MemberType=2) AND (Members.Status="Active"));

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •