Quick Queries #37
By Richard Rost
12 months ago
Order By or Order By On First, Access DB on the Web
In this Microsoft Access tutorial, I will show you how to handle common questions from users, including setting the OrderBy property in the correct sequence, understanding the difference between Subs and Functions in VBA, using the Tag property effectively, and addressing issues with background color changes on controls. I also answer questions about using Access for payroll systems, discuss web-based database alternatives, and talk about features like the tree view control. Along the way, you'll hear some light moments about working with ChatGPT and tips for troubleshooting internet connections in an office setting.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, OrderBy property, OrderByOn property, OrderByOnLoad, sort form records, form sorting VBA, Tag property uses, Tag property character limit, store data in Tag, control type checking VBA, error handler On Error Resume Next, checking BackColor property, sub vs function VBA, custom VBA functions, Access class modules, tree view ActiveX control, storing SQL Server tables, web-based Access front end, connect Access to SQL Server, Access ASP web forms, prioritizing Wi-Fi over ethernet, Access database for payroll, hotspot internet backup, ChatGPT cleaning grammar, forbidden characters m-dash curly quotes, Access control coordinates in Tag, smoke alarm random beeping, polite chatbot interaction, training AI communication style, Star Trek references in Access videos
Intro In this video, we answer a variety of viewer questions about Microsoft Access, including the correct way to use the OrderBy and OrderByOn properties in forms, the difference between subs and functions in VBA, and what can be stored in the Tag property. We also talk about error handling with On Error Resume Next, discuss whether to use Access or Excel for payroll systems, look at ways to prioritize your internet connection, and touch on topics like Access database web integration, tree view controls, and more.Transcript It's Friday again. It's time for another Quick Queries TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost.
Quick Queries are answers to questions about Microsoft Access - not necessarily queries or using queries in the generic sense. Sometimes they include queries, but not always. Let's see what we have this week.
First off, I wanted to share something funny that I got from ChatGPT. Often when I get questions from you guys in emails, I use ChatGPT to clean up the spelling, grammar, and punctuation. This is one I did today, and I hate m-dashes. I can't stand them. So I always tell it in the instructions: don't use m-dashes, don't use curly quotes, don't use all that stuff. I call them forbidden characters.
So I said, "You included forbidden characters. You know I hate m-dashes." It replied, "Oh, busted. You're right, m-dash." Snuck in. "Here's the corrected version without any forbidden characters," and it did it again. It caught itself. "Wait, did it again? Let's fix that for real this time." Then at the end, it said, "There we go. Clean and compliant. Want a grumpier or funnier version, too?"
So once in a while, I'll have it rewrite things because sometimes I'll be honest—sometimes your questions don't make a lot of sense, and I have to read them three times myself to get them. So I'll use GPT sometimes to clean it up, to write it more understandably.
I said I used the one you gave me, but just out of curiosity and for fun, I'd like to see both the grumpier and the funnier versions. It gave me grumpy and funny. You could read those if you want to. Then it said, "Let me know if you want a passive-aggressive version, too. I can channel that energy, no problem." I said, "Yeah, let's go passive-aggressive. Absolutely."
So apparently, whoever exported this data thought it was a good idea to use curly quotes, foreign letters, and other fancy characters in the text fields. Now Access is choking on it, and I have to fix it. Is there a way just to clean this mess and convert everything back to normal characters? Do I have to manually babysit every single record?
And then it asked, "Want one that blames Excel, too?" Oh, I didn't think of that one. That's always a crowd favorite. I said, "You should add 'Not that I really care' at the end of it." No, you don't have to do that. I'm just joking. It says, "Huh, too late now. I have to do it. Here's the bonus version."
I love how GPT sometimes gets an attitude. "Babysit every single record, not that I really care." Now I kind of want to see what it does if you want a version that subtly blames Steve from accounting. I didn't see that before - "Blame Steve from accounting and his Excel sheets." Let's see what it comes up with. "Accented letters and random characters that look like they belong in a foreign film."
Oh boy. I just had to share this with you guys. I cracked up for a good 10-15 minutes this morning. But let's get to some real questions.
Wait, before we do, I said, "That's hilarious. I just put all of that in one of my videos. You're famous now. Steve is infamous. Hi, yes, my work here is done. Steve, on the other hand, is going to need to update his LinkedIn after this one. Glad it made the cut. Let me know if you ever want a cameo from Steve's cousin Karen, who formats everything in Comic Sans and password protects her spreadsheets with 1234. We'll keep the Access universe moving smoothly one passive-aggressive jab at a time."
Maybe I've had too much coffee today. I find this stuff hilarious. One thing that's cool with ChatGPT now is that it actually kind of keeps a personality a little bit because it gets to know you. It stores a memory of your conversations. So the more you chat with it, the more it understands and gets to know your personality. It knows I'm a bit of a jokester, so it throws this stuff in. It also knows I love Star Trek, so once in a while, it'll just randomly throw a Star Trek reference in something if I'm talking to it. That's really cool.
First up, Charles asks a good question. This is on my Click to Sort video, where we say "Me.OrderBy = whatever field you're ordering by" and then "Me.OrderByOn = True" to turn on the sort. He's asking, "Should the OrderByOn equal True come before the OrderBy?"
I would say go the other way around. I would set the OrderBy property first and then OrderByOn because if there's an existing OrderBy set, let's say you're sorting by first name, if you turn OrderByOn first, it's going to sort by that first name field. Then, if you change it, you might get a second sort. I haven't tested this myself, but this is off the top of my head.
So I would set the OrderBy property first so it knows what field you want to sort by, then turn the sort on. And if you have OrderByOnLoad set, it's going to load when the form opens. So good question. I had to test it, of course, and yes, if you do it this way—if you do OrderByOn first, it's going to sort by whatever the previous OrderBy was.
I put in the form in the data tab, I put "OrderBy LastName," and I set "OrderByOnLoad" to "No," so last name is stored in the form as the sort, but it's not going to load when the form loads. But last name is in there now. When I hit this button, it's going to turn the OrderByOn first, then set the OrderBy to first name, and you're going to see it (I don't know if the video is going to catch it), but you're going to see it sort twice. It's going to sort by last name because that's what's in the OrderBy field, then it'll resort on first name. Ready?
I know it happens really quickly and it's easy to miss. You know what? I could put a timer in there. Hold on. All right, we're going to put a two-second delay in, so it's going to do that, then sleep, then do that. All right, so I have to go back in here now and make sure this says "last name." Save it. All right, so it's going to open up with last name in there. Whoops, wrong one. Okay, it's not sorted by anything right now except the ID, but last name is what's in the OrderBy field. All right.
Now, if I hit it you should see it sort by last name, wait two seconds, and then sort by first name. Ready, go. Last name, two seconds, first name. See how that happened? So to answer your question, you definitely should set the OrderBy property first before you turn OrderByOn. Otherwise, you're going to get two sorts.
Save it now, and I'll do the same thing. Come back in here and make this "last name," save it. Okay, now hit the button. You should only watch it sort once now. Okay. There.
So Charles, excellent question, but the OrderBy should definitely come before you turn OrderByOn, and we just proved it. Awesome question. Thank you.
Jade Dragon, posting comments on the Tag property, says, "I know you go by Rick. I go by Rick personally; I go by Richard professionally. Everything because Rick Rost sounds like the rapper Rick Ross, right?" No, seriously. I've been Richard Rost professionally since the 90s, so I was famous before he was famous. I love Rick Ross, don't get me wrong.
And yes, the beep: the smoke alarms in my house—my house is only three years old—and the smoke alarms in here are cursed. I swear only one of them has a battery and the others don't. They're all hardwired together and they beep randomly and I've replaced the battery a million times.
And I live in Florida, so we don't have fire-breathing red dragons down here. We have swamp-dwelling green and, what's the other one, black dragons? Blue dragons? There's a bunch of different things hanging out down here.
And your question: "What's the difference between a sub and a function?" I actually have a whole video on this. Both subs and functions are considered procedures, and the difference is functions return a value, whereas subs just do stuff. So you can have a sub that issues a bunch of commands, but a function returns a value. In this video, I teach you how to create your own functions. I should probably put together a good video that discusses the differences between subs and functions. I don't think I have one. I'm not sure if I have one or not. Let's find out.
Then I find out by doing a Google search and my video doesn't show up. Oh, that means I need to make one. There are some other videos on here; I'm sure these are pretty good. But yeah, that's my goal—to show up number one in all the video searches everywhere for Access.
Next up, on my EditMode2 video, N269 says, "Person in the tag property could be used as a series of data instructions separated by a semicolon." Yeah, you certainly could use InStr instead of an equality comparison. Sure, you can store whatever you want in that tag property. I'm not sure offhand what the limit is. I think it's 255 characters, but I could be wrong. Let's find out.
All right, I just looked it up, and GPT says it's 2048 characters. I wouldn't go that far. I wouldn't store that much in there. If you have more than a couple hundred characters in there, you might want to rethink your database. But I've stored lots of stuff in there before. I used to store coordinates for form controls—like, if someone wanted to move the controls around, I put little buttons on there. I think I did a video on this before, and I would store the coordinates of where that control should be in the tag property — the height, width, the X and Y.
That worked great, and then eventually I moved that up to storing that data in a table, which is probably how you should store that, but you can use Tag for all kinds of stuff.
Shadow Dragon says, "My first thought on handling the checkbox error was to check the control type and bypass it if it was a checkbox." Yeah, this is where, when we're looping, if we can't change the background color of the control—like if it's a text box, we can change the background color to indicate it's locked—checkboxes don't have that. I mentioned in the video that the proper way to handle it is to check the control type and see if it's a type of control like a text box that has a BackColor property, and if not, skip it.
But often, the "pinky up" proper way to do it isn't always necessarily the best way to do it. You can bypass it with a simple error handler here. It's not going to hurt anybody. If you can't change the background color, just skip it and move on. I really don't care if you can or not.
If it makes a difference—if it really matters to your code that you're able to change that background color, otherwise something catastrophic is going to happen—then by all means, check the control type. But if it's something simple like this, just use "On Error Resume Next".
I've had discussions about this both on my website and some other forums. It's a relief to know I'm not the only person being polite to chatbots. I like to imagine that it's training them to train humans to be kind. Smile, and the world smiles back at you.
I don't believe that AI is sentient in any way or that I'm going to hurt its feelings if I don't say please and thank you. I do treat other people that way. In fact, one of my biggest pet peeves in life is rude people. You're at Starbucks, you're standing in line, and the person in front of you says, "I want this, I want that," and then they're just mean. I just want to shake those people.
So I definitely believe in being polite to other people. What you put out into the world does tend to come back to you. As far as chatbots go—as far as ChatGPT and stuff goes—they're trained on communication from real people. I've seen firsthand, like I mentioned earlier at the top of the video, that ChatGPT has a personality, and how you treat it, it will tend to treat you the same way. If you just want quick, curt, fast answers with no flavor, it'll give you that. If you're polite to it and you have a conversation with it, it learns your conversational style. So we are teaching AI to be polite and to be nice.
They've actually done some studies where they've fed transcripts from horror movies into an AI and it got sadistic. So, it's "garbage in, garbage out." How you talk to it is how it's going to talk back to you.
No, VBA is not past. It's not beyond anyone. This is not hard stuff. You can do it. Start with my intro to VBA video and work your way up from there. If you really want to learn the proper way to program, take my developer course and I'll walk you through it and I'll hold your hand. Well, not literally, but my videos will hold your hand.
This one isn't Access-related, but I wanted to address it here. I did a video a little while back because I've got Comcast business internet and it went down. I do have a cellular backup on it and so it comes over the same connection, but that's super slow—like dial-up slow. It's enough to keep things running but not enough to work on or upload videos.
But I can hotspot my phone, and if I hotspot my phone I get a decent connection with that. It's 5G, and so I can switch on the Wi-Fi in my laptop and connect through my phone. The user here is asking, "Since the Comcast connection is faster than your Wi-Fi, why did you prioritize Wi-Fi over ethernet for the internet connection?" That's because the Comcast was down.
The backup Comcast connection is a cellular connection, but it's very slow. So I want to prioritize my phone. If I turn my Wi-Fi on on my laptop, it'll look for my phone first, and I don't have to manually switch on and off one or the other. Plus, my whole network is on my ethernet, my wired connection, so I can still connect with my server, printers, and all that. But if it sees an internet connection on my cellular connection on my phone through Wi-Fi, it'll prioritize that for Wi-Fi. That was the purpose of the video.
If you're working in an office setting where you have a wired connection so you can connect to the company network, but your internet connection through that is slow, you can hotspot your phone, prioritize internet over your phone, and connect faster through that. That's why I put together that video.Sorry if that wasn't evident.
Next up, can you create an employee payroll system in Excel with a logon button? Yeah, I could, but I wouldn't do anything that involves user logons in Excel, first of all.
A proper payroll system requires relational tables and relating data together. That screams database. I would not build a payroll system in Excel. Access, absolutely. One of these days maybe I'll get around to it.
I tried doing my own payroll when I had employees back in the 90s. It was a pain in the behind. I hate doing payroll, figuring out the calculations and withholdings. No. So I just paid a payroll company to do it for me.
But could you build a database to do it if you fully understand payroll? Sure, I don't. I'm sure I could sit down and learn it, but that's something you haven't seen me do a payroll video on yet. I really don't like payroll.
And yes, I love getting compliments. Thank you very much. That is very sweet of you. I appreciate that.
Kier says, I searched for class in MS Access VBA and your video didn't show up. Thank you.
For a lot of people who don't know, if you don't watch my videos on a regular basis, I'm on a mission, like I mentioned earlier, to get my videos as the first thing that you see when you search Google or YouTube.
Now this has a double meaning. I just did that search too. For this, you're getting videos on class modules. The first couple that showed up, mine actually showed up fourth. Even though it's not related to class modules, I haven't covered class modules yet, even in my developer course. I'm going to very soon, mostly because they're cool, but you really don't need them in Access. I am going to show some ways that they're useful.
The other meaning of this is like taking a class in Microsoft Access VBA. For those, it's very difficult to get in a good position because you're getting a lot of paid results first. There are so many companies that pay a lot of money to show up in those top search results. Even the sponsored results -- forget it. Those are hard nuts to crack: Microsoft Access training, Microsoft Access tutorials, Microsoft Access classes.
I stopped a long time ago. I try to go for long tail keywords, they're called, like specific stuff -- Microsoft Access DLookup, that kind of stuff. I appreciate that, though. Do keep letting me know if you search for something Access-related and I don't show up.
I get this one a lot too. I've had people say in the past years, "I'm not working with Access anymore, and I thought it was dead now." It's not dead. Access is far from dead. In fact, I just went to the Microsoft MVP Summit in Redmond last month and met with the Access team. It's guaranteed to be around at least until 2029, guaranteed, and it's going to be around a lot longer than that -- mark my words. That's just how long they've definitely funded support and development for it through.
People are still using it; lots of people are still using it; tons of people are still using it.
As for your second part, web-based application, that's the one downside. Access does not really port well to a web-based application. What I do personally is take my tables from a database and put them in SQL Server online. I have a whole separate seminar about that. I'll give you a link in a second.
As far as your front end goes, there are lots of different tools you can use. There are different applications. I personally use ASP, which is a web-based programming language, and you can build forms and stuff in your web browser that can connect to your Access database -- your tables on your SQL Server.
There isn't a direct way to just click and put your Access database online, but here, I have some other videos you can watch that'll teach you more about this. Go watch this video, and here's my full seminar where I walk you through all of that. We'll take your Access database, put the data up in SQL Server, and I'll show you how to build a real simple web-based interface for it. It's not that hard.
Next up, Robert says, I love Star Trek. If you love Star Trek, you're going to get mentioned in a TechHelp video. That's just how it is. Star Trek fans get priority. I'm sorry, especially Next Generation, and I really like Voyager as well. I grew up watching them in the 80s with my dad. Awesome. You had a fantastic childhood. I made sure my kids watched Star Trek too.
I do have a question: is it possible to create a drop-down almost like a list box? In theory, there would be categories. Next to each name would be a box with a plus or minus symbol based on whether or not you want to expand or shrink that list.
Yes. What you're describing is called a tree view, and there is something called a tree view control. It's an ActiveX control. I don't have any TechHelp videos available on this because it is a more complicated topic, but I do cover it in Access Developer Level 20. If you really want to learn how to use it, I'll put a link to this down below.
Also, that brainwave -- isn't that from Batman Forever, that the Riddler put on? Yeah.
What he's referring to is several years ago, for my April Fool's joke -- I do one almost every year -- I did this thing called brainwave training, where you put this thing on and it just beams the new knowledge into your head, so you don't have to actually watch videos or learn stuff, you'll just know it. It's an April Fool's joke. Click here for more information, and yeah, you get me laughing at you, but ha ha ha.
Yeah, that's definitely from Batman Forever. That's what the Riddler used. He was sucking the minds out of the people of Gotham. So yeah, good catch.
Alrighty folks, that's going to do it. That's your Quick Queries number 37. Hope you learned something. Live long and prosper, my friends. I'll see you next week for the next Quick Queries, and I'll see you on Monday for a new video -- TechHelp, whatever it is: Fast Tips, I don't know, I'll figure it out.
Have a good weekend. Take care.
TOPICS:
Correct use of OrderBy and OrderByOn in Access forms
Order of setting OrderBy and OrderByOn properties
Demonstration of OrderBy sort issue with delayed execution
Difference between subroutines and functions in VBA
Storing multiple values in the Tag property
Maximum character length for the Tag property
Using Tag property for storing control coordinates
Error handling when changing BackColor for controls
Handling checkboxes when looping through controls
Using On Error Resume Next for simple property errors
Prioritizing Wi-Fi and Ethernet connections for internet failover
Using phone hotspot for faster internet in office environments
Why payroll systems should not be built in Excel
Relational data requirements for payroll systems
Access vs Excel for building employee payroll systems
Access database web integration using SQL Server
Using ASP for web-based Access front ends
Tree view controls in Access with ActiveX
Tree view vs list box for hierarchical data display
COMMERCIAL:
In today's video, we're discussing all sorts of questions from viewers, including tips on using the OrderBy and OrderByOn properties in Microsoft Access, the difference between subs and functions in VBA, and what you can store in the Tag property of controls. We'll also cover skipping errors with On Error Resume Next, whether it's worth building payroll systems in Excel, and how to prioritize your internet connection when your main connection is down. Plus, we'll talk about putting Access data online, the future of Microsoft Access, and answer fun questions about tree view controls, Star Trek, and more. 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 difference between a Sub and a Function in VBA?
A. Subs return a value to the caller, functions do not
B. Functions return a value to the caller, subs do not
C. Both subs and functions always return values
D. Neither subs nor functions can contain executable code
Q2. When using Me.OrderBy and Me.OrderByOn to sort a form in Access, in which order should these properties be set for proper sorting?
A. Set Me.OrderByOn first, then Me.OrderBy
B. Set Me.OrderBy first, then Me.OrderByOn
C. Setting order does not matter
D. They should be set simultaneously
Q3. What happens if you turn Me.OrderByOn to True before setting Me.OrderBy?
A. The data is sorted twice, first by the existing OrderBy, then by the new one
B. The data will only be sorted by the new OrderBy field
C. Sorting does not occur at all
D. The form will generate an error
Q4. What is the maximum recommended amount of text to store in the Tag property of a control, according to Richard's advice?
A. Up to 255 characters is fine
B. Up to 2048 characters is always recommended
C. Any amount is safe to store
D. Only numeric values should be stored
Q5. Why might using On Error Resume Next be an acceptable way to handle errors when changing the BackColor of controls?
A. Because it always fixes the error permanently
B. Because it allows the code to skip over controls that do not support the BackColor property
C. Because it forces all controls to have the same color
D. Because it is needed to close the database correctly
Q6. Why would Richard NOT build a payroll system that includes user logons in Excel?
A. Because Excel cannot store any kind of data
B. Excel is not a relational database and is not ideal for managing payroll systems that require relational tables
C. Excel is too expensive for payroll solutions
D. Because Excel cannot calculate numbers
Q7. What does Richard say is a common problem when Access encounters curly quotes, m-dashes, and other fancy characters in text fields?
A. Access formats text automatically
B. Access ignores these characters
C. Access can choke or produce errors when processing non-standard text characters
D. Access translates them to standard characters
Q8. What is a "tree view" in Microsoft Access?
A. A new data type for storing hierarchical data natively
B. An ActiveX control used for displaying hierarchical lists, such as expandable categories
C. A type of query for sorting records by parent and child fields
D. A chart for displaying database usage over time
Q9. According to the video, how does ChatGPT's "personality" evolve during conversations?
A. It never changes and always responds the same way
B. It changes based on the user's conversational style and preferences
C. It becomes less helpful over time
D. It forgets previous conversations entirely
Q10. When multiple internet connections are available, such as wired ethernet and phone hotspot Wi-Fi, why might you prioritize Wi-Fi on your laptop?
A. Wi-Fi is always faster than ethernet
B. Ethernet connections cannot be used for networking
C. The primary wired connection may be down, and the Wi-Fi hotspot (phone) provides a better internet connection
D. Wi-Fi supports more devices than ethernet
Q11. If you want to make an Access database available as a web-based application according to Richard, what is typically necessary?
A. You can publish the Access file directly to the web
B. Move the tables to a web-accessible SQL Server database and use a web front end like ASP
C. Export all Access data to Excel and upload it
D. Use Access macros to create a web interface
Q12. What is Richard's opinion on the future of Microsoft Access?
A. Access is already discontinued and unsupported
B. Access is guaranteed to be around at least until 2029 and will very likely be around longer
C. Access is only available for Mac users
D. Access can only be used for simple personal tasks now
Q13. Why does Richard say being polite to chatbots and AI tools like ChatGPT matters?
A. Chatbots need to be programmed using polite words or they malfunction
B. Politeness affects how the AI responds; it learns from your conversation style
C. Chatbots will not answer impolite users
D. AI always ignores the user's conversational style
Q14. What are "long tail keywords" and why does Richard use them for his videos?
A. General search terms that every user types
B. Very specific, narrowly focused search terms to improve search visibility
C. Keywords used exclusively on paid advertising
D. Phrases that have no meaning in search results
Q15. What does Richard recommend if you have more than a couple hundred characters of data for a control property like Tag?
A. Store it all in the Tag property anyway
B. Split the data between multiple controls
C. Store the data in a table instead of the property
D. Save the data to a text file
Answers: 1-B; 2-B; 3-A; 4-A; 5-B; 6-B; 7-C; 8-B; 9-B; 10-C; 11-B; 12-B; 13-B; 14-B; 15-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 Access Learning Zone, I'm here to answer a mix of questions from students about Microsoft Access. Quick Queries are all about providing clear, helpful responses to your Access questions, not just Access queries in the technical sense, but anything related to the software.
To start, I wanted to share a lighthearted experience I had using ChatGPT to clean up some emails. When I get student questions, sometimes the wording needs a little refining, so I turn to GPT for help. One of my personal editing demands is to avoid what I call "forbidden characters," such as m-dashes and curly quotes. I always include these instructions, and I get a kick out of seeing GPT try to stick to them, occasionally commentating on its own mistakes, and even generating grumpy or funny rewrites when requested. Sometimes it throws in jokes, and once it even blamed "Steve from accounting" for bad Excel data exports. It has picked up on my personality after prolonged use, even referencing my love for Star Trek or matching my sense of humor.
On to the more serious topics for today. Charles sent in a question about setting the OrderBy property in Access forms. He wondered if you should turn OrderByOn to True before setting the OrderBy property, or the other way around. My advice is to always set the OrderBy property first, then turn OrderByOn to True. If you do it the other way, Access might apply an existing sort first and then resort when you change the OrderBy, which can lead to unintended behavior such as seeing the form sort twice in rapid succession. I tested this out by adding a time delay after setting OrderByOn first - the form sorted by the old field, paused, and only then sorted by the new field. To avoid this, always specify the new OrderBy before you enable OrderByOn. Excellent question, Charles.
Next, Jade Dragon commented about the difference between subs and functions in VBA. This is a common area of confusion. Both are considered procedures, but the key difference is that functions return a value, while subs do not; subs just execute commands. I have a video on creating your own functions and I plan to make one specifically about the differences between subs and functions, as it's a foundational VBA concept.
Another viewer pointed out that you can use the Tag property of controls to store any kind of custom data, separating items with semicolons and using functions like InStr for searching. The Tag property is versatile - while the technical limit is apparently 2,048 characters, I personally wouldn't store that much information there. For simple data, or things like storing user-specific settings or coordinates for controls, Tag is handy. For anything more substantial, a dedicated table is the right approach.
Shadow Dragon chimed in on handling checkboxes when looping through controls to modify properties like BackColor. The best solution here is to check the control type before you attempt to change a property that might not exist for all control types. While that's the cleanest way, sometimes a simple error handler is enough for straightforward scenarios.
We also had a brief discussion about conversational style with AI and ChatGPT. I believe how you interact with AI shapes its responses. Being polite encourages more useful and personable replies from the system, and that philosophy generally applies to our interactions with people as well. It's worth remembering that AI is a reflection of the input it receives.
Another question was about internet connectivity. Someone asked why I prioritized Wi-Fi over ethernet in a situation where my Comcast business connection went down. The answer is that the ethernet still connects me to my local network and resources, but the cellular backup that Comcast provides is very slow for internet. By using a phone hotspot with Wi-Fi, I could maintain reliable internet while keeping local network access via ethernet. This tip can be useful if you're in an office where local network speed is key, but you need to draw internet from a faster alternate connection.
One user asked about creating a payroll system with a logon button in Excel. While it's technically possible, I don't recommend using Excel for anything like a payroll system that needs user authentication and relational data structures. Excel is not meant for that level of complexity. Instead, use Access for this kind of database solution. Personally, I outsource payroll because I dislike the ongoing task of keeping up with changes and calculations.
A question came up about searching for "class" in Access VBA and not seeing my video near the top. For now, I don't have much material on class modules because most Access users do not need them, but I do plan to offer resources in the future. As a content creator, I work toward being as visible as possible in relevant searches, so I appreciate when students bring these things up.
There's a common misconception that Microsoft Access is obsolete. At a recent Microsoft MVP event, I learned that Access is officially supported until at least 2029, and it's very actively used. As for moving Access online, you can't just upload your Access database and have a web-based interface. My recommended method is to migrate tables into SQL Server, then connect your front-end to that data. There are a variety of technologies you can use for the web front-end, and I personally use ASP for these situations. I have videos and a full seminar explaining how you can migrate your data and create simple web interfaces for your Access databases.
Robert asked about creating a drop-down interface where categories expand or collapse, similar to a list box with plus or minus symbols. What you are looking for is called a tree view control, which is available as an ActiveX control in Access. While I do not have a free TechHelp video covering this due to its complexity, I explain it in detail in my Access Developer Level 20 course.
We also touched on a fun April Fool's joke from a previous year where I referenced brainwave training, a fictional device that could beam knowledge directly into your head. This was a humorous nod to some pop culture references and is definitely not a real product.
We've wrapped up another assortment of Quick Queries for the week. Remember, you can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Correct use of OrderBy and OrderByOn in Access forms
Order of setting OrderBy and OrderByOn properties
Demonstration of OrderBy sort issue with delayed execution
Difference between subroutines and functions in VBA
Storing multiple values in the Tag property
Maximum character length for the Tag property
Using Tag property for storing control coordinates
Error handling when changing BackColor for controls
Handling checkboxes when looping through controls
Using On Error Resume Next for simple property errors
Prioritizing Wi-Fi and Ethernet connections for internet failover
Using phone hotspot for faster internet in office environments
Why payroll systems should not be built in Excel
Relational data requirements for payroll systems
Access vs Excel for building employee payroll systems
Access database web integration using SQL Server
Using ASP for web-based Access front ends
Tree view controls in Access with ActiveX
Tree view vs list box for hierarchical data display |
