Combo box not passing information to second combo box

  • Thread starter Thread starter Tony
  • Start date Start date
T

Tony

I am creating a directory for our school and using two combo boxes in a form.
The first tells the student's grade. The second combo box is supposed to
use this information (K, 1, 2, 3, 4, or 5) and look up that grade in a table
and list only the teachers for the grade selected in the second combo box.
However, I cannot get the first combo box to pass the information to the
second or the second to ask for the information from the first - help?
 
I am creating a directory for our school and using two combo boxes in a form.
The first tells the student's grade. The second combo box is supposed to
use this information (K, 1, 2, 3, 4, or 5) and look up that grade in a table
and list only the teachers for the grade selected in the second combo box.
However, I cannot get the first combo box to pass the information to the
second or the second to ask for the information from the first - help?
Click to expand...

Could have used more information, but perhaps you can figure the
specifics out if I give you the general information.

You should have a teachers table with the TeacherID, TeacherName and
Grade they teach.
Like this:
ID TeacherName Grade
1 Smith K
2 Jones 5
3 Green 2
4 Mays 5
5 Benson 4
etc...

Then leave the 2nd Combo Box Rowsource blank
Code the first Combo AfterUpdate event:
Me![Combo2].Rowsource = "Select TeacherID, TeacherName from
TeacherTable Where [Grade] = '" & Me.Combo1 & "'"

Combo2 should have the bound column set to 1 (the TeacherID field).
Set the Combo2 Column Count property to 2
Set the Combo2 Column Widths property to
0";1"
Set the Column2 Control source to [TeacherID] (if you intend to store
the Teacher in a table).

When you select a grade in Combo1, it will fill Combo2 with the name
of all teachers (and the teacherID), that teach that grade.

For example, if you select Grade 5 in Combo1, Combo2 will show "Jones"
and "Mays".

Now you can select the one you wish.
 
your second combo box needs to have a filter attached

on your first combo box add the vba code

sub combobox1_afterupdate()
combobox2.Rowsource = "Select TeacherID, TeacherName from
TeacherTable Where [Grade] = '" & Me.Combo1 & "'"
end sub

placing that code will only update combobox2 when combobox1 has a
value

if you use fredgs example you have to use

sub combobox1_afterupdate()
combobox2.Requery
end sub

to update combobox2

hope this helps

Regards
Kelvan
 
Back
Top