basic expression builder issue

[nagsha]

Hi All,



I have a simple expression builder problem. I am using Access 2010 and would like to automatically create a unique primary key entry as a result of some data entered into the table.

Basicly, I am using the expression builder to create a primary key in the format of:

AAA00001

where AAA is the selection of text I want from the expression builder and then the 00001 is automatically incremented from the last entry to create a truly unique entry.

For example, from the data of:

First Name: Billy
Last Name: Bob

The entry should take the first 2 letters of the first name and first letter of the last name (BIB) and then add it to the incremented number.

So, it should end up like: BIB00001

My problem is that I cannot work out how to create this in the expression builder. I do not think there is any other option to create this type of entry as I am using Access 2010 to create a web database.

[pkstormy]

I usually write a bit of vba code to do this kind of thing. I don't really think you can do it in an expression but you can't really increment a non-integer, alpha & number type value combined. I usually fire this in the afterupdate event after the last name is entered (and I've checked to see if the first name is blank). And then use the dlookup to lookup just the value of the last number in a query which just has the digits of the number (max in a summarization type query based on the last X digits of the primary key type field to just then have the number displayed.)

ex:

dim Ini as variant
Ini = left(me!FirstName,2) & left(me!LastName,1)
dim LNum as variant
LNum = dlookup("[MaxOfLastNumber]","qryLastNumber")
LNum = LNum + 1
me!MyPrimaryKeyTypeField = Ini & LNum

Otherwise, your autonumber field is a good field to act as the unique identifier since it will automatically increment and you can also just have an initials type field to combine the first 2 characters of the first name and first character of the last name.

Then if need be, you can easily combine these fields in an expression in a query to produce something like AAB0002. This would be the ideal route to go.

The only issue with using an autonumber value is that it doesn't back-fill in to a deleted number for a new record if an old record by chance were deleted but creating the primary key in code wouldn't do this either unless you wrote a bit of code to do so.