simple expression builder problem

[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.

 

[John Big Booty]

First up welcome to the forum

Have a look at the attached DB to see how this can be done.

Check the On Current event of Form1 to see how a custom auto number can be created, this method will ensure that you get a sequential series of number, something that can not be guaranteed with the built in Auto number function.

 

[MStef]

Look at "DemoNextPKA2003.mdb" (atachment, zip).
Look at Form (VBA), Table, Qurery.
Open Form and try.
I expect you to add a code (VBA) if the user changes
a FirstName or a LastName.

 

[MaxMiller]

@ John Big Booty, is there a way to reset the autonumber back to zero?

I will use the autonumber to identify individual item orders of a customer.
When I move to the next customer, I want it to reset back to zero.

Thank you.

 

[pr2-eugin]

Hello MaxMiller, Welcome to AWF..

AutoNumber does not work like what we want it to.. AutoNumber makes sure that the Number generated or Unique.. It can be Sequential, Positive, Negative.. but never a duplicate.. If you could describe your problem a bit more in detail in a New Thread, people would be happy to help..

 

[MaxMiller]

Hello MaxMiller, Welcome to AWF..

AutoNumber does not work like what we want it to.. AutoNumber makes sure that the Number generated or Unique.. It can be Sequential, Positive, Negative.. but never a duplicate.. If you could describe your problem a bit more in detail in a New Thread, people would be happy to help..

Thanks pr2-eugin! I'll do just that.