1

All the accounts in my database have a unique ID, that begins with the letter "G" (always), and has a 3 digit number following (e.g. "G026", "G123","G005","G999", etc.)

When a user is inputting new-account information through a form, I would like the unique ID to be auto-generated. So I would like a unique "G000" formatted number that is NOT already taken up in the list of users. I have tried a few things, but am having a lot of trouble getting it to work.

Does anybody have any ideas of a module I could implement? I'm thinking of making a module that generates these numbers, and calling this module in the "Default Value" field.

THANK YOU!!

  • And what happens when you get 1001 users? – ypercubeᵀᴹ Jul 16 '15 at 15:53
  • Pretty doubtful that number of accounts will exceed 200, but I can always change the formatting to G0000 instead of G000 – jotakah Jul 16 '15 at 16:00
  • Why don't you use a usual auto incremented integer (say named TableID)? And have a calculated column of 'G' & TableID. It would result in 'G1', 'G2', 'G9', 'G10', ..., 'G99', 'G100', ... – ypercubeᵀᴹ Jul 16 '15 at 16:00
  • Because when I came onto the project, there are already lots of IDs, in random order with spacing in between them. I.E. "G007" then "G026" then "G101" etc. So autonumber may repeat these values and cause the ID to change... – jotakah Jul 16 '15 at 16:38
1

This will work. There may be something more elegant but this should get you started.

 Public Function GetUID() As String
      GetUID = "G" & Right("000" + CStr((CInt(Right(DMax("[YourIDField]", "TableContainingField"), 3)) + 1)), 3)
 End Function
  • Having trouble getting this to implement. I tried calling it from the "Default Value" field, and in a form I've also tried calling it in an "after update" expression for when a new Account Name is entered... – jotakah Jul 16 '15 at 16:38
  • Here is an example of how to call it, if that helps. Public Sub testGUID() Dim strGuid As String strGuid = GetUID() End Sub – Erin Ramsay Jul 16 '15 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.