Update from a Combo box

[Greg Ripper]

I have a form, frmDiscipline, that teachers use to select a student name,
from tblStudentData, from a combo box and then their name, from tblTeacherName,
from another combo box.

This information, and other information they type in, is stored in a table
called tblDiscipline.

Here is what I want..

On this form, frmDiscipline, there are fields that go with the student
that was selected from the combo box, stored in tblStudentData. Grade, ID#,
etc. How do I get those to update from tblStudentData when the student name
is selected? And how do I store that information in tblDiscipline?

Tough one for you guys today.

Rip

Always confused when using Access

 

[Jeff Boyce]

Greg

First, an issue of relational database design -- there's no need to store
the same information in more than one table. If the student information you
wish to see on a Discipline form already exists in the Student table, just
display it, don't save it. The only thing you need to save is the
StudentID, which is your link back to the student data.

Now, on your Discipline form, add several unbound text boxes to hold the
student information you want to display -- again, you don't need fields in
the Discipline table to store that, so you don't need to 'bind' the new text
controls to any table fields.

In the cboStudent combo box's AfterUpdate event, do something like:

Me!txtStudentName = Me!cboStudent.Column(1)
Me!txtStudentGrade = Me!cboStudent.Column(2)
...
Me!txtStudentX = Me!cboStudent.Column(n)

A couple notes ... the .Column() property is zero-based, so Column(1) is
actually the second field of the query you use to populate the cboStudent
control.

Also, you can include as many of the fields from tblStudent as you will need
for (eventual) display, but you could only 'show' a single field by making
the display width of those fields in the combo box = 0. A traditional
combobox design has the ID field first, as that is what is typically bound
to the underlying table data (in tblDiscipline), then as many (visible -
width>0) fields as it would take to make sure you are getting the right one
(?name, age, ...).

Good luck!

Jeff Boyce
<Access MVP>