Setting up automatic, unique IDs on forms

[joneca]

I am developing a request form which lets students submit issues they are having with their accounts. For each request, I want to create a unique ID that starts with the first letter of the request type (e.g. refund, balance) followed by automatic numbering. Is this possible and, if so, what's the best way to do it?

[ItsMe]

I would recommend using the Autonumber data type in almost every table, always. You will be hard pressed to find a reason not to. With that, you can concatenate text to the Autonumber for display purposes. For instance, after a record is created, code will grab the desired text from a name or whatever and append the text to the front of the number generated by the Autonumber field. You could store this value in the table or simply call the code as needed.

If the ID's created must be sequential, for example you are going to be audited by the government and they are going to make your life miserable because there is an AL101 and a KA103, but there is not an XX102. Then the Autonumber should be substituted with another option.

[June7]

Generating custom unique identifier is a common topic. Search forum.

Autonumber is not guaranteed to increment sequentially nor even to be positive, however, I've never seen it do anything but increment positive sequentially. Autonumbers are not really intended for user viewing, they aren't supposed to have meaning. Their primary purpose is to serve as PK/FK link between records in queries and form/subform (report/subreport) design. Aside from all that, for your situation the autonumber might serve.

You will want to format the value for display to user, like: [RequestType] & Format([ID], "00000")

Be aware that gaps can result if user initiates a record but then aborts and does not commit to table. That autonumber is still used up even though it doesn't show in table.