CODEWITHSUNDEEP
ms-accessindexingvbaappendautonumber
Katie
Katie

Reputation: 105

Appending data to an indexed table

I'm attempting to append records to a table that has data indexed by an ID column. I'd like the appended data to continue that indexing and attach the next number to the first appended record and so on. However, that ID column is not an AutoNumber column--the first number needed to be 5001 so it's current Data Type is "Number". The data already in that table is entered via a form with this VBA to format the ID column: 

    If Nz(Me.ID, "") = "" Then
    NewID = Int(DMax("ID", "tComplianceAll") + 1)
Else
    NewID = Me.ID
End If

I currently have an append query to try to append the new data to the table with this SQL for that ID column : Int(DMax("ID","tComplianceAll")+1) AS Expr1

That, however, only works for the first record. The rest do not get appended due to key violations since it's trying to assign the same ID number for all appended records. Is there a way to change that SQL so that it properly indexes the newly appended data?

Upvotes: 0

Views: 604

Answers (1)

kismert
kismert

Reputation: 1692

You surely can use a standard Autonumber ID value here!

  1. Edit table tComplianceAll and change the ID column type to AutoNumber

  2. Then, set the next value using this SQL:

    ALTER TABLE tComplianceAll ALTER COLUMN ID AUTOINCREMENT(5001,1)

    Actually, you would change 5001 to the latest value returned by (DMax("ID", "tComplianceAll") + 1)

That should do it, providing that tComplianceAll is not in any relationships with other foreign tables using the ID field.

Upvotes: 0

Related Questions