SQL Query in a form

[markymark34]

I have a form that allows examiners to be assigned to a particular centre.
So for example i have 10 examiners who all examine different subjects and i have 5 centres one of which is doing an exam in maths. What i want to do have a combo box on the Centre form that only shows examiners that can actually mark that subject(Subject_Ref_Code),

I have SQL that works

SELECT tblExaminer.Examiner_Number,
       tblExaminer.Examiner_Name,
       tblExaminer.Subject_Ref_Code
FROM tblExaminer
WHERE (((tblExaminer.Subject_Ref_Code)=[forms]![frmCentre]![Subject_Ref_Code]));

But this only runs once when i enter the form. Is there a way i can make this code run again when a different Subject Ref Code is selected so only relevant examiners are found?


thanks

Mark

[NeoPa]

Call a .Requery of the object that this SQL refers to after the Subject Ref Code has been selected.

[markymark34]

Call a .Requery of the object that this SQL refers to after the Subject Ref Code has been selected.

spot on cheers.

[NeoPa]

No problem. Glad it's working for you :)

[markymark34]

working well and this next bit seems like such a minor problem but the combo box always says #name? in it and no matter what i do i cant get rid of it!!

im sure its very simple...

[NeoPa]

So the .Requery works ok, but the results are never right?
Is the [Subject_Ref_Code] control empty when this is seen?
Are you still using the SQL posted in post #1 as the RowSource?

[markymark34]

So the .Requery works ok, but the results are never right?
Is the [Subject_Ref_Code] control empty when this is seen?
Are you still using the SQL posted in post #1 as the RowSource?

Sorry didnt explain myself well. You code is spot on and when you click on the combo box the right records are there. The problem is that when the form is loaded it says #name in the combo box and i cant get rid of it!

[NeoPa]

SELECT tblExaminer.Examiner_Number,
       tblExaminer.Examiner_Name,
       tblExaminer.Subject_Ref_Code
FROM tblExaminer
WHERE (((tblExaminer.Subject_Ref_Code)=[forms]![frmCentre]![Subject_Ref_Code]));

Oh right.
That's because your query has a filter on the value in the [Subject_Ref_Code]. This probably has no value when the form first opens. Try adding the SQL to the ComboBox in the AfterUpdate event procedure of the [Subject_Ref_Code] control. The form would start off with nothing in there and then add it, or take it away, in the procedure depending on there being a value in the field.
Does this make sense?