Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Update OrderID (Access 2003 SP2)

    I had to restructure our database just in the last 2 weeks or so, and had to import all orders from November 1 up to now to correspond with our fiscal year. The way I used to have my OrderID was a 'number' format and automatically set with code to add '1' to the maximum current OrderID. I had a 6-digit number that corresponded to the type of order it was, for example, an inmate order would be 840216, an international order would be 830158, with the first number indicating the year, the second number indicating the order type and the other 4 were simply sequential. I no longer need to differentiate order types by OrderID and I have about 550 orders since November that I want to start off with OrderID '1'. Is there some way that I can update a table like this? If not, I will have to update all order numbers manually.

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update OrderID (Access 2003 SP2)

    Do you need to keep all your old orders. If not, I'd rename the current table to Name_Archive and create a new Order file using the structure only option. Then I'd change the OrderId to autonum and load in the new data.
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update OrderID (Access 2003 SP2)

    I think it would be possible, but we'd need to have more detailed information about the structure of the table and the way OrderIDs are currently being assigned.

  4. #4
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update OrderID (Access 2003 SP2)

    Edited by HansV to replace zip file with the picture it contained (converted to .png format to reduce file size)

    This is the code that assigns a new Order Number. Private Sub Form_BeforeInsert(Cancel As Integer)
    If IsNull(DMax("OrderID", "tblOrders")) Then
    Me.OrderID = 1
    Else
    Me.OrderID = DMax("OrderID", "tblOrders") + 1
    End If
    End Sub

    The structure is attached.
    Attached Images Attached Images

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update OrderID (Access 2003 SP2)

    There doesn't appear to be a date field in the table, so it will be difficult to update just the 500 orders that were entered since November, unless there is another way to identify them.

  6. #6
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update OrderID (Access 2003 SP2)

    Oh, sorry. This table actually has only orders since November, having imported them all from the previous database into all new structure, so all I need is to update them. Actually, the primary key is on tblOrders with inner join on OrderID.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update OrderID (Access 2003 SP2)

    But you can't just update OrderID in the Order Details table - you'd violate referential integrity. Instead, make sure that cascading updates have been turned on for the relationship between the Orders and Order Details tables, and update the OrderID field in the Orders table using code:

    Sub UpdateOrderIDs()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim lngID As Long
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblOrders", dbOpenDynaset)
    Do While Not rst.EOF
    lngID = lngID + 1
    rst.Edit
    rst!OrderID = lngID
    rst.Update
    rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    End Sub

    The code needs a reference to the Microsoft DAO 3.6 Object Library (in Tools | References).

  8. #8
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update OrderID (Access 2003 SP2)

    Thanks a lot, Hans. I don't know what I'd do without the help of the helpful folk on Woody's Lounge.

Posting Permissions

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