Create a unique sequencial record number when userform inserts record

[Mike_CS]

Hi

Once again consulting the genius that is the MrExcel Community

I am trying to find a way to create a unique sequential record number when my userform inserts a record in to a table. To be added when i run the below the code.

Have just left column 1 of the table with the easy =Row() which fills down when a new record is entered with the row number but if any records are deleted it messes with the whole dataset as they all change ref numbers as they move up the table.

Access has an Autonumber feature but in my office not everybodys profile can use Access due to licensing issues (plus i've only ever played with access never used properly).

Would anybody know a good way of creating a sequential ref number?

My code is:

Sub Insert_record()

'Copy input values to sheet.

Dim lblAlloc8rFullnameCR As Label
Dim lblAlloc8rPIDCR As Label
Dim txtRequestor As Textbox
Dim txtDateRecCR As Textbox
Dim lblTodayCR As Label
Dim cmbReqType As Combobox
Dim txtReqDetails As Textbox
Dim cmbAlloc8d2Name As Combobox
Dim lblAlloc8d2PID As Label
Dim txtDateRecByCR As Textbox
Dim lblRqTypTm As Label
Dim lblPlayerTm As Label
Dim lblReqType As Label
Dim lblPlayer As Label

Dim the_sheet As Worksheet
Dim table_list_object As ListObject
Dim table_object_row As ListRow
Set the_sheet = Sheets("datastore")
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add

' table_object_row.Range.Cells(1, 1).Value = need something for unique number record here
table_object_row.Range.Cells(1, 2).Value = FrmCreateRequest.lblAlloc8rFullnameCR
table_object_row.Range.Cells(1, 3).Value = FrmCreateRequest.lblAlloc8rPIDCR
table_object_row.Range.Cells(1, 4).Value = FrmCreateRequest.txtRequestor
table_object_row.Range.Cells(1, 5).Value = FrmCreateRequest.txtDateRecCR
table_object_row.Range.Cells(1, 6).Value = FrmCreateRequest.lblTodayCR
table_object_row.Range.Cells(1, 8).Value = FrmCreateRequest.cmbReqType
table_object_row.Range.Cells(1, 9).Value = FrmCreateRequest.txtReqDetails
table_object_row.Range.Cells(1, 10).Value = FrmCreateRequest.cmbAlloc8d2Name
table_object_row.Range.Cells(1, 11).Value = FrmCreateRequest.lblAlloc8d2PID
table_object_row.Range.Cells(1, 12).Value = FrmCreateRequest.txtDateRecByCR
table_object_row.Range.Cells(1, 13).Value = FrmCreateRequest.lblTodayCR
table_object_row.Range.Cells(1, 18).Value = FrmCreateRequest.lblTodayCR
table_object_row.Range.Cells(1, 16).Value = FrmCreateRequest.lblAlloc8rFullnameCR
table_object_row.Range.Cells(1, 17).Value = FrmCreateRequest.lblAlloc8rPIDCR
table_object_row.Range.Cells(1, 22).Value = FrmCreateRequest.lblRqTypTm
table_object_row.Range.Cells(1, 24).Value = FrmCreateRequest.lblPlayerTm
table_object_row.Range.Cells(1, 23).Value = FrmCreateRequest.lblReqType
table_object_row.Range.Cells(1, 25).Value = FrmCreateRequest.lblPlayer

End Sub

Many thanks in advance

Mike

 

[sheetspread]

Perhaps a condition where the first record is 1, after that the previous # + 1

If you paste row() as a value they won't change (can be done via the code also)

 

[steve the fish]

Why not get the max number of the column of records then add 1 to it. Hss to be unique.

 

[Mike_CS]

Why not get the max number of the column of records then add 1 to it. Hss to be unique.

I did try that but couldnt get it to work properly. MAX + 1 was my first thought and hardcode the ref number in.

Would you know a way? My thoughts are writing cheques that my brain can't cash.

 

[Mike_CS]

Perhaps a condition where the first record is 1, after that the previous # + 1

If you paste row() as a value they won't change (can be done via the code also)

But i could end up with duplicate records if i insert at row 66 (create record 66). then delete record 20 and they will all move up. then what would have been record 67 will now be 66 also. I hope that made some sense? I think im going barmy.

 

[sheetspread]

i could end up with duplicate records

Ok then try Steve's idea: application.max(columns(1)) value or something.

 

[steve the fish]

Set table_list_object = the_sheet.ListObjects(1)
[COLOR=#ff0000]Set myCol = table_list_object.ListColumns(1)[/COLOR]
Set table_object_row = table_list_object.ListRows.Add
[COLOR=#ff0000]table_object_row.Range.Cells(1, 1) = Application.Max(myCol.Range) + 1[/COLOR]

Add the lines in red and see if that works