Nested Select statement issue

[Zaeed]

I've got the following Select statement, but vba won't let me compile it with a ) on the end (as shown). Giving a "Expected: end of statement" error.
But Access obviously throws an error if there are no )

Me.frm_Change_ApproversSubform.Form.RecordSource = "SELECT name FROM qry_getName WHERE UserId IN (SELECT approverID FROM tbl_Approval WHERE ChangeNumber = " & GetChangeNumber())

Am I missing something?

 

[pbaldy]

Try

Me.frm_Change_ApproversSubform.Form.RecordSource = "SELECT name FROM qry_getName WHERE UserId IN (SELECT approverID FROM tbl_Approval WHERE ChangeNumber = " & GetChangeNumber() & ")"

 

[Zaeed]

that was one of the first things i tried..

No matter what, if i have anything after the GetChangeNumber it throws the expected: end of expression error and won't compile

 

[pbaldy]

What does the function return? Have you tried creating a string with the SQL so you can examine it as it runs?

 

[boblarson]

Remember NAME is an Access Reserved Word and needs to be in BRACKETS if you are referring to a field. Not good to use NAME as a NAME for more than the standard reason.

 

[Zaeed]

Ah yeah forgot about that Bob.. But its not my issue, I changed Name to one of the other fields in the query and nothing changed..

Paul, the issue isn't the SQL, its that vba won't let it compile to even run.. (code turns red in the VB editor)
The nested SELECT pulls a list of ID's from a table, and the parent SQL pulls the relating name for that ID.. The GetChangeNumber() just gets the ID of the record being being loaded into the form..

I've now dropped the nested SELECT and gone with

 "SELECT [Name] FROM qry_getName, tbl_Approval WHERE (([UserId] = [approverID]) AND ([ChangeNumber] = " & GetChangeNumber()))

But the editor still has an issue with this..
Building a similar query with the query builder works fine, but the vba editor doesn't seem to like it..

 

[pbaldy]

Sorry, I've been in Vegas. Can you post the db, or a sample of it that has this error?

 

[Zaeed]

I finally got it working by changing the end of the string to & "))"

Most random..

Cheers guys