Auto reference number code query

[Haynesey]

Hi, I use the following code to create automatic reference numbers like DHES:1, DHES:2, DHES :3 and so on. This works until it gets to DHES:10 but it now just keeps repeating itself. Any ideas?


If Me.CapitaID.OldValue = "" Or IsNull(Me.CapitaID.OldValue) Then
Me.CapitaID = "DHES:" & _
Format(Nz(DMax("Mid([EmailID], 6)", _
"tblHD", _
"[EmailID] Like '" & "DHES:" & "*'"), 0) + 1)
End If

Thanks in advance

Lee

 

[KenHigg]

Format(val(Nz(DMax("Mid([EmailID], 6)", "tblHD", "[EmailID] Like '" & "DHES:" & "*'"), 0)) + 1)

???
ken