dcsimg
Results 1 to 5 of 5
  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: 120
Size:  5.3 KB
    Name:  4.jpg
Views: 108
Size:  12.4 KB
    Name:  3.PNG
Views: 106
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 01:25 PM.

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    98,932

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

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

    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,041

    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
  •  



Featured


Click Here to Expand Forum to Full Width


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.