Primary key derived from name fields

[Brucear]

I want to generate a StudentID (that I want to be the Primary Key) from the
first 3 letters of the LastName and then the first two letters of the
FirstName automatically after I capture the student names. Can this be done
so that if a duplicate is generated a numeral (1,2 or 3) is added to the key?
Can anyone help?

 

[Klatuu]

Dim strStudentID As String
Dim intNextNum as Integer

strStudentID = Left(Me.LastName,3) & Left(Me.FirstName,2)
intNextNum = 1
Do While True
If IsNull(DLookup("[StudentID]", "StudentTable", "[StudentID] = '" & _
strStudentID & "'" Then
Exit Do
Else
strStudentID = strStudentID & Cstr(intNextNum)
intNextNum = intNextNum + 1
End If
Loop
Me.txtStudentId = strStudentID

 

[Brucear]

Thanks for all this work!
Where precisely do I place this code? I would like th e code generated from
my StudentCapture Form?
I right clicked on the StudentID field in the form ... Build Event ... Code
Builder but I don't understand enough about the programming to get it to work.
Sorry!
--
Bruce Rae

Dim strStudentID As String
Dim intNextNum as Integer

strStudentID = Left(Me.LastName,3) & Left(Me.FirstName,2)
intNextNum = 1
Do While True
If IsNull(DLookup("[StudentID]", "StudentTable", "[StudentID] = '" & _
strStudentID & "'" Then
Exit Do
Else
strStudentID = strStudentID & Cstr(intNextNum)
intNextNum = intNextNum + 1
End If
Loop
Me.txtStudentId = strStudentID

I want to generate a StudentID (that I want to be the Primary Key) from the
first 3 letters of the LastName and then the first two letters of the
FirstName automatically after I capture the student names. Can this be done
so that if a duplicate is generated a numeral (1,2 or 3) is added to the key?
Can anyone help?

 

[Jerry Whittle]

What are you going to do if a student changes their name? Women often do when
marrying or divorcing and often their children sometimes do to.

I'm one of "those" database people that believe a primary key should not
have any "meaning" except to identify a particular record in a table.

 

[Brucear]

These are High School Students 13 - 16 year-olds.

 

[Douglas J. Steele]

There's absolutely no reason why their names won't change. Adoptions happen.

 

[Klatuu]

One place that will work would be the Current event of the form:

If Me.NewRecord Then
'Put the code here
End if

Thanks for all this work!
Where precisely do I place this code? I would like th e code generated from
my StudentCapture Form?
I right clicked on the StudentID field in the form ... Build Event ... Code
Builder but I don't understand enough about the programming to get it to work.
Sorry!
--
Bruce Rae

Dim strStudentID As String
Dim intNextNum as Integer

strStudentID = Left(Me.LastName,3) & Left(Me.FirstName,2)
intNextNum = 1
Do While True
If IsNull(DLookup("[StudentID]", "StudentTable", "[StudentID] = '" & _
strStudentID & "'" Then
Exit Do
Else
strStudentID = strStudentID & Cstr(intNextNum)
intNextNum = intNextNum + 1
End If
Loop
Me.txtStudentId = strStudentID

I want to generate a StudentID (that I want to be the Primary Key) from the
first 3 letters of the LastName and then the first two letters of the
FirstName automatically after I capture the student names. Can this be done
so that if a duplicate is generated a numeral (1,2 or 3) is added to the key?
Can anyone help?

 

[Brucear]

True - then I will change the name which will change the StudentID and I will
probably do it manually as I only teach about 300 students each year ;-)

 

[Brucear]

It doesn't like something about the line that starts:
If IsNull(Dlookup....
A syntax error...?
Regards
--
Bruce Rae

One place that will work would be the Current event of the form:

If Me.NewRecord Then
'Put the code here
End if

Thanks for all this work!
Where precisely do I place this code? I would like th e code generated from
my StudentCapture Form?
I right clicked on the StudentID field in the form ... Build Event ... Code
Builder but I don't understand enough about the programming to get it to work.
Sorry!
--
Bruce Rae

Dim strStudentID As String
Dim intNextNum as Integer

strStudentID = Left(Me.LastName,3) & Left(Me.FirstName,2)
intNextNum = 1
Do While True
If IsNull(DLookup("[StudentID]", "StudentTable", "[StudentID] = '" & _
strStudentID & "'" Then
Exit Do
Else
strStudentID = strStudentID & Cstr(intNextNum)
intNextNum = intNextNum + 1
End If
Loop
Me.txtStudentId = strStudentID

:

I want to generate a StudentID (that I want to be the Primary Key) from the
first 3 letters of the LastName and then the first two letters of the
FirstName automatically after I capture the student names. Can this be done
so that if a duplicate is generated a numeral (1,2 or 3) is added to the key?
Can anyone help?

 

[Klatuu]

Sorry, I had a syntax error, it should be:
If IsNull(DLookup("[StudentID]", "StudentTable", "[StudentID] = '" & _
strStudentID & "'") Then

I assume you have changed the field and table names to what yours really are.

It doesn't like something about the line that starts:
If IsNull(Dlookup....
A syntax error...?
Regards
--
Bruce Rae

One place that will work would be the Current event of the form:

If Me.NewRecord Then
'Put the code here
End if

Thanks for all this work!
Where precisely do I place this code? I would like th e code generated from
my StudentCapture Form?
I right clicked on the StudentID field in the form ... Build Event ... Code
Builder but I don't understand enough about the programming to get it to work.
Sorry!
--
Bruce Rae


:

Dim strStudentID As String
Dim intNextNum as Integer

strStudentID = Left(Me.LastName,3) & Left(Me.FirstName,2)
intNextNum = 1
Do While True
If IsNull(DLookup("[StudentID]", "StudentTable", "[StudentID] = '" & _
strStudentID & "'" Then
Exit Do
Else
strStudentID = strStudentID & Cstr(intNextNum)
intNextNum = intNextNum + 1
End If
Loop
Me.txtStudentId = strStudentID

:

I want to generate a StudentID (that I want to be the Primary Key) from the
first 3 letters of the LastName and then the first two letters of the
FirstName automatically after I capture the student names. Can this be done
so that if a duplicate is generated a numeral (1,2 or 3) is added to the key?
Can anyone help?

 

[John Vinson]

These are High School Students 13 - 16 year-olds.

What if the student's mother remarries and changes the family name?
What if the child changes her first name, with parental permission?
What GOOD does this do you anyway? Will everyone who uses the database
be able to remember that JOHDA1 is David Johnson and JOHDA2 is Darlene
Johannsen?

I'd really suggest just using a sequentially assigned integer
StudentNo; you can use a Combo Box on your form to display the
student's full name (and other identifying information, since you
might have two Bill Smiths) and store the ID. Your proposed
"intelligent" key is, in practice, more of a hassle than a benefit,
and with modern technology *it is not needed*.

John W. Vinson[MVP]

 

[Klatuu]

That will work as long as you don't have any related tables. If you don't,
you don't have a true relational database, If you do, you will leave orphan
records in the related tables.

 

[Brucear]

It still didn't compute! But then I noticed that there was a second missing
")"
i.e.
If IsNull(DLookup("[StudentID]", "StudentTable", "[StudentID] = '" & _
strStudentID & "'")) Then

--
Bruce Rae

Sorry, I had a syntax error, it should be:
If IsNull(DLookup("[StudentID]", "StudentTable", "[StudentID] = '" & _
strStudentID & "'") Then

I assume you have changed the field and table names to what yours really are.

It doesn't like something about the line that starts:
If IsNull(Dlookup....
A syntax error...?
Regards
--
Bruce Rae

One place that will work would be the Current event of the form:

If Me.NewRecord Then
'Put the code here
End if

:

Thanks for all this work!
Where precisely do I place this code? I would like th e code generated from
my StudentCapture Form?
I right clicked on the StudentID field in the form ... Build Event ... Code
Builder but I don't understand enough about the programming to get it to work.
Sorry!
--
Bruce Rae


:

Dim strStudentID As String
Dim intNextNum as Integer

strStudentID = Left(Me.LastName,3) & Left(Me.FirstName,2)
intNextNum = 1
Do While True
If IsNull(DLookup("[StudentID]", "StudentTable", "[StudentID] = '" & _
strStudentID & "'" Then
Exit Do
Else
strStudentID = strStudentID & Cstr(intNextNum)
intNextNum = intNextNum + 1
End If
Loop
Me.txtStudentId = strStudentID

:

I want to generate a StudentID (that I want to be the Primary Key) from the
first 3 letters of the LastName and then the first two letters of the
FirstName automatically after I capture the student names. Can this be done
so that if a duplicate is generated a numeral (1,2 or 3) is added to the key?
Can anyone help?

 

[Klatuu]

Doh!
Sorry, doing too many things at once today.

It still didn't compute! But then I noticed that there was a second missing
")"
i.e.
If IsNull(DLookup("[StudentID]", "StudentTable", "[StudentID] = '" & _
strStudentID & "'")) Then

--
Bruce Rae

Sorry, I had a syntax error, it should be:
If IsNull(DLookup("[StudentID]", "StudentTable", "[StudentID] = '" & _
strStudentID & "'") Then

I assume you have changed the field and table names to what yours really are.

It doesn't like something about the line that starts:
If IsNull(Dlookup....
A syntax error...?
Regards
--
Bruce Rae


:

One place that will work would be the Current event of the form:

If Me.NewRecord Then
'Put the code here
End if

:

Thanks for all this work!
Where precisely do I place this code? I would like th e code generated from
my StudentCapture Form?
I right clicked on the StudentID field in the form ... Build Event ... Code
Builder but I don't understand enough about the programming to get it to work.
Sorry!
--
Bruce Rae


:

Dim strStudentID As String
Dim intNextNum as Integer

strStudentID = Left(Me.LastName,3) & Left(Me.FirstName,2)
intNextNum = 1
Do While True
If IsNull(DLookup("[StudentID]", "StudentTable", "[StudentID] = '" & _
strStudentID & "'" Then
Exit Do
Else
strStudentID = strStudentID & Cstr(intNextNum)
intNextNum = intNextNum + 1
End If
Loop
Me.txtStudentId = strStudentID

:

I want to generate a StudentID (that I want to be the Primary Key) from the
first 3 letters of the LastName and then the first two letters of the
FirstName automatically after I capture the student names. Can this be done
so that if a duplicate is generated a numeral (1,2 or 3) is added to the key?
Can anyone help?