Primary key derived from name fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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
 
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


Klatuu said:
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 said:
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?
Click to expand...
Click to expand...
 
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.
 
One place that will work would be the Current event of the form:

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

Brucear said:
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


Klatuu said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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 ;-)
 
It doesn't like something about the line that starts:
If IsNull(Dlookup....
A syntax error...?
Regards
--
Bruce Rae


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

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

Brucear said:
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


Klatuu said:
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?
Click to expand...
Click to expand...
Click to expand...
 
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.

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


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

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

Brucear said:
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?
Click to expand...
Click to expand...
Click to expand...
 
These are High School Students 13 - 16 year-olds.
Click to expand...

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


Klatuu said:
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.

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


Klatuu said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Doh!
Sorry, doing too many things at once today.

Brucear said:
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


Klatuu said:
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.

Brucear said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top