Missing record with autonumber

[Guest]

I use the autonumber field [Student ID] as the primary key to keep track of
the students records at my school.

A student was deleted by accident (I have to found why, but that's not the
problem here).

Let's say his [Student ID] number was 500. I have all his info in a backup
file with the ID 500, I tried to copy and paste the record but a new number
is assigned to the student, let's say 600.

Obvioulsy all his grades and attendance records refer to number 500, what
can I do? Grades are due Monday!

Any help will be greatly appreciated :-[

 

[Dirk Goldgar]

I use the autonumber field [Student ID] as the primary key to keep
track of the students records at my school.

A student was deleted by accident (I have to found why, but that's
not the problem here).

Let's say his [Student ID] number was 500. I have all his info in a
backup file with the ID 500, I tried to copy and paste the record but
a new number is assigned to the student, let's say 600.

Obvioulsy all his grades and attendance records refer to number 500,
what can I do? Grades are due Monday!

Any help will be greatly appreciated :-[

If there is not currently any record in the table with [Student ID] =
500, then you can use a query to insert the record with that ID. Here's
how:

1. Link to the table in the backup. Let's suppose that the current
table is called "Students", and the linked backup table is called
"Students1".

2. Execute this query:

INSERT INTO Students
SELECT * FROM Students1
WHERE [Students1].[Student ID] = 500;

That ought to do it.

If you had referential integrity enforced on the relationship between
the Students and the Grades table and Attendance table, the record
couldn't have been deleted without also deleting the related records.
Unless you set Cascade Deletes on the relationship, that would be very
hard to do by accident.

 

[Joseph Meehan]

I use the autonumber field [Student ID] as the primary key to keep
track of the students records at my school.

A student was deleted by accident (I have to found why, but that's
not the problem here).

Let's say his [Student ID] number was 500. I have all his info in a
backup file with the ID 500, I tried to copy and paste the record but
a new number is assigned to the student, let's say 600.

Obvioulsy all his grades and attendance records refer to number 500,
what can I do? Grades are due Monday!

Any help will be greatly appreciated :-[

I suggest you may not want to use Autonumber for that use. Autonumbers are
designed to provide unique numbers. It in not designed to provide numbers
in order and for a number of reasons may not do so. As a result using them
in any application where the user sees the numbers is likely to end up with
confusion.

There are other ways of providing the numbers you want depending on the
particual application.

 

[Guest]

I use the autonumber field [Student ID] as the primary key to keep
track of the students records at my school.

A student was deleted by accident (I have to found why, but that's
not the problem here).

Let's say his [Student ID] number was 500. I have all his info in a
backup file with the ID 500, I tried to copy and paste the record but
a new number is assigned to the student, let's say 600.

Obvioulsy all his grades and attendance records refer to number 500,
what can I do? Grades are due Monday!

Any help will be greatly appreciated :-[

If there is not currently any record in the table with [Student ID] =
500, then you can use a query to insert the record with that ID. Here's
how:

1. Link to the table in the backup. Let's suppose that the current
table is called "Students", and the linked backup table is called
"Students1".

2. Execute this query:

INSERT INTO Students
SELECT * FROM Students1
WHERE [Students1].[Student ID] = 500;

That ought to do it.

If you had referential integrity enforced on the relationship between
the Students and the Grades table and Attendance table, the record
couldn't have been deleted without also deleting the related records.
Unless you set Cascade Deletes on the relationship, that would be very
hard to do by accident.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

This the query coding:
Prospect Students = original Db
Prospect Students 1 = backup

INSERT INTO [Prospect Students]
SELECT *
FROM [Prospect Students 1]
WHERE [Prospect Students 1].[Student ID]=1814;

when I run it it asks for the Student ID, I typed 1814 and msgbox is
displaying indicating that 4,500 records will be added (this is the entire
No. of records in backup)

Thank yo for your help again.

 

[Dirk Goldgar]

I use the autonumber field [Student ID] as the primary key to keep
track of the students records at my school.

A student was deleted by accident (I have to found why, but that's
not the problem here).

Let's say his [Student ID] number was 500. I have all his info in a
backup file with the ID 500, I tried to copy and paste the record
but a new number is assigned to the student, let's say 600.

Obvioulsy all his grades and attendance records refer to number 500,
what can I do? Grades are due Monday!

Any help will be greatly appreciated :-[

If there is not currently any record in the table with [Student ID] =
500, then you can use a query to insert the record with that ID.
Here's how:

1. Link to the table in the backup. Let's suppose that the current
table is called "Students", and the linked backup table is called
"Students1".

2. Execute this query:

INSERT INTO Students
SELECT * FROM Students1
WHERE [Students1].[Student ID] = 500;

That ought to do it.

If you had referential integrity enforced on the relationship between
the Students and the Grades table and Attendance table, the record
couldn't have been deleted without also deleting the related records.
Unless you set Cascade Deletes on the relationship, that would be
very hard to do by accident.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

This the query coding:
Prospect Students = original Db
Prospect Students 1 = backup

INSERT INTO [Prospect Students]
SELECT *
FROM [Prospect Students 1]
WHERE [Prospect Students 1].[Student ID]=1814;

when I run it it asks for the Student ID, I typed 1814 and msgbox is
displaying indicating that 4,500 records will be added (this is the
entire No. of records in backup)

Thank yo for your help again.

The fact that it asks for Student ID as a parameter implies that there
is no field in [Prospect Students 1] by that name. Double-check the
table design -- maybe the field is actually named "StudentID", or is
spelled wrong, or is just named "ID" but has its Caption property set to
"Student ID". Something like that.

Also, make sure that both tables, [Prospect Students] and [Prospect
Students 1], have the same field names.

(Side note: the reason it wants to add all the records is that, when
you fill in 1814 for the parameter, the query wants to select all
records where 1814 = 1814. Naturally, that's all the records.)

 

[Guest]

Thank you ,Dirk
You were right, my field name does not have a space. query works ok!

Have a nice weekend

message I use the autonumber field [Student ID] as the primary key to keep
track of the students records at my school.

A student was deleted by accident (I have to found why, but that's
not the problem here).

Let's say his [Student ID] number was 500. I have all his info in a
backup file with the ID 500, I tried to copy and paste the record
but a new number is assigned to the student, let's say 600.

Obvioulsy all his grades and attendance records refer to number 500,
what can I do? Grades are due Monday!

Any help will be greatly appreciated :-[

If there is not currently any record in the table with [Student ID] =
500, then you can use a query to insert the record with that ID.
Here's how:

1. Link to the table in the backup. Let's suppose that the current
table is called "Students", and the linked backup table is called
"Students1".

2. Execute this query:

INSERT INTO Students
SELECT * FROM Students1
WHERE [Students1].[Student ID] = 500;

That ought to do it.

If you had referential integrity enforced on the relationship between
the Students and the Grades table and Attendance table, the record
couldn't have been deleted without also deleting the related records.
Unless you set Cascade Deletes on the relationship, that would be
very hard to do by accident.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

This the query coding:
Prospect Students = original Db
Prospect Students 1 = backup

INSERT INTO [Prospect Students]
SELECT *
FROM [Prospect Students 1]
WHERE [Prospect Students 1].[Student ID]=1814;

when I run it it asks for the Student ID, I typed 1814 and msgbox is
displaying indicating that 4,500 records will be added (this is the
entire No. of records in backup)

Thank yo for your help again.

The fact that it asks for Student ID as a parameter implies that there
is no field in [Prospect Students 1] by that name. Double-check the
table design -- maybe the field is actually named "StudentID", or is
spelled wrong, or is just named "ID" but has its Caption property set to
"Student ID". Something like that.

Also, make sure that both tables, [Prospect Students] and [Prospect
Students 1], have the same field names.

(Side note: the reason it wants to add all the records is that, when
you fill in 1814 for the parameter, the query wants to select all
records where 1814 = 1814. Naturally, that's all the records.)

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)