Assign New, Unique StudentID Numbers

[Guest]

Hi:

We have a Student Registration database we've been using on our job to
register students for classes we teach. Up until yesterday, we only had 140
students in tblStudents. The Primary Key for the table is called
StudentID[text field] and the last number is 0140.

I just imported about 4,400 new student names into our table. I want to give
each new student a unique StudentID number starting with 0141. Can someone
please show me how to do that in Access 2003?

Thanks,
Robert

 

[Guest]

Why don't you just use an auto-number field?

 

[Guest]

I've worked in another database program, Alpha Five, for many years. Our
cardinal rule was very simple, if a field will not be used for calcuating
numbers, it should be a text field. That holds true for phone numbers, social
security numbers, serial numbers, etc. I don't see any difference with our
StudentID number.

In addition, using a text field allows us to use leading zeros such as
"0043". Yes I know you can use an auto number and format the display with
leading zeros, but I don't get it when you can simply make the field text to
start with.

We were doing OK until we appended the new 4,400 students to our table. Now
if I can temporarily make the an auto number field starting with 141 for the
new records, all the way up to approximately 4400, that would be great. I
could then convert it back to a text field with leading zeros.

Robert

 

[David Cox]

Off the top of my head:

create a copy of orginal 140 student table

add an autonumber field.

if the numbers do not match, run an update query from old to new putting the
right information into each record.

reanme and save old table

rename new table to old name.

append the 4400 new records.

I think that this would get you there. There is probably a better way.

 

[Guest]

Lol, do whatever you prefer. If you want to get the numbers quickly, albeit
without leading zeros, all you have to do is insert a new auto-number field.
You can then convert that to a text field.
I do remember though that in the last few days somebody had asked for help
about automatic numbering in a text field, where he had alphanumeric IDs. You
may want to search for that or simply browse the forums and find a solution
that's more to your liking.

Good luck.

 

[CompGeek78]

Run this code to assign IDs to all of the students that don't have IDs.
As always, make sure that you test on a copy of your production
database, not on the live thing.

Public Sub AddID()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim intMax As Integer
Dim strID As String

Set cn = CurrentProject.AccessConnection
Set rs = New ADODB.Recordset
intMax = DMax("studentid", "tblStudent")

With rs
Set .ActiveConnection = cn
.Source = "SELECT * FROM tblStudent"
.LockType = adLockOptimistic
.CursorType = adOpenKeyset
.Open
rs.MoveFirst
Do While Not rs.EOF
If IsNull(rs!StudentID) Then
intMax = intMax + 1
strID = Format(CStr(intMax), "0000")
rs!StudentID = strID
End If
rs.MoveNext
Loop
End With

rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing
End Sub

When you add a new record, if you want it to automatically add a new
ID, you can use the following code on the BeforeInsert event:

Me.StudentID = Format(CStr(DMax("StudentID", "tblStudent") + 1),
"0000")

You will have to adjust the names of the table and field in the various
places in both pieces of code, but this should do what you need.

Keven

 

[Guest]

Roberet:

Here's a variation of Keven's approach using an SQL UPDATE operation. The
StudentID column must be indexed uniquely. It will also work witha table
where no rows's have StudentID values:

Public Sub AddStudentIDs()

Dim cmd As ADODB.Command
Dim strSQL As String
Dim lngNewNum As Long

lngNewNum = Val(Nz(DMax("StudentID", "Students"), 0))

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

Do While DCount("*", "Students", "StudentID IS NULL") > 0
lngNewNum = lngNewNum + 1
strSQL = "UPDATE Students " & _
"SET StudentID = """ & _
Format(lngNewNum, "0000") & """"
cmd.CommandText = strSQL
On Error Resume Next
cmd.Execute
On Error GoTo 0
Loop

Set cmd = Nothing

End Sub

Ken Sheridan
Stafford, England

 

[Guest]

Picking up on another of Keven's points you might like to take a look at my
demo for auto-incrementing numbers when inserting rows into a table via a
form in a multi-user environment at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps


It also allows for resetting the start number for the next number. When
using text you'd just need to format the value returned by the function and
assign the formatted value to the text column.

Ken Sheridan
Stafford, England

 

[Guest]

Hello Guys:

I truly appreciate the excellent suggestions. I think I'm going to start with
CompGeek78's script because that comes very close to what I envisioned. Then
I plan on working with Ken's suggestion.

As for Niniel, your suggestion wouldn't work because we would have to
overwrite the 140 StudentID's we currently have in tblStudents, which are
linked to over 200 records which use their StudentID numbers [TEXT].

OK guys, let me get started.

Once again, thanks to CompGeek78, Ken S. and David for their suggestions. If
I have any problems, I will definitely get back to you in this thread.

Robert

 

[Guest]

Hello CompGeek78:

For the life of me, I tried running it as a Public Procedure and could never
get the correct terminology to run it. It was placed in a Class Module called
Update_StudentID, but no matter what I tried, it would never run.

So I changed it to a Private Module and it worked like a charm. Wow! Thanks
so much. I still want to work with Ken's script because I see this as a
learning experience. You guys are terrific, thanks so much.

Robert

 

[Guest]

Hi Ken:

Thanks so much for showing me how you would do it. Are there any advantages
to doing it your way or is just another option? I noticed that both of you
are using ADO instead of DAO, is there a reason?

I just tried out CompGeek78's suggestion. I couldn't get it working as a
public procedure, so I changed it to a private one and it worked like a
charm. As soon as I have some free time, I want to try your technique. In
addition to the fact that we need this stuff working immediately, I want to
use this as a learning experience.

For starters, I'm going to have to figure out how to run a Public Procedure.

Once again, your suggestions are very much appreciated.

Robert

 

[Guest]

Robert:

Firstly let me apologise for rechristening you in my first reply.

As far as using ADO is concerned I guess we are just following the advice of
the holy trinity of Litwin,Getz, and Gunderloy who argued in the last edition
of the Access Developer's Handbook for the use of ADO, though DAO works fine
of course and there's no reason to believe it will not continue to do so into
the foreseeable future.

The fundamental difference between mine and Keven's is that his establishes
a recordset and iterates through it. Mine ostensibly uses an SQL set
operation, which generally is preferable to a procedural solution, but in
reality works in much the same way as Keven's as the UPDATE statement will
only update one row at a time as it will then raise an index violation error,
so in effect it iterates through each row with a NULL StudentID.
Consequently its not a better solution, just an alternative one. The
constructive use of error handling is something to note as part of the
learning experience you mention. I actually prefer the term 'exception
handling', which you'll come across in the literature, because it doesn't
have the pejorative implication of 'error'.

Ken Sheridan
Stafford, England