I have created an SQL statement as part of te properties (row source) of a field called SupervisorFullName that works only when it is first applied. I want it to work every time.
I have a main form called eEmployerRequestDetailsF which is linked to a table called eEmployer2SiteDetails. This table keeps Site Names and Address Details and two fields: SiteID and CompanyID
This main form has a subform called usysbeEmployer3RequestDetailsSubform which is linked to a table called eEmployer3RequestDetails. This table keeps details relating to the type of work experience on offer as well as: SiteID and CompanyID and SupervisorsFullName. SupervisorFullName is a combo box.
I also have a seperate table (eEmployer4SiteSupervisors) which keeps all the details relating to Supervisors and also has SiteID and CompanyID
fields. Each site can have more than one supervisor.
SiteID;CompanyID both act as the child/master fields between the main form (eEmployerRequestDetailsF) and the subform (usysbeEmployer3RequestDetailsSubform).
If I get the SupervisorFullName combo field on the usysbeEmployer3RequestDetailsSubform to lookup the eEmployer4SiteSupervisors table it gives me all supervisors kept in the table for all employer sites.
So I thought I would create a SQL statement that would pull out just the detail relating to the site selected on the main form. IE I would get the query to give me all supervisors belonging to the CompanyID from which the user would have to pick a supervisor they wanted to use. To do this I had to reference the CompanyID selected on the main or sub forms.
As a test - If I save the SQL statement as a query and create a button on the form which references the query, the query works perfectly, every time. The only problem is that it opens the query and does not fill the combo box and therefore does not allow the user to select the supervisor.
When I make the SQL statement as part of the properties (row source) of the SupervisorFullName field, that is when I am getting the problem which is leading me to ask for your help.
Using the navigation buttons on the main form I can scroll through the records and select any EmployerSite record and then click on the SupervisorFullName field and it will give me the correct details relating to Supervisors working with that Employer. If I then continue to scroll through the EmployerSite records and then click on the SupervisorFullName field, instead of giving me the Supervisor Names relating to that next employer, it gives me the same supervisor name(s) from the first selection. This happens no matter what EmployerSite record I click on.
Remember this same statement works as a button ie I can scroll through the records and select any EmployerSite record and then click on the button and it will give me the correct supervisor(s) details. It will not work if I have it as part of the properties (row source) of the SupervisorFullName field.
What am I doing wrong? The SQL statement is as follows.
SELECT eEmployer4SiteSupervisors.SupervisorsFullName, eEmployer2SiteDetails.SiteName, eEmployer2SiteDetails.Suburb, eEmployer4SiteSupervisors.CompanyID
FROM eEmployer4SiteSupervisors INNER JOIN eEmployer2SiteDetails ON eEmployer4SiteSupervisors.SiteID = eEmployer2SiteDetails.SiteID
WHERE (((eEmployer4SiteSupervisors.SupervisorsFullName) Is Not Null) AND ((eEmployer4SiteSupervisors.CompanyID) Like [Forms]![eEmployerRequestDetailsF]![CompanyID]) AND ((eEmployer4SiteSupervisors.Status)="Available"))
ORDER BY eEmployer2SiteDetails.SiteName, eEmployer4SiteSupervisors.SupervisorsFullName;
I have even tried referencing the CompanyID via the subform ie replace:
My apologies to the Forum for the long description of my problem, but as I did not know what the problem was I thought I should tell you what I have done. I have seen others get some turse responses because they failed to give enough information.
Anyway thank you NTC for responding, but I can't seem to get your suggestion to work.
I am assuming that you are saying that I need to put the following into the 'SupervisorsFullName' combo box. I made it an onclick event.
I have also tried simply adding DoCmd.Requery
With both statements I get the following error when I move to another employer record and try to select a supervisor from the list and it still only gives me the same selection of supervisors from the first selection.
Run time error 2488
you cant use the apply filter function on this window.
If I don't add DoCmd.Requery ([SupervisorsFullName]) I have no problems with the form other than the issue described in the first post.
Can you please advise further what I might be doing wrong.
NTC thank you for your assistance with this, it is greatly appreciated.
Information for the forum -
The following ended up working:
As the combobox [SupervisorsFullName] was on the subform, I went to the subform's form properties, and under 'On Current' I added the following: