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

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

"Robert T" wrote:

> 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

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

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.


"Robert T" <(E-Mail Removed)> wrote in message
news:12318B06-15B4-4E5D-AFE1-(E-Mail Removed)...
> 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

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.

"Robert T" wrote:

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

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

Robert T wrote:
> 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

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

"Robert T" wrote:

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

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/...g=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

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

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