Quick Queries #46
By Richard Rost
6 months ago
Unique Fields, Composite Key, Monaco, Security, More!
In this Microsoft Access tutorial I will show you how to prevent duplicate values in your tables using indexes and composite keys, discuss the best ways to zoom in on your database views, address questions about working with the new Monaco SQL editor, explain how to set up weighted grades and ranked grading, troubleshoot common syntax issues, share advice on multi-user environments, demonstrate how to log user activity, and answer questions about copying historical data and cascading combo boxes. This is part 46.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Quick Queries, indexing, composite key, unique values, no duplicates, primary key, fiscal sort, Shift+F2 zoom, Monaco SQL editor, weighted grades, ranked grading, user log, database security, login system, database updates, batch file, VBA tracking, encrypt front end, cascading combo boxes, historical transactions
Transcript
It's Friday folks, you know what that means. It's time for another Quick Queries brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. This is episode number 46.
What are Quick Queries? Well, I take a bunch of questions and comments that people have posted on my YouTube channel and on my website, and sent me by email. I put them all together and I regurgitate some Microsoft Access goodness to you. I'm gonna baby bird you with some details.
All right, let's get to the mailbag.
First up, an email from Albert: How do I stop people from entering the same value twice in my Access table? I want to make sure things like account numbers or other fields stay unique.
Well, for this we're going to turn to indexing. Indexing is great for single fields like Social Security number or phone number. Remember back in the day, you go into a video store like Blockbuster and they'd say, "What's your phone number?" and you're like, "I'm not giving you my phone number, you creep," and they're like, "No, no, it's so we can look up your account." Oh, okay. A lot of stores used to use your phone number as your membership number because they would use that to look you up. So you wouldn't want two people with the same phone number, right? So you can index that field and say "no duplicates." This is basically what happens with your primary key or your AutoNumber, right? Your customer ID is indexed with no duplicates.
So that's one way to do it. Now, that's for a single field. For multiple fields, you can create something called a composite key. I cover that in this video that makes you have the ability to have two or more fields in the same table that you can't have a duplicate in a combination of those fields. For example, what I do in this order is a product and order ID. Those have to be unique. That combination has to be unique for each record in your order detail table, so the same order can't have the same product twice.
You can do something like first and last names. Let's say like the Motion Picture Academy, you can't have two Robert Downey Juniors, for example. So you would index that on those fields so you couldn't have duplicates, or like a Rick Ross. You couldn't have two Rick Ross; you can have two Ricks, you can have two Ross, but that would be a composite key. Both of these videos explain those different situations in a lot more detail. Check those out. I'll put links down below.
Next up, let's head over to the YouTubes. We got, let's see, my fiscal sort video that I did, what was it, yesterday or the day before? I asked you guys whether you prefer the Shift+F2 zoom in my videos, which I think is clearer. That's the one I like, or the Windows zoom that zooms in the whole thing. Most of you said you prefer Shift+F2 and one person likes the Windows zoom but you're outvoted there, Runge Carl. Plus, two of my moderators, Kevin and Sammy, both said they like the Shift+F2 zoom. So, Shift+F2 zoom wins. I will continue using that in my videos moving forward.
I also have video editing tricks where I can zoom in with my recording software but that's a bit of a pain. I'm not exactly chasing high effects and swipes and all that stuff in my videos. I want to keep them simple, keep everything readable, and still have fun. So yeah, we'll just use Shift+F2 from now on.
It'd be nice if Access had a way to zoom in in Design View and from what I hear, that's in the pipe coming down soon, so we'll see.
Oh look, here's the Microsoft 365 roadmap. Let's slide. Oh, look at this: "Add zoom slider magnification to Microsoft Access," the same feature that Word, PowerPoint, Excel have had for literally decades. So I am looking forward to this. They're working on it. They mentioned this at the MVP Summit and Access Day, so I'm excited for this.
This brings me to the next comment, also by Runge Carl: Can you do a video on the new Monaco SQL editor? Yeah, I do plan on making some videos on it soon. This is one of those new features they just kind of released a little while ago. Well, it's been in the beta channel forever, and I still see people reporting bugs with it and behavior that's not working right sometimes.
I like to, I'm a late adopter. I play around with this stuff myself. I goof around with it on my test machine, but I don't update my actual Access database that I work on or the one I use on this machine for teaching until I'm sure there's no more bugs. I've had to roll back several times because of bugs and just stuff which happens.
That's why I'm a late adopter. I'm going to be doing another video pretty soon to explain to all of you what the different channels mean and how you can, if you're the kind of person that doesn't mind a little experimentation and being the first to play with new features, sure, you can get the newest updates as soon as they're released. Me, personally, I like the more stable versions.
All right, but yes, some videos on the Monaco SQL editor are coming out pretty soon. I've been playing with it a little bit. It's pretty neat, so stay tuned. It's coming.
Next up, PTZ Cool. Pepe? PTZ Cool. How do you pronounce it? He's using the Students and Grades video. One suggestion: he wants to know if you can add weighted grades to it. For example, tests are 40, assignments 30, quizzes 30. All add up to 100, then you rank them based on ABCD based on that. It may or may not be possible. Yes, it's definitely possible, and yes, I've had this on my list to do a video for. A few people have asked me about it since the Students and Grades came out. So yes, it's possible. Yes, it's coming.
Hang in there, and if you also, not you PTZ Cool, but if other people want to see this, post a comment down below. My list of what videos to make, squeaky wheel gets degrees, so the more people comment that they want something, the sooner I am to do it. So if you want to see ranked grading like this, weighted averages, post a comment down below, and I'll be sure to make that video. But yes, it's on my list, it's coming.
I would probably say nine times out of ten when someone says, "Is it possible to..." in Microsoft Access, the answer is almost always yes. It's just a matter of how much work you want to put into doing it and if Access is the best tool for it or not.
Database related stuff, obviously you're not going to turn Access into Photoshop, but I mean, if it's database related, business flow kind of stuff, can you do this? Almost always. Some things are a lot of work, some things are better off in other tools, like advanced charting is still better in Excel, but most stuff you can do it.
That's why when people used to tell me they were using Access with Crystal Reports, I'm like, "Why?" I've never seen a report that I couldn't generate in Access that Crystal Reports could generate. I don't, but then again, I'll be honest, I haven't used Crystal Reports in probably five to ten years. If you guys can think of anything, let me know, but I'm no longer a Crystal Reports expert. But why have both? I don't know, Access has really good reporting.
T-Covving31 posted on one of my videos: "Too bad. I bought a brain waves device and need none of this." Touché. You don't need training in the traditional sense if you've got a brain wave device. For those of you who are curious, I'll put a link down below to exactly what brain wave training is.
All right, so click on that. And since I know some of you are too lazy to find it in the description (myself included), when video creators say "click on the link," I generally don't, but there's the link if you're curious.
Next up, we got David Chapman. He says, "All I get is an invalid syntax error," and that's what he's using now. This is my Numbered Form Items video. That's where I show you how to create this right here, where you get a number next to each record. So, you'll want to do 4, 5, all right.
Now, David, if you copied and pasted this directly from your database, I can see two problems here. Access, just like most programming languages, is very picky about things like spacing. I can see right there you got a space between that double quote and the OSR ID. That space right there is going to cause problems. All right, so get rid of that space. Another thing is you have underscores in your field names there.
I can tell you haven't watched my Access Beginner One class because that's one thing I try to drill into people's heads from lesson one: don't use anything but letters and numbers in your field names and your table names.
But the most important thing is, that right there is supposed to be the table name, not the field name. So you're looking up star, which means all the records from what table, where the field is less than or equal to. This is a field in your form.
So three things: A, fix the table name. B, try to get rid of those underscores if you can. C, you gotta watch that spacing.
But that's okay. It's a learning moment. This is what we're here for. We're all here to learn. I was where you are at one point, too, making mistakes like this. And the way that we learn is by making mistakes, so I'm glad that you asked the question. I hope this helps. Try it again and let me know if it works.
Cyberman1964 says, "Nice videos, wanting what to do if multiple people are allowed to make changes." Now, in this video, my Read-Only Form video, all I did was I set up a simple button you could click on and it pops up an input box that says enter your password, designed for a single manager, or a single manager password at least.
But if you've got multiple people, you could just simply set up two names that are okay, and check to see if S is equal to one of two things. Simple If...Then statement, or set up actual proper logins for your users. That is something that I'll show you how to do in this video. So go watch this and it'll explain how to set each user up with their own login, username, and password, all that stuff. Then you can just simply use that instead of a simple pop-up. I was just doing the simple pop-up as a quick example for this class, but if you want real security, go check this out.
Got several questions from Timothy Whorl. First, he says, "Say there's five users, each for the local front end, and you amend a report or form, how do you ensure each computer is updated?"
Well, that's definitely an issue. You can run around to each machine and update their front end manually, or you can write a batch file or something that will just copy it. What a lot of people do is they'll make a batch file that the user runs instead of actually running the database, and so that batch file will copy down the front end, which is usually a relatively small file, a couple megs, maybe 10, 15, 20 megs. Then after the batch file copies the file down, it launches the database.
I also have a template available on my website that does the same thing basically, but it's smart. When you make an update, you click a button, it pushes the update to the server, to a special location, and then when everyone's other databases open up, they'll say, "Hey, I see there's an update there, do you want to download it?" and it'll just download it, or you can force it to download automatically, that kind of stuff. So check it out.Here's the corrected version for spelling, grammar, and punctuation, as you requested:
There's the link. I'll put a link down below.
Next question about my tracking user log on/log off. As an author, you might need to also know when someone logs off during the day and they go to lunch, leaving the system logged in and someone sends a transfer. You can certainly use the same technique to track different things inside the database. You could track when they click a button, you can track when they open a form, you can track when they print a report. Everything in the database is trackable with a little bit of VBA. It's all dependent on how detailed you want your logs to be, and I cover all of that and more in exquisite detail in my Security Seminar. I created a detailed system log to track users, whatever they're doing, you can watch it.
Next up, same guy: so encrypting the front ends would prevent the user from going directly into the table to amend a record. How do you set the database so it requires a password and create a log of when the individual user has logged on?
The other video, which I think you posted this comment before you watched, covers tracking a log on. Encrypting your front end will require a password to get into the front end, but in a split database, your tables are not in your front end. Your tables are in the back end. So it gets a little more complicated now. You want to keep people out of your tables, you have to put a password on the back end as well. But all of your users are going to need that password too. There's a lot to it.
Again, go watch that user-level security video that I pointed you to. That covers a lot of it in more detail. The bottom line really is if you have sensitive data in your tables, like social security numbers or credit card numbers, you shouldn't be using an Access back end. You should be using SQL Server because that's the only thing that really can protect that data at a business level. Access is great for a lot of things, but super sensitive data is not one of them. But it is a fantastic front end for pretty much any database back end.
Can you limit the number of log in attempts? Sure, again, this goes back to what I said a few minutes ago. Can you do dot dot dot, absolutely. You can code it however you want to in VBA. Just set up a variable, count the number of times it tries to log in, and if not, give them the boot.
Timothy really took some time and watched a bunch of my videos, which is great. I love it.
My store to calculate it. What about things like that, or bank interest, where the rate can change? If the bank rate goes from, for the basic rate, goes from 17 to 15, you recalculate the form. Wouldn't it apply the new rate to historic transactions? Absolutely, and I've got several videos about this.
For example, in this video I show you how to copy the customer's address to an order at the moment that he places the order. If Jim Kirk places an order today, and then a month from now he moves and you change his address in the customer table, well now it's going to look like that order was shipped to his new address, which is not right. It was shipped two months ago to his old address. You have to copy that information over.
It's the same thing I show in my invoicing video in the extended cut where you copy product information to each order. Because if you go in your product table and update your pricing, you don't want all the orders that you have in your system changing pricing. You need that to be set at that moment. So that's when you have to copy data between tables, and it does require a little automation, either a macro or some VBA. I use VBA.
Both of those videos are covered in my full developer courses as well. But it's the same thing for your question. You want to know what that bank rate was as of the moment of the transaction.
And I believe I do have another video coming down the pipe in another couple days here on something very similar with tracking historical transactions. It's a very common problem. In fact, I tried to go through my entire expert course without doing this.
This question is about cascading combo boxes, and he wants to know if you've got your box set up like this with a code that represents a bank and a branch, could you select this code and have two different fields populated with the bank and branch? I'm assuming you want these to be two separate fields in whatever form you're working with. Yes, you can do this; just use an AfterUpdate event and copy those values over just like I showed in the copying the address video.
But if this code always represents this bank and this branch, there's no real need to do that if this can never change. The only time you really have to copy this is if this can possibly change, but if this code here always represents that bank branch, then really you're just representing a branch with it, because this branch is always going to be from that bank.
That's up to you.
And finally from Timothy, about multiple cascading combo boxes: he says, what if you have a number of places with the same name, for example Newport, Newport Shropshire, and Newport Isle of Wight? This would be the same thing here in the States if we had Buffalo, New York, Missouri, and Buffalo, Montana. They're all cities named Buffalo, but in different states.
It doesn't really matter because you're going to set up the state first. Set up New York, and then you're going to have Buffalo, Albany, whatever under that. Then you set up your next state, Missouri, and then you have different cities in there, like Buffalo, Buffalo. You're going to pick the state first, and then that state's going to show you the list of cities in the state. So it doesn't go the other way. If you want to go the other way, that's a whole different database. That's a whole different video.
But you could set up combo boxes in reverse if you want to pick the city first and then show a list of states that include that city. You could do it that way, and I've seen databases built that way. In fact, if you type in an address on a lot of e-commerce websites, you start typing in your street address and it just has your city, state, and zip, because it's pulling out of a giant database. It's all however you want to set it up. For this particular video, that's what I picked: it's top down, country, state, county, city, that kind of stuff.
All very good questions and thank you very much for posting them.
Next up, not tttttvbear says: Bro, just please do the stuff. Well, I am doing the stuff. If you're one of those people complaining that I don't get to the stuff fast enough, too bad. Go watch someone else's video. I like to answer questions, but I like to explain what I'm doing first and give you some background so that you have a better appreciation for the answer.
If all you're looking for is just a quick answer, either go do a Google search or ask ChatGPT because I'm here to teach you, whether you want to learn or not. So bye.
For everybody else, whenever I am going to answer your comments and questions in a Quick Queries video, I post that link there that just takes you to a page on my website that says, hey, your question has been selected. It'll be in the next Quick Queries video; come back and check it out for an answer. It's just easier than trying to type the little thing.
I intentionally put this on that guy's comment because I know he's not going to take the time to watch this video to find it, so he'll never see it. And if he does, hey, you found it, great, awesome.
Also, more importantly, I made sure that was at the end of the video.
So that's going to do it, folks, for today. That's Quick Queries 46. I hope you learned something. Live long and prosper, my friends. Enjoy your weekend, and I'll see you next time.
TOPICS:
Preventing duplicate values in Access tables
Using indexing to enforce uniqueness
Creating composite keys for multiple field uniqueness
Primary keys and AutoNumber fields in Access
Difference between single-field and multi-field indexing
Zooming features in Access forms and design view
Comparison of Shift+F2 zoom and Windows zoom
Upcoming zoom slider magnification in Access
Handling issues with the Monaco SQL editor
Stable release channels versus beta channels in Access updates
Setting up weighted grades in Access student database
Copying address data to orders at transaction time
Preventing updates to historical transaction data
Copying product data from products to order details
Avoiding use of underscores and spaces in field names
Fixing invalid syntax errors in Access expressions
Setting up read-only forms for single or multiple users
Creating user logins with username and password in Access
Automating front-end updates for split Access databases
Batch files for easily distributing Access front ends
Tracking user login and logout times in Access
Creating detailed user activity logs with VBA
Password protecting front and back ends in split databases
Limitations of Access back ends for sensitive data
Recommendation to use SQL Server for secure data
Limiting the number of login attempts in Access
Copying bank rates or interest rates at transaction time
Setting up cascading combo boxes in forms
Populating multiple fields from a single combo box selection
Handling duplicate city or place names with cascading combos
Top-down and reverse cascading combo box setups
COMMERCIAL:
In today's video, we're discussing a variety of questions from Microsoft Access users in episode 46 of Quick Queries. You will learn about preventing duplicate values in your tables using indexing and composite keys, hear opinions on the best ways to zoom in Access, and get updates on new features like the Monaco SQL editor. We also talk about setting up weighted grades, managing front-end updates across multiple users, tracking user activity, adding security with password protection, handling changing data like interest rates, and working with cascading combo boxes for locations. A little humor and lots of helpful advice along the way. 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 primary method recommended to prevent duplicate entries for a single field, such as an account number, in an Access table?
A. Enable referential integrity
B. Use field indexing with no duplicates allowed
C. Require data validation in forms only
D. Use macros to check for existing values
Q2. What is a composite key used for in Microsoft Access?
A. To index a single field with unique values
B. To ensure a unique combination of values across multiple fields
C. To enable referential integrity between tables
D. To automatically generate sequential numbers for each record
Q3. Why might stores historically have used a customer's phone number as a membership number in their databases?
A. Phone numbers are always unique
B. It allows for easy look-up of a customer account
C. Because phone numbers never change
D. It is required by Microsoft Access
Q4. What is the Shift+F2 shortcut commonly used for in Microsoft Access?
A. Running queries
B. Zooming in on the entire user interface
C. Opening the Zoom box to edit field contents
D. Saving the current record
Q5. What upcoming feature for Microsoft Access was mentioned as being on the Microsoft 365 roadmap?
A. Built-in PDF export
B. Zoom slider magnification in Design View
C. Dark mode for forms
D. Integration with Power BI
Q6. What is the main reason the instructor prefers to wait before using new beta features in Access, like the Monaco SQL editor?
A. He does not use SQL in Access
B. New features are always more difficult to use
C. Waiting avoids issues from bugs and instability
D. Beta features are not compatible with macros
Q7. How did the instructor suggest handling automatic updates to the front end of a split Access database?
A. Update each computer manually
B. Email users a new front end file
C. Use a batch file or automation tool to copy the updated front end from a server
D. Only update the back end since the front end never changes
Q8. Which statement best reflects the possibility of adding weighted grades to an Access database, as asked by a viewer?
A. It is impossible due to Access limitations
B. It is only possible using macros, not VBA
C. It is possible and will be featured in a future video
D. Weighted averages are only available in Excel
Q9. Why does the instructor caution against using underscores or special characters in field and table names in Access?
A. Access does not support any symbols in names
B. It can cause syntax errors and make the database harder to maintain
C. It slows down database queries
D. It requires additional VBA code to handle
Q10. In the context of Access, what is the recommended way to track exactly when users log on, log off, or perform actions like opening forms?
A. Create a manual log in Excel
B. Use VBA to record actions in a system log table
C. Access tracks all activity automatically
D. Require users to submit activity reports
Q11. For secure handling of sensitive data such as Social Security or credit card numbers, what is recommended over using an Access back end?
A. Storing the data in Excel
B. Using encrypted text files
C. Using SQL Server for the back-end database
D. Storing the data in plain text within Access
Q12. What can be done in Access to limit the number of failed login attempts?
A. It cannot be limited in Access
B. Use a table to track failed attempts and lock the user after a set number
C. Set a property in table design view for login attempts
D. Only allow admins to log in
Q13. Why is it important to copy data such as customer address or product price into order records at the time of the transaction?
A. To increase database speed
B. To ensure historical accuracy if the source data changes later
C. To comply with database normalization rules
D. So that duplicate data is always available
Q14. What is the main advantage of cascading combo boxes in data entry forms?
A. Allows data input in any order
B. Reduces data entry errors by filtering options based on prior selections
C. Restricts the form to one combo box
D. Automatically generates reports
Q15. If a code always maps to the same branch and bank, what does the instructor suggest regarding storing the branch and bank names separately?
A. Always store them separately to avoid confusion
B. Only store them if the code may change its mapping
C. Never store the branch and bank names
D. Use macros to store the information automatically
Q16. When might it be necessary to set up combo boxes in a reverse order, such as selecting the city before the state?
A. When cities are always unique
B. When users prefer to select cities first
C. When several states have cities with the same name
D. It's never necessary; top-down (state to city) is always best
Q17. What does the instructor say is the answer to most "Is it possible to..." questions about Access?
A. No, due to Access limitations
B. Yes, usually, but it may require extra work or another tool
C. Only if you are an advanced user
D. No, unless you know SQL
Q18. What was the instructor's response to viewers wanting him to get to answers more quickly without explanations?
A. He prefers to give full explanations for better understanding
B. He will change his style to be quicker
C. He ignores such viewers completely
D. He deletes their comments
Answers: 1-B; 2-B; 3-B; 4-C; 5-B; 6-C; 7-C; 8-C; 9-B; 10-B; 11-C; 12-B; 13-B; 14-B; 15-B; 16-C; 17-B; 18-A
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 am answering several of the questions you have sent me via YouTube, my website, and email, all centered around Microsoft Access.
Let's start with a question from Albert, who asked how to prevent users from entering duplicate values, like account numbers, in an Access table. This is a classic case for using indexing. When you have fields like Social Security numbers or phone numbers that must be unique, you should use indexing with no duplicates allowed. This is how the primary key or AutoNumber field in Access works by default. For situations where uniqueness must be maintained across a combination of fields, like an order ID and a product ID in an order details table, you should use a composite key. Setting up composite keys ensures that no two records can have the same combination of values. For instance, you might want to ensure you do not have duplicate combinations of first and last names for something like a membership roster. These concepts are covered in greater depth in dedicated tutorials on my site—check the links for more information.
Next, there was a discussion about preferences for zooming styles in my video tutorials. I use the Shift+F2 zoom because it provides a clear, concise view of specific content. Most viewers agree this makes things easier to read, so I will stick to this method in future videos. There was also some good news on the horizon for Access users: Microsoft is planning to add a zoom slider magnification feature, similar to what you see in Word, PowerPoint, and Excel. This was recently announced on the Microsoft 365 roadmap, and I look forward to seeing it in action.
The next topic was a request for a video about the Monaco SQL editor, a new feature in Access. While it has been available in the beta channel for a while, I tend to wait before using new features extensively, because new releases often come with bugs. Once I am confident that it is stable, I will record a video and share my experience. If you enjoy experimenting with new updates as soon as they are available, you can subscribe to the appropriate update channel for Access, but personally I prefer more stability.
I also received a suggestion about weighted grades in the Students and Grades template—things like tests being 40 percent, assignments being 30, and quizzes being another 30 percent. This is both possible and requested by several users, and it is already on my list for a future tutorial. If you want this feature prioritized, leave a comment. The more requests a topic receives, the higher it moves on my production schedule.
In general, if your question about Access starts with "Is it possible to…" the answer is almost always yes. Most business and database problems can be handled in Access, although some tasks—like advanced charting—are often better handled in other tools such as Excel. There were questions about using tools like Crystal Reports alongside Access; in my experience, Access reports are very powerful and suitable for almost all needs.
Next, there was a comment about brain wave device training and its relevance to Access training. Just for those interested, I shared a link with more information about brain wave training. Most people, however, are here for database help, so let's keep the focus on that.
David Chapman mentioned running into an invalid syntax error in one of my examples about numbering form items. In this case, the issues were mostly minor: spaces in the wrong places, underscores in field names, and possibly using a field name where there should be a table name. Access, like other programming environments, is particular about naming and spacing. Avoid underscores in your field and table names, and always double-check for extra spaces or incorrect names. Everyone makes mistakes like this when starting out, so do not be discouraged.
Cyberman1964 asked about how to handle allowing multiple people to make changes using forms. In my Read-Only Form video, I demonstrated a simple password box for granting access. If you need more flexibility, such as different logins for multiple users, you can use a user table with separate logins and passwords. I have a whole video that shows how to create your own login forms, which provide real security rather than just a quick workaround.
Timothy Whorl had several good questions. First, how do you keep the front end of an Access database updated across several users? The simplest method is to distribute updates manually or use a batch file to copy the latest front end from a shared server location to each user's computer before launching the database. There is also a more sophisticated template on my website that handles this automatically, checking for updates and offering to download them at startup.
Another question from Timothy involved tracking user log on and log off activities, as well as setting up restrictions so the database can require a password and maintain a log of individual users. You can track user actions with VBA, capturing events like opening forms or performing specific actions. For those needing detailed auditing, my Security Seminar covers logging all kinds of activities at a granular level. In terms of requiring a password, remember that with a split database, you also need to secure your back-end tables adequately. For very sensitive information, such as social security numbers or payment data, use a more robust back-end like SQL Server.
Timothy also asked about limiting the number of login attempts—a restriction you can add using a simple variable and a test in VBA to lock out users after too many unsuccessful tries.
Regarding how to store historic data, such as interest rates that may change over time, you should copy values like customer address or bank rates into the order or transaction record at the time it occurs. This ensures, for example, that if a customer moves or if interest rates change, historical records remain accurate to what they were at the time.
Timothy also had questions about cascading combo boxes where, for example, multiple places might have the same name but be in different regions. The solution is to filter your selection lists based on parent fields—the user selects the state or region first, and then only the relevant cities or branches appear as options. This avoids confusion where, for example, "Buffalo" exists in multiple states.
Another viewer, not tttttvbear, said I should just "do the stuff" and cut to the chase. To clarify, my teaching approach is focused on providing the foundations and context for understanding, not just jumping straight to the solution. This ensures you gain a deeper understanding and not just a surface-level answer.
When I respond to comments and questions in a Quick Queries video, I notify the question owner with a special link to let them know their query will be featured. This system ensures everyone knows where to find their answers.
That wraps up this Quick Queries session. I appreciate all your insightful questions and comments about Microsoft Access. If you want to see any of the topics discussed here demonstrated step by step, visit my website for the complete video tutorials.
Live long and prosper, my friends.
Topic List
Preventing duplicate values in Access tables
Using indexing to enforce uniqueness
Creating composite keys for multiple field uniqueness
Primary keys and AutoNumber fields in Access
Difference between single-field and multi-field indexing
Zooming features in Access forms and design view
Comparison of Shift+F2 zoom and Windows zoom
Upcoming zoom slider magnification in Access
Handling issues with the Monaco SQL editor
Stable release channels versus beta channels in Access updates
Setting up weighted grades in Access student database
Copying address data to orders at transaction time
Preventing updates to historical transaction data
Copying product data from products to order details
Avoiding use of underscores and spaces in field names
Fixing invalid syntax errors in Access expressions
Setting up read-only forms for single or multiple users
Creating user logins with username and password in Access
Automating front-end updates for split Access databases
Batch files for easily distributing Access front ends
Tracking user login and logout times in Access
Creating detailed user activity logs with VBA
Password protecting front and back ends in split databases
Limitations of Access back ends for sensitive data
Recommendation to use SQL Server for secure data
Limiting the number of login attempts in Access
Copying bank rates or interest rates at transaction time
Setting up cascading combo boxes in forms
Populating multiple fields from a single combo box selection
Handling duplicate city or place names with cascading combos
Top-down and reverse cascading combo box setups
|
