re-assign TeacherID to null or zero

[Tim]

I have designed a form on which student info is displayed and a combo box
that pull teacher info out (teacherID, teachername, etc.). A user will click
on the combo box to assign a teacher to that specific student.
Now, that user wants to go back to delete that teacher. That means the
teacherId this time should be null or zero. How can I do that?
Thank you for your help in advance. -tim

 

[Dirk Goldgar]

I have designed a form on which student info is displayed and a combo box
that pull teacher info out (teacherID, teachername, etc.). A user will
click
on the combo box to assign a teacher to that specific student.
Now, that user wants to go back to delete that teacher. That means the
teacherId this time should be null or zero. How can I do that?
Thank you for your help in advance. -tim

When you say "delete the teacher", do you mean delete the teacher *from the
student's record* -- thus removing the link between the student and the
teacher? or do you mean to delete the teacher entirely from the database --
from Teachers table?

I would guess that it's the former you have in mind -- removing the link
between the student and the teacher. If the combo box on the student form
is bound to a TeacherID field in the student table, then all the user has to
to do is delete the value currently in the combo box, and the bound
TeacherID field will be set to Null. That's the simplest case.

But if you're using code in one of the combo box's events (AfterUpdate or
Click, for example) to set the student-teacher link, then you'll probably
have to modify that code. Without more information about the tables
involved and how the combo box is set up, I can't say more.

 

[Tim]

Hi Dirk:
Thanks very much for your post. Your guess is right; I mean removing the link
between the student and the teacher. The form bounds to tbl student and the
combo box on it bounds to tbl teacher. The problem is tblTeacher has the
TeacherId as a primary key and an auto-number that start by 1. I prefer to
the simplest case you mentioned below. However, since the TeacherId is a
primary key and already seeded at 1, I can't add a blank record into
tblTeacher so that the user would select that blank record (Id=0 or null) to
delete the link.

Can I change the autonumber (seed=1 to seed=0 on the SQL server backend) so
that it re-arranges the current TeacherIds (i.e., starting from 0)
automatically? That way I can assign TeacherId=0 and the rest of the fields
will be an empty record. Thanks

:

 

[Dirk Goldgar]

Hi Dirk:
Thanks very much for your post. Your guess is right; I mean removing the
link
between the student and the teacher. The form bounds to tbl student and
the
combo box on it bounds to tbl teacher. The problem is tblTeacher has the
TeacherId as a primary key and an auto-number that start by 1. I prefer to
the simplest case you mentioned below. However, since the TeacherId is a
primary key and already seeded at 1, I can't add a blank record into
tblTeacher so that the user would select that blank record (Id=0 or null)
to
delete the link.

This doesn't make sense to me, so at least one of us is confused. <g>
Please post the following:

1) A description of tblStudent, with a list of its fields

2) A description of tblTeacher, with a list of its fields

3) If there's any other table involved in this relationship, a description
of that table

4) The RecordSource property of the form. If it's a query, post the SQL of
that query.

5) The ControlSource property of the combo box.

6) The RowSource property of the combo box.

 

[Tim]

Good questions...
1) A description of tblStudent, with a list of its fields
->tblStudent(TeacherId, StudNum(PK), StuName, StudAddress)
2) A description of tblTeacher, with a list of its fields
->tblTeacher(TeacherId(PK), TeacherName, TeacherAddress)
3) If there's any other table involved in this relationship, a description
of that table
->Nope.
4) The RecordSource property of the form. If it's a query, post the SQL...
->SELECT tblStudent.TeacherId, tblStudent.StuNum, tblStudent.StudName,
tblStudent.StudAddress FROM tblStudent;
5) The ControlSource property of the combo box.
->TeacherId
6) The RowSource property of the combo box.
->SELECT [tblTeacher].[TeacherId], [tblTeacher].[Name],
[tblTeacher].[Address] FROM [tblTeacher];

 

[Dirk Goldgar]

Good questions...
1) A description of tblStudent, with a list of its fields
->tblStudent(TeacherId, StudNum(PK), StuName, StudAddress)
2) A description of tblTeacher, with a list of its fields
->tblTeacher(TeacherId(PK), TeacherName, TeacherAddress)
3) If there's any other table involved in this relationship, a description
of that table
->Nope.
4) The RecordSource property of the form. If it's a query, post the
SQL...
->SELECT tblStudent.TeacherId, tblStudent.StuNum, tblStudent.StudName,
tblStudent.StudAddress FROM tblStudent;
5) The ControlSource property of the combo box.
->TeacherId
6) The RowSource property of the combo box.
->SELECT [tblTeacher].[TeacherId], [tblTeacher].[Name],
[tblTeacher].[Address] FROM [tblTeacher];

This is the design that I expected. But based on this, you misspoke when
you said the combo box was bound to tblTeacher -- it's bound to the
TeacherId field in tblStudent, which again is what I would expect. That
leaves me wondering where the problem lies. If you have opened the form to
a particular student, and see that the combo box is currently showing a
particular teacher, and you want to "break the link" between this student
and that teacher, all you have to do is select the value currently displayed
in the combo box and press the Delete key to delete it. The combo box
value, and hence the TeacherId field in the student's record, will become
Null.

No, you won't be able to drop down and select a "(none)" value in the combo
box. It's possible to set up the combo to behave that way, but not without
a little rowsource trickery. My point is that you don't need to do that --
just deleting the value in the combo box is sufficient. Am I missing
something?

 

[Bob Quintal]

Hi Dirk:
Thanks very much for your post. Your guess is right; I mean
removing the link between the student and the teacher. The form
bounds to tbl student and the combo box on it bounds to tbl
teacher. The problem is tblTeacher has the TeacherId as a primary
key and an auto-number that start by 1. I prefer to the simplest
case you mentioned below. However, since the TeacherId is a
primary key and already seeded at 1, I can't add a blank record
into tblTeacher so that the user would select that blank record
(Id=0 or null) to delete the link.

From what I understand you saying, teacher is a field in student,
which causes a problem (actually many problems).
Teacher-Student should be in its own table, with 2 (possibly 3)
fields, TeacherID, StudentID and optionally Subject.

This table is bound to a subform in your existing form, so that
deleting the teacher-student link is as simple as deleting the row
in the subform.

Can I change the autonumber (seed=1 to seed=0 on the SQL server
backend) so that it re-arranges the current TeacherIds (i.e.,
starting from 0) automatically? That way I can assign TeacherId=0
and the rest of the fields will be an empty record. Thanks

:

 

[John W. Vinson]

From what I understand you saying, teacher is a field in student,
which causes a problem (actually many problems).
Teacher-Student should be in its own table, with 2 (possibly 3)
fields, TeacherID, StudentID and optionally Subject.

Well... that's true if this school has multiple teachers for each student.
Many elementary schools do not; each student stays all day with one.

I'm guessing that's the OP's situation.

 

[Tim]

Thanks, Dirk for your help! Well, per your second paragraph, I can not delete
the value in the combo box, the error message, "You tried to assign the Null
value to a variable that is not a Variant data type." popped up! ANYWAY, I
have found a way working around it: I enter a new record into tblTeacher with
the TeacherId is the autonumber (it's 222) and all other fields are 0. So
when the user wants to delete the teacher, she/he just selects the first one
that has the row value 0's.

You are RIGHT when you say you are missing something! It's my fault because
I forgot to tell you that I have a report that shows a list those students
who don't have any teacher yet! The record source of the report is tblStudent
(a query). I just add a condition in the query saying TeacherId = 222.
Cheers!!!

P.S.: thanks all of you again!

 

[Dirk Goldgar]

Thanks, Dirk for your help! Well, per your second paragraph, I can not
delete
the value in the combo box, the error message, "You tried to assign the
Null
value to a variable that is not a Variant data type." popped up!

The mere act of deleting a value in a bound combo box would not cause this
message. I think you must have code in one of the comb box's events that is
not handling the possible Null value properly.

ANYWAY, I
have found a way working around it: I enter a new record into tblTeacher
with
the TeacherId is the autonumber (it's 222) and all other fields are 0. So
when the user wants to delete the teacher, she/he just selects the first
one
that has the row value 0's.

There are differing views on the subject of using special values to
represent a no-data condition. I don't care for it, but whatever works for
you.

You are RIGHT when you say you are missing something! It's my fault
because
I forgot to tell you that I have a report that shows a list those students
who don't have any teacher yet! The record source of the report is
tblStudent
(a query). I just add a condition in the query saying TeacherId = 222.

If you were using Null to represent that, then the criterion in your query
would say "TeacherId Is Null".