Open Report 2 Ways
By Richard Rost
2 years ago
Open a Report with Criteria from Form or a Prompt
In this Microsoft Access tutorial, I will show you how to open the same report using either a form field or a prompt for criteria. You'll learn to remove dependency on a pre-opened form by integrating an input box, applying a Where condition, and enhancing user prompts without complex coding. This tutorial will make your Access reports both user-friendly and versatile.
Samantha from Clinton, Oklahoma (a Silver Member) asks: If I open an invoice report while the order form is open, the query uses Forms!OrderF!OrderID to get the currently open invoice to display. Is it possible to open an invoice from the Main Menu form without having the order form open but also not having the user see that unfriendly prompt? Maybe give them something like "Enter Order ID"? - Paraphrased
Members
There is no extended cut, but here is the database download:
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, report criteria from form, form field criteria, user prompt criteria, input box for criteria, where condition in Access, custom query criteria, VBA programming Access, dynamic report generation, user-friendly Access forms, create accessible database inputs, validate numeric input Access, DLookup function usage, Nz function example, error handling in VBA, dynamic SQL conditions Access, Access report customization
Intro In this video, I will show you how to open the same report in Microsoft Access using two different methods: by passing criteria from a form field, such as an order ID, or by prompting the user for input if the form is not open. We will adjust the query setup, add VBA code to handle both scenarios, and use input boxes, error checking with the isNumeric function, and DLookup with Nz to validate the user's input and ensure a smooth experience when opening reports from different places in your database.Transcript Today we've got an interesting one. I'm going to show you how to open the same report from either a form field criteria, like a field on a form, like an order ID, or if that form's not open, how to prompt the user for the criteria. Today's a very interesting question from Samantha in Clinton, Oklahoma, one of my Silver members. Now this is paraphrased because we went back and forth on the forum and I'm just trying to understand what she's looking for, but basically it's this. If I open an invoice report while the order form is open, the query uses forms order F, order ID to get the currently open invoice to display from whatever order is displayed on the screen.
Is it possible to open an invoice from somewhere else, say the main menu, without having the order form open, but also not having the user see that unfriendly prompt. Maybe give them something nice like enter order ID. Let me show you what she's talking about first. Here I am in my TechHelp free template. This is a free database. You can grab up my website if you want to. Now, we have a customer form. We have an order form. Here is an order right and if I click on the invoice button it opens up the invoice report and the invoice report gets the order ID from this form. In fact, there's a query underneath it called order invoice queue and there's a parameter right over here that says get your parameter get your criteria from forms order F order ID okay and that's not a problem as long as this form is open. But if you try to open the report from somewhere else and that's not open. Let me right click, print preview, you get that first: forms, order, F, order ID. And all Samantha really wants is for that to be some kind of nice, you know, pretty parameter name. Like, you know, enter the order ID or something that's not that, because the user's not going to understand what that means.
Now I tried a bunch of different ways to play around with this and I cannot come up with a solution that does not involve VBA. So if any of you can figure it out, let me know. I tried like three or four different things. I even asked ChatGPT which is kind of useless in this manner but it usually is when it comes to access. It's good with VBA but it's not good with regular access solutions. So I'm going to show you a way to do this but it's going to involve a little bit of programming. First some prerequisites. If you have not yet watched my invoicing video, go watch this so you understand how my invoicing system is built, especially the order form and the invoice report and how they get their values. And to get that value from the customer, if the form is not open, we're going to use an input box. Yes, there are many different ways you can do it. Input box is the easiest I think, so we're going to use this. So go watch this video too if you have not yet already. And now optionally we're going to add a few little embellishments to it. We're going to add an if then statement. We're going to use D lookup to make sure that order exists. And we're going to use the NZ function. Now these last three are optional. You don't have to watch those if you don't want to. I'm going to show you the basics first and then show you some extra little tips you can throw in there. Okay? So go watch these first and then come on back.
All right, so the first thing we're going to do is we're going to remove the criteria from the order invoice queue. I built it this way initially because it's easiest for beginners to understand by just putting a criteria right inside the query. So open up the query in Design View, the order invoice queue, and we're just literally going to take this out of here because order ID already exists in here way at the beginning. We got order T. star. That was for the criteria. All right, so save that. Now if we go and open up the order and hit invoice, we're getting everything in there. We're getting all the line items, all the invoices. So we have to put a criteria in the report itself. But I'm not putting it in the report. I'm putting it in the button that opens the report, okay? We're going to add a where condition. Now if you look in the code for the button, right-click, build event. We've got me refresh, so it refreshes the current record and saves it to the table and then we're going to open report, order invoice R, AC view preview for print preview. Next we're going to go to the end of that line and go comma, comma. We're going to use a where condition. I prefer where conditions over filters. All right, we're going to say where the order ID in that report equals, close up your quotes, and order ID. What that's going to do is it's going to take the order ID off the current form which is the order form and then use that as a criteria. Save it, close it, let's close this and reopen it. And now when I hit the invoice button, the button supplies the criteria. There's just order one. If I go to another one and I hit the button, there you go. It supplies the criteria, we got order three. Easy enough.
Now, what Samantha wants is for her people to be able to do a quick lookup without having to go to the customer, open the order form, all that stuff. So if she wants a button here, we'll just prompt them, what's the order ID? So for that, we're just going to use an input box. You could use a form field, you could do this, lots of different ways you can do this. I think the input box is the easiest. So here we're going to just put open report or open invoice. How about that? Open invoice. Right-click, build event. That brings us in here. Now we don't want status hello world this is our hello world button right get rid of that so the first thing we're gonna do is we're going to input box the user and say enter order ID or whatever friendly prompt you want and when we get that response we got to save it somewhere right so we're gonna dim a variable dim s as a string we're gonna say s equals input box all right what's the prompt what do you want the user to see enter order ID like that the rest of these are optional. You can give it a title. Like open invoice or whatever you want. All right, you can specify a default value here. Although I recommend you don't, all right? For this particular case. Close that and press enter.
So now they're gonna ask the user to enter an order ID. Now we'll put one simple check here just to make sure they put something in or didn't hit cancel. We're gonna say if S equals an empty string then exit sub. If they don't put anything in or they hit cancel input box returns an empty string and it'll just exit out of the sub. Okay we'll put some more checks in a few minutes. Now we have S we have the order ID as a string so we're gonna say do command dot open report same thing we had in the button in the order ID in that report equals S, whatever the user typed in. Save it. Always good to throw in a debug compile once in a while. Close it. Close the main menu. Let's reopen it. And now open invoice. Okay. Enter order ID. Give me a three. And there's your criteria. There's order three. And that's a nice friendly prompt, enter order ID, one. And there's order one. See, we're just supplying the criteria from the button that opens up the report instead of having the report get the criteria from a query. Now if you're happy with that, great, we're done. But there are some other things you may want to check. For example, what happens if the user types in something like that, a bunch of letters. You're going to get an and a parameter value. Okay, so let's check first of all to make sure that the user has put in a numeric value. And for that we can use the isNumeric function. If not isNumeric is going to take this value, s, which is a string, and say is there a number value in that string? And if not, it's going to say, nah, can't handle it. Alright, let's save that. Come back over here, let's try that again. Put a bunch of garbage in and hit okay. Alright, there we go, invalid order ID, see, nice. It's making our code a little more user friendly. How about if they do something like this? They put in a big number like that, 315 blah blah blah. It's a number, it's numeric, but that order doesn't exist in the database so they get an empty invoice. Now, yes, you could use an on no data event in here, but an easier way is just to check to see if that invoice exists, if that order ID exists. So let's go back to our code. Let's do a D lookup. D lookup is nice and easy. Now we need a value to store the D lookup return to value in. So we're going to say order ID as long. Alright, we're going to come down here now. We're going to look, let's put some comments in here too. If nothing is entered or the user hits cancel. Alright. If a non-numeric value is entered. Alright. And remember, comments aren't necessarily for other people there for you ten years from now i wish i would have commented my code that i wrote ten fifteen years ago i was like i don't need comments yeah yeah older you's gonna thank you for your comments now we're gonna say make sure order exists okay order id equals nz dlookup what are we looking up? an order id in the order t where the order ID equals S. Now at this point we know S has a numeric value in it so it's safe to just send it S. It's going to tack it on the end of that string. Comma zero. So what's going to happen is we're going to look up an order ID from the order table where the order ID equals whatever the user typed in. Yes, that's perfectly valid. If that doesn't exist it's going to return a null value which NZ will then convert to a zero. That's the beauty of NZ. Now we can say if order ID equals zero then message box order not found or whatever exit sub else open it up and if save it debug compile of course all right looks good come up here try this again let's hit cancel okay that returned an empty string. Ready? Let's try it again. Big number. Hit okay. Order not found. There you go. See how simple that is? Just a few lines of code, but it makes your database a whole lot more usable. You can use the same report, the same query. The users can now open it from both places without any change. All right, and now you've given yourself a nice quick way to look up that invoice from here, verify the information is valid, and then show them the invoice they want. See? Nice and easy. This is why we learn a little bit of programming folks. See? Just a little bit of VBA programming can really make your databases go to the next level. Whole nother level. If you want to learn more about opening a specific record when you open a report, I got another separate video on that. Go watch this. It's basically what I just showed you now, but this is a couple of little tips in it. I cover that isNumeric function along with a whole ton of other functions. IsError, isNull, isDate, string functions. I've got a whole comprehensive guide to access functions. It spans multiple classes. Check it out. It's on my website. I'll put a link down below. And of course, if you like learning with me and you want to learn some more programming, I've got tons of developer lessons. Cover all kinds of stuff and I take you from the beginning to the end straight through as you should learn it. So check it out. Again, it's on my website. You'll find links down below. But, that is going to be your tech help video for today. I hope you learned something. Live long and prosper my friends. I'll see you next time.
TOPICS:
Opening a report using form field criteria
Handling queries without open forms
Using an input box for user input
Removing criteria from a query (Order invoice query)
Adding a WHERE condition in VBA to open reports
Using an input box to set report criteria dynamically
VBA programming to handle report openings
Implementing error handling for non-numeric input
Validating user input with isNumeric function
Checking if an order ID exists using DLookup and Nz functions
Error message handling with MsgBox in VBA
Using comments for code clarity
Debugging and compiling VBA code in AccessQuiz Q1. What is the primary issue Samantha from Clinton, Oklahoma is facing with her database application?
A. She is unable to delete invoices from the database.
B. Her users do not understand the automatic prompt generated when opening an invoice from the main menu.
C. The database does not support the entry of order IDs.
D. She wants to remove the capability to open invoices entirely.
Q2. What does the parameter 'forms order F order ID' signify in the context of Samantha's problem?
A. It represents a method to directly delete a form in the database.
B. It indicates a link to an external database form not related to invoicing.
C. It functions as a placeholder to retrieve the order ID from an open order form.
D. It is a command to close the order form automatically after use.
Q3. Why did the solution to Samantha's problem involve using VBA?
A. Because it is the only way to create a form in Access.
B. Because Access alone could not provide a user-friendly input prompt without VBA.
C. Because VBA is required for deleting records in Access.
D. Because the report needed to be written exclusively in VBA.
Q4. What purpose does the 'input box' serve in the solution provided for Samantha?
A. To record user login times.
B. To reset the database each time an order ID is entered.
C. To allow users to manually enter an order ID when the order form is not open.
D. To automatically generate order IDs.
Q5. What is used to verify that a valid numeric order ID was entered before attempting to open the invoice?
A. A DLookup function.
B. An IsNumeric function.
C. An NZ function.
D. A boolean check function.
Q6. What does the function 'DLookup' accomplish in the code?
A. It logs user access to the database.
B. It retrieves a value based on a specified condition to check if the order ID exists in the database.
C. It calculates the total sum of all invoices.
D. It creates a new record in the Order table.
Q7. What happens if the 'DLookup' function does not find the specified order ID in the database?
A. It generates a new, random order ID.
B. It will still open the invoice with incorrect data.
C. It opens a new blank order form.
D. It returns a null value that is converted to zero by the NZ function and displays a message indicating the order was not found.
Answers: 1-B; 2-C; 3-B; 4-C; 5-B; 6-B; 7-D.
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone covers a common scenario in Microsoft Access where you want to open a report using specific criteria. In this case, I will explain how to open the same report either by using a value from a form field, like an Order ID from an open order form, or by prompting the user for that value if the form is not open. The goal is to provide a user-friendly prompt, such as "Enter Order ID," instead of displaying a confusing parameter prompt that Access generates by default.
Let me outline the situation. Normally, if you have an order form open and use a button to open an invoice report, the underlying query uses the value on that form–typically Order ID–to filter and show the correct invoice. The query might use a parameter like Forms!OrderF!OrderID, which works great as long as the form is open. However, if someone tries to run the report from somewhere else, say the main menu, and the order form is not open, Access will prompt with a confusing message asking for Forms!OrderF!OrderID. This is not user friendly, and most users will not know what to enter.
After experimenting with several approaches, I found that solving this problem without VBA is not really feasible. You need a bit of programming to handle it smoothly. What I am going to show you involves a simple VBA solution, some basic error checking, and a much friendlier experience for the user.
Before we get started, there are a few things you should know. If you are not already familiar with my invoicing example, which explains how the order form and invoice report work together, I recommend reviewing that first. Also, for those unfamiliar with using the InputBox function in Access VBA, it's helpful to check that as well. These are not mandatory, but having that background will make this process easier to follow. Additionally, I will add some tips for verifying user input, such as checking if the input is a number, ensuring the order actually exists in the database using DLookup and Nz, and incorporating basic error handling, though these enhancements are optional.
To start, you need to remove the parameter from your report's driving query. When reports depend on a form field that may not always be available, it is better to manage this process directly in the event that opens the report. Open your query in Design View and remove the criteria from the parameter that currently references the form. Saving the changes ensures the query now returns all records.
Next, you will add the criteria directly in the code for the button that opens the report. This is done by supplying a WHERE condition in the DoCmd.OpenReport statement. The WHERE condition restricts the report to just the Order ID specified on the open form. When the user clicks the invoice button from the order form, this works as usual.
The improvement comes when you want the flexibility to open the report from a different place, like the main menu, without the order form being open. Here, you can use an InputBox to prompt the user for an Order ID. You'll start by creating a new button, perhaps labeled "Open Invoice," and writing a simple event procedure for it. When the button is clicked, the code will display an InputBox with a friendly message asking for the Order ID. The user's response is captured in a variable.
To prevent errors, it is important to check if the user actually enters something or cancels the prompt, in which case nothing happens. Next, validate that the input is numeric using the IsNumeric function, which prevents users from entering text or symbols by mistake. If the entered value is not numeric, inform the user and exit the procedure.
You should also verify that the entered Order ID actually exists in your database. This is where the DLookup function is handy. By searching for the Order ID in the Orders table, you can determine if a record with that ID is present. If it does not exist, you can display a message notifying the user that the order was not found. If it does exist, the report will open, showing just that order's invoice.
Throughout all this, it is wise to use comments in your code for clarity. They help you and anyone else who may work on the database in the future understand the logic behind each step and can be invaluable if you need to revisit your work years down the road. Also, remember to compile your code occasionally to catch any typos or mistakes early in the process.
By following these steps, you now give users a much smoother and more intuitive experience. They can open the invoice report either from the order form using a button that supplies the Order ID directly, or from somewhere else in your application, such as the main menu, with a prompt that explains exactly what they need to enter. You also make the system robust by checking for valid input and for the existence of the order in the database.
This small bit of VBA programming can significantly improve your database's usability and demonstrates how even a little code can help you go from a basic database to a much more professional application.
If you want to learn more about techniques for opening reports to specific records, or learn about other validation functions like IsNumeric, IsError, IsNull, and many more, I have several comprehensive guides and tutorials that you can find on my website. All the details covered here are part of a complete video tutorial that walks you through each step. You can find that video and others at the link below.
Live long and prosper, my friends.Topic List Opening a report using form field criteria
Handling queries when the form is not open
Using an input box to prompt for criteria
Removing criteria from the query design
Adding a WHERE condition in VBA to open a report
Writing VBA to open a report with dynamic criteria
Prompting users for Order ID in a user friendly way
Checking for empty input or cancelled input in VBA
Validating user input with the isNumeric function
Using DLookup and Nz to verify Order ID exists
Displaying error messages with MsgBox in VBA
Supplying report criteria via a button on the form
Replacing query parameter prompts with custom dialogs |
