By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,494 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

SQL Query in a form

P: 14
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

Expand|Select|Wrap|Line Numbers
  1. SELECT tblExaminer.Examiner_Number,
  2.        tblExaminer.Examiner_Name,
  3.        tblExaminer.Subject_Ref_Code
  4. FROM tblExaminer
  5. 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
Feb 12 '07 #1
Share this Question
Share on Google+
7 Replies


NeoPa
Expert Mod 15k+
P: 31,186
Call a .Requery of the object that this SQL refers to after the Subject Ref Code has been selected.
Feb 12 '07 #2

P: 14
Call a .Requery of the object that this SQL refers to after the Subject Ref Code has been selected.
spot on cheers.
Feb 13 '07 #3

NeoPa
Expert Mod 15k+
P: 31,186
No problem. Glad it's working for you :)
Feb 13 '07 #4

P: 14
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...
Feb 14 '07 #5

NeoPa
Expert Mod 15k+
P: 31,186
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?
Feb 14 '07 #6

P: 14
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!
Feb 15 '07 #7

NeoPa
Expert Mod 15k+
P: 31,186
Expand|Select|Wrap|Line Numbers
  1. SELECT tblExaminer.Examiner_Number,
  2.        tblExaminer.Examiner_Name,
  3.        tblExaminer.Subject_Ref_Code
  4. FROM tblExaminer
  5. 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?
Feb 15 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.