Results 1 to 3 of 3
  1. #1
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544

    Question Duplicating numbers....why??

    I have the below code running a one-time process to update 675 records. Every time it gets to 415 I get a run-time error violating the primary key rules. Why is this happening? I cannot understandy why my code would violate the primary key rules. I am aware that I am updating the records of a primary key, but the i = i+1 should never duplicate right?

    Code:
        Dim varMetricNum, varMetricName
        Dim i As Integer
        Dim fldSum As Long
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim mySQL
     
    '    DoCmd.Hourglass True
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tblMetrics_1", dbOpenDynaset)
     
        rs.MoveFirst
        i = 0
        Do While Not rs.EOF
        i = i + 1
        varMetricNum = "MET-" & i
        varMetricName = rs![Metric Name]
     
        mySQL = "UPDATE tblMetrics_1 SET [Metric Number]='" & varMetricNum & "' WHERE [Metric Name]='" & varMetricName & "'"
        CurrentDb.Execute mySQL, dbFailOnError
        rs.MoveNext
        Loop
    '    DoCmd.Hourglass False
        MsgBox "Complete.", vbOKOnly


  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Are you sure there isn't a record existing with 415? Also, since you already have a recordset on the desired record, I wouldn't bother with the SQL. I'd use the .Edit method of the recordset. These seems to be a different direction than we talked about on the other thread. Are you going to save them together?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jgelpi16 is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Mar 2010
    Location
    Charlotte, NC
    Posts
    544
    No, was trying two techniques for the same thing. Essentially making things 100 times harder for myself. Your other solution worked just find. Thank you.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. duplicating records
    By kstyles in forum Queries
    Replies: 7
    Last Post: 12-31-2010, 02:31 PM
  2. labels duplicating themselves
    By RedGoneWILD in forum Forms
    Replies: 2
    Last Post: 08-17-2010, 07:42 AM
  3. Report duplicating itself many many times
    By TrudyD1474 in forum Reports
    Replies: 1
    Last Post: 06-24-2010, 11:59 AM
  4. duplicating and editing the record
    By Airis in forum Forms
    Replies: 1
    Last Post: 04-12-2010, 07:41 AM
  5. Duplicating lines in an invoice report
    By GordonEdinburgh in forum Forms
    Replies: 0
    Last Post: 04-21-2007, 12:20 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums