What do i need to do...

[Villarreal68]

Hello Everyone,

A bit of background:
I'm working on a database for a friend. She has a Gym and she would like to change from manual labor to automation. So I'm creating a Club Membership/Fee/Payment/Invoice tracking database.

So far everything is good. I have created a membership table and a DUES table (with its necessary secondary tables to fill in the transaction type, payment method, Member type, etc. Life is good so far.

Here's my question:

What do I need to do to automatically charge the monthly fees? I've been brainstorming and can't think of how to automate the monthly fee. She charges the monthly fee on the 1st or the 15th of the month as the member chooses. So I would like to hit the database on those days and charge the right member the right charge in an automated manner.

I do have a MemberType (which has the charge amount in a linked table)and have the DuesDueOn field (for the 1st or 15th date to charge) in the Membership table. I can do a query to select the proper records to charge the right fee, the part that gets me is how do I enter the new monthly fee records into the DUES table in an automated manner.

Any help in the right direction would be appreciated. If this is already explained in another post please direct me to it. I don't even know how to do a search for what I need since I dont even know what exctly i need.

Thanks in advance!

René

 

[Alansidman]

I thinking that you would want to have an update query to the dues tables that would be triggered through a macro. That macro would execute based upon the Windows Scheduler.

There may other solutions using VBA, but initially, this one seems to be most direct to me.

This may help you to get started in thinking about how to do it. Search this forum on update queries. There is lots of information.

http://www.datapigtechnologies.com/flashfiles/schedulemacro.html

Alan

 

[Villarreal68]

Hey Alan, thanks first of for taking the time to respond (and so quickly!)

I'll look at that link and will do the research on the Update Query!

Thanks!

René

 

[dkinley]

You could create a module that will go through and create all of the necessary records using DAO/ADO/SQL only because I am against macros.

To test, hook it to a command button so that it will fire when you want to. If you are satisfied with those results, then get to playing around with an autoexec or a hidden form to test the date and if the date is the 1st or 15th, then run the module.

Depending on your implementation and/or as a safety, you might have to have some way of recording that the event happened to prevent the creation of new records over and over on the same day - so it would only happen once.

Just some thoughts ...
-dK

 

[Villarreal68]

Hi dkinley,

Thanks for the info. I like the advice on a safety net to not double post. I'll keep that in mind. As to the module recommendation, I've never worked with modules. Could you point me in the right direction to read on it?? Or should I find some info by doing a search on the forum??

Thanks again!

René

 

[dkinley]

Keep in mind, I am not advocating any particular method - I know plenty of people that use macros and like them. I also know of many of others that are pure VBA; however, it seems the more advanced the application - the fewer macros there are.

I also still do not consider an update query out of the question. Whichever method you go with there might be another validation check.

Suppose that there 1000 members of your club but only 800 of them are current members (you might not want to delete the other 200 to keep their information for future recruitment campaigns). So you run your billing function without discrimination. Now, according to the system, you have 1000 total bills waiting to be paid. You can remove this fear by having some sort of way to annotate if a member is active or not and check your criteria of the update query or DAO/SQL/ADO to ensure only active members get billed. You probably already thought of that - just making sure.

Here are some links for some VBA help on Queries/DAO/ADO/SQL. It also goes into some module stuff.

http://www.techonthenet.com/access/index.php
http://www.functionx.com/vbaccess/

I would also check out the samples on this site - lots of great stuff from the people have been posted there. I pillage it weekly for good snippets and how-to's.

Hope that helps and please post back if you hit any major roadblocks.

-dK

 

[Villarreal68]

Hi dK,
This will keep me busy for a while. And thanks for the tip on the inactive members. I already had a checkbox for these, but never hurts to be reminded of those details.

Thanks again for the information.

René