Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    appending with the next number (Access 2000)

    I am appending 2 equal tables using the following function

    AppendToTable "customers1", "customers", "customerID", "customerid"
    Is it possible to append but preserving the old customerid order in the table customers? For example the customers in the table "customers1" have their numbers beginning with 9000. The last number in the table "customers" is 2400.Is it possible to append the customers but taking on new customer numbers, for example 2401,2402 etc?




    Function AppendToTable(SourceTable As String, TargetTable As String, ParamArray LinkFields())
    ' AppendToTable "tblSource", "tblTarget", "FieldName1", "FieldName2"
    Dim StrSQL As String
    Dim i As Integer
    On Error GoTo ErrHandler
    StrSQL = "INSERT INTO [" & TargetTable & "] " & _
    "SELECT * FROM [" & SourceTable & "] " & _
    "WHERE Not Exists " & _
    "(SELECT * FROM [" & TargetTable & "] As T " & _
    "WHERE "
    For i = LBound(LinkFields) To UBound(LinkFields)
    StrSQL = StrSQL & "(T.[" & LinkFields(i) & "]=[" & _
    SourceTable & "].[" & LinkFields(i) & "]) AND "
    Next i
    ' Get rid of last " AND "
    StrSQL = Left(StrSQL, Len(StrSQL) - 5)
    StrSQL = StrSQL & ")"
    ' Execute append query
    CurrentDb.Execute StrSQL, dbFailOnError
    ExitHandler:
    Exit Function
    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Function

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: appending with the next number (Access 2000)

    If you dont append the customerID field this will happen, it will start the numbers from 1 more than the last number used.

  3. #3
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: appending with the next number (Access 2000)

    Thank you. But how can i refer to the customerid in this function ?

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: appending with the next number (Access 2000)

    I wouldn't do it the way you ae doing it. why have you built a functio where you could simply build a append query manually and run that?

  5. #5
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: appending with the next number (Access 2000)

    Thank you . I cannot understand how to build the query so as not to append the number from products1.The following query does not give me any result. I do not know whether this is possible at all.How is it possible not to select productid ?

    INSERT INTO products1 ( Productid, grade )
    SELECT products.Productid, products1.grade
    FROM products INNER JOIN products1 ON products.Productid = products1.Productid;

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

    Re: appending with the next number (Access 2000)

    This query attempts to insert records into products1 with a productid that already exists in the table.

    Can you try to explain what you are really trying to accomplish? What is your goal?

  7. #7
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: appending with the next number (Access 2000)

    Thank you. I see i couldnt explain well my goal. I have 2 tables customers and customers1. Each of them follow different autonumber.Lets us say the table customers begins with 1 and has reached 280. Table customers1 begins with 4000 and has reached 4600. I want to append the customers from table customers1 to the table customers, but these customers should acquire a new customerid number.For example
    customerid name
    4000 aaaa
    4001 bbbbb

    when i append them to the table customers, i want that they receive a new customerid number,relevant to the autonumber of this table and not the numebr 4000 or 4001.

    I do not know if this is possible.The reason for my efforts are that we cut short one office , we must carry out a take over and we will have to transfer all the customers to the other office.But each office begins with different autonumber.

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

    Re: appending with the next number (Access 2000)

    An AutoNumber field is just a convenient unique identifier for a record. It doesn't have any intrinsic meaning, so it doesn't matter if there are gaps in the sequence.

    Moreover, if you don't transfer the CustomerID, all records in other tables that refer to CustomerID will contain incorrect values.

Posting Permissions

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