You are here:

Using MS Access/Nz Dmax for Custom unique user ID



I was wondering if you could assist with a problem ive been having. I am trying to create a unique user id which is based on the first initial of the first name and the last 3 letters of the surname followed by a number. I need it to check existing users ids and pick the last one and increase the number by 1. ie if we have skni1 for Shane Knight for example and skni2 for Steven knight then if for Sean knight it would be skni3.

Its kinda works except it doesnt always increment it uses a number already used before.  Do you know where i am going wrong ?

Thanks in advance

The Code i'm using is

Private Sub btnGenerateRiOID_Click()

Dim strRiOID As String
Dim strLastRiOID As String
Dim strNewRiOID As String

strRiOID = Left(FirstName, 1) + Left(Surname, 3)

strLastRiOID = strLastRiOID = Nz(DMax("GenPersonID", "GenPerson*", "LEFT([GenPersonID],3) = '" & Left(FirstName, 1) + Left(Surname, 3) & "'")

strNewRiOID = Left(FirstName, 1) + Left(Surname, 3) & _
         Format$(Val(Mid$(strLastRiOID, 5)) + 1)

Me.GenPersonID = strNewRiOID
DoCmd.RunCommand acCmdSaveRecord

End Sub

ANSWER: I disagree with your methodology. You are storing redundant information that is unnecessary.

Please check out my blog on Sequential Numbering. It details exactly what you are trying to do here. If you have any further questions after reviewing the blog, please feel free to ask.

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Hi There, I did read through the blog and i tried a few options, part of which is incorporated into the above, but i'm only a beginner and sadly and couldn't get it to work correctly, i wasnt 100% sure which bits to substitute to get the required value. esp. using Left(FirstName, 1) + Left(Surname, 3) to create the first part of the ID initially.

Are you able to help me with the correct version of what i need ?

Thanks again

OK, First, The sequential number should be stored separately. The main problem in what you are doing is that you are trying to store and use the number as part of a stored identifier. The blog specifically says that you should NOT be doing this.

So your GenPersonID field should be a long Integer datatype. Your code should be something like this:

Private Sub btnGenerateRiOID_Click()

Dim strAbbrName As String

strAbbrName = Left(Me.FirstName, 1) + Left(Me.Surname, 3)

Me.GenPersonID = Nz(DMax("GenPersonID", "GenPerson*", "Left(FirstName, 1) &  Left(Surname, 3) = '" & strAbbrName & "'"),0)+1

DoCmd.RunCommand acCmdSaveRecord

End Sub

strAbbrName creates the abbreviated name from the values on the form for checking. You then generate the sequence number by using the DMax. If there are no people with the same combination of 1st initial and 1st 3 letters of surname, then the Nz part returns 0 which it then incremented by 1. Otherwise it returns the highest number that matches and then increments by 1. (note: is GenPerson* the table name? That's what should be there.)

So now you have a sequence number stored in your table as GenPersonID. Wherever you need to display the identifier, you use the expression:

=Left(FirstName, 1) + Left(Surname, 3) & GenPersonID

Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA  

Using MS Access

All Answers

Answers by Expert:

Ask Experts




I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.


I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Brooklyn College BA

©2017 All rights reserved.