sql - Generate a sequential number (per group) when adding a row to an Access table

Question

I have an MS Access (.accdb) table with data like the following:

Location Number
-------- ------
ABC      1
DEF      1
DEF      2
GHI      1
ABC      2
ABC      3

Every time I append data to the table I would like the number to be unique to the location. I am accessing this table through MS Excel VBA - I would like to create a new record (I specify the location in the code) and have a unique sequential number created. Is there a way to setup the table so this happens autmatically when a record is added? Should I write a query of some description and to determine the next number per location, and then specify both the Location & Number when I create the record?

I am writing to the table as below:

    Set rst = New ADODB.Recordset
    rst.CursorLocation = adUseServer
    rst.Open Source:="Articles", _
         ActiveConnection:=cnn, _
         CursorType:=adOpenDynamic, _
         LockType:=adLockOptimistic, _
         Options:=adCmdTable
    rst.AddNew
    rst("Location") = fLabel.Location 'fLabel is an object contained within a collection called manifest
    rst("Number") = 'Determine Unique number per location
    rst.Update

Any help would be appreciated.

Edit - Added the VBA code I am struggling with as question was put on-hold

Answer 1

You need to add a new column to your table of data type AutoNumber.

office.microsoft.com: Fields that generate numbers automatically in Access

You should probably also set this column as your primary key.