Excel VBA Error "Subquery returned more than 1 value"
So I have a query in Excel VBA code that's usign a subquery which is returning more than 1 row, so I get this:
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"
How do I go about handling this error? I'd like to have a form popup (which Ive already designed" which prompts the user for a name. I'd then run another query specifying that name.
How do I check to see if more than 1 row was returned, or how do I check for this error so I can have the other form display? Thanks!
"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"
How do I go about handling this error? I'd like to have a form popup (which Ive already designed" which prompts the user for a name. I'd then run another query specifying that name.
How do I check to see if more than 1 row was returned, or how do I check for this error so I can have the other form display? Thanks!
ASKER
Hello and thanks for your reply. Yes it would matter; what I'm dealing with here are two separate records that have the same reference number, which is what the query is using in its where clause.
They aren't supposed to have the same reference number but someone (or some people) dropped the ball. I'll eventually have to fix it but for now I'd liek them to be able to then specify the name if there are two records returned to be more specific about the query.
Any suggestions?
They aren't supposed to have the same reference number but someone (or some people) dropped the ball. I'll eventually have to fix it but for now I'd liek them to be able to then specify the name if there are two records returned to be more specific about the query.
Any suggestions?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So you're saying I should use the built-in SQL rowcount function? Should I store the value in a variable and then check the value in my vb code, ie:
Run query, check rows affected
select @variabletoShowNumberofRow
strRows = query.ExecSQL("Query statement here")
If Rows > 1 Then
'Display form asking for Name value
'Run new query with name value
End If
Run query, check rows affected
select @variabletoShowNumberofRow
strRows = query.ExecSQL("Query statement here")
If Rows > 1 Then
'Display form asking for Name value
'Run new query with name value
End If
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
To quick-fix it, add a TOP 1 in there, e.g. for a query like
select customername, (select contactname from contacts where contact.customerid=custome
from customer
where customer.id=123
You can change it to
select customername, (select TOP 1 contactname from contacts where contact.customerid=custome
from customer
where customer.id=123
The question is would it matter which record of the subquery (in this case contact) is showed?