Quick Queries #27
By Richard Rost
14 months ago
Error Handling, Non-breaking Spaces, Inventory, More!
In this Microsoft Access Quick Queries tutorial, I will show you how to tackle a variety of user-submitted questions in our ongoing Quick Queries series. Topics include handling search form errors with error handlers, tips for distributing text in long text boxes, and addressing runtime errors when adding new records in subforms. We'll also explore how to maintain accurate inventory levels, manage relationships in databases, and troubleshoot issues with data display in forms. Additionally, I'll answer queries about using the desktop version of Microsoft Office, modifying report background colors, and setting up relationships properly. Join me for another informative session of Quick Queries.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, error handler, record source update, search box error, runtime error 3021, orphaned records, quantity on hand, inventory handling, event procedure, non-breaking space, print margins, report background color, subform issues, link master and child fields, compact and repair Access, relationship setup, product price adjustment, offline Office use, Google Docs offline, Access subform troubleshooting, Access forums comments
Intro In this Quick Queries video, we talk about a wide range of Microsoft Access questions, including handling error 2185 in form search code, tips for distributing text and using non-breaking spaces, fixing runtime 3021 errors and orphaned records in subforms, and inventory quantity updates. We also discuss setting up event procedures, linking subforms with master and child fields, the limitations of coloring report backgrounds, exporting reports to Word and PDF, maintaining table relationships, and best practices for product pricing. We finish with a comparison of Microsoft Office Online and desktop versions, and notes about Google Docs offline use.Transcript It's Friday, folks! You know what that means. It's time for another TechHelp Quick Queries. This is number 27 brought to you by AccessLearningZone.com. I'm your instructor, Richard Rost. Quick Queries is a video dedicated to all the questions that we get either in the forums or on my YouTube channel that don't necessarily need a whole video themselves, but are interesting stuff to talk about. So, that's what we're gonna do today.
One of my Platinum members, Todd, posted this in the forums just yesterday. He's using my Search As You Type video, and it basically changes the record source of a form based on what you type into a search box. The problem is if you don't put anything in the search box, this is gonna error out. Now, there are all kinds of ways you can check for it, but Kevin came up with a genius solution. I'm just gonna scroll down to his code down here. He's handling it with an error handler instead of worrying about what's in there. He's just saying if you get an error, go to the error handler. So, if any of this stuff throws an error, it's gonna jump down here.
Now, the error number that the guy is getting is 2185. So, if that's the case, all you got to do is just reset it to a basic SQL statement and show all the records or show just Entity equals zero. Bottom line is if you get a weird error instead of having to jump through all these hoops to try to figure out what it is, just trap it. So, if I get this error, I know what it is. It happens, just do this basic thing instead. So, great solution, Kevin. Thanks for sharing that.
Next up, some comments on my Distribute Text video. This is where I show you how to get that last line in a long text box to distribute properly across the row. NinjaGarrow says that what they like to do is add invisible text - white characters on a white background. Yeah, that would work too. If you just made this text box white, then you could just insert any characters that you want, and with rich text, that is definitely possible. BJones9942 says when you have thousands of records, it's easier to abandon your need for justified text. Yeah, I guess so. In Word, a Control Shift Space gives you a non-breaking space, never tried it in Access. Well, I know for a fact it doesn't work in Access, but for everybody else, in case you want a Word tip, if you've got something like this, let's say you've got a space here right after Exhibit. Let's pretend these are two small words. You put a space there, it's gonna break over that line with the word wrap. But if you put a Control Shift Space there, look at that, it doesn't break. You still got a space there, but it's a non-breaking space, so it won't slide back up here. That's a pretty cool tip. Thanks for sharing that, BJones.
Next up, some questions on one of my more popular videos, the invoicing video. Sean says when trying to add a new product to a blank order, I get the following runtime error 3021: You cannot add or change a record because a related record is required in table OrderT. Right, if you go to a customer's orders and try to add a new order, if you just try putting a record down here in the subform, there's no record up here yet. See it says New? That means there's no ID down here to create that relationship. You got to have a record in the parent table first. So you can either put something up here, set the date or whatever, because now I've got an ID, now I can come down here and add stuff. If you don't, you'll be creating what are called orphaned records. They don't have a parent. And of course, I show you how to fix that in this video with orphaned records. And basically, with a teeny tiny bit of code, a couple of lines, you can just say that if a record is being inserted into the subform and there's nothing up here, just change the value up here. It's really easy to do.
MegaEldragoDestroyer says, is there another tutorial to be able to tackle the scenario where the field Quantity is added for each item in the ProductT table? I'd like the quantity to be decreased once the product has been added to the order form. Yeah, sure. You're basically talking about inventory handling. You want to keep a Quantity On Hand, and then when products are added or, I like to do it when the product is shipped, you mark it Product Shipped. At that point, it'll go through and take all the inventory items off. And you can trigger it whenever you want. Go watch this. I got tons of inventory videos on my website.
Next up, this user is saying that the database is asking if they want an event procedure, not an embedded macro. What is going on here? When you go to activate the builder, whether it's the Code Builder or the Macro Builder or the Expression Builder, you'll sometimes get a little pop up. I teach how to turn this pop up off in my very early classes. But go watch this video; I'll put a link down below and teach you how to set your database to always use event procedures. If you're doing VBA programming, which is what I use in most of my videos, you want to always use event procedures. I never use the Macro Builder, I never use the Expression Builder. I explain why in this video.
JunkerYorg, or Yorg, whichever, says about the point where I drag my OrderDetailF onto the order form, and for some reason, no data is showing up in the order detail fields. If I open the OrderDetailF by itself, all the data populates. I'm sure I'm missing something simple, but I don't know what it is. My Link Master and Child Fields are both looking at OrderID. That's where people usually go wrong. This guy right here, when you create your order form, usually what happens is they create this subform, and the Link Master Fields/Link Child Fields have to be whatever field links those two tables together. In this case, it's OrderID. This is where people usually have a problem. Maybe sometimes this doesn't get created automatically for you. But if it's working for you, if you've got that, the only thing I could suggest is maybe you don't have a parent order. If you're on a blank order like that or an OrderID where there are no details, you won't see anything. That's the only thing I could think of. If you've got matching records with the same OrderID, you should see the records. If not, try recreating the subform again. Maybe something went wrong. Maybe it's corrupted. And of course, you can run down my troubleshooter. This was designed to catch all the weird problems that don't necessarily have a definitive fix. I've tried to order them from easiest to hardest, so back up your database first, restart Access, restart all Access databases, Compact and Repair, Compile your database, etc., etc. Run down this list. It might be something simple like your database is corrupted. Give Windows a fresh boot. Don't pull your hair out for two hours trying to figure something out. Try running down the simple stuff on here first, and if it still doesn't work, let me know, and maybe we'll take a look at it and figure something else out.
Hannah wants to know how you can change the background color of the report. I use the bucket tool, but it still doesn't fully color the page. Yeah, that's actually one of the limitations of Access. Access doesn't have many, but this is one of them. It's not really designed for doing fanciful things like that. You can make some really good reports, but yeah, there's a margin at the edge that you just can't change. So if you come into Design View, you can change, remember there's multiple sections in here. So if you want to change the background, let's say you want to do like a light blue, you can change the background of the Report Header, you can change the background of all of your objects or just make them transparent, shape fill transparent. You could change this section. So I get it. Oh, I didn't get it. Where is it? Recent colors down there, the Detail Section, and so on. All the sections you can change. And when I do a Print Preview now, it's a little less than ideal. That's not very nice.
You can try to do something like a rectangle in the background behind everything. But even that doesn't work. I've tried it before. You could take a rectangle, and then you could drop a rectangle in here. You can make this guy have that background color. You can slide it up here. You could try to make it eight and a half across and then however big you want it, and send it to back. But no matter what you do, you're still gonna have those margins around it. Even if you can't set the margins to zero. That's one of the limitations. You go to Page Setup and go to Margins. You could try setting these to zero, but no. .2 is the smallest, depends on your printer. So there's really no way to easily do that inside of Access by itself.
So what you can do is you can export this as a PDF or a Word document and then you can change the color in that application. Now, I know Word isn't perfect, but if you export to Word and open it up and so here it is in Word, yeah, you miss some of the format. You could export it as a PDF, and you'll get closer to what it actually is supposed to look like with the lines and stuff. In Design, you can come over here, go to Page Color, and make your color like this. This is good if you want to make PDFs to send to people. Most printers are still gonna have a white margin around because they can't print all the way to the edge of the page. I've tried. But if you want to make a nice pretty PDF to send someone like an online brochure or something, you can do with that. But you've got to use another application, Access can't do it by itself. Sorry, again, Access doesn't have many limitations, but it does have some, and that's one of them.
I don't know, Sammy, should we add that to the list? Maybe, you know, have margins of zero so people can create PDFs out of this stuff and make a background page color. I looked, I searched, I even asked ChatGPT, there is no background color setting for the entire page. There should be. So yeah, let's add that to the list.
User Fafafan1000 wants to know can we take the relationship out of the picture completely? Not really, no. If you want to set this up the right way, you need a relationship between the order and the order details. Otherwise, you'd have to have all the order information on each detail, and then you're just going back to Excel. So you really need a relationship between these tables and a relationship between orders and customers if you want to have it built right. If not, do it your way; don't listen to me. What do I know?
Delayne says, thank you very much. I'd like to know how to adjust the price on a product without altering previous records. Any information will be helpful. Thanks again for your guidance. Yeah, this is a problem that you have if you put your products directly into the order details, for example. And I see this a lot. In fact, I actually build the database this way in my expert classes so I can teach you how to do it the wrong way so you get a better appreciation for doing it the right way.
What a lot of people do is if you want to have a product table with a list of products that you can pick from, you make this a drop-down, and then it automatically can figure out what the price is from the value in the combo box. If you go back and change the product pricing, like when eggs go up to $5,000, it will adjust all of your previous orders if these are linked together. So what you have to do is you have to pick the product but don't save that ID right in the product details in like a combo box in here. What I do is I put it down on the bottom, let you pick the product, and then it copies it up there with a little bit of VBA. It's a little bit more work, and I actually do cover how to do that in the extended cut for this invoicing video, which is available for the members. You make a little box down here, a little combo box with your products in it and their prices. You hit add, and that copies the product and the details up here. So that's stored in the order details, and if you go back and change these for future orders, it doesn't change all your existing ones. It does involve some coding. Again, I show you that in the extended cut for this video.
Next up is a question from last week's Quick Queries. User Asks if the online version of Microsoft Office can be downloaded to the user's computer. I hate online versions that aren't useful when the internet is not available. The answer is no, if you're using Office Online, the web browser-based version of Word, Excel, whatever else they got, no, you have to be connected for that to work. I agree, I don't like it where you can't use it offline either. If you need to be able to work offline, you need the desktop version, which is what I recommend anyways. The desktop version has a lot more features too.
It is interesting to note though that Google Docs, Sheets, and Slides can be used offline, but you got to set it up ahead of time and enable offline mode. I actually have a video that I'll be doing about how to use this in the near future. It's on my list. It's been on the list for a while, but I know most of my users are Microsoft Office users and not Google Docs, but I use Google Docs myself a lot. It's very lightweight, it's fast, and it's got some benefits that even the online version of Word doesn't have. Look for a video coming out on that soon.
Well, there you go, folks. Another Friday, another Quick Queries video in the can. That's gonna do it for today. I hope you learned something. Live long and prosper, my friends. I'll see you next Friday for another Quick Queries and I'll see you on, well, let's see, this Monday's a holiday. It's President's Day.So I'll see you on Tuesday for a new TechHelp video.
Take care and have a great weekend.
Special thank you and shout out to our sponsors:
One Soda with Access Experts: Software solutions manufacturing experts specializing in Access and SQL Server.
Sammy Shama with Shama Consultancy: A certified Microsoft Access expert who offers personalized one-on-one tutoring.
Amanda Nicole Consulting: Specializing in helping businesses move from complex Excel sheets to an Access database.
You'll find links to the sponsors in the description down below the video.
TOPICS:
Error handling with error number 2185
Distributing text in a long text box
Non-breaking spaces in Access vs Word
Runtime error 3021 and orphaned records
Inventory handling and quantity updates
Using event procedures in VBA
Link Master and Child Fields setup
Report background color limitations in Access
Exporting Access reports to Word or PDF
Importance of relationships between tables
Adjusting product pricing without affecting old records
Difference between Office Online and desktop version
Offline capability of Google Docs vs Office Online
COMMERCIAL:
In today's Quick Queries video, we're tackling a range of interesting questions and solutions shared by our viewers. From handling form errors with clever error handlers to adjusting text in long text boxes, we've got you covered. We'll explore how to resolve runtime errors in Access forms, tackle inventory handling tips, and discover why non-breaking spaces work wonders in Word but not Access. We'll also discuss defaulting databases to event procedures, troubleshoot subform link issues, and reveal techniques for adjusting product prices without altering existing records. Plus, learn some handy background color tricks for reports and whether the online version of Microsoft Office can be used offline. So grab your popcorn and join us for this action-packed episode. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What is the main purpose of the TechHelp Quick Queries video series?
A. To answer questions from the forums and YouTube that need full video tutorials
B. To address questions that don't require a full video but are interesting to discuss
C. To provide detailed training on advanced Access features
D. To promote Richard Rost's books and courses
Q2. What issue did Todd encounter when using the Search As You Type feature?
A. The form would crash when typing too fast
B. The search box could not handle special characters
C. An error occurred if nothing was typed in the search box
D. The search results were incorrect
Q3. How did Kevin suggest handling errors in the Search As You Type feature?
A. By modifying the SQL query syntax
B. Using an error handler to manage unexpected issues
C. Preventing errors by applying input validation
D. Coding a workaround using embedded macros
Q4. What video topic was related to justifying text and non-breaking spaces in Access?
A. Invoicing
B. Form creation and linking
C. Distributing text within a text box
D. Handling database relationships
Q5. What is required before adding new order details to a blank order in a subform?
A. Confirming the inventory quantity is available
B. Adding a related record in the parent table first
C. Ensuring the user has appropriate permissions
D. Configuring the correct page setup margins
Q6. What suggestion was given for handling inventory quantity when an item is added to an order?
A. Decrease the quantity immediately after adding the product
B. Define zero quantity if not yet shipped
C. Adjust manually with each new order
D. Update quantity when the product is marked as shipped
Q7. What limitation of Access was mentioned when dealing with changing report background colors?
A. Limited color palette
B. Inability to export colored reports
C. Margins that can't be fully colored
D. Lack of gradient support
Q8. What solution is suggested for preserving past product pricing in orders?
A. Enter price directly into a price table
B. Avoid storing product prices in the database
C. Use VBA to copy product and details to order details
D. Automatically update past orders with new prices
Q9. What is Richard Rost's recommendation regarding the use of Office Online for offline work?
A. Enable offline mode in settings
B. Use Google Docs as a replacement
C. Download Microsoft Office desktop version
D. Wait for Microsoft to add offline capabilities
Q10. What was noted about the offline capabilities of Google Docs compared to Microsoft Office Online?
A. Neither offers offline capabilities
B. Both have good offline support
C. Google Docs can be set up for offline use
D. Google Docs lacks certain online features
Answers: 1-B; 2-C; 3-B; 4-C; 5-B; 6-D; 7-C; 8-C; 9-C; 10-C
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 In today's Quick Queries video from AccessLearningZone.com, we explore a variety of questions and solutions related to Microsoft Access. I'm your instructor, Richard Rost, and today's session, number 27, brings together interesting queries from our forums and YouTube channel that don't necessitate a full video but still offer valuable insights.
A recent query from Todd, one of our Platinum members, involved an issue with the Search As You Type tutorial. The form errors out when the search box is empty. Kevin proposed a smart solution by using an error handler to manage this. Instead of checking for content in the search box, he suggests redirecting to an error handler whenever an error occurs, specifically error number 2185. In such cases, resetting to a basic SQL statement to display all records can be a simple fix. It's a clever approach to managing unexpected errors without extra complexities. Thanks, Kevin, for this helpful contribution.
We also received feedback about the Distribute Text tutorial. NinjaGarrow pointed out a trick involving invisible text with white characters to distribute text across lines in a textbox. BJones9942 suggested using a Control Shift Space in Word for a non-breaking space, although it doesn't work in Access. This Word tip prevents word wrapping by maintaining space continuity. Appreciate the tip, BJones.
A common problem in my invoicing video relates to runtime error 3021, encountered when adding new products to blank orders. Sean discovered that this issue arises from attempting to add records in a subform before having a record in the parent table. This leads to orphaned records. I've previously covered how to solve this using a bit of code to ensure related records exist in both tables.
MegaEldragoDestroyer inquired about handling inventory by decrementing the product Quantity when added to the order form. This involves inventory management, ensuring the Quantity On Hand is updated when a product is shipped. I have various videos on this topic available on my site.
Another user mentioned issues with activating the event procedure instead of an embedded macro. I address this in detail in a video showing how to set your database to favor event procedures. This is crucial if you're utilizing VBA programming, which I recommend over the Macro Builder and Expression Builder.
JunkerYorg raised a question about the absence of data in the OrderDetailF after being dragged onto the order form. Usually, this stems from incorrect Link Master and Child Fields settings. Ensuring these fields correctly link to the tables is essential. If issues persist, I recommend running through a troubleshooting checklist to resolve potential database corruption.
Hannah asked about changing the report's background color. Access imposes limitations here, such as unchangeable margins, meaning you might not achieve full-page color. However, exporting to PDF or Word allows more flexibility with background colors, though printers will still show white margins.
User Fafafan1000 wanted to know if relationships between tables could be bypassed. I explained that proper database organisation necessitates relationships between orders and their details. Otherwise, data integrity issues arise.
Delayne inquired about adjusting product prices without affecting historical order records. This requires capturing product details at the time of order entry, ensuring subsequent changes do not alter past orders. This involves a bit of VBA, which I demonstrate in the extended cut of the invoicing video for members.
Finally, a query from last week's Quick Queries asked if the online version of Microsoft Office could be used offline. Unfortunately, it cannot - a desktop version is necessary for offline access. Google Docs offers offline capabilities with setup, which I plan to cover in a future video.
This concludes today's Quick Queries session. I hope you found it informative. You can find a complete video tutorial with detailed step-by-step instructions on these topics on my website at the link below. Live long and prosper, my friends.Topic List Error handling with error number 2185
Distributing text in a long text box
Non-breaking spaces in Access vs Word
Runtime error 3021 and orphaned records
Inventory handling and quantity updates
Using event procedures in VBA
Link Master and Child Fields setup
Report background color limitations in Access
Exporting Access reports to Word or PDF
Importance of relationships between tables
Adjusting product pricing without affecting old records
Difference between Office Online and desktop version
Offline capability of Google Docs vs Office Online |
