Ladies and gentlemen i thank you for your assistance in my system that i have been developing. my organisation is very impressed by it.

i was thinking of a situation whereby my system could give a notification when a person's copies he subscribed run out. i mean when the copies remaining fall to zero it has to notify that so and so dont have copies remaining. and it doesnt have to continue subtracting into the -1, -2, -3 etc. it has to stop at zero.


Okay, if you are talking about subscriptions, i.e. magazine subscriptions or something of the like. Would you not want to warn the customer a couple of issues prior to recieving their last issue that their subscription is running out???

If so, then you might want to do something like this...

strSQL = "SELECT * FROM tablename WHERE fieldname <= 3"
strSQL = "SELECT * FROM tablename WHERE fieldname = 3"
strSQL = "SELECT * FROM tablename WHERE fieldname = 2"
strSQL = "SELECT * FROM tablename WHERE fieldname = 1"

Where 3 equals the first notification to the customer to say something like "Hey, your subscription is running out! You will only recieve two more issues! RENEW NOW for the discounted price of ..."

Where 2 equals the second notification... "Only one issue left!"

And where 1 equals "This is your last issue..."

Then on your side, not only have you recieved these three notices, you would also recieve the final notice, i.e. WHERE fieldname = 0. Then when it comes to your update, you would want to modify your update command with a where clause... WHERE fieldname > 0, thus no negative numbers...

Good Luck

thanx a lot vb5prgrmr. you actually solved a problem of negative numbers in my database. but how do i notify my clients of the status of their account. i was thinking i could use an email sent to their address but how do i automate VB to send emails..or is there any other way.

For sending e-mails with vb, it is possible, but because of all the spammers that were created using MS products, MS has clamped down on the technology to automate that technology. So if you search the web with your friends, you may run across some solutions that worked years ago but are no longer viable today so you will need to test it before you release it.

As for the rest,… Well that all depends upon current processes and the amount of integration you are able to achieve with those current processes....

Previously, I showed you how to pull records based upon how many issues they have left (see above post) and those should be done prior to the update so they/you act upon current status of your subscriptions.

Now, as for how to notify your customers of the status of their account....

Do you use VB to print the mailing labels? If so, you could also use VB to switch printers and print out a reminder card that could be stuffed/inserted into package.

Another way would be to send the customer an instruction card on how to decode the seemingly meaningless numbers that appear on their mailing label, that is if you are doing the printing of the mailing label, and those numbers would be something like 1/12, which would mean this is the first of 12 issues. Or in reverse 12/1, which would mean the last of 12 issues. (Meaning, you would put this information in one of the corners of the mailing label so it would not interfere with the USPS address recognition system, which is an OCR (Optical Character Recognition) system.)

Then again, there might be other ways in which to do this, but as we are unfamiliar with your processes, it is kind of hard to tell.

Good Luck

Try using scheduled jobs, if your database is ORACLE.

can you put up the bit of coding you have done sp that it is easier for us to understand what exactly you have done and where the error lies.

lemmi finish what im working on now. ...i will give it when im done.
as pertaining to the question i was asking on the best way to do it. it was a situation whereby i wanted to know if anyone knew how i could do it.