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
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