How to create a membership ID field - unique and always increasing by 1

[redekopp]

Good morning, I am still pretty new with more complex things and was hoping someone could help me out. Basically I have a member form and anytime we enter a member obviously there is the primary key which is an auto number ID, but I would also like a membership ID field that is short text such as "MEM-0001" and then always progresses by 1 each time we create a new member. So "MEM-0002", "MEM-0003" etc. How do I go about doing this while ensuring the membership ID is always unique? It will be a field on the form and a short text in the table called MembershipID

Thank you!

[orange]

What exactly is the purpose of "MEM-"?

For display purposes you could use

membershipId = "MEM-" & format (ID,"0000")

[redekopp]

Clients request, they would use it for documentation and lookup I suppose. they want MEM as the prefix. Is it loads easier to just drop the prefix?
They also have another field which is the same thing, a file number that they wanted to use the Prefix S25M-****

[Minty]

These request are always historical - It's just a display thing.
Orange has given you the answer you need. Keep it as a number and just add the pretties on afterwards, and save yourself a whole heap of pain.

[redekopp]

These request are always historical - It's just a display thing.
Orange has given you the answer you need. Keep it as a number and just add the pretties on afterwards, and save yourself a whole heap of pain.

So how do I make the number field always unique for each record and always incrementing and using 4 digits? I do not want to use it as a primary key, either.

Thank you for your responses!

[orange]

Use the ID (autonumber as your Primary Key)
Use the "MEM-" & format (ID,"0000") for the user display

You can use similar technique with your other table(S25M-****).

Note: With autonumber you cannot guarantee sequential values. A value will be dropped if user aborts a process with new autonumber.

See this on Autonumbers