Cut and pasting new records into a table (1 Viewer)

[DMorrisPE]

I work for the City of Charlotte, NC, Dept of Transportation. We created a simple Access DB 10 years ago to issue Permits to occupy our street Rights of Way for periods of time greater than 30 minutes and less than 24 hours. Our main table, rightly or wrongly, contains 12,000+ records, each containing 42 fields, which include the Client's name address, etc. We did not set up a separate table for the Client data (I said it was simple).

We get a lot of repeat business from some of the Clients, so, in the past, we have copied an existing record, then pasted it into a New Record and modified the Permit dates and location. The field for the Permit Number has an AutoNumber property. In the past, the copy process generated a new, next unused number, for the Permit Number field.

The City recently installed Office 2010 and Access no longer generates a number that is the next unused number. Rather it is incrementing, by one digit, the OLD number so that we now have TWO records with the SAME Permit number - the original next sequential numbered record and the newly copied record. We think that is not proper behavior and is definitely not what we want or expected. Is there a simple solution for this, other than manually retyping the entire set of data?

 

[DMorrisPE]

Never mind, I found the problem: The Primary Key, which was assigned to the Permit Number field, was removed. Don't know how, because it was there in Office 2007, but not in Office 2010.

 

[Pat Hartman]

Sounds like the autonumber seed has been corrupted.
1. Back up the database
2. Copy the following code into a new code module.
3. Compile it. If you get an error, add a reference to the adox library
4. Create a test sub to run it.

Public Function RunResetSeed()
    Call ResetSeed(yourtablename)
End Function

5. Run it - note - I have not used this particular procedure but I can't find the one I wrote for myself.

Function ResetSeed(strTable As String) As String
    'Purpose:   Reset the Seed of the AutoNumber, using ADOX.
    Dim strAutoNum As String    'Name of the autonumber column.
    Dim lngSeed As Long         'Current value of the Seed.
    Dim lngNext As Long         'Next unused value.
    Dim strSql As String
    Dim strResult As String
    
    lngSeed = GetSeedADOX(strTable, strAutoNum)
    If strAutoNum = vbNullString Then
        strResult = "AutoNumber not found."
    Else
        lngNext = Nz(DMax(strAutoNum, strTable), 0) + 1
        If lngSeed = lngNext Then
            strResult = strAutoNum & " already correctly set to " & lngSeed & "."
        Else
            Debug.Print lngNext, lngSeed
            strSql = "ALTER TABLE [" & strTable & "] ALTER COLUMN [" & strAutoNum & "] COUNTER(" & lngNext & ", 1);"
            Debug.Print strSql
            CurrentProject.Connection.Execute strSql
            strResult = strAutoNum & " reset from " & lngSeed & " to " & lngNext
        End If
    End If
    ResetSeed = strResult
End Function

''--------------------------------------------------------------------------------
Function GetSeedADOX(strTable As String, Optional ByRef strCol As String) As Long
    'Purpose:   Read the Seed of the AutoNumber of a table.
    'Arguments: strTable the table to examine.
    '           strCol = the name of the field. If omited, the code finds it.
    'Return:    The seed value.
    Dim cat As New ADOX.Catalog 'Root object of ADOX.
    Dim tbl As ADOX.Table       'Each Table in Tables.
    Dim col As ADOX.Column      'Each Column in the Table.
    
    'Point the catalog to the current project's connection.
    Set cat.ActiveConnection = CurrentProject.Connection
    Set tbl = cat.Tables(strTable)
    
    'Loop through the columns to find the AutoNumber.
    For Each col In tbl.Columns
        If col.Properties("Autoincrement") Then
            strCol = "[" & col.Name & "]"
            GetSeedADOX = col.Properties("Seed")
            Exit For    'There can be only one AutoNum.
        End If
    Next
    
    'Clean up
    Set col = Nothing
    Set tbl = Nothing
    Set cat = Nothing
End Function

''--------------------------------------------------------------------------------

 

[DMorrisPE]

Thanks, Pat, for the speedy response. As I posted a few minutes ago, I had a flash of inspiration, and decided to check the Primary Key. It was not set for the Permit Number field of the table. Re-assigning the key fixed the problem. Something so simple, but easy to overlook.