Results 1 to 5 of 5

Thread: [RESOLVED] duplicate button with id feild that wont change

  1. #1

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    54

    Resolved [RESOLVED] duplicate button with id feild that wont change

    i am trying to change a primary key that is the same as the previous record i duplicated and i keep getting an err.
    Name:  1.PNG
Views: 257
Size:  5.3 KB
    Name:  4.jpg
Views: 259
Size:  12.4 KB
    Name:  3.PNG
Views: 267
Size:  5.9 KB
    the ID field is an auto number field
    here is the vb code i have created for my duplicate button
    Code:
    Private Sub Duplicate__Record_Click()
    Dim sql As String
    Dim onn As String
    Dim Answer As Integer
    Dim onn1 As String
    
    onn1 = [ID].Value
    onn1 = Format(onn1 + 1)
    onn = [order number].Value
    'MsgBox onn
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Delete * From Duptbl"
    DoCmd.RunSQL "insert into Duptbl select * from [Order Master] where [Order Number] = '" & onn & "'"
    onn = Format(onn + 100000000, String(12, "0"))
    DoCmd.RunSQL "update Duptbl set [Order Number] = '" & onn & "' "
    DoCmd.RunSQL "update Duptbl set [ID] = '" & onn1 & "' "
    DoCmd.RunSQL "insert into [Order Master] select * from Duptbl"
    
    
    
    DoCmd.OpenForm "Order Master", acNormal, , "[Order Number] = '" & onn & "'"
            Me.Refresh
    MsgBox "record added to order master with order number " + onn
    Attached Images Attached Images  
    Last edited by cubsm22p; Feb 19th, 2018 at 02:25 PM.

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: duplicate button with id feild that wont change

    If that column is the primary key then it can't contain duplicate values by definition. If it really does contain a duplicate value then your database is broken. As the error message says, you can't set the value in an AutoNumber column. The whole point of that data type is that the database generates the value. Again, if your database has generated duplicate values then it is broken. Stop using that database and either go back to a backup that isn't broken or create a new database and migrate the data without the duplicate.

  3. #3
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: duplicate button with id feild that wont change

    one can set an autonumber field to any number within the long range
    an autonumber field can have any number of duplicates
    but: if one places an unique constraint on an autonumber, one can not expect to have duplicate autonumbers
    after all an autonumber is nothing but a long integer
    but access does not allow to update an autonumber field

    cubsm22p,
    why do you use a surrogate key as the primary key ?
    after all, there is a perfect natural key : the order number
    ( unless of cource the very same order can have different order numbers ???)
    do not put off till tomorrow what you can put off forever

  4. #4
    Frenzied Member
    Join Date
    Jun 2014
    Posts
    1,084

    Re: duplicate button with id feild that wont change

    cubsm22p,
    you probably have already solved that problem, but anyway:
    consider the following
    an autonumber is nothing but a long integer
    but access does not allow to update an autonumber field
    but access does allow to update a long integer field

    so what would be the solution to that problem ?
    iow:why is the ID field of table Duptbl an autonumber and not a simple long integer ?
    do not put off till tomorrow what you can put off forever

  5. #5

    Thread Starter
    Member
    Join Date
    Feb 2018
    Posts
    54

    Re: duplicate button with id feild that wont change

    Quote Originally Posted by IkkeEnGij View Post
    cubsm22p,
    you probably have already solved that problem, but anyway:
    consider the following

    but access does allow to update a long integer field

    so what would be the solution to that problem ?
    iow:why is the ID field of table Duptbl an autonumber and not a simple long integer ?
    You my friend solved the problem manipulated the field in the Duptbl after changing the criteria of that field only in that table

Posting Permissions

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



Click Here to Expand Forum to Full Width