Run-time error '2001'

[mickafen]

Hello, i hope somebody out there can help.

I'm making a query based on user selections from three multiselect list boxes, one for Subject, one for Author, one for Publishing organisation. In theory the query should loop through the selections in each list and add them all to a SELECT query held in a string (strSQL).

This works fine when i have it set up for only one of the lists, but including all 3 gets me "Run-time error '2001': You cancelled the previous operation."
If i knew what this meant i could possibly figure out the problem, but when i click 'help' it says the help file does not exist.

Anybody know what causes this run-time error? I'm using Access 97 if that helps.

Thanks in advance

 

[Mute101]

You need to post the code you have written to form the SQL statement before anyone can really help you out.

----------------------------------------
Of all the things I have lost in my life, the thing I miss the MOST is my mind!
----------------------------------------

 

[artcabot]

That's one of those irritating messages that usually just means "I can't figure this out, so I'm giving up and going home!"

Let me see how you're retrieving the values and putting them into the SQL...

Art Cabot
Strickland Technical Services, Inc.
Augusta GA

 

[mickafen]

Hi, thanks for the responses!

Sorry if i was a bit vague previously, i was just trying to find out what error 2001 actually means as there's nothing in the help file.

I'll post part of the code below - at the start of this extract strSQL contains the table joins and nothing else. "qSubfm" displays the results of the query.

'BUILDING SQL STATEMENT - specify WHERE clause from contents of the 3 listboxes
'get list of subjects
For i = 0 To lstSubject.ListCount - 1
If lstSubject.Selected(i) Then
strInSub = strInSub & "'" & lstSubject.Column(0, i) & "',"
End If
Next i

'add subjects to WHERE clause
If Not (Len(strInSub) = 0) Then
strWhereSub = " WHERE subject.sub_code IN (" & Left(strInSub, Len(strInSub) - 1) & &quot "
strSQL = strSQL & strWhereSub
End If

'get list of organisations
For j = 0 To lstOrg.ListCount - 1
If lstOrg.Selected(j) Then
strInOrg = strInOrg & "'" & lstOrg.Column(0, j) & "',"
End If
Next j

'add to WHERE clause
If Not (Len(strInOrg) = 0) Then
If Not (Len(strWhere) = 0) Then
strWhereOrg = " AND lookup_organisation.or_ID IN (" & Left(strInOrg, Len(strInOrg) - 1) & &quot"
strSQL = strSQL & strWhereOrg
Else
strWhereOrg = " WHERE lookup_organisation.or_ID IN (" & Left(strInOrg, Len(strInOrg) - 1) & &quot"
strSQL = strSQL & strWhereOrg
End If
End If

'get list of authors
For k = 0 To lstAuthor.ListCount - 1
If lstAuthor.Selected(k) Then
strInAu = strInAu & "'" & lstAuthor.Column(0, k) & "',"
End If
Next k

'add to WHERE clause
If Not (Len(strInAu) = 0) Then
If Not (Len(strWhere) = 0) Then
strWhereAu = " AND lookup_author.au_ID IN (" & Left(strInAu, Len(strInAu) - 1) & &quot"
strSQL = strSQL & strWhereAu
Else
strWhereAu = " WHERE lookup_author.au_ID IN (" & Left(strInAu, Len(strInAu) - 1) & &quot"
strSQL = strSQL & strWhereAu
End If
End If

' Set RecordSource property of subform.
Me!qSubfm.Form.RecordSource = strSQL


Thanks again.

 

[Mute101]

I think the way you are constructing the SQL statement is at fault. A WHERE clause cant read;

WHERE item.item IN item1,item2

it needs to read;

WHERE item.item = item1 OR item.item = item2

Hope that points you in the right direction.
Simon

----------------------------------------
Of all the things I have lost in my life, the thing I miss the MOST is my mind!
----------------------------------------

 

[Mute101]

erp sorry ignore me...

I will keep looking

----------------------------------------
Of all the things I have lost in my life, the thing I miss the MOST is my mind!
----------------------------------------

 

[artcabot]

Can't tell if Mute101 is still working on this or not, but I'm going to look at the code and see what I can see. I'm heading out to a client site right now and won't have Internet access, but I've printed the code and will look at it.

Art Cabot
Strickland Technical Services, Inc.
Augusta GA

 

[artcabot]

This sure sounds like a classic student project; are you sure it's not? You're using a lot of structures that look like they're right out of documentation --- and with about twice as much code as you actually need.

Anyhow, here's the way I usually do this sort of thing, along with a few of my notes...

' open the parens enclosing the WHERE conditions here (will close it at the end)
strsql = " WHERE (("

    ' subjects to look for
    For i = 0 To lstSubject.ListCount - 1
        If lstSubject.Selected(i) Then
            strsql = strsql & "(subject.sub_code = '" & lstSubject.ItemData(i) & "') OR "
        End If
    Next i
    
    ' strip off the last "OR" and add the closing parens
    strsql = Left$(strsql, Len(strsql) - 4) & ")"
    
    ' first part of the WHERE clause is done
    
    ' just reuse the i variable instead of declaring new one
    ' and we already have a WHERE clause started, even if no conditions yet
    
    If Len(strsql) > 7 Then
            ' we have conditions, so continue with AND
            strsql = strsql & " AND ("
        Else
            ' no conditions yet, starting fresh with what we've got
        End If
        
    ' organisations to look for
    For i = 0 To lstOrg.ListCount - 1
        If lstOrg.Selected(i) Then
            strsql = strsql & "(lookup_organisation.or_ID = '" & lstOrg.ItemData(i) & "') OR "
        End If
    Next i

    ' strip off the last "OR" and add the closing parens
    strsql = Left$(strsql, Len(strsql) - 4) & ")"
    
    If Len(strsql) > 7 Then
        ' we have conditions, so continue with AND
        strsql = strsql & " AND "
    Else
        ' no conditions yet, starting fresh with what we've got
    End If
    
    ' authors to look for
    For i = 0 To lstAuthor.ListCount - 1
        If lstAuthor.Selected(i) Then
            strsql = strsql & "(lookup_author.au_ID = '" & lstAuthor.ItemData(i) & "') OR "
        End If
    Next i

    ' strip off the last "OR" and add the closing parens
    strsql = Left$(strsql, Len(strsql) - 4) & ")"
    
    ' done with the whole WHERE clause, so close the parens and terminate the SQL statement
    strsql = strsql & ");"
    
    ' set RecordSource property of subform (don't really need the Me!)
    qSubfm.Form.RecordSource = strsql

[\code]

Art Cabot
Strickland Technical Services, Inc.
Augusta GA