Incrementing ID

[Gannet]

I have tried to increment an ID if it already exists without much luck. The current code executes on the Before update event on my sbjRELATION field. I'm basing a new ID on an old one so if I have a person with two sons then I want to take their id of "10000" and have "1000011" for the first son and "1000012" for the second son. Problem is not duplicating ids. I have my form increment upon error but this seems inefficient. Is there a way to check an id while you're still in the record to have it increment until it finds a unique one in the sequence?

Public Sub create_id()
Select Case sbjRELATION
'these are many different relations to original subjects
'each group of 10 represents a different kind of relation


Case "Spouse"
Me.sbjHCID = Me.sbjID & "01"

Case "Son"
Me.sbjHCID = Me.sbjID & "11"

Case "Daughter"
Me.sbjHCID = Me.sbjID & "21"

Case "Niece"
Me.sbjHCID = Me.sbjID & "31"

Case "Nephew"
Me.sbjHCID = Me.sbjID & "41"

Case "Mother"
Me.sbjHCID = Me.sbjID & "51"

Case "Father"
Me.sbjHCID = Me.sbjID & "61"

Case "Grandmother"
Me.sbjHCID = Me.sbjID & "71"

Case "Grandfather"
Me.sbjHCID = Me.sbjID & "81"

Case "Other"
Me.sbjHCID = Me.sbjID & "91"
End Select


End Sub

 

[dcobau]

what type of field is sbjID? Alpha or numeric? You are adding alpha characters to the end of sbjID. What you have is ok if sbjID is an alpha field but if this field is numeric you should have:

sbjID + 9900011 (or whatever number is required to reach the total you want).

Dave

 

[Gannet]

what type of field is sbjID? Alpha or numeric? You are adding alpha characters to the end of sbjID. What you have is ok if sbjID is an alpha field but if this field is numeric you should have:

sbjID + 9900011 (or whatever number is required to reach the total you want).

Dave

The field is text. The code I'm using works great to increment the id but it doesn't know it has a unique id until it errors. Then I have code to increment the code if it finds the 3022 error code, but this only happens if I leave the record. I need a way to have it check while still in the same record so that someone doesn't close the form without having a unique id.

 

[Gannet]

Ok this is not the best way but it worked.

I have this on a button that closes the form.

[SIZE="2"]Private Sub cancel_and_close_Click()
On Error GoTo Err_Close_Click
    Dim err_code As Integer
    Form_frm_IW_AddHC.Requery
    
    DoCmd.close

Exit_Close_Click:
    Exit Sub

Err_Close_Click:
    
    err_code = Err.Number
    If err_code = 3022 Then
        MsgBox "The next id will be used"
        Me.sbjHCID.Value = Me.sbjHCID.Value + 1
    Else
        MsgBox Err.Description
    End If
    Resume Exit_Close_Click
End Sub[/SIZE]

This will error when it finds the id and then if it's a 3022 error it will increment the ID by 1.