Auto populate a text box on basis of a combo box selection

[sunilkeswani]

Hi

I have a table with the following columns

StudentID StudentName Teacher School

On a form, when the student selects his name, I want the txtStudentID,
txtTeacher, txtSchool to be auto-populated with all details of that
particular student.

Any help would be appreciated. I am a novice user, so you might find
this simple.

Cheers

Sunny

 

[sunilkeswani]

Cool. I got it. after update of combo box

me.txtTeacher=me.cmb.column(2)

Cheers

 

[John Vinson]

Hi

I have a table with the following columns

StudentID StudentName Teacher School

On a form, when the student selects his name, I want the txtStudentID,
txtTeacher, txtSchool to be auto-populated with all details of that
particular student.

Any help would be appreciated. I am a novice user, so you might find
this simple.

I see you found the answer... but I do have a concern.

You should emphatically NOT be storing the student information
redundantly in a second table. If you're copying the student name,
school, etc. from the Student table into *bound* textboxes in order to
store them in a second table, don't. The second table should contain
only the studentID; you can *display* the fields from the student
table but you should not store them.

John W. Vinson[MVP]

 

[sunilkeswani]

Thanks John, It is just that I am storing some fields from the Student
table to my main database, and yes the text boxes are bound to the main
db..Is this going to create a problem? My db is going to be used by
100+ users at anytime. So what are the other precautions I should take?

Thanks
Sunny

 

[John Vinson]

Thanks John, It is just that I am storing some fields from the Student
table to my main database, and yes the text boxes are bound to the main
db..Is this going to create a problem?

Yes, it certainly will!

Relational databases use the "Grandmother's Pantry Principle": "A
place - ONE place! - for everything, everything in its place".

What if you store a student name in the Students table, and then store
the same name (with the same ID) in the "main table"?

No problem, you say - other than the waste of disk space - but then
the student's family changes the family name, or they move to a new
address. Now you have to track down everyplace that student's name or
address was copied and correct it.

Store the name ONCE, and once only, in the student table where it
belongs; you can use a Query joining the "main table" to the Student
table by StudentID to determine the name and other info about the
student. You can *display* the name on a Form by using a combo box
(bound to the StudentID but displaying the name, or by using unbound
textboxes with Control Source =cboStudentID.Column(n)) but you should
not store it. This is FUNDAMENTAL to how Access or any relational
database works!

My db is going to be used by
100+ users at anytime. So what are the other precautions I should take?

Be sure you understand how relational databases work. If you've made
this error, you may be making others, I don't know! Check out the
Database Design 100 and other resource links at:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

John W. Vinson[MVP]

 

[sunilkeswani]

Thanks John, the link is helpful

Yes, it certainly will!

Relational databases use the "Grandmother's Pantry Principle": "A
place - ONE place! - for everything, everything in its place".

What if you store a student name in the Students table, and then store
the same name (with the same ID) in the "main table"?

No problem, you say - other than the waste of disk space - but then
the student's family changes the family name, or they move to a new
address. Now you have to track down everyplace that student's name or
address was copied and correct it.

Store the name ONCE, and once only, in the student table where it
belongs; you can use a Query joining the "main table" to the Student
table by StudentID to determine the name and other info about the
student. You can *display* the name on a Form by using a combo box
(bound to the StudentID but displaying the name, or by using unbound
textboxes with Control Source =cboStudentID.Column(n)) but you should
not store it. This is FUNDAMENTAL to how Access or any relational
database works!


Be sure you understand how relational databases work. If you've made
this error, you may be making others, I don't know! Check out the
Database Design 100 and other resource links at:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

John W. Vinson[MVP]