re-assign TeacherID to null or zero

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tim - 02 Apr 2008 16:09 GMT

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

Reply to this Message

Dirk Goldgar - 02 Apr 2008 16:24 GMT

>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
[quoted text clipped - 3 lines]
> 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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Tim - 02 Apr 2008 20:29 GMT

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

> 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
[quoted text clipped - 11 lines]
> 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.

Reply to this Message

Dirk Goldgar - 02 Apr 2008 20:36 GMT

> Hi Dirk:
> Thanks very much for your post. Your guess is right; I mean removing the
[quoted text clipped - 8 lines]
> 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.

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Tim - 02 Apr 2008 20:54 GMT

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, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)

Reply to this Message

Dirk Goldgar - 02 Apr 2008 22:08 GMT

> Good questions...
> 1) A description of tblStudent, with a list of its fields
[quoted text clipped - 13 lines]
> ->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?

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Tim - 03 Apr 2008 14:35 GMT

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!

> 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
[quoted text clipped - 12 lines]
> just deleting the value in the combo box is sufficient. Am I missing
> something?

Reply to this Message

Dirk Goldgar - 03 Apr 2008 15:17 GMT

> 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
[quoted text clipped - 3 lines]
> 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".

Signature

Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Reply to this Message

Bob Quintal - 02 Apr 2008 23:07 GMT

> Hi Dirk:
> Thanks very much for your post. Your guess is right; I mean
[quoted text clipped - 6 lines]
> 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.,
[quoted text clipped - 18 lines]
>> more information about the tables involved and how the combo box
>> is set up, I can't say more.

Signature

Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Reply to this Message

John W. Vinson - 03 Apr 2008 01:59 GMT

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

Signature

John W. Vinson [MVP]

Reply to this Message