Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2007
    Posts
    16

    Unanswered: Order Id number Increment

    Hi All,

    I have come across a small problem if someone can help me out,
    basically i have an ordering form which when an order is put through it the order will get saved and go to the next order number say order 24 has been placed so next order number will be order number 25, i know i can use Autonumber but the problem is that the order gets deleted and the end of the day i have used some variables and then managed to increment it by 1 each time only problem there is that if the form closes it goes back to first record in database and access starts saying you cant add to this record etc.

    so if anyone has an idea on how to do this i would be greatfull, oh and the reason why i am not using autonumber is when the order does get deletted the record number doesnt go back to 1 which i need it to.

    Thanks in advance.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Autonumbers are arbitrary integers and should be used as such. They have no meaning and are used when there is no other alternative for a primary key, to uniquely identify a record in a relational database.

    What you're suggesting here is that your field has meaning outside of this - but I would like to question your reasoning.

    Is your order number used to uniquely identify each order?
    If so, why is it essential for the order number to reset to 1 after deletion?
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by georgev
    If so, why is it essential for the order number to reset to 1 after deletion?
    that's not a feature, that's a bug

    i think he's saying the number goes back to 1 and he doesn't want this
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Nov 2007
    Posts
    16
    Hi Guys,

    Thanks for rhe replys,

    Right the reason why it needs to go back to one at the start of each day is that it is being used as a restaurant ordering system is if it did not then the employee would ahve to end up shouting order number 1348 is ready etc so there fore it needs to reset back to one which is not the problem i have managed to do this but sometimes the dabase might get closed accidentally or they might close it to use another programme but whe it opens up again the order id starts at 1 again with out deleting the records so it has records there and cant overrite them so if that happens half way through the day then you have to hit the reset button.

    And the autonumber the problem there is when you do delete the orders they get deleted that is fine and you can close and open the database again and wit will go to the next order id number but when you delete all teh records the autonumber is still at the last point i have heard of a way of compacting and repaiaring the database but i think this will end up a longer procedure.

    Thanks all, hope this makes some sense.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Aha, that sounds like a good enough reason to me!

    I suggest that instead of storing the number in memory - you store it in a table, preferably with the date next to it to

    OrderNumbers(OrderNumber, dateCreated)

    Or something similar.
    You could then select the highest order number for any given date (i.e. today) and add 1 to it when needed.
    Code:
    SELECT Max(OrderNumber)     As [lastOrder]
         , Max(OrderNumber) + 1 As [newOrderNum]
    FROM   OrderNumbers
    WHERE  dateCreated = Date()
    George
    Home | Blog

  6. #6
    Join Date
    Nov 2007
    Posts
    16
    HI Georgev,

    Thanks for the reply, yes the data is already stored in a table i have 2 tables 1 called orders and the other order details which are linked togeather i think the way you suggested might work it will have to be a select statement or of some sort and i think that might work if i have that on the form load or activate event.
    Thanks i will try this out later. cheers.

Posting Permissions

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