Hi Guys,



My database has 2 operations both centered around a “UNSPSC” code. This given code and 4 other fields (FedID, CommAmt, PODate, PONumber) are my entire database. To give some meaning to this UNSPSC code, it is an 8 digit unique number that links to Purchase orders in my office. The two operations my database can perform are to search a UNSPSC code to retrieve the records that correspond with this number and to input a new code if the code doesn’t exist, or if there is a new record (with a new PODate and CommAmt) to add to a given code already entered in the DB.

Another important field on the Input/Search forms is CommAmt. This is the amount the user is going to spend given the Purchase order and date (of the PO) that is on the UNSPSC form. The catch is given this UNSPSC code, a user CAN ONLY spend 20,000 in a 365 day span from any PO dates. For example:

PODate--CommAmt---CommAmtRemoved---Remaining Limit

3/1/08---$10,000---------$0----------------$10,000

10/1/08--$10,000---------$0----------------$0

3/2/09---$0-------------$10,000------------$10,000

5/1/09---$5,000--------$0------------------$5,000

10/2/09--$0-------------$10,000-----------$15,000

5/2/10----$0-------------$5,000------------$20,000

---spacing purposes

Basically what I need, to try and simplify:

One of two things(approaches)

1. To have the user enter a new PO under an existing UNSPSC where code in the form will check to see if the amount to be entered is allowed (meaning will not exceed the 20,000) budget. If this is true, the form code will also delete (or set 0 to the commAmt) of records that are 366 days or older than the PO's Date you just entered. (swap these, I think it has to work where it deletes the old ones first and then checks the amounts, unless you can set it up to check the amts of a one year span dating back from the PODate you just received)?? Not really sure what code is capable of.

For example. There's already two POs entered under UNSPSC code of 123, dated on 2/1/08 and 5/1/08. A third purchase order comes dated 2/2/09 under this 123 code. I want the form code to delete or set the CommAmt to 0 on all records with the PODate 366 days or older. Then I want the code to verify if this amt to be entered is allowed (under 20 K for the total of the POs active in the year span). Therefore, 2/1/08 would either be deleted, or it's CommAmt set to 0 for that date (whatever is easier to code I guess). Then, 5/1/08 date and 2/2/09 records would be checked to see if those two CommAmts are under 20,000. If the CommAmt is not true, it will Display an error message. Come 5/2/09, the 5/1/08 record should be deleted (or set CommAmt to 0) and 2/2/09 and 5/2/09's CommAmts would be checked if a PO was entered on 5/2/09.

So I need code to do this.

OR (and I don't know if this is possible)

2. To possibly set up a query to be run every time the database is opened that has code embedded that will set the CommAmt to 0 or to delete all the POs that are 366 days or older from Today's date. If this option is possible and used, there still would have to be code set up somewhere on the input form i'd say to make sure the CommAmt doesn't exceed 20 K.

So I need a Delete or Update query?? and also some Form Code.

This is bootleg code, but I'm trying to give even more clafication of what exactly I need:

If PODate (these would have to be dates already in the system) > (365 + PODate (the one you are about to enter))
Set CommAmt = 0 (the previous date(s) CommAmt, not the one you are entering now).

Then

If CommAmt (this would be the new CommAmt) + (CommAmt (these are already in the system) <20,000
Msg (“Approved”)

Then

If CommAmt =0
Delete Record.

Thank you everyone in advance for taking the time to look this over.

I've attached my database just in case.

Josh