How to generate username based on table lookup? (1 Viewer)

mmai · Sep 21, 2019

Need a function or code or expression that will generate a username value for that will not conflict with any username value that already exists in Table_MASTER. If the value already exists, then increment the generated value by a number (consecutively) until no conflict.

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

TableCalculated, username field has value: john
TableCalculated, NEWusername field = 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.

June7 · Sep 21, 2019

Cross post https://www.accessforums.net/showthread.php?t=78345

CJ_London · Sep 21, 2019

NEWusername field = FUNCTION ??

google what is frequently called dmax+1

mmai · Sep 21, 2019

I would definitely be interested in using the getmax, but don't know how to make if work in the context I described

June7 · Sep 22, 2019

As suggested in other forum, use a combobox.

arnelgp · Sep 22, 2019

As suggested in other forum, use a combobox.

can't help but noticed on two forums.
but seems invisible as the discussions continues.

this might help you.
you pass the username to the function.
if the username is not in the table, it will return same username.
otherwise it will return username(+number), that is not in the database.

dim usrname as string
usrname="mmai"
usrname = genUser(usrname)

Code:

Public Function genUser(ByVal user As String) As String

    Const table_name As String = "table_master"
    Const field_name As String = "username"
    
    Dim new_user As String
    Dim i As Integer
    
    new_user = user
    While DCount("1", table_name, field_name & "=" & Chr(34) & new_user & Chr(34)) > 0
        i = i + 1
        new_user = user & i
    Wend
    
    genUser = new_user
        
End Function

How to generate username based on table lookup? (1 Viewer)

mmai

New member

June7

AWF VIP

CJ_London

Super Moderator

mmai

New member

June7

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread