Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Passing multiselect values to report returns #error instead of values

Status
Not open for further replies.

tgikristi

Technical User
Jul 31, 2002
39
US
Hello,

I have a multiselect listbox and a select query based on table that uses a Where statement to pass the selected items to a report. The query seems to be running correctly, but when the report opens, it lists enough lines for all the items in the listbox, but all the values appear as #Error. When I close the report and try to debug, I am always forced to shut down due to a fatal error. (its an Access 2K database).

Questions

1. Actually, I would prefer the report to include all the items in the listbox, but I haven't figured out how to incorporate a "Select All" statement. I am just kind of confused why the report seems to be doing this on its own, even when I only select a couple of items from the listbox.

2. The code is below--can anyone figure out why I can't pass the values and only get #Error in return?

Thanks in advance for any responses!!
tgikristi


Private Sub PrintUsers_Click()
Const SUBNAME As String = "PrintUsers_Click"
On Error GoTo PrintUsers_Click_Err

Dim dbs As Database, qdf As QueryDef, strSQL As String, IDitem As Variant, strcrit As String
Dim miscnum As Integer, quote As String, tempaddquote As String

miscnum = 0
strcrit = ""
For Each IDitem In Me!List34.ItemsSelected
If miscnum > 0 Then
strcrit = strcrit & " or "
End If
quote = """"
tempaddquote = quote & Me!List34.ItemData(IDitem) & quote

strcrit = strcrit & tempaddquote

miscnum = miscnum + 1
Next IDitem

Set dbs = CurrentDb
strSQL = "SELECT [Users].[UserName], [Users].[PID], [Users].[Group] FROM [Users]WHERE ((([Users].[UserName])=" & strcrit & "));"

Set qdf = dbs.CreateQueryDef("usersquery", strSQL)
Set dbs = Nothing
Dim stDocName As String
stDocName = "Users and Groups"
DoCmd.OpenReport stDocName, acPreview
DoCmd.DeleteObject acQuery, "usersquery"

PrintUsers_Click_Exit:
Exit Sub

PrintUsers_Click_Err:
Dim intAction As Integer
intAction = ErrorHandler(lngErrorNum:=Err.Number, strErrorDescription:=Err.Description, _
strFormName:=FormName2, strRoutineName:=SUBNAME)
Select Case intAction
Case ERR_CONTINUE
Resume Next
Case ERR_RETRY
Resume
Case ERR_EXIT
Resume PrintUsers_Click_Exit
Case ERR_QUIT
Quit
End Select

End Sub
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top