Need help with this sql statement

keyur

Registered User.
Local time
Today, 16:06
Joined
Jun 22, 2004
Messages
41
hi

The following sql command gives me an error 'Can return at most one record.' It was working fine until now. i have no clue why it stopped working. any ideas please??

Code:
DoCmd.RunSQL "UPDATE Courses SET Courses.Required = TRUE " & _
            "WHERE Courses.CourseID = (SELECT WorkgroupCourses.CourseID FROM " & _
            "WorkgroupCourses WHERE (WorkgroupCourses.WorkGroup = '" & wg & _
            "' OR WorkgroupCourses.WorkGroup = 'Basics') AND " & _
            " WorkgroupCourses.CourseID  = Courses.CourseID)"


Thanks
 
Your sub-query is returning more than one record. You main query expects only one record with one value to be returned from the sub-query.
Take your sub-query and just view it as a datasheet, if all the numbers it returns are the same, you could change "SELECT" to "SELECT DISTINCT" in the sub-query. If it's returning different numbers, then you need to rethink your WHERE clauses. Ain't easy, spent a week struggling with this very kind of thing not too long ago. One week for one (very long) SQL statement ain't pretty.
 
thanks man, i will give it a try
i do have another long way but if i cant get this to work, my code would be lot faster.
cheers
 
hi

here's the new one which is working now. as i said in the first first the old one was working fine and all of a sudden it started giving me an error. hope this one doesnt do the same

Code:
DoCmd.RunSQL "UPDATE Courses INNER JOIN WorkgroupCourses ON Courses.CourseID = " & _
            "WorkgroupCourses.CourseID SET Courses.Required = TRUE " & _
            "WHERE (WorkgroupCourses.WorkGroup = '" & wg & _
            "' OR WorkgroupCourses.WorkGroup = 'Basics')"

thanks everyone who put a thought into this.
 

Users who are viewing this thread

Back
Top Bottom