Filtered records

[hinchi1]

I have a form which is used to create reports. if i don't filter the records on the form using a textbox to use keyword search. the report button will happily output a report of all the records in Word,PDF and excel. however, if i filter the records and the records that have been filtered exceed 495 records i cannot get a report to be outputted. any thoughts please.



code for full report:
Private Sub cmdPreviewReport_Click()
On Error GoTo cmdPreviewReport_Click_Err
Dim oApp As Object
Dim oDoc As Object
Dim sTmpltName As String


'Dim AppWord As Application
'Dim Doc As Documents
'Dim NameOfDocument


' john's original code:
'DoCmd.OutputTo acOutputReport, "rptBuildings", "PDFFormat(*.pdf)", "", True, "", 0, acExportQualityPrint


' nic meddling from here
'NameOfDocument = "arse"


'case statement here for pdf word or excel


Select Case frmReportFormat.Value
Case 1
DoCmd.OutputTo acOutputReport, "rptBuildings", "PDFFormat(*.pdf)", "", True, "", 0, acExportQualityPrint
Case 2
DoCmd.OutputTo acOutputReport, "rptBuildings", "RichTextFormat(*.rtf)", "", True, "", 0, acExportQualityPrint
Case Else
DoCmd.OutputTo acOutputReport, "rptBuildings", "Excel97-Excel2003Workbook(*.xls)", "", True, "", 0, acExportQualityPrint
End Select


' to here


cmdPreviewReport_Click_Exit:
Exit Sub


cmdPreviewReport_Click_Err:
'MsgBox Error$
'Resume cmdPreviewReport_Click_Exit


End Sub

Code for filtered report:
Private Sub CmdViewSelection_Click()
On Error GoTo CmdViewSelection_Click_Err
Dim strWhere As String
Dim ctl As Control
Dim varItem As Variant


'make sure a selection has been made
If Me.SearchResults_Multi.ItemsSelected.Count = 0 Then
MsgBox "At Least 1 Record Must Be Selected!!", vbInformation, "Record Selection Required"
Exit Sub
End If


'add selected values to string
Set ctl = Me.SearchResults_Multi
For Each varItem In ctl.ItemsSelected
strWhere = strWhere & ctl.ItemData(varItem) & ","
'Use this line if your value is text
'strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem
'trim trailing comma
strWhere = Left(strWhere, Len(strWhere) - 1)
DoCmd.OpenReport "rptPartBuildings", acViewReport, , "RoomID IN(" & strWhere & ")"




Select Case frmReportFormat.Value
Case 1
DoCmd.OutputTo acOutputReport, "rptPartBuildings", "PDFFormat(*.pdf)", "", True, "", 0, acExportQualityPrint
Case 2
DoCmd.OutputTo acOutputReport, "rptPartBuildings", "RichTextFormat(*.rtf)", "", True, "", 0, acExportQualityPrint
Case Else
DoCmd.OutputTo acOutputReport, "rptPartBuildings", "Excel97-Excel2003Workbook(*.xls)", "", True, "", 0, acExportQualityPrint
End Select


'open the report, restricted to the selected items
'DoCmd.OpenReport "rptBuildings", acPreview, , "RoomID IN(" & strWhere & ")"
'DoCmd.OpenReport "rptPartBuildings", acViewReport, , "RoomID IN(" & strWhere & ")"


'DoCmd.OutputTo acOutputReport, "rptBuildings", "PDFFormat(*.pdf)", "", True, "", 0, acExportQualityPrint
'DoCmd.SelectObject acForm, "frmBuildingSearch"
'DoCmd.Close
DoCmd.SelectObject acReport, "rptPartBuildings"
DoCmd.Close
CmdViewSelection_Click_Exit:
Exit Sub


CmdViewSelection_Click_Err:
End Sub

[June7]

Please post lengthy code between CODE tags to retain indentation and readability.

You use textbox and combobox to filter the listbox then multi-select listbox is used to build filter criteria for report? RoomID is a text field?

Could be the WHERE CONDITION string is too long. Access does have limits. One is a limit of 255 characters in a query parameter and since the WHERE CONDITION argument is equivalent to the WHERE CLAUSE of query, the same limit may apply.

I did a test with an IN parameter array that had a trailing comma and it still opened filtered report. Tells me that possibly Access is truncating the string.

Is user manually selecting hundreds of records in the listbox?

[ArviLaanemets]

Maybe instead of collecting data for filter condition into listbox, collect them into some dummy table. Whenever you start selecting rooms you delete all entries from this table, and then add new ones. The source query for report will then be something like
SELECT ... WHERE RoomID IN (SELECT RoomID FROM DummyTable)
You also can try
DoCmd.OpenReport "rptPartBuildings", acViewReport, , "RoomID IN(SELECT RoomID FROM DummyTable)"

[hinchi1]

Thanks for your replies. The combobox is a separate filter to the list. Sorry I should have made that more clear. Not sure how you mean create a dummy table so any examples will be greatly appreciated. Also, how do I overcome the truncating of the string. I am still new to Access and learning all the time. Thanks again for your input.

[hinchi1]

I missed part of your replies. The user filters the list by keywords and then selects the filtered results by clicking on the Select All button. Hope this makes sense.

[June7]

So did you understand response in post 2? The WHERE string is too long. The only way to overcome is to have a shorter string. Why not use the 'keywords' to filter the report?

[hinchi1]

To be honest no I didn't. No idea how to implement this code or what was meant by using a dummy table, sorry.

[hinchi1]

I do use the keyword to filter the listbox, but don't know how to shorten the search string.

[June7]

Do the listbox and the report have the same fields, basically the same recordsource? Apply the same filter criteria to the report as you do for the listbox.

[hinchi1]

Thanks for your reply. I will check later but I am sure they do. If so, how do I apply the same filter to the report?

[hinchi1]

Do the listbox and the report have the same fields, basically the same recordsource? Apply the same filter criteria to the report as you do for the listbox.

the rowsource of the list box is qryRoomInformation and the record source of the report is qryRoomInformation. I am open to all options to reslove this as i have 4 other forms holding diffrernt data but have a similar filter set up. Thanks again.

[June7]

How are you filtering the listbox? Post the RowSource SQL statement.

[hinchi1]

How are you filtering the listbox? Post the RowSource SQL statement.

Hi, below is the SQL from the query builder. As i type in the textbox the listbox filters out. the keywords i have setup are: RoomNumber,Campusnmae, BuildingName, etc. The OnChange event of the textbox is:

If SearchFor_Multi.Text = "" Then
CmdViewSelection.Enabled = False
cmdPreviewReport.Enabled = True
Else
CmdViewSelection.Enabled = True
cmdPreviewReport.Enabled = False
End If


Me.SearchResults_Multi = Me.SearchResults_Multi.ItemData(0)
'Me.SearchResults.SetFocus
'DoCmd.Requery "SearchResults_Multi"
Me.SearchResults_Multi.Requery

SQL From Query Builder

SELECT qryRoomInformation.RoomID, qryRoomInformation.CampusNames, qryRoomInformation.BuildingName, qryRoomInformation.RoomNumber, qryRoomInformation.FloorID, qryRoomInformation.Floor, qryRoomInformation.SquareFootage, qryRoomInformation.BuildingID, qryRoomInformation.TypeID, qryRoomInformation.ActivityID, qryRoomInformation.CampusID, qryRoomInformation.UsedByID, qryRoomInformation.FrequencySpaceLevelsWeek, qryRoomInformation.HoursAvailableWeek, qryRoomInformation.FrequencyCalculation, qryRoomInformation.AvgPeoplePerHour, qryRoomInformation.MaxPeoplePerHour, qryRoomInformation.OccupancyCalc, qryRoomInformation.SpaceUtilisation, qryRoomInformation.RoomActivity, qryRoomInformation.RoomType, qryRoomInformation.WorkType
FROM qryRoomInformation
WHERE ((([qryRoomInformation]![CampusNames] & [qryRoomInformation]![BuildingName] & [qryRoomInformation]![RoomNumber] & [qryRoomInformation]![RoomActivity] & [qryRoomInformation]![RoomType] & [qryRoomInformation]![WorkType]) Like "*" & [forms]![frmBuildingSearch]![SearchFor].[Text] & "*"))
ORDER BY qryRoomInformation.RoomNumber;

[June7]

I never use dynamic parameterized queries but you could try that same WHERE clause in the report recordsource.

User is supposed to enter only one value in the textbox?

[hinchi1]

Thanks for the reply but you clearly have a better understanding than I do as I really don't know how to do this. But will keep looking. How would putting the where clause in the report recordsource help?