[cross-posting removed]
If the itemcode can be sorted, you can use the DMax function to
retrieve its value. What does an itemcode look like?
The item code can consist of both letters and numbers for example 123TRW123
If there is a sequence involved, you can create a custom function to
parse it and pass that to the DMax function.
It looks like you have multiple information stored in this field ...
although perhaps useful to management in this format as output in
reports, this kind of "intelligent key" becomes very unwieldy when it
comes to extracting the information it contains, and it can be a major
cause of data inconsistency.
For example, let's assume that you have numeric product id's for
products which are stored in different warehouses. The numeric part of
your item code might identify a part number; the alpha part could
represent the physical storage location. You can imagine how hard it
will be to keep things consistent when the location changes,
especially when the changing data needs to cascade into multiple
related tables. Even worse, the same numbers might refer to different
kinds of items entirely depending on where they are stored.
Unfortunately, many people still design databases this way instead of
normalizing the data strucures.
If there is no sequence which can be constructed from the itemcode, I
suggest adding a DateTime column to the table with a default value of
"Now()" which you could query for the latest entry. This would record
the date and time for new rows (only accurate to one second, but
probably good enough for this unless you have lots of concurrent users
who are adding rows at the same time).
Note that you would need to populate the field somehow for the
existing records...it would remain blank (null) unless you run some
kind of update query to fill it in.
