Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2004
    Posts
    34

    Unanswered: Manually Incrementing Numbers...

    Hi,

    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.

    e.g.

    OrderID* OrderLine*
    123 01
    123 02
    124 01
    124 02...etc.

    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...

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    HI

    Try this after for before update statement

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
        If Me.NewRecord Then [OrderLine] = DMax("OrderLine", "YourTableName", "OrderID = " & [OrderID]) + 1
    End Sub
    This assumes the OrderLine and OrderID fields are available (or their bound controls) to the form creating the new record & they are both long numbers.

    Your composite primary key should be OK with this.

    Hope this give you some ideas.

    MTB

  3. #3
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    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.
    ghozy.

  4. #4
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    though if it is not a critical application, you can follow Mike's method, which is very elegant and simple.
    ghozy.

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi again

    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!

    MTB

  6. #6
    Join Date
    Jun 2004
    Location
    Florida, US
    Posts
    521
    an atomic transaction is series of SQL commands executed together. I see you know what atomic transaction is but probably didn't hear the term before.
    ghozy.

  7. #7
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hi

    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.

    Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •