Sequential alphanumeric field

[greenr]

Hi Everyone,

Am trying to find an easy way of creating a sequential alphanumeric ID through a form consisting of the first 3 letters of a surname and a three digit number.

eg abc123, flw124, swe125, xxx126, yyy...

I know it seems a bit of a daft way style of ID, but it is for teaching purposes, and the data for the database is given to the students with this style of ID field already populated.

I can split it up and convert the "numbers" to integers using CInt, but am looking for a way of generating a new alphanumeric from a form during data entry. Am thinking something to do with DMax?

Can anyone help

Many thanks in advance

Bob

 

[jzwp22]

Since the value is obtained by concatenating a number with a part of the surname, it is technically a calculated value and, in general, calculated values are not store in the table. I understand that this is a teaching assignment, but why teach them something that is not a best practice?


With that said, you will indeed need the DMax() function, but first you will have to create a query that strips the text portion off and convert the remaining number to an integer. You would then reference that query in the DMax() function

The query would look something like this:

SELECT Cint(right([myID],3)) AS IDNumberPortion
FROM Tablename


You will need code in the before insert event (if my memory serves) of the form. The code would look something like this:

me.myIDcontrolname=left(me.surnamecontrolname,3) & DMax("IDNumberPortion", "queryname")+1

Now if you start out with numbers less than 100 you will need to add the leading zeroes, so you will want to use the format() function

me.myIDcontrolname=left(me.surnamecontrolname,3) & format(DMax("IDNumberPortion", "queryname")+1,"000")

Of course, for the first record the code above will error out since you would not have any records. So you will need to check for the presence of records, if records exist then use the above expression; if no records exist yet use this expression


me.myIDcontrolname=left(me.surnamecontrolname,3) & "001"

You will probably need to use an IF..THEN...ELSE...END IF in conjunction with the DCount() function.

 

[greenr]

Hi jzwp22, very many thanks for your reply, and yes I fully agree with your comment about best practices, for the particular exercise I wouldn't even use a database as it would all fit on the back of a cigarette packet, and be easier to handle, but it is set by someone from a different school who seems to be playing "Guess what's in my head" games.

Thanks again, am off to try out your suggestions.