Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Home > TechHelp > Directory > Access > Open Report 2 Ways < Blackout Times | Object Dependencies >
Back to Open Report 2 Ways    Comments List
Transcript Pinned    Upload Images   Link   Email  
Richard Rost              
6 months ago
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 Access

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Open Report 2 Ways.
 

 
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 11/6/2024 4:23:39 PM. PLT: 1s