Quick Queries #30
By Richard Rost
11 months ago
Main Menu, User Security, Push Buttons w Keys, More!
In this Quick Queries tutorial, I will address a wide range of practical questions related to Microsoft Access. You'll learn about the benefits of using list menus versus image buttons, setting up modal forms to manage form dependencies, and ensuring secure user access with different levels of permission. I'll also guide you through using keyboard shortcuts for forms, creating composite keys to prevent duplicate entries, and designing custom sort orders in databases. Whether adjusting layout settings, utilizing cloud storage effectively, or transitioning from Excel to Access, this tutorial provides concise solutions to help you optimize your database experience.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Quick Queries video, Main Menu video, list menus, modal form, restricted user access, user level log-ons, keyboard shortcuts, Alt key tricks, composite key, unique index, running sum, numbered list in report, overlapping windows, tabbed documents, Access cloud storage issues, cheap Access 2019, membership database design, use OneDrive with Access.
Intro In this video, we'll talk about a variety of Microsoft Access topics from viewer questions, including using pictures or list menus for main screens, handling form dependencies with modal forms, managing user access levels, and adding keyboard shortcuts to buttons. We'll discuss naming conventions, preventing duplicate records with composite keys, numbering items in reports, moving fields in tables, choosing between tabbed documents and overlapping windows, and best practices for storing your Access database with OneDrive or cloud storage. Other topics include membership database structures, custom sort orders, and troubleshooting missing objects.Transcript Welcome to another TechHelp Quick Queries video brought to you by AccessLearningZone.com. I am your instructor Richard Rost. Quick Queries are videos that contain questions that maybe they don't need a whole video on themselves, but they're good questions anyway, so we're going to cover them, and it's Friday. That means it's Quick Queries day. So here we go.
Got lots of comments on yesterday's Main Menu video. This is one of those videos where I've covered this topic in a lot of other videos, but it didn't have a video on its own. When people ask me, well, how do you do a main menu, I have to point them to another video that's a half an hour long. So I just decided to put this together, but I got tons of questions on this one. So that's great.
Pete says he doesn't need a new menu for him, but he's gonna make a new one now just because it's fun. Yes, it's a lot of fun. I love doing this stuff. Nick says he likes to use a picture as a button. List menus are less worked out. List menus are easier to maintain, especially if you're making changes often. Pictures can look better though. So it all depends on what you're going for. Are you going for those cool aesthetics? Are you going for easy to update?
Art Tat One says it's easy for one form to open another. But what about dependencies from one form to another? In other words, you may not want your users to click back on the first form if the second form is still being used. Yes, definitely. If this is the case, I recommend setting up what's called a modal form. A modal form means you can't click on anything behind this form until you close it.
So if you have customers and contacts or customers and orders, you put a button on your customer form to open the order form, and then you can't get back to the customer form unless they close the order form. Craig says, what about restricted user access to some menus? Developer has full access, manager has a little less. Of course, managers should always have less than admins. Basic user, not much. Will this involve setting up user level log-ons on your account?
Now I have covered this in several other videos. Start with this one. This will give you the basics. I'll remind everybody that Microsoft Access is secure enough for most environments. It's not top-level security, though. If you have financial data or other super sensitive stuff, you really want to use SQL Server to store your data on. But Access, as far as the front end goes, you can lock down a lot of stuff. I have a whole seminar series on this, but start with this video. It'll give you the basics.
Aferin Sir wants to know how can you open a form without clicking, only the keyboard shortcuts? Well, for that you can use the Alt key tricks on the buttons. You can make button shortcut keys. You can make it so that the Enter key is the default. The Escape key will close the menu, and then you can put different little shortcut keys on each button. Like here, you can see it's an underline under that C there. That tells the user pressing Alt+C will close that form or do whatever other thing you want. It's literally as easy as just coming in here and putting an Alt character in front of a letter in the caption of a button. Now you can only use one for C so you couldn't put a C in front of that one. Maybe make that one L. But now when you open that form up, all I have to do is press Alt+C and it pushes that button. Very simple.
Shadow Dragon says I'd like to see more videos to flesh out this menu technique. More are coming. I didn't realize how popular this topic would be. I presume the Rule of Three would drive the button versus list decision or other considerations? For those of you who don't know, my Rule of Three basically involves putting fields and tables: addresses, phone numbers, contact names, those kinds of things. It's a personal rule. If you're gonna have more than three of them, they belong in a different table. Phone numbers, for example. It's okay if you want to do work phone, home phone, cell phone. All right, fine. That's okay. But if you're gonna be doing more than that, fax number, alternate phone number, whatever, more than three, put it in a separate table. That should be in a separate related table, and you can have multiple different phone numbers. Same thing with addresses. Built to, shipped to, that's fine. You got six addresses? No. So, now, does this pertain to menus? I would say no, because I think menus are more of an aesthetic consideration. What do you think looks good? What works well for your users? If you like buttons, and I got menus with 15 buttons on them, it all depends on what works for your database. Now, if you're constantly making changes to your menus, if you're always adjusting them, you want to be able to make modifications or even pop up different menus for different users, then I think the list box works wonderfully. If it's something that you don't change often and you like the way buttons look or images as the previous user mentioned, then stick with images or buttons. So, that's this. I wouldn't say the Rule of Three applies to this. That's more about tables and relationships and things of an angel, but very good observation. Thank you.
Oriel Ramirez says, would it take longer to use the AllForms collection to check if the form is there, regardless of the nomenclature? What he's talking about is in my video, I say that you can look at the name of the object and see if it ends in an F or an R, because that's how I name my stuff. All my forms end in F, all my reports end in R, all my tables end in T. Guess what my queries end with? So, I can use that to look at what the object name is and determine if it's a form or a report. There is a thing called the AllForms collection, as Oriel mentioned, and you can loop through that to determine if that form exists in your database. Could you do that? Sure. There's no reason why you couldn't. I would suggest, though, if you're not using my nomenclature to name your forms and reports, just make another field and indicate what it is. Is it a form? Is it a report? It could be a checkbox. It could be a yes or no value. Is it a form? Is it a report? Then you can adjust it accordingly. I wouldn't bother looping through the forms collection. That seems like a lot of extra work. But that's totally up to you. If you like doing that, then great. You could, however, have an object that is named the same that is both a form and a report, and so the system might not know what it was unless you gave forms priority. If you got a form named Customers and a report named Customers, how do you know which one to open? See, that's the problem. So I would either use my naming convention or just have another field indicate, is this a form or a report? That's the easiest way to do it, I think.
Next question is about this video called Split Part Numbers, where I tell you how to take data that comes in like this. Let's say you got a description of a job, and that's got these part numbers separated by commas. I then teach you how to take it and split it so that it comes like this. So each job, job one, it'll take each one of these numbers and then put it in its own individual record. Knowing that, here's the question. The question comes from Jess Foster, so I may have missed it in the video, but will this prevent duplicates? As new jobs are added and you click the button to run the process, it's going to grab the same part number from past jobs and create basically new records for it. She answered it herself, no, it does not prevent duplicate entries. But is there any way to index for unique since you wouldn't have to have multiple records into the same parent? Yes, you could do this quite easily using something called a composite key. A composite key lets you set up a key that you can control whether or not you can have duplicate values across multiple fields. For example, in the example that I give in the video, let's say you don't want a user to be able to put the same product with the same product ID on a single order more than once. They can change the quantity to have two of them, but you don't want phasers showing up on this order twice. You can set up a composite key that looks at both the order ID and the product ID, and if those two things are not unique, then it doesn't allow a duplicate. You do the same thing here in this table. You'd set up a composite key between job ID and part ID so that it doesn't allow the same job to have the same part on it if that's what you want, that's what you're talking about. Or if it's simply you don't want the same part on any other job, you just set up a key for this field by itself and allow no duplicates. If I understand your question correctly, you're looking for you don't want to have the same job number with the same part number on it again. I hope I answered your question correctly. If not, correct me, let me know.
Jeremy says, I Googled how to number a list of items in a report, but I didn't get a video of yours. I did get another helpful video, but I'd become spoiled with your tutorials if you have anything on this. I tell everybody, if you Google how to do something in Microsoft Access and one of my videos doesn't show up, I want to know about it. So Jeremy, thank you very much, and you're right. I Googled it myself. I Googled how to number a list of items in a report, and after the AI stuff, scroll down here, one of my videos does show up. But I show how to do this in a form, which is actually a lot more complicated. I don't show how to do it in a report. I'm gonna make one on how to do this. It's super easy. I'm gonna show you how to do it right now real quick, but I'm gonna make a full-featured video on it just so I get on this list here. We'll see in a month or two if I show up.
I'm gonna show you the quick way how to do it real quick. Let's say you got on your orders here. We've got an invoice report. Let's say you want to number these items in here. Here's how easy this is. Go to design view. We're going to just add a text box. I'll sneak it over here to the left of the product name. I'll just copy it. In fact, you just copy the product name if you want. Copy, paste. We're gonna slide this up next to it like so. We're gonna open up its properties. We're gonna come over to all over here, and the control source, we're gonna say equals one. That's it, just equals one. Give it a good name, like a, you know, record count or whatever. Now go to the data tab and find this guy here that says running sum. Drop this down and over all or over group over all for this since this is, you know, the detail section is all of you can do them over groups. Save it, close it, restore it, click it, and boom, there's your numbers. See? Because it basically, you're adding a field in here whose value is one, but you're making it a running sum. So every record adds one to the previous one. That's it. I do actually cover this. I've got videos on doing running sums and running balances for things like a check register. So it just adds the balance above it. But if you just make this value one, it's the same way as getting a numbered list. So that's the quick way to do it. There's a little few other little nuances I can show you. I'll put those in a full video coming up pretty soon. But again, Jeremy, thank you very much for letting me know, and the same thing goes to everybody else. If you try to Google something or search on YouTube for a video about Microsoft Access and mine doesn't show up at least first, I want to know about it. I'll make another video.
Next up, Charles has a question related to Access Beginner 1. He's a new Microsoft Access user. Welcome to the community. He says he has a question about moving fields around. If you have a bunch of code placed in a table and then move the fields around, does it cause a problem? I have an older 2003 database I'm working with, and there are a couple fields I'd like to see together. Will it cause a problem if I place them side by side? No, absolutely not. It will not cause a problem. Whether you're talking about a field in a table, a query, a form, or a report, the layout of those objects has absolutely nothing to do with anything else than just how they appear to you. Feel free to click and drag, move them around, reorganize them, change the way they look. None of that will affect the data in the table, and it won't cause any problems. So feel free. I always tell people, before making changes, always, always, always make a backup copy of your database first before you go playing with it. Always back up your data.
I hope that answers your question, and if you have any more, please feel free to post them.
While we're on Access Beginner 1, let's go back through the older questions that are on the same video. I like to do this to clear off all the questions that are on the video, because sometimes they go back years. This one, I think we're only going back a year, so it's not too bad. Kaz Delvin says, I created a 5 by 3 size form in design. Then, when viewed, it fills the window-wise and not showing it's 5 by 3. Sounds to me like you've got tabbed documents on and not overlapping windows, which is what I recommend, and I show that in Access Beginner 1. Here's another video that expressly discusses this topic. Overlapping windows is what you want, and you can make it exactly the size you want it to be, as opposed to this one, which is just tabbed documents. Tab documents is actually the default setting for Access for new databases. I don't like it at all. I'm not a fan of tabbed documents. I like overlapping windows, classic windows. I'm old school. Sorry. It sounds to me like you might also be dealing with a report. I know a lot of people who are not Access experts tend to refer to reports as forms because they print it out. They're making a form to be printed out. They think of it as a form when, really, they're talking about a report. If you want to print out exactly 5 by 3, the same thing applies. You can set your margins and stuff so that you get exactly 5 by 3. I assume you probably are talking about this, where it's set as a tab document and not the exact size you want. Check that video out. If that's not it, post a follow-up.I promise to try to get to it sooner than six months. It's also why I tell folks if you have questions and want a faster answer, post it on my website. I read my website a lot more often than I do the YouTube comments, and I've got a great group of moderators. I have like six or seven guys that every day are helping people, answering questions, checking things. They do far more than I could possibly do on my own. So if you have a good question, post it on the website.
I get this one all the time: where do you find the links that I mention in the video? They're down below the video. You have to click on "more," and you'll see them. I get asked this so much, I made a separate video just on this topic. You'll see a little "show more" down there underneath the video window on YouTube. That's where the links are. Click on that. I know YouTube does a really good job of hiding it, but it's there.
This is another great question that I get asked from beginners a lot, especially people coming from Excel into Access. The question is, how can I add a new record but not at the end of the records? I want to add the new record in between two existing records. See, a database doesn't exactly work like that. When you've got a spreadsheet and you want to add a record between two other records, it's easy to just insert a row and you can put your new record there. That's how a spreadsheet works.
With a database, you're not really inserting a record in here. I mean, you can add a new record, but it adds it on the bottom, and a lot of people are confused because they think this auto number is some kind of a record numbering thing, and it is, but it's not for you. This auto number is not for your purposes; it's for the database internally so we can keep track of unique records. If you want to have relationships between customers and their orders, you'd use that customer ID 8. But this is not something that you can modify or change or insert a record in there between eight and nine.
In fact, I have a whole other video on this topic that explains that auto numbers are not for your use; they're for the database internally. In fact, you don't even have to display these auto numbers on your forms and reports unless you want to. On this page, you'll find a whole bunch of different links on things you can do if you want sequential numbering. You can add your own record numbering.
So you can add your own customer number or whatever customer identifier that you want that's not an auto number. If you want to be able to insert a record there, then you have to ask yourself what is the reason that you want to insert it there. Is it just to have it in a particular customer ID range or is it more of a date thing? You can always insert dates in between other dates unless, of course, it's to the second. But if you want your own numbered list, for example, then see the thing that I mentioned earlier about numbering lists. You can also do the same thing in forms too.
To answer your question, that's not how databases work. That's not how the records in a table work. You don't really insert a record between two other records unless you've got some other numbering system aside from just the order that they're in the table. You can also make your own custom sort order if you want to pick the way that the records are ordered. I do this a lot with a drop-down combo box that has a list of countries in it, and you want people to pick what country they're from, and you want the United States to be first if you're from the U.S. Then maybe the ones that are popular for your customers like Canada, UK, that kind of thing. You want to have a custom sort order so those show up at the top of the list. In fact, I've got an exact video about that too.
Here's how to choose a custom sort order. So you've got these ones here that come first, and then after that, everybody else is alphabetized. So you have to ask yourself why you want to insert that record at that position. Usually, the order in which these appear is meaningless. If you want to sort this list, you want to insert somebody inside of there, maybe with an E last name, add them to the end, and then just resort the list. That's what queries are for, so you can view this information in any way that you want, sort it by last name, for example. But that's a very good question, and I do get that question all the time from beginner students.
Here's another question that I get. A lot of people, especially from countries or locations that have poor internet, want to know how you can download my videos. I know there are different utilities out there that allow you to download videos from YouTube directly. I have not used any of them, but if you come to my website and pick any of my videos, I'll just pick this one here that I just did yesterday. If you right-click on the video here, you can go "save video as" and that will let you download that video.
As far as my full courses go, if you go to my Access courses, I'll just pick Beginner and then Level 1. All of my courses, if you go to the lessons page, here's all the lessons here, and these will play over here. You could download them individually if you wanted to, or just scroll down and you'll find a full video zip file. It's 500 megabytes. If you plan on watching this on a plane or you're going to be going out to the cabin in the woods and you're you're going to be reading, you can download this and take it offline, do whatever you want with it. Most of my full courses have a zip file you can download as well.
Related question: Mary wants to know if I have a hard copy you can get of the course. If by hard copy you mean a handbook, then yes. Most of my courses also include a printable handbook, which is right there. I don't know why the 2010 one is the most recent one. I've got the 2019 one sitting in my folder. I guess I haven't updated it in a while and no one has said anything to me. But here's the older one. You click on it, there's the PDF. It has pretty much all the same stuff. This course hasn't changed much. I've added some stuff to it, but it's pretty much all the same stuff. If you want information on my slides, well, okay, there you go. My slide decks are not for distribution; those are for me. I've got notes in there just for my own use, so you can't have them.
Here's another question: this user says that he uses his database for a while, and then sometimes objects just disappear. The tables remain with the records inside, but sometimes forms, queries, modules, or reports just disappear. I pointed him to the troubleshooter because generally for any weird problems, I suggest going to my troubleshooter. But I have seen something similar to this happen for people that try to use their Access databases off of cloud storage drives. Especially split databases on cloud storage drives where your tables are in one file and your forms and reports and stuff are in another.
If you're storing your data on a cloud storage drive, don't. It should be on a local hard drive because that could cause synchronization issues. You're working on a copy of the database to make some changes, you think you saved it, but it somehow synchronizes with the other copy on the cloud storage device between a different machine, and it can cause all kinds of problems. Just make sure you're not doing that. Go see this video for more information on why you should never put your Microsoft Access database on a file sharing service.
This person says I have Access 2000. What's the best place to get a really cheap version of Access 2019? Be careful about buying cheap versions because there's a lot of fraudulent software vendors out there. They'll send you a burned copy of it with some kind of cracked version or a registration code that's invalid or whatever. Me personally, I strongly recommend that you get the subscription to Microsoft 365. You could try buying a copy on eBay, maybe find a used copy, but I don't normally recommend that. This video talks about all of my thoughts as far as how to buy Microsoft Access. This is the 2025 edition so I just redid it.
So Z says, do you have a suggestion or video on a membership database where we have a spouse and children associated with the account, but we need to pull up the spouse at any given time, so like, we have John Doe but the spouse is Jane Smith? Would you include her with John Doe or create a separate membership? A lot of this boils down to how you want to do it. There's lots of different ways to put the Legos together. It's all completely up to you.
In this particular case, it sounds like you've got a membership, so I'd have the membership table, maybe have a primary member on there, and then you can have separate related records for other people. I've got lots of different videos where I cover memberships. Here's one. Here's another one. This is called the association database, but basically, it's for any kind of association, whether it's a club, a membership, a wholesale club, a golf club, a church, whatever. I actually had to determine the tables that you need and set up the relationships with them.
Another way you could do it is to just add all the people and then use self-joined relationships between them, which I cover in my genealogy video. You can have the husband, the wife, and the kids, for example, all related to each other and they're all person records. Again, there's a million different ways to do it. You want to build the database so it reflects how your business works, not the other way around. Me personally, what I would probably do is have a membership table and then a related secondary table with all the people covered under that membership. So they're all just people, and then you could indicate which one of those was the primary member with a yes-no field. There's a million ways to do it.
Finally, today we have a user using OneDrive who wants to ask if it's okay to use OneDrive to store his database as long as he's the only user. Is the database still going to get corrupted? Yes and no. I strongly recommend against running the database out of your OneDrive folder or Google Drive or Dropbox, whatever. If you want you can store a copy of it there, copy it down to your local drive, work with it there, and then copy it back up if you want it to be saved there to be backed up. This only works well for small databases.
Me personally, I used to have my own little contact database, and I had an office that was about a half an hour drive away. So what I would do is I'd work on my contact database at home, and then when I wanted to go into the office, I would copy it into my Google Drive folder so that by the time I drove to the office it would be sitting there and I could copy it back down again. I would always remove it from the Google Drive folder. I'd never leave a copy there. I would literally move it, just cut and paste it. So there aren't two copies floating around. Is that okay? Sure, as long as you're the only one using it and it's a small database and that's your purpose for doing it so you can have it between two different locations.
I would not run it out of that folder. I would only run it off of a local hard drive, but that's my opinion. I've seen databases get corrupted running in the OneDrive folder because OneDrive is constantly trying to sync that file, and Access doesn't like that. It's trying to sync it up to the server and keep a copy on your workstation and whatever other workstation might have access to it at the time. If you know you're one user with one small file, copy it down to your desktop, use it there, and then when you're done, copy it back into your OneDrive folder, and then leave it as the only copy that you've got. So then the next time, if you're on your laptop or your office or your home, you can copy that file down and work on it. Don't leave it in the OneDrive folder while you're working on it.
That's my experience, and I'm going to leave it at that.
All right folks, so that's going to do it for Quick Queries number 30. Hope you learned something. Live long and prosper, my friends. Enjoy your weekend. I'll see you on Monday for some more new stuff.
TOPICS:
Quick Queries introduction
Main menu video feedback
List menus vs. picture buttons
Dependencies between forms
Setting up modal forms
Restricted user access
User level log-ons
Opening a form with keyboard shortcuts
Alt key tricks for buttons
Rule of Three in database design
Naming convention for objects
AllForms collection
Split part numbers into separate records
Preventing duplicate entries with composite keys
How to number a list of items in a report
Setting running sums in reports
Moving fields in tables
Tabbed documents vs. overlapping windows
Video links location
Inserting records in databases
Downloading tutorial videos
Access 2000 and Access 2019 purchasing options
Membership database structure
Running databases from cloud storage
Using OneDrive for database storage
Tips on database design and maintenance
COMMERCIAL:
In today's video, we'll talk about setting up menus using pictures or list menus in Access and discuss how to handle dependencies between forms. We'll explore setting up modal forms so users can't navigate back to the first form while still on the second. We also tackle managing user access with different permissions for developers, managers, and basic users. Plus, I'll answer questions on using keyboard shortcuts, troubleshooting missing objects, and indexing for unique records with composite keys. If you're curious about numbering items in reports, you won't want to miss this! 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 does a "modal form" mean in Microsoft Access?
A. A form that appears in a separate window and remains open while allowing interaction with other forms
B. A form that forces the user to interact with it before returning to the parent form
C. A form that contains multiple pages or tabs within it
D. A form that is minimized and not visible until an action is performed
Q2. What solution does the instructor recommend for managing restricted user access in Microsoft Access?
A. Implementing password-protected menus for each access level
B. Utilizing user-level security features to define permissions for different roles
C. Creating separate front-end databases for each user with different levels of access
D. Using macros to hide certain forms based on the user login
Q3. What is the purpose of using shortcut keys in Microsoft Access?
A. To enhance form security and prevent unauthorized access
B. To make form navigation and feature access quicker via keyboard commands
C. To allow for automated data entry in forms
D. To create visual effects and animations on form buttons
Q4. What is a composite key in a Microsoft Access table?
A. A unique identifier for each record in the entire database
B. A single field used to ensure records are uniquely identified
C. A combination of two or more fields used to ensure uniqueness in a table
D. A field that is automatically generated and cannot be altered
Q5. Why should you avoid storing a Microsoft Access database on cloud storage services?
A. Because Access databases cannot be opened from cloud storage
B. To prevent data corruption caused by synchronization issues
C. To ensure faster data retrieval and processing speeds
D. Because cloud services do not support Access file formats
Q6. How can you display a sequential number next to each record on a report in Access?
A. Use a calculated field that generates a random number for each record
B. Create a macro that assigns numbers based on record creation date
C. Add a text box, set its control source to "=1", and enable "Running Sum"
D. Assign an auto-number field as the primary key for the report
Q7. What does the Rule of Three refer to in Microsoft Access database design?
A. A method for naming tables, queries, and forms systematically
B. A strategy for determining when related fields should be separated into different tables
C. A guideline for setting up primary keys with a maximum of three fields
D. A sequence for debugging code modules effectively
Q8. How does the instructor suggest dealing with a membership database that includes spouses and children?
A. By creating separate membership entries for each individual
B. By adding related records in a secondary table linked to the primary membership
C. By using Excel alongside Access to manage complex relationships
D. By integrating the membership database with an external CRM system
Q9. What advice is given for moving fields around in a Microsoft Access table or form?
A. Avoid moving fields, as this will disrupt the database integrity
B. Always move fields alphabetically for consistency
C. Move fields freely, as the layout does not affect the actual data
D. Convert the table or form to a design view first, then move fields
Answers: 1-B; 2-B; 3-B; 4-C; 5-B; 6-C; 7-B; 8-B; 9-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 address a variety of questions that don't each warrant a full-length tutorial but are still very much worth exploring. It's Quick Queries Friday, so let's jump into the questions.
I've received lots of feedback on the Main Menu video I released yesterday. Although I've touched on the topic of main menus in several other videos, I realized there wasn't a standalone video for it. The interest this topic has garnered is wonderful, and I'm glad it has sparked so many questions.
Pete mentioned he didn't necessarily need a new menu, but he's motivated to create one just for the fun of it. Making menus can indeed be enjoyable! Nick pointed out a preference for using images as buttons over list menus, which are generally simpler to maintain. The choice between aesthetic appeal and ease of updates ultimately depends on one's priorities.
Art Tat One raised a concern about dependencies between forms, such as ensuring users cannot switch back to a previous form if it's still in use. For these situations, a modal form is recommended. A modal form prevents users from interacting with other forms until the current form is closed, ensuring they follow a logical flow, like having to close an order form before returning to a customer form.
Craig asked about implementing restricted user access, suggesting a hierarchy where developers have full access, managers have a bit less, and basic users the least access. This can be managed with user-level logins, and while Microsoft Access is suitably secure for many applications, for extremely sensitive data, SQL Server is recommended. Access can still serve well as a front-end interface with options to lock down various elements.
Aferin Sir inquired about opening forms using keyboard shortcuts instead of clicking. This is achievable by assigning Alt key shortcuts to buttons and defining default actions for keys like Enter and Escape. By embedding an Alt character in a button's caption, you can create shortcuts, like pressing Alt+C to execute an action.
Shadow Dragon expressed interest in more videos about menu techniques and touched on my Rule of Three, which mainly applies to structuring tables. Menus, however, tend to be more about aesthetics and functionality. Whether you prefer buttons or list menus depends on how often you need to update them.
Oriel Ramirez asked whether using the AllForms collection to verify form existence would be beneficial, as I typically rely on naming conventions. While looping through the AllForms collection is possible, it's more straightforward with consistent naming conventions or an additional field specifying the object's type (form or report).
Next, about the Split Part Numbers video: Jess Foster raised a concern about duplicate entries. The solution lies in using a composite key, which ensures unique records by combining fields, like job ID and part ID, preventing duplicates if that's the objective.
Jeremy asked for a method to number listed items in a report. While I haven't made a video on this specifically, you can add a text box with a control source of "=1" and set it as a running sum for an easy fix. I plan to create a detailed video on this soon.
Charles had questions about rearranging fields in Access databases, affirming that adjusting field order in tables, queries, forms, or reports won't affect data integrity. Always back up your database before making such changes.
Kaz Delvin experimented with form sizes and found tabbed documents not to his preference; I recommend using overlapping windows for more control. If it's a printed form issue, ensure correct margin settings.
Regarding Access Beginner 1, here's how you can resolve issues with disappearing database objects: ensure you're not running your database off a cloud storage drive like OneDrive, which can cause synchronization issues.
For those seeking inexpensive versions of Access 2019, exercise caution against fraudulent vendors. I advise subscribing to Microsoft 365 for a legitimate and up-to-date solution.
Z had questions about designing a membership database that incorporates family members like spouses and children. This can be addressed by having related tables for primary and additional members or using self-joined relations covered in my genealogy lessons.
Lastly, concerning OneDrive use: it's best to store your database file locally to prevent corruption. Transfer it to OneDrive only for backup purposes after working on it.
That wraps up Quick Queries number 30. Remember, you can find a complete video tutorial with step-by-step instructions on the topics discussed here on my website at the link below. Live long and prosper, my friends. Enjoy your weekend, and I'll be back with more content on Monday.Topic List Quick Queries introduction
Main menu video feedback
List menus vs. picture buttons
Dependencies between forms
Setting up modal forms
Restricted user access
User level log-ons
Opening a form with keyboard shortcuts
Alt key tricks for buttons
Rule of Three in database design
Naming convention for objects
AllForms collection
Split part numbers into separate records
Preventing duplicate entries with composite keys
How to number a list of items in a report
Setting running sums in reports
Moving fields in tables
Tabbed documents vs. overlapping windows
Video links location
Inserting records in databases
Downloading tutorial videos
Access 2000 and Access 2019 purchasing options
Membership database structure
Running databases from cloud storage
Using OneDrive for database storage
Tips on database design and maintenance |
