Today's Quick Queries number 35. What's a Quick Query? Well, it's a video that contains questions and answers about a lot of other little things that maybe don't go into their own video. So I put them all together into a Quick Queries video. Let's take a look at the first one for today.

We're going to start off with a question from Kenneth in Clemonds, New York. How far from where I used to live in Buffalo? One of my silver members, Kenneth, says, do you suggest limiting the amount of data that I would add to a combo box? How many selections in a combo box are too many?

This actually depends on a lot of factors. Are you using the database locally, in other words, just on your computer? Are you the only user? Are you using it on a network, a local area network in your office? Are you using it over the internet, for example? If you've got your back end in an SQL server, because how and where you're using it really depends, it really changes the answer to this question.

If you're just the only user, it's a database on your machine, yeah, you could have a few thousand items in there without a problem because it doesn't have to pull it across a network. If you're working across a local area network and you've built a database that you're sharing with a bunch of people, you've got a couple thousand items in there. It may take a few seconds for that combo box to load up all its values. If it's running online, we've seen Sammy and I did some tests, and loading a couple of combo boxes, even with a few dozen items, can really slow things down. Yes, in that case, I would try to limit the amount of data in the combo box.

Plus, to be honest, if you've got more than a thousand items, who wants to scroll through that many items? You can start typing in the first couple of characters, and it'll bring you down there, but it just becomes unruly after you get more than, I'd say, a few hundred items in it.

What can you do? There's a couple of things you could do. You could filter that combo box, or if it's something like a product that falls into product categories, you could set up what I call cascading combo boxes. This is where you've got multiple levels of combo boxes. For example, you've got the state, and then when you pick the state, that will filter the next box for a city, so you only see cities from that state. The same thing works with products and product categories. You pick the category first, like motherboards or hard drives, and then the product combo box will filter down to just those items.

I even did this video lately, which is a little more complicated. This is more of a developer-level video where you can do multiple cascading combo boxes. You've got country, state, county, city, street. You can drill straight down through five of them. That was a little more complex. This video, again, a little bit more complicated. It's developer-level, but this one will show you how to filter the results in the combo box based on typing, and you can type in characters that appear anywhere, not just the beginning. The way that a combo box works by default is you have to type in the beginning of the data that's in here. With this video, I'll show you how you can type in anything. If I typed in WIL, William Riker would show up.

Check out these videos. They're a little more complicated. They require a little bit of VBA, but even the beginner one does. The first cascading combo box one does. It requires just a little tiny bit of VBA because you have to automate this. You have to fire an event once the first combo box with a filter is set. But that's my recommendation. Again, how many items in the combo box? That's up to you. Whatever you find usable as far as the number of items in there and what's working for you on your setup.

Hope this answers your question. Anders said just a small detail. When I'm faced with unfamiliar code, I often see superfluous comments. In fact, they don't help me at all. No comments are needed when the code speaks for itself. Good code comments explain why things are done, but only if it's not obvious, never what is done.

I used to agree with you 100%. I used to never comment my code because I figured I'm the only one who works on my projects. I don't work with teams. I don't share my code with anybody else. So I figured, why do I need comments? About 10 years ago, I decided to update a database that I hadn't worked on in a while. I'm like, what was I thinking here? I didn't quite understand what I had done. Now I use comments. So future me can understand what past me did. Even if the code is obvious, sometimes a block of code doesn't necessarily read as easily as a line that describes what it does.

Plus honestly, if you're looking at a bunch of code, especially if you're looking for errors and stuff, your brain sometimes tends to get code blindness. Not only is this green, and it shows up easier to see than the rest of the text, but it's a nice way to not have to think, oh, okay, this allows the user to pause execution. While I kind of agree with you, I kind of don't. I do like putting comments. Now, I never used to when I was younger, but now I comment a lot. I got in the habit because of teaching and trying to figure out stuff that I had written 10 years ago. I'm trying to do myself a favor 10 years from now. I like comments, but I can appreciate it if you don't.

A couple of comments on my reset database video from a couple of days ago, which teaches you how to blank all the data out of it and reset all the auto numbers. Shadow Dragon says, I've gotten in the habit of adding a subroutine to the closed database button that asks the users if they want to back up the database, runs VBA code and all that stuff. It protects me during the development process.

I tend to not put the onus of backing up the data on the users. Literally, they're just getting a front-end file with no data in it, or just temporary data for processing on their machine. All of the important data resides on the server and the back-end. I think that's the administrator's job. The database developer, you, that's your responsibility to make sure that's backed up, preferably with an automated, nightly routine. If you've got individual users with their own databases with their own data in it, then that's a good idea.

Richard from Concord says, what is this database that everyone wants? Now people need answers. Texsome says, great video. Thank you. I would still like to know how to copy a record and copy the auto number. This seems impossible. Again, auto numbers are really not for you. You really shouldn't have to worry about them. But it does occasionally happen where you might accidentally delete a record. Let's say you delete an order number or you delete the order record, but there are still child order or detail records. You want to replace that order. It is possible. There is a trick. You can do it with an append query. I've got a whole separate video on exactly how to do this. I don't recommend it, but if you need to do it because you messed something up, then you can do this.

Don't rely on those auto numbers. They're only used for relationships and making sure that records are unique. You shouldn't really care what they are. It could be six. It could be six thousand five hundred and twenty-two. If you do delete one and you need to replace it, there is a trick.

Rem 1989 says, so using auto numbers as a quote number is not the way to go? I recommend against it. I recommend using your own quote number, your own product number, your own invoice number, your own customer number. I don't usually recommend letting the customers see the auto numbers. I mean, you can if you want to. That's the way that I teach my beginner lessons, just to keep things simple. As you get a little more advanced and you want to build a more professional system, it's a good idea not to let people see those numbers.

The main reason why is because if you've got your customer database, and you print the customer number on your invoices, and you're using an auto number, and the customer gets the number four. Six months go by, and someone they know gets the customer number seven. Now they know that you've only added three customers in those six months or order numbers. The same customer places an order. He's got order number thirteen, and two months later, he's got order number seventeen. It kind of gives things away.

It reminds me of the German tank problem. During World War II, the allies would capture German tanks that weren't completely destroyed, and they would be able to see the serial numbers on some of the parts. Based on that, they could roughly tell how many tanks the Germans were making because they could see what serial numbers were available and what were missing.

It's the same kind of thing in business. You don't want your customers to know too much about your business. I talk about this in detail in this video. Auto numbers good or bad. Why you should use an auto number as a primary key in your table, but you don't want to necessarily make it visible to customers.

Here's another very popular video. Auto numbers and how they're not for you. You shouldn't care about what they are because they're only meaningful inside the database for tracking relationships and making sure records are unique. On both of these pages, there are links to other videos that teach you some different solutions for what you can do for customer-facing data.

For example, I have a video on how to create your own customer code so they can't see the ID. Here's one on sequential numbering. What I recommend you do for your customers, for example, for invoice numbers, is give them sequential invoice numbers. Each customer would have invoice 1001, 1002, 1003. Every customer would have their own unique one. You can match that with their customer code that you create. Your customer might be customer ID 565. That's just whatever number you assign to them. So their invoice number would be 5651001, 5651002, and so on. That's a more professional numbering system. These videos will explain how to do all of that stuff.

Finally, today, Troopers Travel says there's only one cap to the enterprise, and it is Picard. Hey, those are fighting words. I love Picard. I grew up with Picard mostly because I remember watching the original Star Trek when I was a kid, but I didn't quite understand it. I was born in 72, so I watched reruns. Next Generation came on in 87, so I'd have been 13-ish. Picard was awesome. The special effects were amazing at the time. I do love Kirk and Spock and Bull. I mostly love the original episodes because of the triforce. That Kirk-Spock-McCoy dynamic. That's what made that show special. But Next Generation is where it's at, man. Sorry, I'll fight you on that one.

All right, folks, that's going to do it for today's quick queries. Hope you learned something. Live long and prosper, my friends. I'll see you next time.

TOPICS:
Limitations of combo box data size  
Impact of network type on combo box performance  
Need for combo box data filtering  
Use of cascading combo boxes  
Creating multiple levels of cascading combo boxes  
Typing anywhere in combo box for filtering  
Role of VBA in combo box automation  
Importance of code comments  
Resetting database using auto numbers  
Backup responsibility of database administrator  
Using append query to recover deleted records  
Risks of using auto numbers as public identifiers  
Creating customer codes and sequential numbering

COMMERCIAL:
In today's Quick Queries video, we're discussing some interesting questions from viewers. First, we'll explore the best practices for limiting data in combo boxes—what's too much and how to manage it efficiently. We'll also touch on the value of comments in code, even if they seem redundant at times. Dive into the importance of not using auto numbers for public-facing data and why creating custom identifiers can protect your business insights. Plus, catch a fun debate on the captains of Star Trek. Whether you're new or advanced, there's something here for everyone. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper, my friends.