Quick Queries #43
By Richard Rost
8 months ago
Dynamic Default, Copy Paste, Security, Macros v VBA
In this Microsoft Access tutorial, I will show you how to handle a variety of common questions submitted by viewers, including copying form data to new records, troubleshooting issues with cutting and pasting labels in design view, customizing form fields for different users, playing sound files with VBA, and discussing options for user-level security and control visibility. I also cover related topics like integrating Access with home networking and offer tips for database projects and learning strategies.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, dynamic defaults, copy labels in form header, control cut paste label issue, user configurable forms, hide fields by user role, show hide fields with VBA, play sound with VBA, play wav files VBA, code vault, user level security VBA, attendance warning email, send emails by absence count, export access form to web, user-specific form configuration, control visibility with macro, chatgpt vba integration, calendar color coding, google calendar integration, multi-user form field selection, attendance database automation, vba sound notifications, form field configuration table, customize form fields per user, warning system for student absences
Intro In this video, we'll talk about a variety of questions from viewers about Microsoft Access, including how to set dynamic default values for new records in forms, troubleshooting copy and paste issues with form controls, customizing forms so users can choose which fields to display, and how to play sound files using VBA. We'll also discuss options for sending attendance warnings by email, setting up user-level security to control record access, using macros versus VBA to control field visibility, thoughts on naming subroutines, and whether to build your own database solutions or buy off-the-shelf software.Transcript Quick Queries: you take a bunch of questions people send in. If you have questions, send them in. Post them in the comments down below, or post them on the forums on my website. Whatever, let's get to it.
Starting off, Death Scene says, is there a way to take information on the form and copy that information in a new record? Let's say I have multiple line items that are different, but the data from the form would be the same for each.
Yeah, you can do this. I'd recommend this video, which I call Dynamic Defaults. Basically, you can put a field in the header or footer, put a value in it, and then make that the default value for new records. So as you type in new records on the bottom, it just uses that default value. You could do two, three, four values across here if you want to. As many as you want. Watch the video for more information.
Next up, Wat Jocks says, sometimes when I Control X to cut labels and put them in the header section, Control C doesn't work. I want to have to use Control Z and do that.
You are not alone. Somewhere along the line, the Access team changed the way Cut, Copy, and Paste works with controls and design view. I have gone back and watched some of my old videos to make sure I wasn't crazy. You used to be able to select just a couple of labels like this and go copy, Control C, and then paste, and it would paste them in there just as labels. Or you could come up here in the form header and paste, but it doesn't paste.
Let's try it again. Copy, Paste. Okay, see that time it works. It works sometimes, and other times it doesn't. Like I have a copy and again, Copy, Paste, Paste. See, now it's just not. Sometimes you have to click, and then click off of them, and then paste them. And okay, that time it worked. So it's just, it's wonky. It used to be a lot more reliable, and something happened along the way where it's just weird now.
But one thing I did note is that you can't usually copy and paste just attached labels if they're attached to a text box, but sometimes you can. Okay, you can't copy them and then click and paste them here. But you can copy them and click and paste them up. Nope, see, it just worked a second ago. That exact thing I just did worked a second ago. It's weird. It's definitely gotten weird.
So when in doubt, try to just copy and then click and then paste one object at a time. I know you can usually get away with multiple controls in here. Copy, see now, now it's not working. Copy, Paste, nope. It just worked for me a minute ago. Copy, Paste, or copy, Paste, don't. But it did work a second ago. I just tried it. Copy, Paste, nope. Yeah, it's not just you. Copy, Paste, all right, there's one. I did one of them. So yeah, I don't know. I could spend an hour trying to figure out exactly what the rules are now, but it keeps changing.
So let me know if anyone out there has got any solid, better ideas of why this does this, because I'm at a loss. I don't know. I know it didn't always behave like this. I never had problems copying and pasting objects like this until a couple of years ago. Then it just started getting weird. At first, I thought it was me, but no, they've changed something.
Access team, any ideas, guys? If you guys even watch this, probably not.
Bolin T wants to know, or is it Bolin Ty? Is there a simple way to create a form based on a query with many fields, but the user will choose which field will be shown in the particular opening of the form? For example, name, surname, phone, email address, but next time for a different user, I'm guessing surname, job, city, position. Is it possible?
Yeah, anything's possible. It's just going to be a lot of work to do it. I would envision a form with a bunch of fields, and then every user could have a configuration file. A configuration record in a table, a user table that would indicate which fields they want to see. Then when the form loads, you could just read in the data from that table and either hide or disable the fields that they don't want. With some additional VBA, you could even move the fields around on the form so that it doesn't appear to leave gaps.
Is this possible? Definitely. Is it going to be easy? Nope. But if you want to see how to do it, and if a few other users are interested, maybe I'll put a video together on it. I could see it being useful, especially if you've got a situation where your front sales people have to see certain values to put an order in, and your back-end people like in the shipping department have to see certain values to be able to ship an order, and your accountant has to be able to see different data.
It's all part of the customer record, but your shipping guys don't need to see, let's say, credit information. So I can see where this would be useful. And if you don't want to make multiple forms, which is what I usually suggest in this case, just have a different form open up depending on who's in the database. You could do it with one master form and then just show or hide fields depending on the user or the user group, like admins, shipping, sales, that kind of thing.
So it's definitely possible. But if enough people want to see it, post some comments down below and I'll do a video on it.
Herubicundists wants to know if it's possible to get the database to make that I I I sound from Jurassic Park. I said that in one of my previous videos. I, I didn't say the magic word.
Yes, it's definitely possible to play sound files using VBA. I have a video that shows you how to play regular sounds. And in the extended cut, I show you how to play any wave sound file. Now the extended cut is normally for members, and I strongly encourage everyone to join up as a member. But since I'm going to give this to people who loyally watch the Quick Queries videos, here you go. Here's a look inside the code vault. There's the code that you need to play any sound file. Pretty easy to figure out.
Gold members get access to this code vault with tons and tons of stuff in it. I'll zoom in a little bit so you can see it better. Just copy that, put it in a global module, send in the sound file. You need a wave file, which they're very easy to find online. Just do a search on Google for wave files.
I've got tons of Star Trek sounds. Every hour, as my hourly chime, my master database reboots that runs on a loop every hour, and it plays that transporter sound. That's why I say when someone's beaming in, that means it's the top of the hour, or sometimes the bottom of the hour. So there you go, benefits of membership.
I'm sure you can find that Dennis Nedry "I" wave file out there. I'm sure someone's recorded it. Watch out for copyright and stuff. Don't sell it. I'm not going to put it in this video. I included a very, very short clip from something in a previous video, and YouTube flagged me for copyright content, which is fine. I understand you can't monetize a video if you've got other people's copyrighted stuff in it. So basically they said that I could include that clip. I think it was a short clip from Star Trek. I could include the clip, but I couldn't monetize the video. So I'm done including clips. I need my four cents from your view. Thank you.
For people that watch all my videos, you might have seen I did my little Rick's pool box video. It's just this cool little box that I made to take up on my pool where I could put my phone and my Alexa speaker in it to keep them out of the sun. I put little fans in, a power bank, and a solar panel on it. I was really proud of it, so I just made a short little video about it. I'll put a link down below.
But Fuegoas Morales says, what? No VBA? I thought about it. I've been thinking about getting into Raspberry Pi because apparently with Raspberry Pi, you can somehow hook that up with your Wi-Fi and get ChatGPT to work with it. That's another one of those projects I've been thinking about doing.
Has anyone else ever done anything like that? If so, let me know, post in the comments down below. But no, sadly, no VBA in my pool box. I tried. I thought of how I could get some VBA in.
The Randy Gur says, I miss doing this kind of stuff. It's about the only drawback I've found since I retired. Retirement is no excuse to not keep learning. I have to learn something new every day. I watch documentaries, I read books, and I try to learn stuff that's not Access related because that's what I do all day long. But I do love to learn new Access stuff too.
But if you enjoy it, do it. There's no reason to stop using this stuff if you like it. I use Access databases for all kinds of personal stuff, keeping track of collections and things of that nature. Just play with it. Have fun.
James Womax says about the pool box, cool project, but he asks, curious if you have videos on home networking or server setup.
No, but I've been thinking about it. I've got a whole ton of other stuff that I did lessons on in the past: Excel, Word, Windows. I've been thinking about doing home networking, mostly for the point of, I get a lot of people asking me how to set up a shared database, but they need the step before that too, like, okay, how do I network my Windows computers together?
So I have been thinking on going back to the drawing board, doing some Windows classes. I started re-recording Windows 11, beginner level one, and I just never finished it. A lot of the times I'll put a video or two up on YouTube, and if they don't get a lot of views, it kind of stops there. But I really need to push through and get those out, just so I have a solid library if nothing else.
But I'm definitely thinking on it. Home networking, maybe not covering every aspect, but at least enough to teach you how to get up and running to run your Access database in your office with multiple computers.
Spoiler alert: don't use wireless. Wireless is notorious for being bad with Access. But it's definitely on my possible to do list.
I actually got more comments on that pool box video than I thought I was going to get. If I do decide to upgrade it in the future, I'll let you know. I don't think I'll be adding lights to it because the whole point of the box is to keep things out of the sun, so I probably wouldn't need it at night. RGB strips and some pirate stickers, definitely. But yeah, I'll keep you guys posted.
It's a nice sunny day today in Southwest Florida, hoping to get out there soon for a little bit. But got work to do. I'm just kidding. I don't really consider this work. I have a lot of fun doing what I do and hanging out with you folks.
For this one, Natalie J Williams, the question doesn't make a difference. What matters is two things. First of all, I just realized you're using Star Trek names in your database. Love it. Yes, always awesome to hear from a fellow Trekkie.
What's important is right down here. It turns out I asked this question before I finished the video, so never mind, LOL. Thank you. That's important. Before you post a question, finish the video first because a lot of the times I will intentionally leave things hanging at the beginning of a video because I'm going to show you at the end.I intentionally do that to build your curiosity sometimes. Sometimes I will even do things wrong up front to give you a better appreciation for why you do things the right way. Because sometimes if I just show you the right way first, you are like, well, why do we have to go through all those steps? What is the point of that? But you do not appreciate it unless I show you that what people normally do is bass ackwards. By showing you that and then the right way to do it, you get a better appreciation for it.
I have run through a lot of these things many, many times in the past 30 years and I have kind of developed a weird way of teaching things. But I think it works.
Yes, moral of the story: finish the video first. Write down your question if you have it. I mean, write it down - who uses paper and pen anymore? Type it into Notepad or something and then post it if it is still not answered by the end of the video, definitely.
Also, if you are on YouTube, be sure to try to find that video on my website. Usually there is a link in the description down below because the Q and A on my website forums for each video are a whole lot more involved than what happens here on YouTube. Because it is only me on YouTube going through the comments and questions. But on my website, I have a whole team of guys that answer questions. My moderators are fantastic, and they are usually a lot better at answering questions than I am. So check it out there.
Shadow Dragon, one of my regulars, nice to see you, Shadow Dragon.
With the attendance series, if you thought about having the start and text boxes default to the Monday through Friday dates with an option to add weekends, there is so much you could do with this. There are a million options. I just obviously cover what I could cover in a little five-part series. You could definitely have whatever you want the start day to be, the end date you want. You can modify this however you want. I have thought about it. Am I going to do it? Well, like I said, if enough people post comments and want to see certain features, I will add it. I will add another video on it.
The problem with these series that I do is I really love doing these little mini-series - five, six, seven videos on the same topic - because I can dive a little deeper into stuff. The problem is the views drop way off. I might get, you know, let us say a thousand views on part one, and then by part two I get 600 views. By part three, I am down to 300 views, and so on. So by the end of a six-part series, I might get a couple hundred views tops. People definitely drop off.
While they are nice for me and I love to create these, I might end up doing it where, if there are enough people that are interested in a few topics, maybe move it to a seminar or something else on my website because they just do not get the YouTube views. Unfortunately, that is what I am doing; I am obviously trying to get the views. So it is better for me from that aspect to make another new video on some different topic. But, yes, sure, that is a fantastic idea.
It is the same problem I have with my courses too, by the way. My beginner level one might get, let us say, a hundred thousand views, but by the time you get to the end of the developer series, I have like a hundred or two hundred students. Some people learn enough and then they drop off because they are satisfied with what they learn. Other people just do not want to learn that much because they are not interested or whatever.
Teresa, back talking about my user level security: What do I need to do if user one can view all records? It sounds like an admin kind of thing, which are made up of user one and two. User two can view only user two, user three can view only user three. So basically each user can only view their own records, but user one can view all of their records.
It is just basically setting up some kind of an admin thing. This would not necessarily be something that you would set up in your table. This would have to be something like in your VBA. You would have to specifically give people permissions to see what records they would need. I cover a scenario like this in my full security seminar. I do spend a lot of time going over user group security inside the database. So, who has access to what, for example. I will put a link to this down below.
Fred Wild wants to know if you apply different names to subs that call other subs and pass arguments to it, and subs that take arguments and perform the function. Copilot calls them caller and dispatcher subs and worker or process subs.
Not really. When you get into more advanced development and you are working with different multiple layers and levels of these things, you can add fancy names to them if you want. Most of the databases that I build and the stuff that I teach people how to build are small business databases. So you do not get into the very depths of object-oriented proper naming conventions. I do not bother with all of that. My sub names are pretty simple: status, create customer record, order, print order, that kind of stuff. You do not need to get fancy with your names until your project gets really, really big and complicated and you are working in teams. Then you need to make sure you handle all the fancy naming conventions. That is my thought on it.
Next up, James says he would like to see how to show or hide controls or buttons in a form using a macro without VBA. I try to use video on my form and I get a bunch of errors.
First thing I am going to say is if you want me to help you with the errors, I have to know what the errors are. When people say to me, "I get an error, I try what you said, but I get an error." Well, what is the error? "I get a bunch of errors." That tells me nothing. I need more information if you want me to help you.
Second, I do not use macros much because macros are not that much easier than using VBA. You could do this very easily with one line of VBA or a little if-then in a visible statement. That is it. It does not take much. In fact, I personally think it is more time-consuming and difficult to use a macro to do something like this than a couple of lines of VBA code. But if you really want to learn how to do it with a macro, my advanced series, which follows the expert series but is before the developer series, covers using macros. I made six lessons specifically for people who do not want to be VBA programmers but want to learn how to do some basic automations. One of the things I cover in "More Control Properties" is how to make a field visible or not visible based on the value in some other field. You still need an event to kick it off and it will run a macro instead of VBA code. But I am telling you, VBA is much, much easier. With a little tiny bit of VBA, you can do all kinds of great stuff. I will put a link to this down below.
But I probably will not do many TechHelp videos on macros just because I do not recommend them. I do not like them. I do not use them. Sorry. There are a couple of things you can only do with macros, and I know that is a topic for a whole different video, but most of the time you want to use VBA.
Back to the attendance video, Cyberman wants to know: Is it possible to include a type of warning system where it gives you a notice whenever a student did miss a certain number of classes? If they miss, like, three sessions you get a warning and maybe even an email going out to that student.
Yes, absolutely. You definitely could process something like this in there. We have already generated a count of how many absences they have. You could just run another report, and for all of those students, either print out the list or print out letters to them to mail out, or you could run a recordset loop to automatically send out those emails. You can put a field in their student record that indicates that they got a warning on this date. There are a lot of kinds of stuff you can do.
So, can you do it? Yes, absolutely. It is just a matter of if you want to put the work in to do it or not. Again, if enough people want to see stuff like this, post some comments down below, and I will gladly continue the series or make a seminar out of it.
Most of the time when people ask me if something is possible, I understand, yeah, you want me to show you how to do it. I definitely will if there is enough interest. I have to go where the squeaky wheel gets the grease. Sometimes people also just genuinely want to know, is it possible? Can it be done before I waste the time trying to figure out how to do it? I am the same way sometimes. Sometimes I just want to know, I have an idea, and I am like, is it possible? Can this be done? A lot of the times I will build a prototype and get a working proof of concept. Then, oh, okay, I can do it. Then I put it on the shelf instead of releasing it as a video or a finished product.
I have got a lot of started projects where I proved something could be done and then I just kind of set it aside. Things like this and recording my day-to-day videos have to take precedence. I have bills to pay too.
In fact, about a year or so ago, I built a proof of concept where I can take a Microsoft Access form, click a button, and then it exports it to the web. Your form, exactly as you look at it in your database, appears in a web browser. It works. It still needs some tweaking, but I proved the concept. Now it is just a matter of releasing it as a finished product.
I started building an update to my calendar seminar too. I have a pretty new calendar that has got colors and all that stuff. I just have to get the Google calendar integration finished and then, you know, it can be done. I proved it can be done, but it is just a matter of getting off my butt and doing it.
So yes, Cyberman, that can be done.
Star Trek Nerd reference.
Reynolds Air says for someone who is desperate for social interaction, Barkley seems like he would go to class more often, because I guess in my attendance database, Barkley has got a lot of absences. I do not think he is desperate for social interaction. In fact, I think Barkley does his best, at least in The Next Generation years, to avoid social interaction. He likes social interaction on the holodeck because he knows they are all fake people. It is not real, so he can be himself or who he thinks he is. In the real world, he is awkward as hell. It gets better toward the Voyager years, but he is still not what I would consider a social butterfly.
There is a comment on my ChatGPT VBA code video from Katie8CatJoe. He says between you, ChatGPT, I am creating an awesome CRM inventory ordering database. That is amazing. I have already developed one for my tax lottery business with your help. The locksmithing database is near completion. Then I will be starting on a database for my rental properties. That is amazing. I am on fire with this, thanks to you.
I appreciate hearing that. There is nothing Access cannot do, in my opinion. Yeah, I tend to agree. There is very little that Access cannot do. There is very little that I have to port off to something else.Like charting, for example, you can do really basic charting in Access, but if you want really cool, complicated charts, you just take your data and put it over in Excel and it can do it.
Why buy generic overpriced programs when I can build it? The only reason to buy a generic overpriced program or an online service is when you can't build it, when you don't know how to build it yourself, or you don't have the time to build it yourself.
I know a lot of businesses that just don't have the people on staff to build an Access database for their business. So they go into these overpriced programs where you have to pay an exorbitant, lumpy fee or whatever for something you could probably build in Access on a weekend. But that's their choice. So we know better.
All right, folks, that's going to do it for another Quick Queries. I hope you enjoyed. Hope you learned something. Hope you enjoyed listening to me rant.
Live long and prosper my friends. I'll see you next week. Well, I'll see you Monday for another regular TechHelp video, but I'll see you next week for Quick Queries 44.
Take care. I'll see you next week.
TOPICS:
Setting dynamic default values for new records in forms
Troubleshooting cut copy and paste for form controls in Access
Understanding attached labels and text box copying behavior
Form customization based on user-selected visible fields
Using configuration tables to save user form preferences
Showing or hiding form fields dynamically for user groups
Playing sound files using VBA in Access
Locating and using WAV files for Access sound alerts
Sending warning emails based on student attendance tracking
Counting and reporting on student absences in Access
Implementing field visibility with macros instead of VBA
Managing record-level user security with admin overrides
Granting different users access to specific records
Naming conventions for subroutines and procedures in VBA
Creating attendance solutions with flexible date ranges
Generating automated notifications for attendance rules
COMMERCIAL:
In today's video, we're discussing questions sent in by viewers, including how to copy form data to new records, troubleshooting copy and paste behavior with labels in Access forms, making user-customizable forms, playing sound files using VBA, suggestions for using macros versus VBA for showing or hiding form controls, and setting up user-level security. We'll also touch on sending automated warnings for attendance databases, thoughts on naming subs in VBA, and whether you should build your own solutions versus buying expensive software. If you want your Microsoft Access questions answered or hear what other users are struggling with, this is the video for you. 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 method does the presenter recommend for quickly entering multiple records with the same data in Access forms?
A. Use the Duplicate Record button built into Access
B. Use Dynamic Defaults by setting values in header or footer fields as default for new records
C. Use an append query to copy data fields
D. Manually copy and paste data between records
Q2. What issue has developed in recent versions of Access when copying and pasting labels and controls in Design View?
A. Copy-pasting always works reliably for all controls
B. Only unbound controls can be pasted now
C. Copy-paste of labels and controls has become unpredictable and sometimes fails
D. You can only paste into the footer, not the header
Q3. What is the presenter's typical recommendation for showing different sets of fields for different users on a form?
A. Use one master form and hide or show fields based on user settings
B. Create multiple forms, one for each user or user group
C. Let users rearrange form fields manually every time the form opens
D. Always display all fields and let users ignore what they do not need
Q4. Is it possible to play custom sound files such as a specific movie sound effect from within Access?
A. No, Access cannot play sound files
B. Only default Windows sounds can be played, not custom ones
C. Yes, with VBA code and a wave file you can play custom sounds
D. Only .mp3 files are supported in Access VBA
Q5. What does the presenter note about including copyrighted sound or video clips in YouTube tutorials?
A. There are no restrictions for brief clips
B. You can use any clip as long as you credit the creator
C. Copyrighted clips may get your video flagged and demonetized
D. YouTube encourages educational use of media in videos
Q6. What does the presenter think of using macros instead of VBA to show or hide controls in Access forms?
A. Macros are always recommended over VBA
B. Macros are easier and preferred for beginner users
C. VBA is easier and more powerful for most tasks, including visibility toggling
D. Macros are required in all secure databases
Q7. How does the presenter suggest handling per-user field visibility on forms based on user preferences?
A. Write one form for each possible field combination
B. Store user preferences in a table and show or hide fields at runtime using code
C. Ask the user to configure fields every time manually
D. Use Access macros to adjust fields for each session
Q8. What does the presenter suggest is the main limitation of wireless networks with Access databases?
A. Wireless works better than wired for Access
B. Wireless connections are unreliable and not recommended for Access databases
C. Only Access 2013 and newer require wired networks
D. Home networking always requires a domain controller for Access
Q9. Regarding warning systems in an attendance database, what does the presenter say about tracking excessive absences?
A. It is not possible in Access
B. It requires a third-party plugin
C. You can easily generate reports, letters, or automatic emails using existing data
D. You can only count absences, not automate notifications
Q10. What is the presenter's advice about asking questions on YouTube videos?
A. Only ask questions after you finish the video to see if it gets answered
B. Ask questions as soon as you have them, even if you have not finished the video
C. Only ask questions in the first 10 minutes of the video
D. Always ask questions on external forums, not YouTube
Q11. According to the presenter, what is usually the best approach to securing records so that only certain users can view specific data (such as admins seeing all records and regular users only seeing their own)?
A. Set permissions at the table level only
B. Use VBA code to enforce user-specific record visibility
C. Restrict access to forms but not data
D. Use only macros for security enforcement
Q12. What best describes the presenter's philosophy regarding naming conventions for subs (procedures) in Access VBA?
A. Always use complex object-oriented naming conventions in all databases
B. Use simple, descriptive names unless working on very large, team-based projects
C. Avoid naming subs; use numbers instead
D. Follow Copilot's recommended caller/worker conventions at all times
Q13. What is the main reason the presenter sometimes holds back on releasing more advanced or multi-part video series?
A. He does not enjoy advanced topics
B. Longer series get fewer views as the series progresses
C. Students find advanced content too simple
D. YouTube does not allow multi-part educational series
Q14. What does the presenter recommend when you encounter error messages and need help?
A. Simply say you got an error, that is enough information
B. Provide the exact error messages you encountered
C. Only describe your general problem but not the errors
D. Errors cannot be helped over comments or forums
Q15. What is the presenter's overall advice for learning Access and working on projects, even in retirement?
A. Stop using Access once you retire
B. Keep learning and working on Access projects if you enjoy it
C. Avoid using Access for personal projects
D. Only use Access for business, not fun
Answers: 1-B; 2-C; 3-B; 4-C; 5-C; 6-C; 7-B; 8-B; 9-C; 10-A; 11-B; 12-B; 13-B; 14-B; 15-B
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 addressing a variety of questions sent in by viewers. If you have any questions of your own, feel free to leave a comment or post them on the forums at my website. Let's get started.
The first question involves copying data from a form into a new record when you have similar items to enter but some information stays the same. There's a great technique I call "Dynamic Defaults" for handling this. The idea is to put desired default values in fields in your form's header or footer, and then use those values as defaults for new records. As you enter new records, those default values automatically carry over. You can set this up for as many fields as you need. Take a look at my video on Dynamic Defaults for the full explanation.
Moving on, someone mentioned a problem with using Ctrl-X and Ctrl-C to cut and paste labels in form design view. Sometimes cut and paste works, sometimes it does not, especially with labels attached to controls. I've noticed the same issue and have gone back to older videos to check how it used to behave. It turns out something changed in recent years, and now copy and paste with controls is much less predictable. Sometimes you have to select and paste each item individually; sometimes pasting works, other times it does not. Unfortunately, there is no clear pattern, and I do not have a definitive fix. If anyone else has figured this out, let me know. This is a recent change in Access, not something you are doing wrong.
Next, I received a question about creating a form based on a query with many fields, allowing each user to choose which fields are shown every time the form opens. For example, some users might want to see names and phone numbers, others might prefer to see job titles and locations. While this is definitely possible, it involves some work. I'd suggest maintaining a users table where each user's viewing preferences are stored. When the form opens, you could read those preferences and show or hide fields accordingly. With a bit more VBA, you could even rearrange controls to avoid gaps. This approach lets you use a single flexible form for everyone, rather than creating separate forms for each user. If this sounds useful and there's enough interest, let me know and I'll consider making a video on it.
Another interesting question asked whether it is possible to play a sound like the "I didn't say the magic word" clip from Jurassic Park using VBA in Access. The answer is yes. You can easily play sound files using VBA. I have a video that demonstrates how to play basic sounds, and I also show how you can play any wave file with a little extra code. If you're looking for specific sounds, just be careful about copyright issues if you plan to share or monetize your projects.
On a lighter note, someone commented on a project I did where I built a pool box to protect electronics from the sun, and asked whether I could have included VBA in it. Microsoft Access VBA and a pool box do not go together, but I have considered experimenting with Raspberry Pi and connecting it to Wi-Fi for advanced projects. If anyone out there has tried integrating Raspberry Pi with ChatGPT or something similar, let me know in the comments.
Another comment came in from someone who misses doing these projects since retiring. I want to point out that retirement is no reason to stop learning. Even if you are not working, keep learning something new every day. Personally, I use Access databases for hobby projects, tracking my collections, or just for fun.
Someone else was curious if I have videos about home networking or server setup. I do not have any yet, but I've considered making tutorials on networking, especially since many Access users want to know how to share their databases across multiple computers. Ideally, you should not use wireless networking with Access, as it is not reliable, but I may put together some basic networking guides in the future, mainly to cover what is necessary for setting up shared databases in an office environment.
A quick humorous observation was made about the Star Trek character Barkley's absences in the attendance database. My opinion is that Barkley actually prefers isolation in real life, even if he enjoys socializing in virtual environments like the holodeck.
Another common issue: posting questions before finishing a video. Often, I structure tutorials to gradually build understanding, and sometimes I even start off doing things the wrong way so you can appreciate the correct method better later on. Before submitting your question, make sure you finish the full video. Many answers show up later in the lesson.
With regards to using macros instead of VBA, one viewer asked how to show or hide controls or buttons in a form through a macro. I honestly believe VBA is much easier and more efficient for this than macros, but if you truly want to stick with macros, I cover their use in my advanced Access series, especially for those who do not want to dive into VBA yet want basic automation.
There was a question about setting up user-level security where each user only sees their own records, except for one admin who can see everything. The simplest way to do this is with logic in your VBA code, not in your tables. You check the user's permissions and filter the records accordingly. I cover these techniques in my Security Seminar, which is linked on my site.
On another note, a viewer asked about naming subroutines, referencing external terms like "caller" or "worker" subs. My advice is to keep things simple unless you are working on a large, complex project with multiple developers. Straightforward subroutine names are just fine for typical business databases.
Returning to the attendance series, I got a question about adding a warning system for students who miss too many classes, maybe even triggering an email. This is absolutely possible. You can already count absences and, from there, generate warnings, reports, or emails automatically. If there is enough interest, I might extend the attendance series to cover this feature.
Someone also shared how they are building impressive databases for their business using help from my videos and ChatGPT. I agree, there is very little Access cannot handle if you put your mind and time into it. For very advanced charting or other functionality, sometimes it makes sense to send your data over to other programs like Excel.
To wrap up, thank you all for your questions and comments. Keep them coming, and remember to check my website—there, you'll find even more detailed discussions, and my moderators do a fantastic job answering your questions. Forums on my site are far more interactive than the YouTube comments.
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 Setting dynamic default values for new records in forms
Troubleshooting cut copy and paste for form controls in Access
Understanding attached labels and text box copying behavior
Form customization based on user-selected visible fields
Using configuration tables to save user form preferences
Showing or hiding form fields dynamically for user groups
Playing sound files using VBA in Access
Locating and using WAV files for Access sound alerts
Sending warning emails based on student attendance tracking
Counting and reporting on student absences in Access
Implementing field visibility with macros instead of VBA
Managing record-level user security with admin overrides
Granting different users access to specific records
Naming conventions for subroutines and procedures in VBA
Creating attendance solutions with flexible date ranges
Generating automated notifications for attendance rules |
