Filtered records

[hinchi1]

I think I will have to go back to the drawing board on this one as I don't have a clue. I have done quite a lot of coding for my database and this problem seems really simple to solve, but clearly not.

[June7]

It would filter the report same as the listbox. Report RecordSource could be:

SELECT qryRoomInformation.*
FROM qryRoomInformation
WHERE ((([qryRoomInformation]![CampusNames] & [qryRoomInformation]![BuildingName] & [qryRoomInformation]![RoomNumber] & [qryRoomInformation]![RoomActivity] & [qryRoomInformation]![RoomType] & [qryRoomInformation]![WorkType]) Like "*" & [forms]![frmBuildingSearch]![SearchFor].[Text] & "*"))
ORDER BY qryRoomInformation.RoomNumber;

[hinchi1]

I will give it a go after i have eaten and will get back to you. Thanks again

[hinchi1]

Still no joy with that. i just dont get why the filter does not exceed 495 records. if i dont filter the list i can print a full report of over 1000 records.

[June7]

Provide your db for analysis, follow instructions at bottom of my post.

[hinchi1]

I will do that tomorrow. May take a while as the are lots of forms, tables that I no longer need in there. Thanks again for replying.

[hinchi1]

I will do that tomorrow. May take a while as the are lots of forms, tables that I no longer need in there. Thanks again for replying.

I have attached part of the database which relates to the building reports. there is nothing sensitive here, only room numbers. locations, etc. The full database contains 4 other report forms which are identical to this one but link to different data, so if you can solve this issue then you would enable me to use the solution on the other forms. The default output is to PDF. Word output works but the Excel output needs more work on it as there are two unknown numbers fields at the top of the spreadsheet( i believe they are the autodate and autotime controls in the report). I need to create seperate reports for this this and remove those controls. Also, all populated fields are greyed out (unsure why). As you will see if you select no more than 495 records the report will output, anything more than that then nothing happens. I am guessing my code behind the "Part Report" button needs a bit of work. The reports are nothing fancy but do the job. At some point i may addd graphs, charts, etc, but for now they are functional. Thanks again for spending time on this it is very much appreciated.Database Export.zip

[ArviLaanemets]

In OnChange event of SearchFor_multi textbox, you set the button cmdPreviewReport disabled whenever anything is entered into SearchFor_multi texbox - and it isn't enabled unless you clear the textbox.

PS. For button cmdViewSelection exact opposite applies.

[hinchi1]

In OnChange event of SearchFor_multi textbox, you set the button cmdPreviewReport disabled whenever anything is entered into SearchFor_multi texbox - and it isn't enabled unless you clear the textbox.

PS. For button cmdViewSelection exact opposite applies.

The idea is that when an item or items are selected from the listbox or text is entered into the "SearchFor_multi textbox" the user wants to just print out the selected items (Part Report), that is why the buttons are diabled in that way. The naming of the buttons may need changing to avoid confusion but i do have them active or disabled in the correct format, hopefully and thanks for your feedback.

[ArviLaanemets]

OK. In OnClick event for CmdViewSelection you had defined OnError part of code, but you didn't use it. When I activated error trapping, then you get an error at command

DoCmd.OpenReport "rptPartBuildings", acViewReport, , "RoomID IN(" & strWhere & ")"

When the command returned an error, the Error put into Watch window returned a message "The filter operation was canceled. The filter would be too long." Btw. the string strWhere was 3126 characters long.

[hinchi1]

OK. In OnClick event for CmdViewSelection you had defined OnError part of code, but you didn't use it. When I activated error trapping, then you get an error at command

DoCmd.OpenReport "rptPartBuildings", acViewReport, , "RoomID IN(" & strWhere & ")"

When the command returned an error, the Error put into Watch window returned a message "The filter operation was canceled. The filter would be too long." Btw. the string strWhere was 3126 characters long.

I agree with you and this is the problem i am having as i don't know how to resolve the "string is too long issue". Any feedback is much appreciated. Examples of how to overcome the error would help, if you have any. Like i said in an earlier post i have 4 other forms using the same filter but at this time the other forms do not exceed 495 records, but at some point in the future they will. Granted my code my be a little rough round the edges and not having using Access for 15 years or more until 5 months ago, in the main i am happy with my efforts. Please keep your ideas coming.

[ArviLaanemets]

In your attached table, I created a table tblDummy with a single field RoomID (Long Integer, PK), and filled it with all RoomID's from qryRoomInformation;
In OnClick event of cmdViewSelection, I changed the command line

DoCmd.OpenReport "rptPartBuildings", acViewReport, , "RoomID IN(" & strWhere & ")"

to

DoCmd.OpenReport "rptPartBuildings", acViewReport, , "RoomID IN (SELECT RoomID FROM tblDummy)"

Now I selected something in text box SearchFor_Multi (simply to get the button activated, anyway 1096 rows report was generated), clicked the button, and got the report.

You have to figure out, how to fill the tblDummy (or whatever you name it) from your form. And of-course to clear previous RoomID's from there too before inserting a new selection.

The difference is 40-character condition string vs. several thousand character condition string.

[hinchi1]

In your attached table, I created a table tblDummy with a single field RoomID (Long Integer, PK), and filled it with all RoomID's from qryRoomInformation;
In OnClick event of cmdViewSelection, I changed the command line

DoCmd.OpenReport "rptPartBuildings", acViewReport, , "RoomID IN(" & strWhere & ")"

to

DoCmd.OpenReport "rptPartBuildings", acViewReport, , "RoomID IN (SELECT RoomID FROM tblDummy)"

Now I selected something in text box SearchFor_Multi (simply to get the button activated, anyway 1096 rows report was generated), clicked the button, and got the report.

You have to figure out, how to fill the tblDummy (or whatever you name it) from your form. And of-course to clear previous RoomID's from there too before inserting a new selection.

again i agree, but at this time it is something i will have to research as this is new to me. i firstly dont understand how to create a dummy table or even if say i have selected more than 495 records from the listbox and to insert them into the dummy table. Should say if i select more records than 495, copy these records to a dummy table output to a report and then delete the tempoary table. I have seen a few posts on creating temp table but this is a little too advanced for me at this stage, but will investigate further.

[ArviLaanemets]

A dummy table is an ordinary table which main task (99.999% of it's live time) is to be empty . When you need, you put some data into it, use those data, and after that you delete data. No need to delete the table - it will sit there patiently, and wait when it is needed again.

And no need to design a different code depending on number of rows (which can vary depending on length of RoomID's you select). Have you selected 1 RoomID, or million, the schema with dummy table will work always.

As to "How to fill the dummy table" - obviously you have to execute some INSERT query string in OnClick event of cmdSelectAll. The query string may be something like

"INSERT RoomID INTO tblDummy FROM qryRoomInformation WHERE Left(CampusNames, Len(" & SearchFor_Multi & ")) = '" & SearchFor_Multi & "'"

And (before this and) after running the report, you have to execute the query string

"DELETE FROM tblDummy"

Remark. Somehow after making an entry, I often can't edit it immediately - "save" simply don't work,

[hinchi1]

so could i do an update and delete query to the same temp table, before the delete obviously output data to the report? To be honest this is getting a little confusing. i have it in my head as to what i need to do, but unsure how to execute the task a this time.