Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Change number in table (Access 2000)

    I have an appended order from a foreign database with an autonumber that does not follow the autonumber of my table. In my example my last autonumber is 142929 while the appended row is 142929. I want to change this last number to the norml autonumber of the table. is it possible?
    Attached Files Attached Files

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

    Re: Change number in table (Access 2000)

    You cannot change an AutoNumber value after it has been assigned. You could have set it when you appended the records.

    But it doesn't matter, an AutoNumber is just an arbitrary unique number. It will continue from 142930 when you add new records normally. The gap between the numbers shouldn't cause any problems.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change number in table (Access 2000)

    In that case I should not append the order at all. Since it will create havoc in my database.I think the way to do it is to create a new order and then update and not append order 142929 to this new order. Am I right? I think to use the following to create a new order:

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    ' Create a new order and obtain its OrderID
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Orders", dbOpenDynaset)
    rst.AddNew
    Or is there any simpler way to add the order 142929 to my database with a regular orderid, being the nex order in my table?

    I think i must start a different thread since clealry i must abandon my first intention to append

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

    Re: Change number in table (Access 2000)

    You can specify the value to be used for OrderID in the append query.

    But why do you say "Since it will create havoc in my database"? Access has no problem with gaps in the AutoNumber sequence.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change number in table (Access 2000)

    The table orders in my database uses only one autonumber and the same database from the other company uses a different autonumber in order to differentiate. The last number in our autonumbers is 2189. I must not change this order and the next number should be 2190,since it is tied up with our invoices. Actually I succeeded in creating a new order on the table orders. Thus i get a new number and will not use the number 142929. The number 142929 remains in the table orders1 and the details in the table orderdetails1.But how coud I create the order details table and how to append the fields from the table order details1? After i append or update the data i will detele the tables with endings 1.

    The new ordr is created in the following way :
    Private Function CreateOrder()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim lngOrderID As Long
    Dim StrSQL As String
    On Error GoTo ErrHandler

    ' Create a new order and obtain its OrderID
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("Orders", dbOpenDynaset)
    rst.AddNew
    rst!Customerid = 124
    rst!SubOrder = True
    rst!Audit = True
    rst!bankid = 1
    rst!PaymentMethodID = 1
    lngOrderID = rst!orderid
    rst.Update

    End Function

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

    Re: Change number in table (Access 2000)

    If the values of the OrderID field have a specific meaning, it shouldn't be an AutoNumber field.

    You can open a recordset on the Order Details table and use lngOrderID when adding records to it.

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Change number in table (Access 2000)

    I have succeeded ! Thank you !

Posting Permissions

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