I have a combo box, that has the row source set by vba with a case statement on change of a different combo. Based on the selection of the first combo, the entire row source is set to a sql statement.
For 4 of the 7 options, I have the row source working and populating the 2nd combo appropriately. For 3 others, I am having issues. Below is one of the three that I am working through. I put a debug.print on the rSource (which is the sql statement) and the statement looks good. I put it into a SQL query window (outside of Access) and it runs great. When I try it in SQL mode of an Access query, it says I have syntax error. It highlights an odd part of the query too. I can't see why it has a problem.
This is the query:
Basically this application is project centric (tblProject). From there, projects are assigned to buildings in a cross table, called tblProjAssignBuilding. Then projects in buildings are assigned staff - tblProjBuildStaff. For this, I am pulling up all staff (and being bound to the staff id) for all staff assigned in any building that is assigned to the current project. This combo that will have this information, will sit on a form tab.
I hope I explained this well enough. Sorry if I didn't. If anyone can help, I would really appreciate it.
Thanks!
For 4 of the 7 options, I have the row source working and populating the 2nd combo appropriately. For 3 others, I am having issues. Below is one of the three that I am working through. I put a debug.print on the rSource (which is the sql statement) and the statement looks good. I put it into a SQL query window (outside of Access) and it runs great. When I try it in SQL mode of an Access query, it says I have syntax error. It highlights an odd part of the query too. I can't see why it has a problem.
This is the query:
Code:
SELECT s.ID, s.txtStaffName FROM tblProjBuildStaff pbs
LEFT JOIN tblProjAssignBuilding pb ON pbe.FKProjAssignBuilding = pb.ID
LEFT JOIN tblStaff s ON pbE.FKStaff = s.ID
WHERE pb.FKProject = 99
ORDER BY s.txtStaffName
Basically this application is project centric (tblProject). From there, projects are assigned to buildings in a cross table, called tblProjAssignBuilding. Then projects in buildings are assigned staff - tblProjBuildStaff. For this, I am pulling up all staff (and being bound to the staff id) for all staff assigned in any building that is assigned to the current project. This combo that will have this information, will sit on a form tab.
I hope I explained this well enough. Sorry if I didn't. If anyone can help, I would really appreciate it.
Thanks!