Autonumber - Order and Line Items tables

[Guest]

My database has a customer orders table - order no is pk and autonumber. I
also have a line items table as each order can have many line items, the
orderNo is the fk in this table. I need the line item no to auto increment,
but need this to start at 1 for every order. e.g order no 1 has lines I, 2
and 3, order2 has line 1 and 2, order 2 has line 1 etc... hope this explains
what I mean! any help would be appreciated!

 

[Douglas J. Steele]

You cannot use an Autonumber data type for that.

You'll have to "roll your own". If this is a single user system, in the
BeforeInsert event of the form, determine what the highest used number is,
and increment it. If it's multi-user, you may need to use a more elaborate
approach.

 

[Guest]

Douglas, My database will be multi user....any help will be appreciated!

thanks

 

[Rick Brandt]

Douglas, My database will be multi user....any help will be appreciated!

But what are the chances that two users will be working on the same order? Near
zero I would guess. In that case BeforeInsert should still be fine. If you are
worried about it just use BeforeUpdate instead and test for NewRecord (since
BeforeUpdate can fire more than once on a record).

The difference is that BeforeUpdate ends with the record being committed to
disk. It is the only event that has that feature and thus has the smallest
"collision window" for multiple users.