Is it possible to create an sequestial ID for each Record

B

Benkong2

I have the need for each record to have a sequential ID number. For
example I nee the characters ECYY01 "YY would be the last 2 digits of
the current year. So it would be EC0401 next record would be EC0402 etc.

I want this to happen to each new record when it is added to the table.

My pseudo is this

take a record in a separate table tblseednumber

When the entry form loads populate the uniqueID field with a number that
is like EC0401 and gets incremented by one each time a new record is
added. This could then be in the table that holds the other information.

Any suggestions, examples and code to help would be greatly appreciated.

Thanks,

Here is some code that I am trying to work out.

Dim strFirstTag As String, strAlphaBit As String, strTemp As String
Dim iCounter As Integer, iNoTires As Integer, lNumericBit As Long

lNumericBit = (Right(strFirstTag, 4))'This gets the YYXX number part of
the record
strAlphaBit = Left(strFirstTag, 2) 'This takes the EC part
of the string
For iCounter = 0 To iNoTires - 1
strTemp = Trim(Str(lNumericBit + iCounter))
Do While Len(strTemp) < 4
strTemp = "0" & strTemp 'Correct xx00 to xx000 + 1
etc
Loop
strTagArray(iCounter) = strAlphaBit & strTemp 'Put the
val into the array
strMsg = strMsg & strAlphaBit & strTemp & vbTab & vbCrLf
Next iCounter
 
M

Marin Kostov

I have just tried something, but I don't know if it will help you...
Make that field AutoNumber. So it will increment for any new record added in
the DB.
Now we have a sequence, and we need to have the non-numeric chararcters.
So in the Field properties (Table design view) in the Format row type:
"EC04"00

"ABC" - Display anything inside quotation marks as literal characters.
0 - Digit placeholder. Display a digit or 0.

When you open the table, you will see EC0401, next record will EC0402 and so
on.
 
T

Tony Toews

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can the below code be modified for select. Drive Letter, Dir. 2
How to count existing records where StudentID = ThisStudent 2
Run-time error 76 1
return without gosub error 0
Access Serial Number VB Programming 0
parameter pop up box problem 7
QBF on Steroids with List Control --almost there, but problem 3
MS Download Create QBF-how to use listbox 10

Top