Solved Report based on a Query that pulls from User Input in a Form (1 Viewer)

Bean Machine

Member
Local time
Today, 12:12
Joined
Feb 6, 2020
Messages
98
Okay the title might be confusing so I'll explain in greater detail. I have a form in which a user can input information into comboboxes and then run a query to pull up the data they had input. For example if I wanted to run a query to search a student name, I would input the student name and it would pull up all records on that student. You can get as specific as an item that the student signed out. Anyway, I want the user to be able to generate a report based on what they queried for, the only problem is that when creating a report for the query I get a pop up window that says "Enter Parameter Value", which makes sense as that was what my code in the query itself does but I don't want that window to pop up when trying to create a report as that information was already input into the query by the user. I hope this isn't too confusing, I'm just not sure where to go from here. Here is a sample of my database, I am currently trying to work around it using the "master report". The "Database Search" form is what the query pulls from and the query is called "Items Master List".
 

Attachments

  • Loans Database Sample.zip
    471.8 KB · Views: 126

vba_php

Forum Troll
Local time
Today, 11:12
Joined
Oct 6, 2019
Messages
2,884
bean,

I see you are using the LIKE keyword with regard to incorporating it in with checking for NULL values in combo boxes that i told you about in your last thread. that's good. however, your master report has no record source with it. can you please give it a go, and bind that report to the query, debug it a bit, and then come back and post here with the result of what you run into? your query is quite large. I ran a test search using the "run query" button and it seems to return the correct records. but I only inputted one value, which was the student's name.
 

GinaWhipp

AWF VIP
Local time
Today, 12:12
Joined
Jun 21, 2011
Messages
5,901
Hmm, well I just connected Items Master List to Master Report and opened Database Search. I picked several Students and the report opened each time to the person with no *Enter Parameter Value* box popping up. Did you do something differently?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:12
Joined
Feb 19, 2002
Messages
42,970
I didn't look at the database but if your query prompts, then it will prompt once when you preview the report and a second time when you print it. To avoid this, add unbound (or bound) controls to the form that runs the report. Have the user enter the parameters in the textboxes and change your query to refer to the form controls.

Select ...
From ...
Where somefield = Forms!yourform!somefield OR someotherfield = Forms!yourform!someotherfield
 

Bean Machine

Member
Local time
Today, 12:12
Joined
Feb 6, 2020
Messages
98
Ended up finding my own way around it, I appreciate the help though! Thank you everyone!
 

vba_php

Forum Troll
Local time
Today, 11:12
Joined
Oct 6, 2019
Messages
2,884
well that's good, bean. but when that happens, it kind of makes me sad because we offered advice and nothing really came out of it. but you found the solution, so great! mark this solved if you come back. thanks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:12
Joined
Feb 19, 2002
Messages
42,970
Bean, if you can explain your solution, you might help someone else who finds this thread later.
 

jdraw

Super Moderator
Staff member
Local time
Today, 12:12
Joined
Jan 23, 2006
Messages
15,364
Bean,
One purpose of this forum is to share ideas and solutions to problems/issues. I realize you have only been a member of the forum for a few days, but I would ask that you post a description of your solution and/or a database showing same.
We are all glad that you have participated in the forum and that you have found a solution.
Good luck with your project.
 

Bean Machine

Member
Local time
Today, 12:12
Joined
Feb 6, 2020
Messages
98
I apologize I have been hard at work on other tasks lately and did not notice your messages. I solved the issue by querying the query that had the parameters set, i.e. I had an master query that would show any and all information associated with the database and a user could filter that information via combobox, I then set up a query that would pull the specifics of the master query and connected it to a report so that the user would not be prompted to enter redundant information. I found that using a subquery and the requery function were a lot more useful than having the query open in another window. Hope that's descriptive enough and that anyone else with this problem can learn from what I've done. Thanks for all the help you all have provided!
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 12:12
Joined
Feb 19, 2002
Messages
42,970
Thanks. That works in some instances but you might want to refer to my earlier suggestion to use a form field instead. It gives you more flexibility and if you preview and then print, you won't get prompted twice.
 

Users who are viewing this thread

Top Bottom