Hi, I am in a bit of a jam here. I have an order form, that is accessed by two or more users

in different computers. The form is written in VB2k5, and it is writing to an Access database

on two tables, tblOrder and tblDetOrder. The tblOrder fills in the main part of the Order,

such as Order Number, Customer, Sales, etc. While the tblDetOrder fills in the detail parts,

such as stock code, quantity, price, etc.


The tblOrder primary key is a Long integer, but not autonumber (to take away the problem of

skipped numbers if the operator cancells/deletes, but generated by the code by querying the

Max(Number). Both the entry to tblOrder and tblDetOrder is saved silmutaneously with a

transaction, when the operator hits the OK button. I already done a precaution in the code,

where the code will checked if the number is saved, if it is, and there is no rows of the

details shown in the datagridview, it automatically researches for the max(number).


However, I still gets errors from the operators that will not be there if the form is only

accessed by a single person. So, apparently collisions happen. Can anybody give a better

suggestion than what I have done?

There's really no good way to prevent this completely. A common first step is to write the number to the database immediately when you request it (which is how autonumber works) but results in missed indexes on a cancel (as you said). The only thing I can suggest is to have the check for Max(Number) as close to the insert statement as possible (right in the stored proc would be ideal). You might get some grumbling from users about seeing 0 as the number on the screen until they do the insert, but it might get you closer to elminating the exception.

Another thing you might try is to put a while condition in there to check for the specific exception that occurs when a conflict is found and then just increment Number and attempt to save until it succeeds.

Your best solution is to use an AutoNumber field: that's what it's there for.

---

Stephen J Whiteley

You could us an Autonumber or a roll your own autonumber from a flat network avaialable counter file.Thebinary file is extremely fast and havetested it from several seprate threads and it never displays a dupe. If you attach it or an autunumber to the Before -Insert property of the Access form then it will generate a number a soon as the 1stfield in the record is "dirtied"

There's really no good way to prevent this completely. A common first step is to write the number to the database immediately when you request it (which is how autonumber works) but results in missed indexes on a cancel (as you said). The only thing I can suggest is to have the check for Max(Number) as close to the insert statement as possible (right in the stored proc would be ideal). You might get some grumbling from users about seeing 0 as the number on the screen until they do the insert, but it might get you closer to elminating the exception.

Another thing you might try is to put a while condition in there to check for the specific exception that occurs when a conflict is found and then just increment Number and attempt to save until it succeeds.

Your best solution is to use an AutoNumber field: that's what it's there for.

---

Stephen J Whiteley

OK Guys, thanks

You could us an Autonumber or a roll your own autonumber from a flat network avaialable counter file.Thebinary file is extremely fast and havetested it from several seprate threads and it never displays a dupe. If you attach it or an autunumber to the Before -Insert property of the Access form then it will generate a number a soon as the 1stfield in the record is "dirtied"

Never say never. Do not rely on the fact that an object that isn't explicitly thread-safe accessed from multiple threads will not cause a problem: it will.

---

Stephen J Whiteley