I use an ID like this in my IT-Devices database, where DeviceID is in format "CCyyyymmdd000", where:
"CC" determines device group (p.e. "CD" is for desktop computers, "CS" for servers, "DM" for monitors, "NM" for manageable switches, etc.);
"yyyymmdd" determines registering/purchase/lease contract date;
"000" is unique number for device in given group on given day. I.e. I can register p.e. up to 999 desktop computers on same date.
The Device Registry subform (sfDevices with fmDevices as source) is bound to parent form, where device group is selected. On fmDevices is an unbound invisible text box which reads the current device group from parent form.
Code:
txtParentDevGroup = [Forms]![fmMain]![sfDeviceGrouping]!txtDevGroup
The OnCurrent event controls, is the current row a new one, or an existing one. When the record is a new one, a text box is enabled with current date as default value for registering date (user can edit this date), and a button is made visible for registering the device. and all other controls on form are disabled until user registers the device or abandons registering pressing Esc or moving to another record without registering. When the device is registered, then registering date text box is disabled, device registering button is made not visible, and enabled statuses for controls the user is allowed to edit are set True (By default all controls are not editable - the user checks a control to allow edits for active row, and when finishing clicks a button visible in editing mode to save changes he did made.
To calculate the new DeviceID, a couple of additional invisible text boxes are used.
Code:
txtCalcDeviceID.ControlSource = [txtParentDevGroup] & Format([txtRegDate];"yyyy") & Format([txtRegDate];"mm") & Format([txtRegDate];"dd")
which calculates left 10 characters of new DeviceID
Code:
txtCalcDeviceID2.ControlSource = CInt(Nz(Right(DMax("DeviceID";"tblDevices";"LEFT(DeviceID,10)='" & [txtCalcDeviceID] & "'");3);0))
which calculates current MAX value of last 3 right characters for DeviceID's where left 10 characters equal with txtCalcDeviceID
When the button for registering the new device is cliked, a new DeviceID is calculated
Code:
Private Sub comRegisterDevice_Click()
Me.txtDeviceID = Me.txtCalcDeviceID & Format((Me.txtCalcDeviceID2 + 1), "000")
...
End Sub