In microsoft access is it possible to have a combo box where the options depend on the entry in another feild?

Question

This is for a computer science project where i have to make a database for and examinations board. I have a table of examiners where all the names of examiners are listed and an ExaminerID Feild and the SubjectID that they mark papers for. In the Centres table i need to create a combo box under ExaminerID which lists all the Examiners that mark the specific SubjectID which has already been entered in the Centre Table.

I have tried using this code.
"SELECT Examiner.[Examiner ID] FROM Examiner WHERE Examiner.[Subject Reference Code]=Centre.[Subject Reference Code]; " (without quotes) to no success, Access simply asks me to enter "centre.[Subject Reference Code]" if i do enter it however it does exactly what i want, but is there a way to do it without having to re-enter the number.

Answer 1

In these situations very technical things matter a great deal. Things such as names of fields, tables, etc.

To be sure your names are entered correctly, don't enter them, let the query builder enter them for you:
Go into your Combo Box properties and find your Data tab and the Row Source property. Select it and then click on the ellipse (...) This will bring up the query builder. Bring in your Examiner table and your Centre table. Link them, if they are not already, on Subject Reference Code. Drag the Examiner ID field from the Examiner table onto the fields grid in the bottom of the query builder. Then close the query. This should build the query based on your tables and have all the names correct for you.

Now you want your combo box to only show you examiners that mark a certain selected subject? I hope I assume correctly that the combo box you want is on a form.

In my example, I added the subject ID field to the fields grid and put a criteria in: 55, This is the resulting sql in the row source of the combo box:

SELECT Examiner.examinerID FROM Examiner INNER JOIN Centre ON Examiner.SubjectReferenceCode = Centre.[Subject Reference Code] WHERE (((Centre.[Subject Reference Code])="55"));

I believe you need to be able to refer to a control on a form, so you have a form, you have the subject ID on the form and it is running and a certain code is showing in it. You take the reference to that control on the form and you put it in place of the "55" part of the above sql in the row source of your combo box.

If the combo box you desire is in a table, then you need to set up your relationships correctly. Create your tables, put in the correct primary keys and or indexes and then go into the relationships window. (Tools, Relationships) Place your tables in and drag the related ids to connect them.

Once you do that you open one of the tables and you will have a plus sign that will show you the related records in the related table.

I gave two options because I interpreted your question two ways.

Good Luck with your project.

Answer 2

You have to make two combobox. In the first, you select the Subject and then you say

"Select * from Where subjectID = " & combobox1.Value

Helmut