[Originally posted by Chuck Phillips]
I have created a application for a local corporation written in Access 2000. This application has 2 different parts.
1.) A application that records all sales made in the cafeteria. We will call this the Cafeteria.
2.) A application that will allow employees to purchase items that are created at this corporation. We will call this the Employee Market.
When a employee makes a Cafeteria purchase, it is immediatly turned into a invoice, a unique invoice number is retreived from a access table, and is marked as paid.
When a employee Makes a purchase in the employee market, it is created as a sales order. At a later date, the order is filled, and it is converted into a invoice, a unique invoice number is retreived from the SAME access table, the employee picks up his/her goods, and leaves.
This system has been up and running for the past 2 months. Recently, a problem has arisen. When the employee market order is filled and converted to a invoice, it is sometimes 'smooshed' or merged with a order made in the cafeteria under the same invoice number. I have been trying to find some way to successfully lock the table where the invoice numbers are retreived from (to prevent 2 invoice numbers that are the same to be given out) with no success.
[Originally posted by Andon K.]
It would be more helpful if you give more details about your method of generating invoice numbers and if possible, a snipet of code.
But if the system worked OK for the past couple of months, try to find what changed in the environment of the system, or in the way it is used. For example, the invoices were originally entered on two machines, and now there are 5 or 6 of them. I hope it is because the choice in your cafeteria and offer of items on sale became so attractive that the number of invoices suddenly sharply raised, so your method of generating invoice numbers is not good enough in the new conditions.
[Originally posted by JPicasso]
Why not use strings for order numbers?
I.e. #####C˙ (for cafeteria orders)
and˙ #####EM˙ (for employee Market orders)
or somthing similar?
otherwise, just have Access return an autonumber ID from some "dummy" database.
make a table with one table with one field.
Call it "INVnumber" and make it an autonumber field.
then have either the invoice or sales order create a record and get the nuymber from the recordset.
[Originally posted by Ed Ardzinski]
I do a lot of development in a Transactional Database as opposed to a relational Database...where I have to pay more attention to details like Unique ID's for records.˙ What I do often is just when I'm about to create a new record I find out the last ID number in the table and add one to it.
So you can try to query the DB table at the moment you want to add the new record "Select Max(InvoiceID) From Invoices" and use that result plus 1 to give you your new ID.
It may not be fool proof, but I work on a DB/Network with about 40 users and the method hasn't failed me yet.
[Originally posted by Anantha PAdmanabhan]
The solution is BeginTrans method, followd by CommitTrans or RollBackTrans methods. This solution will guarantee good data even if the cafeteria has 2 workstations at a later date.
Also, try to bunch your file updates. This is basic concurrency theory.
Hope this helps.
End of mail.
[Originally posted by Nick Williams]
Yet another method (because we can never have to many methods) is to have a table that is used only to hold the last invoice number. By reading this and locking the record you can then add 1 to it knowing that only one person at a time is generating the next invoice number. You must then close the recordset to ensure that the next person can now generate their invoice number. Hope this helps.
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center