Unanswered: text or number field iterated by a function
I am considering shifting the Key field from AutoNumber to a text or number field iterated by a function.
Often I want to prefix the key/autonumber with text. Is there an elegant solution for that? I guess either a function would have to deal with a text field (slow), or one add such static text directly in forms. Any other ways that will not be slowing down the querying too much? Even if I would apply another standard Autonumber field in addition to the main key field, say, the invoice number, the - in this example - invoice number is still the field that would be used for sorting I guess. If for example an invoice was deleted by an error, or other reason, then later added, sorting by the AutoNumber field would place it wrongly if other invoices was added inbetween. So I think that it is the invoice number field - even if a text field, that will be used for sorting.
In most of my cases, tables are rarely larger than thousands (maybe tens of thousands) of records. Most often, it is a fairly new/fast normal user PC that runs the DB, not a server. This may not be "a lot", not significant enough for performance concerns? However, sometimes one of more tables are accessed by other users (generally 2-20) through either their own MDE front ends, and/or through a browser using Access Data Pages. Updates are rarely happening exactly at the same second from several users. Mostly querying data, adding/editing single records every now and then. Will shifting an index/key field away from AutoNumber be worth considerations on performance/speed ?
Would appreciate code examples / experiences / suggestions on this.