Primary Field Value calculation and insertion

I currently run an application in Excel which is now too RAM heavy to remain
fast and efficient. There is much functionality built into this Spreadsheet
which is dead easy to do – just get into the cell of your choice and go crazy
with expressions statements etc. So much so, there is very little actual VBA
written into the application.
Now I am trying to get the same functionality into Access, a different
animal and very clunky to the uninitiated. I was told that I would not need
to use much VBA in Access either, because there are “ways� to get around it.
So I am keen to see this and ask the Access cohorts out there to please show
me the light.
This particular thread requires that my Primary key contains variable
prefixes –

example:
If my field contains parts like Widgets, Gadgets and Gizmos the first 3
letters are use as a prefix. If a new record refers to a widget, Access is
required to seek out the highest assigned widget number and then add 1 – so
upon saving (exiting) the record the primary field must find all WID prefix
numbers (say 0876) and add 1 to = 0877 and complete the field insertion with
the result “WID0877�

Which is the easiest way to achieve this result (via Query, field
expression, function, macro, combination, etc) please?

Many thanks for your time and assistance

I am by no means qualified to give this advice but i have seen discussions on
this before and most suggest seperate Fields for the prefix and Number then
maybe with a query limit Prefix to "WIDGET" and do a MAX on the Number
Column. Just an Ignorant Tidbit..

"MMWC" wrote:

I currently run an application in Excel which is now too RAM heavy to remain
fast and efficient. There is much functionality built into this Spreadsheet
which is dead easy to do – just get into the cell of your choice and go crazy
with expressions statements etc. So much so, there is very little actual VBA
written into the application.
Now I am trying to get the same functionality into Access, a different
animal and very clunky to the uninitiated. I was told that I would not need
to use much VBA in Access either, because there are “ways� to get around it.
So I am keen to see this and ask the Access cohorts out there to please show
me the light.
This particular thread requires that my Primary key contains variable
prefixes –

example:
If my field contains parts like Widgets, Gadgets and Gizmos the first 3
letters are use as a prefix. If a new record refers to a widget, Access is
required to seek out the highest assigned widget number and then add 1 – so
upon saving (exiting) the record the primary field must find all WID prefix
numbers (say 0876) and add 1 to = 0877 and complete the field insertion with
the result “WID0877�

Which is the easiest way to achieve this result (via Query, field
expression, function, macro, combination, etc) please?

Many thanks for your time and assistance