Please Help - Link combo boxes

[anish]

Hello:

I have a form called "frmContract" which I created but have a few problems. First, you have to select a contract from the top right "Select Contract." When you select a contract, the values below change. All of them change except StudentID and TeacherID. Each "contract" like 3100 3110 corresponds with a student and a teacher. That's why when you select a contract, it should change.

The students are listed in tblStudent.

The teachers are listed in tblTeacher.



I attached the file for you to work on.

Thank you,

Anish

[maximus]

can you attach the file in ms access 2000 format

[anish]

Here you go. Access 2000 file.

Thanks,

Anish

[TheShabz]

Hi.

In your query for student ID and teacher ID, use your contracts table instead. That way you can do "WHERE contractID = Forms!frmContract!Combo25" *note: I'd rename that combobox to something relevant.

then in the afterupdate set the rest of the form fields to requery.

more info here: http://www.databasedev.co.uk/filter_combo_boxes.html

[maximus]

TheShabz has suggest a very good solution. That is a simple way out. But how will I display the the value i.e. this code will populate the combo with the StudentId or the TeacherID but how you will make the combobox select the value. In a text Box the value would have been displayed but in a combobox I have to make the combobox dropdown and select a value.
I have used SQL to populate the TeacherID and the StudentID combobox. The Code is attached to the AfterUpdate Event of the ContactComboBox:

Private Sub Combo25_AfterUpdate()
Dim strCondition As String
Dim strSQL As String 'Sql to populate StudentID ComboBox
Dim strSQL1 As String 'Sql to populate TeacherID Combobox

strCondition = "'" & Me.Combo25.Column(2) & "'" 'Student Id Collected from Contract Combo
strCondition1 = "'" & Me.Combo25.Column(1) & "'" 'Teacher Id Collected from Contract Combo

'Below are the sql to populate the combobox

strSQL = "SELECT tblStudent.[FirstName]&" & """ " & """" & "&tblStudent.[LastName] from tblStudent Where StudentID=" & strCondition & ";"
strSQL1 = "SELECT tblTeacher.[FirstName] &" & """ " & """" & "&tblTeacher.[LastName]from tblTeacher Where TeacherID=" & strCondition1 & ";"

'Populating the StudentID Combo and slecting the first Item in it as each contract involves 1 Student

Me.Combo19.RowSourceType = "Table/Query"
Me.Combo19.RowSource = strSQL
Me.Combo19 = Me.Combo19.ItemData(0)
'Populating the StudentID Combo and slecting the first Item in it as each contract involves 1 Teacher

Me.Combo34.RowSource = "Table/Query"
Me.Combo34.RowSource = strSQL1
Me.Combo34 = Me.Combo34.ItemData(0)

Me.Text36 = Me.Combo25.Column(2)
Me.Text38 = Me.Combo25.Column(1)
End Sub

I have used this to Display the full Name of teacher and student. If you want to display only the StudentID Make the following alterations to strSQL and strSQL1

strSQL="Select * From tblStudent Where StudentID=" & strCondition & ";"
strSQL1="Select * From tblTeacher Where TeacherID=" & strCondition1 & ";"

I assume you were trying to display the names and not the IDs as the database that you had sent me the ID column in both teachers and student column width is set to 0.

if this solves your problem please mark the thread solved.

[anish]

Thank you maximus.

Appreciate it.