Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    mmai is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    6

    VBA code to generate username based on table lookup?


    Need a function that will generate a username value for each user listed in TableCalculated, that will not conflict with any username value that exists in Table_MASTER. The function needs to increment the generated username value by a number (consecutively) until no conflict with any existing value in Table_MASTER.


    Example:
    Table_MASTER/username field has values: John, John1, John2, John3


    TableCalculated/username has value: john
    TableCalculated/newusername = FUNCTION ?? This function would ideally generate the value "John4" since it will not conflict with any existing values in Table_Master, and is the next available number to use after John3.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Generating custom unique identifier is a fairly common topic. For a start, review https://www.accessforums.net/showthread.php?t=23329
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    mmai is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    6
    It's not a custom unique identifier, i really need the function to look at the value supplied already in the table (example "John") and increment by one digit in order not to conflict with any values from Table_Master

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That means the value is unique and it's an identifier (as opposed to a quantity value), therefore a 'unique identifier'. And it's custom because it's not an autonumber.

    Is 'John' the only text? No Bill, Bill1, or Bertha, Bertha1?

    Technique in example code still applicable. Adapt it to your situation. Something like:
    Code:
    Function GetID() As String Dim strID As String, varNum As variant strID = Nz(DMax("[fieldname]", "tablename"), "") If strID = "" Then GetID = "John" Else varNum = Mid(strID, 5) GetID = Left(strID, 4) & IIf(varNum = "", 0, varNum) + 1 End If End Function
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    mmai is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    6
    Thank you so much for the sample code! So to answer your question, "John" is not the only text. The Master username table will have all usernames in the company, like John1, John2, John3, John4, Bill, Bill1, Bill2, Jane, Jane1, Jane, Jane3, Jane4, etc..

    My "NewUser" table has a bunch of pre-populated values for username field that need to be checked against this master list, and if no-conflict, use the pre-poluated value supplied, but if conflict exists, increment by 1 digit.

    I don't know how to supply the function GetID() with the value from the field in table "newuser", field username.

    If I create a new module in VBA, it never shows up as an available function when I try to build the expression in the access query.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Doesn't really make sense to use in query. If you want to save this calculated value then call function from some event behind a form.

    Query can see custom functions only if they are in a general module. Function and module cannot have same name.

    But why would you be creating user names as opposed to using names assigned by company?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    mmai is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    6
    So can I add this to a general module to use in a query? In terms of why I'm creating usernames, because the usernames are not assigned by the company sadly.. And I'm trying to avoid assigning a username that already exists. If I could make this work in access via query using a calculated field/expression that calls the function, would be amazing, but it seems as though you're saying that the only way is via form? I'll even accept using a form as the only work around but I'm still not sure how to get the form to compute the new username values for each user that is supplied in my "new user" table. I don't want any constants like in the sample code the constant = "john"

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, code goes in general module.

    Referencing this function in a SELECT query serves no purpose. The calculated value would not be saved to table.

    How should code know whether to use "John" or "Bill" or "Jane"?

    Conventional approach would be to generate an identifier when a new user 'logs in' to database. But you have not described the business process that will use this identifier, when and where validation should occur.

    Are you working on a company network that you have to log in to? If yes, then the company certainly does assign usernames. You can capture those names and use them in a "Users" table.

    Why would you have a "MasterUsers" table and a "NewUsers" table? - should be only one "Users" table.

    Sorry, I don't understand what you are really trying to accomplish.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    mmai is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    6
    I do capture the company user names and use them in a users table. This becomes my "mastersusers" table.

    My "newusers" table is where I enter in the default username for a newhire (which is firstinitialLastName), and if it conflicts with a value in the masters table, I need the code the generate a new value incremented by consecutive digit. Does that make sense?

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So this is not a user who has logged onto a computer and is opening the db? This is YOU creating a new record for a new hire? So when does the user get a network USERNAME? Is that what you are doing?

    So you type in say "JSmith" and you need code to look for that value in MasterUsers to see if already exists and if it (or some increment of it) does then assign next number?

    So modify my code.
    Code:
    Function GetID(strUser) As String Dim strID As String, varNum As variant strID = Nz(DMax("[fieldname]", "MasterUsers"), "UserName LIKE '" & strUser & "*'") If strID = "" Then
    GetID = strUser Else varNum = Mid(strID, 5) GetID = Left(strID, 4) & IIf(varNum = "", 0, varNum) + 1 End If End Function
    Now call that function in the AfterUpdate event of UNBOUND textbox where you type "JSmith" or a button Click event.
    If IsNull(Me!UserName) Then Me!UserName = GetID(Me.tbxUserName)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    mmai is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    6
    Yes, this is me creating a new record for that user. The record exists in the table "newUser", I create all potential usernames for new hires in this table called "newuser". So I don't use any form. What I would is based on my table "newuser" to generate a new field that will be the realusername assigned to the user based on the cross-reference with master-table. Does that make sense?

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you don't want a form then cannot use code.

    Could have a combobox that lists user names from MasterUsers. You can see if what you type matches an item in the list and type whatever sequence number you want based on what you see.

    You can build this combobox in table design or on a form.

    Does record in NewUser eventually get eliminated?

    Let's hope you never have two new hires "JSmith" in same day.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    I'm still wondering why you need 2 tables. Why do you need to separate the data. Are the 2 tables the same?
    Another option to cut down on duplicates would be to include a middle initial if they have one. You could have JSmith, JRSmith,JTSmith,etc.

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Middle initial is still no guarantee of uniqueness, true, duplicate highly unlikely but not impossible. Which is why my university used the sequence number approach and believe it or not, there would have been duplicate IDs if number not used, even with middle initial.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  15. #15
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by June7 View Post
    Middle initial is still no guarantee of uniqueness, true, duplicate highly unlikely but not impossible. Which is why my university used the sequence number approach and believe it or not, there would have been duplicate IDs if number not used, even with middle initial.
    At one school I taught at, there were 2 pairs of unrelated students, each pair had the same first, middle and last names and the same date of birth.
    We used admission numbers as the primary key field so there was no risk of confusion as far as databases were concerned.
    However human error led to all sorts of problems.
    For example when a 5 day exclusion letter was sent to the parents of the wrong child.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-01-2017, 12:14 PM
  2. Replies: 3
    Last Post: 12-01-2015, 12:20 PM
  3. Replies: 10
    Last Post: 09-23-2015, 11:26 AM
  4. Lookup column based on table value
    By greenkevin86 in forum Access
    Replies: 9
    Last Post: 04-30-2012, 06:03 PM
  5. Replies: 17
    Last Post: 12-20-2011, 04:36 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums