Adding a search box to a report in order to open new report (1 Viewer)

mikenyby

New member
Local time
Yesterday, 22:37
Joined
Mar 30, 2022
Messages
22
Hey y'all,

I have a database that generates a lot of reports based on parameter queries. However, I'd like to add a search box to the reports so that the user doesn't have to keep returning to the home navigation form in order to open a new report. I've put some leg work in to get it to work, and it all looks like it should be functional, but for some reason it's not pulling the input from the search box I've made. Here's the lay of the land:

Here is what the report I'm working on looks like. It's called "Box Search by Box Number". I just added an unbound text box and control button, circled in red. The text box which is being used as the search box is called NewSearchArchivalNumber1 and the control button labeled "New Search" is named "Command 21".
1650302291668.png

Command21, when clicked opens a new report named "Box Search by Box Number New Search", which is a copypaste of this report, except in the report's linked query, the criteria for "Archival Number" is:

Like "*" & [Reports]![Box Search by Box Number]![NewSearchArchivalNumber1] & "*"

As far as I can tell, this should prompt access to pull whatever the user types into text box [NewSearchArchivalNumber1] as the input criteria for the new report. Except it doesn't. It opens a report with literally every record in the linked table regardless of what is entered in the text box. I've tried removing the [Like] function and leaving it as just [Reports]![Box Search by Box Number]![NewSearchArchivalNumber1]. That will open a report with no records at all, regardless of what is entered into the text box.

I feel like I'm missing a step but I can't figure it out myself.

Thanks!
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:37
Joined
Feb 19, 2002
Messages
36,849
Buttons are only active on "reports" when they are opened in "ReportView" rather than "PrintPreview". Keep in mind that the two produce very different results and none of the break code will run in ReportView so you can't have any page breaks or totals.

If you need to support printing or you want totals, just go back to the report form and let them choose again.
 

mikenyby

New member
Local time
Yesterday, 22:37
Joined
Mar 30, 2022
Messages
22
Buttons are only active on "reports" when they are opened in "ReportView" rather than "PrintPreview". Keep in mind that the two produce very different results and none of the break code will run in ReportView so you can't have any page breaks or totals.

If you need to support printing or you want totals, just go back to the report form and let them choose again.
Thanks, but I am only using report view. There is no practical reason anyone would need to print anything from this database.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:37
Joined
Feb 19, 2002
Messages
36,849
Why are you using LIKE? Are you really using a partial string? If you are not, try removing the wildcards and use equal instead. If you need to use the wildcard because you have a partial string, you might want to put a stop in the code on the line that opens the report and print the value in the control to the debug window using the reference you use in the query. [Reports]![Box Search by Box Number]![NewSearchArchivalNumber1]

Another alternative, is to use an unqualified query and use the Where argument of the OpenReport method to pass in the criteria.
 

June7

AWF VIP
Local time
Yesterday, 18:37
Joined
Mar 9, 2014
Messages
4,450
What happens when you type in this box? Does it hold the input?

Reports are not intended to be interactive.

You have two identical reports - one opens the other? Then they have to close the second and return to the first if they want to open another filtered version? How is this different from returning to a form?
 

mikenyby

New member
Local time
Yesterday, 22:37
Joined
Mar 30, 2022
Messages
22
Why are you using LIKE? Are you really using a partial string? If you are not, try removing the wildcards and use equal instead. If you need to use the wildcard because you have a partial string, you might want to put a stop in the code on the line that opens the report and print the value in the control to the debug window using the reference you use in the query. [Reports]![Box Search by Box Number]![NewSearchArchivalNumber1]

Another alternative, is to use an unqualified query and use the Where argument of the OpenReport method to pass in the criteria.
I'm using Like because the field has a lot of names with recurring prefixes. It's for an archives, and many box archival numbers all start with the same string of letters and numbers. So instead of having to search for box ABCXYZ0143, I would like them to be able to just search 143.

There must be a simpler way of doing this. This is my first attempt at a search bar but it's just not working. Could it be because I'm working with reports? Would this be simpler if all my reports were view-only forms?
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:37
Joined
Feb 19, 2002
Messages
36,849
Did you try the debugging technique I suggested? If the new report is opening with all records, then the likely problem is that there is a typo in the name used to reference the control.
 

mikenyby

New member
Local time
Yesterday, 22:37
Joined
Mar 30, 2022
Messages
22
What happens when you type in this box? Does it hold the input?

Reports are not intended to be interactive.

You have two identical reports - one opens the other? Then they have to close the second and return to the first if they want to open another filtered version? How is this different from returning to a form?
When I type in the box, the data holds, but if I press enter it disappears. I realize that I may have made a mistake in using reports instead of read-only forms, but I've managed to add a number of other controls on my reports that work just fine.

And yes, two identical reports because I wasn't sure of another way to enter new criteria from a different source. And the new report has a similar search function but the control button ostensibly closes the current report and reopens it with the new search criteria
 

mikenyby

New member
Local time
Yesterday, 22:37
Joined
Mar 30, 2022
Messages
22
Did you try the debugging technique I suggested? If the new report is opening with all records, then the likely problem is that there is a typo in the name used to reference the control.
Unless you would like to walk me through how to do that, I'm afraid that's entirely outside of my skillset. I'm doing this design as part of a graduate student co-op placement and this is the first time I've designed a database from scratch. Learning as I go here.
 

mikenyby

New member
Local time
Yesterday, 22:37
Joined
Mar 30, 2022
Messages
22
Did you try the debugging technique I suggested? If the new report is opening with all records, then the likely problem is that there is a typo in the name used to reference the control.
On the query design screen I used the autoprompts, so I'm sure there's no typo in the control name.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:37
Joined
Feb 19, 2002
Messages
36,849
Not sure why you are pressing enter. I thought you wanted to press a button.

To add a stop to the code, open the report in design view.
Open the procedure that is in the click event of the button that is to open the second report.
In the margin to the left of the OpenReport method line, press the left mouse button. A circle should appear and the line will be highlighted like this:
VBAStop.JPG

Save and close.
When you press the button, the code will stop on the highlighted line. Then in the debug window, type

? [Reports]![Box Search by Box Number]![NewSearchArchivalNumber1]

What value do you see?

If the print shows nothing, then print using a direct reference to the control"
? Me.[NewSearchArchivalNumber1]

What value do you see?
 

June7

AWF VIP
Local time
Yesterday, 18:37
Joined
Mar 9, 2014
Messages
4,450
Me. won't work in Immediate Window.

AFAIK, cannot type into a textbox on report and have it retain value. And since you have to press Enter or click a button to trigger an event, textbox loses input as soon as loses focus. Buttons can work on report in ReportView as well as right click menu quick search/filter but that's about as far as report can go for interactive behavior.
 

SHANEMAC51

Active member
Local time
Today, 05:37
Joined
Jan 28, 2022
Messages
294
I have a database that generates a lot of reports based on parameter queries.
This is one of your databases – a form for filtering by 10 fields (highlighted in green) or you can add a checkbox to select
Yellow entries should be protected from accidental correction
When you click on the orange field – a detailed (full) display in the form of the selected record
Send only filtered or marked records to the report
 

Attachments

  • Screenshot_6.png
    Screenshot_6.png
    34.6 KB · Views: 116

mikenyby

New member
Local time
Yesterday, 22:37
Joined
Mar 30, 2022
Messages
22
Me. won't work in Immediate Window.

AFAIK, cannot type into a textbox on report and have it retain value. And since you have to press Enter or click a button to trigger an event, textbox loses input as soon as loses focus. Buttons can work on report in ReportView as well as right click menu quick search/filter but that's about as far as report can go for interactive behavior.
This has to be the problem. You are correct that the text in the search box disappears as soon as I click the button. If I want this to work I'll have to convert every report to a form. It's what I should have done to begin with as they aren't intended to be printed but I'm new to Access so I didn't realize the functionality differences between forms and reports until too late.
 

mikenyby

New member
Local time
Yesterday, 22:37
Joined
Mar 30, 2022
Messages
22
Not sure why you are pressing enter. I thought you wanted to press a button.

To add a stop to the code, open the report in design view.
Open the procedure that is in the click event of the button that is to open the second report.
In the margin to the left of the OpenReport method line, press the left mouse button. A circle should appear and the line will be highlighted like this:
View attachment 99997
Save and close.
When you press the button, the code will stop on the highlighted line. Then in the debug window, type

? [Reports]![Box Search by Box Number]![NewSearchArchivalNumber1]

What value do you see?

If the print shows nothing, then print using a direct reference to the control"
? Me.[NewSearchArchivalNumber1]

What value do you see?
I was using an embedded macro but converted it to VBA to try this out. Did exactly as you suggested, clicked the button and all it does is bring up the VBA again exactly as I left it, no values, no report.

I am confident that the problem is as another user stated:
"cannot type into a textbox on report and have it retain value. And since you have to press Enter or click a button to trigger an event, textbox loses input as soon as loses focus. Buttons can work on report in ReportView as well as right click menu quick search/filter but that's about as far as report can go for interactive behavior."

I may try to convert all my reports to forms and see if that fixes it.
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 22:37
Joined
Feb 19, 2002
Messages
36,849
I confirmed June7's statement. Therefore, you are left with converting to forms. Depending on what version of Access you are using, you might be able to get Access to do this for you. Try the Save As option.
 

Users who are viewing this thread

Top Bottom