requery combo box in subform from main form record

[scubadiver007]

I have a main form called "appraisals form". Contained within this form are "first name", "last name" and a checkbox for appraiser (an appraisal is a yearly performance review and each doctor needs one).

In a subform control called "appraisals subform" I have a combo box which contains a list of all doctors who are appraisers. The query for this combo box is as follows:

SELECT Tble_Doctors.GMC_ID, [First_Name] & " " & [Last_Name] AS Appraiser, Tble_Doctors.AppraiserNotes
FROM Tble_Doctors AS Tble_Doctors
WHERE (((Tble_Doctors.First_Name)<>[forms]![appraisals form]![first_name]) AND ((Tble_Doctors.Last_Name)<>[forms]![appraisals form]![last_name]) AND ((Tble_Doctors.Appraiser)=True))
ORDER BY Tble_Doctors.First_Name;

Since a doctor cannot appraise themselves, I need to exclude the name from the list when it comes to the appropriate main form record.

At the moment I have this:

Forms![appraisals form]![appraisals subform].Form![Appraiser_ID].Requery

I have tried other variations and even changed the event but nothing seems to work. If the query can be improved, let me know.

Cheers

 

[smig]

in what event you run the .Requery ?
Try to run it in the .OnCurrent and in the .checkbox_AfterUpdate events

 

[scubadiver007]

I already tried it in the oncurrent event but it doesn't work. If I open the query independently of the form, I can see that the query works so it must be the requery code that is the problem.

I always get a bit confused over this kind of code.

 

[smig]

did you use the builder to create the query ?

Try this:

WHERE (((Tble_Doctors.First_Name)<>[COLOR=darkred]forms[/COLOR]![appraisals form]![first_name]) AND ((Tble_Doctors.Last_Name)<>[COLOR=darkred]forms[/COLOR]![appraisals form]![last_name]) AND ((Tble_Doctors.Appraiser)=True))

Forms should not be in the square brackets

It's very bad habit to put spaces in names - table name, form name, field name and any other object. Using under_line is OK

 

[scubadiver007]

The query is not the problem. I can't get the combo box in the subform to refresh when I move between records.

The following doesn't work either:

Me![Appraisals_subform].Form![Appraiser_ID].Requery

 

[smig]

Try using the full naming, and ME