I have a small problem which I am hoping someone will be able to help with.
I need to hold details of orders, but each order can contain multiple items (or lines). Hence I envisioned having the primary key a composite of an OrderID field, and OrderLine field.
I had thought of using an Autonumber field for the OrderLine field, but then realised that this wouldn't restart when a new OrderID was entered - not good!
Hence I was hoping someone could tell me how to manually increment numbers - i.e. check what the last OrderLine number relating to the relevent OrderID, and then increment once.
I assume this would have to be coded, and would be most appreciative of any advice on how to achieve this...
it is in fact very troublesome work. what if two users tries to enter a new data at the same time? they both would get same number. I believe you should keep the autonumber value in a seperate table, and after you get new ID, increase it in same atomic transaction. you should make sure increasing the autonumber after you get one in an atomic transaction. other wise there is always chance of 2 users getting same autonumber value.
Yes it would fail due to the composite primary key (isn't that wat it is for!)
You just try again !?
I have certainty used a variation of this but had to used triasactions to due to the use of update and append queried on different tables to trap any error of this sort and rollback those queries that had succeeded. I must confess I do not know what an ATOMIC transaction is ?
It's just that with autonumber you lose control of the number!
The way I read kdm’s description is that we are using only one table here (forgive me if I’ve mis-understood). I would be inclined to follow the more traditional method of having an OrderHeader table and and OrderDetail table.
OrderHeader holds info like OrderNumber (PK), CustomerID, DeliveryDate
OrderDetail holds info like OrderNumber (PK,FK), LineNumber (PK), Product, Quantity, Value
The tables are linked by the FK and referential integrity enforced.
Create an entry/edit form that only allows access to enter/edit order details via the order header i.e. OrderHeader form with OrderDetail as a subform. So if a user creates a new order the grabbing a unique OrderNumber via auto-number, the line numbers can be generated via Mike’s code without risk (since the user has exclusivity to OrderNumber and OrderNumber and LineNumber form the PK for OrderDetail). Providing users are only allowed to use the form (and not the back door) then everything should be ok.