MS Access VBA Function – create UID starting at prescribed number

Useful function if you are taking two tables with overlapping identity key to be placed in a table that will have a further child record and you wish to separate the new keys.

Public Function WriteUID(LCounter As Long) As Long
 
Dim rstC As DAO.Recordset
Dim LCountStart As Double
 
LCountStart = LCounter
 
Set rstC = CurrentDb.OpenRecordset("TABLEREQUIRINGUNIQUEID")
 
Do Until rstC.EOF = True
 
rstC.Edit
rstC!UID = LCounter
rstC.Update
 
LCounter = LCounter + 1
 
rstC.MoveNext
 
Loop

MsgBox "Finished UNIQUEID write"
 
End Function

Author: Mark

Mark Brooks a forty something individual working and living in and around Edinburgh