CODEWITHSUNDEEP
sqlvbams-access
CaseyU
CaseyU

Reputation: 15

Alter Table Auto Increment starting number criteria from another query or field

I have a database that stores all our distribution partner sales. Every week I get updated sales that I load into what I call tblImport. I then have some queries I run that parse out new sales lines to tblUpdate and tblNewAccounts. What I am struggling with is automating what I call the Bill To ID(BT_ID) field in the NewAccounts table. I know how to find the highest BT_ID from the master customer table. What I cannot figure out is how to get the tblNewAccounts to start auto numbering the BT_ID field with the max BT_ID +1 from the master customer table.

I figured out Alter Table and here is the code I used in an SQL Query that works but I need the value 128071 to actually be 1 plus the MAX BT_ID from the Master Customer Table.

ALTER TABLE [tblUpdate2_NewAcct]
ALTER COLUMN [BT_ID] AUTOINCREMENT(128071,1)

Here is something else I tried but I keep getting errors.

SELECT Max(tblALX_BillTo.BT_ID) AS MaxOfBT_ID
FROM tblALX_BillTo;
ALTER TABLE [tblUpdate2_NewAcct] ALTER COLUMN [BT_ID] AUTOINCREMENT([MaxOfBT_ID],1);

I have tried doing this in VBA as well and it hasn't worked either. I am assuming it all has to do with that criteria field for the start of AutoIncrement.

Upvotes: 1

Views: 228

Answers (1)

HansUp
HansUp

Reputation: 97101

With fairly easy VBA code, you can use DMax to grab the maximum current BT_ID value plus 1. Then write that value into your ALTER TABLE statement, and execute it.

Dim strDDL As String
Dim lngNewSeed as Long
lngNewSeed = DMax("BT_ID", "tblALX_BillTo") + 1
strDDL = "ALTER TABLE tblUpdate2_NewAcct" & vbCrLF & _
    "ALTER COLUMN BT_ID AUTOINCREMENT(" & lngNewSeed & ", 1)"
CurrentProject.Connection.Execute strDDL

Upvotes: 1

Related Questions