My query forces Access to close (See Microsoft KB Article - Q295277)

[tgikristi]

I think this article best describes my problem:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q295277

The article indicates "When you write SQL queries, avoid enclosing the items listed in the SELECT clause in parentheses."

Could someone review this code to see if there is a better way to write this query so Access doesn't shut down everytime I run it? Code (abbreviated to remove error handling, etc.):

Dim ctl As Control
Dim varItem As Variant
Dim strSQL As String

Set ctl = Me.List34
strSQL = "Select * from Users where [UserName]="

For Each varItem In ctl.ItemsSelected
strSQL = strSQL & "'" & ctl.ItemData(varItem) & "' OR [UserName]="
Next varItem

strSQL = Left$(strSQL, Len(strSQL) - 15)

CurrentDb.QueryDefs("usersquery2&quot.SQL = strSQL
DoCmd.OpenReport "Users and Groups", acViewPreview, , , acDialog
End Select


THANKS very much for any help!!
tgikristi

 

[GoDawgs]

Well one problem might be your missing the semicolon...make your statement something like this:

strSQL = Left$(strSQL, Len(strSQL) - 15) & ";"

Hope that helps.

Kevin

 

[tgikristi]

I tried that and it still crashes...I wouldn't be surprised if this code has tons of errors, as I am very new at Access and VB.

Thanks for the suggestion,
tgikristi

 

[tgikristi]

I know I don't have the terminology correct here, but by playing around, I have managed to get the data that I want to a report several different ways, using several different versions of SELECT statements as well as without, and I can do it successfully, the problem comes when I close that report, my form/access crashes as indicated above. Because I have used the same SELECT statments on other forms without this occurring, I think it is because in this instance, the listbox that I am referencing in my WHERE statement has a function as its rowsource as opposed to a table or query.

SOOOO....Is there a way I can get the data from my listbox to a table or something (that will be re-written every time the listbox is re-written) so that I can make another hidden listbox that references the table, and try referencing the hidden listbox for the SELECT statement? Or something like that?

Thanks,
tgikristi

 

[KyleS]

Yeah, that's doable,
but first let's take a look at the function running your listbox...?

Saide note: To do the table the way you ask you wouls just update your table for all selections before the report is opened and erase the table when the listbox gets focus (or some other event)

Kyle

 

[tgikristi]

I figured out how to pass the code from the listbox to a table and reference the table for the report and when it didn't work, for some reason, I realized that I had declared my listbox as a control instead of a listbox (I have a big DUMMY stamp on my forehead) and changing that made my crashes go away. Apologies all around for my stupidity and wasting everyone's time.