SQL Rowsource Doesn't work in Access Query - Empty Combo? (1 Viewer)

misscrf

Registered User.
Local time
Today, 16:10
Joined
Nov 1, 2004
Messages
158
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:
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!
 

spikepl

Eledittingent Beliped
Local time
Today, 21:10
Joined
Nov 3, 2010
Messages
6,144
Build your query in the QBE , i.e the query designer, step by step. Your query is sorely lacking in brackets, that Access liberally sprinkles all over the place, and complains when they are missing

Update:
And check your aliases -they seem to be all over the place too :D
 

misscrf

Registered User.
Local time
Today, 16:10
Joined
Nov 1, 2004
Messages
158
That's the thing. I did build it in the qbe. Then I cleaned it up in a text editor to remove all that bracket junk on purpose lol. It's so unnecessary! Isn't it???

What is wrong with the aliases? I noticed one typo, but that is because I had to alter the statement that I would post on here, to not be exactly what I am doing for this project.

this is what is should be:

Code:
SELECT s.ID, s.txtStaffName FROM tblProjBuildStaff pbs 
LEFT JOIN tblProjAssignBuilding pb ON pbs.FKProjAssignBuilding = pb.ID 
LEFT JOIN tblStaff s ON pbs.FKStaff = s.ID 
WHERE pb.FKProject = 99 
ORDER BY s.txtStaffName

Thank you for responding. I appreciate you taking the time to help.
 

spikepl

Eledittingent Beliped
Local time
Today, 21:10
Joined
Nov 3, 2010
Messages
6,144
So you had something working, then you "cleaned it up" and now you have something not working?

Gee, I wonder ....

:confused::confused::confused::confused::confused::confused::confused::banghead::p
 

misscrf

Registered User.
Local time
Today, 16:10
Joined
Nov 1, 2004
Messages
158
it worked in the QBE. I had to clean it up to convert it to VBA for the sql rowsource. I always clean up queries to put in aliases and take out un-needed brackets. If I run that query in sql management studio, it runs. The query is a working query. The issue is Access is choking on it. I just need something that will read in the vba and in the QBE I guess. I get results in SQL query outside of access. When I run the combo to that choice, the resulting combo is empty, with no error. The debug gives the the sql statement that runs ok outside of access. I don't understand why access won't handle it correctly.
 

spikepl

Eledittingent Beliped
Local time
Today, 21:10
Joined
Nov 3, 2010
Messages
6,144
You want to pick a fight with the gnomes of Redmond go ahead. Otherwise go with the flow and live with the brackets. "I had to convert" .. hmmm.. I take that statement with a grain of salt :D
 

misscrf

Registered User.
Local time
Today, 16:10
Joined
Nov 1, 2004
Messages
158
I hear you on the brackets. But I did have to convert it. I had to take

Code:
SELECT s.ID, s.txtStaffName FROM tblProjBuildStaff pbs 
LEFT JOIN tblProjAssignBuilding pb ON pbs.FKProjAssignBuilding = pb.ID 
LEFT JOIN tblStaff s ON pbs.FKStaff = s.ID 
WHERE pb.FKProject = 99 
ORDER BY s.txtStaffName

and make it this:

Code:
rSource = "SELECT s.ID, s.txtStaffName FROM tblProjBuildStaff pbs " _
& "LEFT JOIN tblProjAssignBuilding pb ON pbs.FKProjAssignBuilding = pb.ID " _
& "LEFT JOIN tblStaff s ON pbs.FKStaff = s.ID " _
& "WHERE pb.FKProject = 99 "
& "ORDER BY s.txtStaffName"


some of my rSource queries have embedded iif statements, and concatenation of fields with parens. So, yes - grain of salt, I did have to convert them ;-)
 

misscrf

Registered User.
Local time
Today, 16:10
Joined
Nov 1, 2004
Messages
158
for the ones that weren't working, I followed your advice and let the QBE dictate the syntax. It's annoying, because it's way more text than should be necessary, but it works. My OCD nature likes it cleaner is all. Thanks for your help.
 

vbaInet

AWF VIP
Local time
Today, 20:10
Joined
Jan 22, 2010
Messages
26,374
The most important thing to note is that the parentheses in a FROM clause is absolutely necessary. Everywhere else you can "clean-up" as long as it's logical.
 

misscrf

Registered User.
Local time
Today, 16:10
Joined
Nov 1, 2004
Messages
158
That's interesting. Do you know why that is? Why, if I clean up the whole statement, can I run it in SQL management studio, but not in the QBE? Why is access unable to read a perfectly good SQL statement?
 

vbaInet

AWF VIP
Local time
Today, 20:10
Joined
Jan 22, 2010
Messages
26,374
There are slight syntax differences between Access' Jet/ACE SQL and SQL Server's SQL. Just like there are differences between SQL Server and Oracle.
 

Users who are viewing this thread

Top Bottom