Auto Incrementing

[JD via AccessMonster.com]

I am trying to create a field where the primary key field will produce JDP-
001; where the three letters come from the first name, middle intial and
last name of my table. I want to auto increment only if the three letters
are reproduced.

e.g. JDP-001
JDP-002
YPT-001
YRT-001
RPT-001

When you reply, let me know if I can do it in the table design view or the
form design view or both.

--
Message posted via http://www.accessmonster.com

[shumaker]

I would assume you are trying to do this when inserting new records?

There is a function, Left(firstname, 1), or maybe it is lowercase,
left(), anyhow, it will extract the first letter of a field(the second
parameter tells how many letters).

You could extract the first letter of each field and concactenate them
together.

As far as determining if that Primary key already exists, I would do a
query on the primary key field to find all keys that start with that 3
letter string, and sort them, look at the number on the last one(using
the Right() function to extract just the number), increment the number,
and then concatenate that onto your three letter combo.

I don't think this can be done in the table design view, but I'm not
absolutely sure. The only time I insert new records is in DTS
packages, and I would use a scripted transfoormation link in this case
I think. I'm a noob though, so there are probably better ways to do
this.

[Kevin Nechodom]

Access really isn't up to this job. However, you can do it
by creating a function that stores a "Next Number" for each
Initials combination in an "Initials" table. Then you can
call the function on the form that inserts the data.

Without real database triggers, I'm afraid that's about the
best you can do, if you HAVE to have that as a primary key.

Kevin

JD via AccessMonster.c om<fo***@nospam .AccessMonster. com>

5/9/2005 9:56:21 AM >>>I am trying to create a field where the primary key field
will produce JDP-
001; where the three letters come from the first name,
middle intial and
last name of my table. I want to auto increment only if
the three letters
are reproduced.

e.g. JDP-001
JDP-002
YPT-001
YRT-001
RPT-001

When you reply, let me know if I can do it in the table
design view or the
form design view or both.

--
Message posted via http://www.accessmonster.com

[JD via AccessMonster.com]

The field that I have created will be used as the primary key, therefore I
need to make each record unique.

I know how to auto increment with numbers, but I was trying to find out how
to do it with letters and numbers combination.

I need someone to add more to Shumaker comment.

--
Message posted via http://www.accessmonster.com

[shumaker]

Explain for me. What is the scenario for adding records? Are you
wanting the user to be able to use the Add new record button of a form
and add records individually? Or is this in an insert query where you
are inserting a batch of records?

[rkc]

JD via AccessMonster.c om wrote:

The field that I have created will be used as the primary key, therefore I
need to make each record unique.

I know how to auto increment with numbers, but I was trying to find out how
to do it with letters and numbers combination.

I need someone to add more to Shumaker comment.

What's the point of a letter number combination being used as an
artificial primary key? Is it a requirement from the client/boss or
something you dreamed up yourself?

[JD via AccessMonster.com]

I want to have it semi-automated on the form, where the user enter the
three intitials and populate the next number sequence if the member id
three initials are the same.

The reason why I want it like this, because I don't want the user to have
to keep guessing what number is in next number sequence for a particular
three initials pair.

Right now, I have created a input mask as such: >LLL\-000 for the table
field: Member ID. It works fine, but like I explained in the last
paragraph the user will have to keep guessing what's the next number
sequence until they achieve the correct one, when Access prompt them that
the member ID is not unique.

When anyone creates a database we want them to be user friendly.
Kevin:
Explained to me how to write a simple Next function for my application. I
have just begun to learn VBA and I don't know all the bells and whistles of
this powerful program.

--
Message posted via http://www.accessmonster.com

[Alan Webb]

JD,
I keep a one row table with as many columns as I need for each different
sequence in my database. I have code I wrote that retrieves the old
sequence number from this table and increments it to create my new number.
It is designed to fail if the update to the sequence table fails. It works
pretty well. In your case you could concantenate together the text you want
appended to the number with a function that returns the next sequence
number. Sample code for the sequence function is below:

Public Function NextItemNo() As Long
On Error GoTo Err_NextItemNo

Dim cn As New ADODB.Connectio n
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim SQL As String
Dim RetVal As Long

Set cn = CodeProject.Con nection

SQL = "SELECT ITEM_NO + 10 AS NEW_ITEM_NO FROM SEQUENCE_TBL;"

With rs
.ActiveConnecti on = cn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.Source = SQL
.Open
If Not .EOF Then
RetVal = !NEW_ITEM_NO
Else
SQL = "SELECT MAX(CLNG(NZ(ITE M_NO,0))) + 10 AS NEW_ITEM_NO FROM
ITEM_MASTER_TBL ;"
.Requery
If Not .EOF Then
RetVal = !NEW_ITEM_NO
End If
End If
.Close
End With

SQL = "UPDATE SEQUENCE_TBL SET ITEM_NO = " & RetVal & ";"

With cmd
.ActiveConnecti on = cn
.CommandType = adCmdText
.CommandText = SQL
.Execute
End With

Done:
On Error Resume Next
NextItemNo = RetVal
Set rs = Nothing
Set cmd = Nothing
cn.Close
Set cn = Nothing

Exit_NextItemNo :
Exit Function

Err_NextItemNo:
Debug.Print "There was an unexpected error in getting a new item number:
" & Err.DESCRIPTION
Resume Done
End Function

The basic algorythm would work as a stored procedure in a buncs of rdbms'
with changes to account for differences in the scripting language of the
rdbms in question.
--
Alan Webb
kn*******@SPAMh otmail.com
"It's not IT, it's IS
"JD via AccessMonster.c om" <fo***@nospam.A ccessMonster.co m> wrote in message
news:de******** *************** *******@AccessM onster.com...

I am trying to create a field where the primary key field will produce JDP-
001; where the three letters come from the first name, middle intial and
last name of my table. I want to auto increment only if the three letters
are reproduced.

e.g. JDP-001
JDP-002
YPT-001
YRT-001
RPT-001

When you reply, let me know if I can do it in the table design view or the
form design view or both.

--
Message posted via http://www.accessmonster.com

[Kevin Nechodom]

I would agree with Alan that this would be the simplest way
to handle the job, as long as you don't need identical
initials to have consecutive numbers.
Like:
ABC-001
XYZ-002
MNO-003
ABC-004

If you DO need them to be consecutive, change the code below
to query a table like INITIALS_SEQ instead of SEQUENCE_TBL,
and insert 1 row per unique set of initials.

Table: InitialsSeq
- Initials Text(3) (Primary Key)
- NextSeq: Integer

You will have to be more careful here, because you will have
to inspect the table for existing initials and insert a new
row if not there.

Public Function MyPrimaryKey(My Initials as string) as
String
' Step 1: Test to see if new initials, retrieving CurrSeq
' Step 2: If New,
' Step 2a - insert a new record with NextSeq = 2
' Step 2b - Set CurrSeq = 1
' Step 3: Else,
' Step 3a: - Update the existing record with NextSeq =
CurrSeq + 1
' Step 4: Format Key with initials and CurrSeq
' Step 5: Return Key
End Function

Kevin

Alan Webb<kn*******@ hotSPAMmail.com > 5/10/2005 9:53:57

AM >>>JD,
I keep a one row table with as many columns as I need for
each different
sequence in my database. I have code I wrote that
retrieves the old
sequence number from this table and increments it to createmy new number.
It is designed to fail if the update to the sequence table
fails. It works
pretty well. In your case you could concantenate together
the text you want
appended to the number with a function that returns the
next sequence
number. Sample code for the sequence function is below:

Public Function NextItemNo() As Long
On Error GoTo Err_NextItemNo

Dim cn As New ADODB.Connectio n
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim SQL As String
Dim RetVal As Long

Set cn = CodeProject.Con nection

SQL = "SELECT ITEM_NO + 10 AS NEW_ITEM_NO FROM
SEQUENCE_TBL ;"

With rs
.ActiveConnecti on = cn
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.Source = SQL
.Open
If Not .EOF Then
RetVal = !NEW_ITEM_NO
Else
SQL = "SELECT MAX(CLNG(NZ(ITE M_NO,0))) + 10 AS
NEW_ITEM_NO FROM
ITEM_MASTER_TB L;"
.Requery
If Not .EOF Then
RetVal = !NEW_ITEM_NO
End If
End If
.Close
End With

SQL = "UPDATE SEQUENCE_TBL SET ITEM_NO = " & RetVal &
";"

With cmd
.ActiveConnecti on = cn
.CommandType = adCmdText
.CommandText = SQL
.Execute
End With

Done:
On Error Resume Next
NextItemNo = RetVal
Set rs = Nothing
Set cmd = Nothing
cn.Close
Set cn = Nothing

Exit_NextItemN o:
Exit Function

Err_NextItemNo :
Debug.Print "There was an unexpected error in getting anew item number:
" & Err.DESCRIPTION
Resume Done
End Function

The basic algorythm would work as a stored procedure in a
buncs of rdbms'
with changes to account for differences in the scripting
language of the
rdbms in question.
--
Alan Webb
kn*******@SPAM hotmail.com
"It's not IT, it's IS
"JD via AccessMonster.c om" <fo***@nospam.A ccessMonster.co m>wrote in message
news:de******* *************** ********@Access Monster.com...

I am trying to create a field where the primary key field

will produce JDP-

001; where the three letters come from the first name,

middle intial and

last name of my table. I want to auto increment only if

the three letters

are reproduced.

e.g. JDP-001
JDP-002
YPT-001
YRT-001
RPT-001

When you reply, let me know if I can do it in the table

design view or the

form design view or both.

--
Message posted via http://www.accessmonster.com