Quick Queries #9
By Richard Rost
3 years ago
General Read Error, Price Changes, ChatGPT, ACCDR
Welcome to another Quick Queries video brought to you by Access Learning Zone. I am your instructor, Richard Rost. Quick Queries videos are for me to answer your questions that may not need a whole video themselves. In today's video...
Topics Covered
- Access for Online Referee Performance Reviews, Pictures
- Two Helper Data Tables Instead of One
- General Read Error
- Relative Path: CurrentProject.Path
- Run a Query in an IIF Function
- Loan Payment Interest Amount
- Calculations Involving Years
- Product Price Changes in Older Orders
- My Company Info on Forms/Reports
- Change Enter Parameter Value Text
- How to Save DLookup Values to a Table
- ChatGPT At Capacity, Bard, Bing AI
- ChatGPT as a Stepping Stone
- Continuous Forms: Lock One Record, it Locks All!
- Global System Relationships
- Student Grades, Attendance
- Creating Forms in VBA
- Delete Contact, Takes Customer With It!
- ACCDR Files
- Highlight Entire Row of Continuous Forms
- Accounts Payable, Receivable
- Buttons Not Working
- Access on a Mac
- ACCDE Errors, ACCDB Doesn't
- Show Active or Inactive Customers
- ActiveX Controls
- Buying Access
- Replication IDs Hard to Work With
- Access Poem
- Adding Data to Different Tables
- Denormalization Broke My Mind!
- 100,000 Customers in a Combo Box
- Backup File Smaller
- Tech Support is NOT Guaranteed!
- Append a Copy of the Records to Table
- Notes Field Combined or Separate
Previous Quick Queries
Links
Keywords
microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, quick queries, qq, Access for Online Referee Performance Reviews, Pictures, Two Helper Data Tables Instead of One, General Read Error, Relative Path: CurrentProject.Path, Run a Query in an IIF Function, Loan Payment Interest Amount, Calculations Involving Years, Product Price Changes in Older Orders, My Company Info on Forms/Reports, Change Enter Parameter Value Text, How to Save DLookup Values to a Table, ChatGPT At Capacity, Bard, Bing AI, ChatGPT as a Stepping Stone, Continuous Forms: Lock One Record, it Locks All!, Global System Relationships, Student Grades, Attendance, Creating Forms in VBA, Delete Contact, Takes Customer With It!, ACCDR Files, Highlight Entire Row of Continuous Forms, Accounts Payable, Receivable, Buttons Not Working, Access on a Mac, ACCDE Errors, ACCDB Doesn't, Show Active or Inactive Customers, ActiveX Controls, Buying Access, Replication IDs Hard to Work With, Access Poem, Adding Data to Different Tables, Denormalization Broke My Mind!, 100,000 Customers in a Combo Box, Backup File Smaller, Tech Support is NOT Guaranteed!, Append a Copy of the Records to Table, Notes Field Combined or Separate
Intro In this video, we tackle a wide variety of viewer questions related to Microsoft Access and also touch on topics in Excel and database management in general. You'll hear advice on when to use Access versus other tools, discussions about table normalization, referencing images, managing forms and queries, working with data integration, handling parameter queries, and recommendations for tackling common database issues. There's also discussion about tools like ChatGPT, troubleshooting database problems, working with replication IDs, importing data from Excel, and suggestions on when to use conditional formatting, combo boxes, and more. This is Quick Queries 9.Transcript It's time for another TechHelp Quick Queries video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.
Quick Queries is where I answer some of the questions emailed in to me or sent in by YouTube or Facebook, or anywhere—my forums on my website—over the past few weeks and months. Maybe these questions don't quite merit a video on their own, but they're interesting nonetheless.
So, let's get started.
Matt asks: I'm a soccer referee and mentor looking for a way to have a mentor form that I fill out when I'm assigning a referee. I want to send this form to all the mentors and assigners to use. After everyone fills out the form, I want it to go to a secure database that we can all access so we can see how a referee is performing. The database would consist of referee info, including pictures, but also assignments that the referee has received. Is Access the program I need to learn?
Honestly, Matt, even though I'm a huge Access proponent, I think in this case Access might be overkill for what you're trying to do. You might just get away with a simple Word document and a shared Google Drive if you trust all of these people—all the different mentors and referees. Just give them access to a Google Drive shared folder, keep the pictures and the forms in there, and that'll probably do what you need.
Could you build an Access solution to do this? Yes. Access's main strong point is an inter-office database for people to work on in the same physical location. You can use it online—I have several videos on how to do that, and I'll put some links down below. You could set up an Access database on a cloud server or SQL server, but I honestly think, unless you've got some real tight security needs, you're probably best off with just a shared Google Drive folder and then use Word and maybe Excel, and store the pictures in folders too. I would start simple like that, and then later on if you decide you need a database, then get back to me for sure.
I'm not going to try to push everyone into an Access solution. Yes, Access can do this, but is it necessarily the best tool for the job? I don't know. Let me know, give me your feedback.
Next up, a different Matt has a question about the Helper Data video. He says: Regarding your Helper Data video, why did you use two tables instead of one? I understand normalization, but I thought the point was to reduce the quantity of small tables. I got the concept for your Helper table in another video, but I implemented it before watching this one. I put a type field in the Helper table instead of creating a Helper Type table, and it seems to be working. I just want to make sure I'm not missing something or setting myself up for some future pain.
There's always future pain, Matt, when you're dealing with Access.
For those of you not familiar with the Helper Data table, what this basically does is get rid of all the tons of small little tables you might have in your database. For example, if you want to track name prefixes like Mr., Miss, Mrs., whatever, suffixes, Junior, Third, Sr., lead sources, gender, any other little list—you won't have a million tables out here; you can store that all in a Helper table. It's basically a table full of lists.
What Matt is saying is that instead of using a HelperTypeID and linking to a HelperType table, he just put the description straight in here. And that'll work just fine. I like the two-table solution because it is better for proper normalization. Normalization doesn't always have to be perfect. In fact, I talk about it with multiple addresses—when you have orders for a customer, you want to copy that address to the order, which kind of violates normalization rules, but you want to know historical data—you want to know when that order was placed, what was the address at that point.
A lot of this is preference. I prefer it this way. If you have, for example, namePrefix here, here, here, and here, that's an example where you really do want to normalize that and you'll be making two tables instead of 50. I wouldn't copy namePrefix to each one of these records because that's an example where if you want to make one change here, now you have to change 30 records over here.
If a single-table system is working for you, great. Me, I can just look and say, "Okay, MaritalStatus is three," and then I come over here and, I guess I don't have any over here, but then you'd have a bunch of three records.
I wouldn't say you're setting yourself up for future pain. It's just if you are going to change the nameSuffix, you're going to have to change it in all of these records over here. That's why I have the two-table situation. I'd rather have two tables than 30; that's the point of the Helper Data.
I hope that answers your question.
Sirius says: I'm getting the following general reading error: Access Database Engine cannot find the input table or query 'Frame Assignments Summary.' Make sure it exists and that its name is spelled correctly.
First thing is, make sure it exists and that its name is spelled correctly. Second thing, you're breaking my cardinal rule number one, which is: Don't use anything except letters and numbers in your object names. Get rid of those spaces and that dash; that can cause weird problems in different places.
Also, a general reading error sometimes tells me that you've got hardware issues—you've got a hard drive problem or you might even have a networking issue, and if you can't find the file, it could be because you've got a connection problem there somewhere. So, I'm going to suggest run down my troubleshooter; that's my online troubleshooter for Access. It's got a whole big long list of stuff to try in order. Try all this, and if you get through this entire list and you still have a problem, well, it could be a hardware issue. But definitely, if you can, picture your object names, get rid of those spaces and that dash right there; I've seen that cause all kinds of weird problems.
Pisc Nikolov is asking if there's a way to reference images by the relative path instead of hard coding the full path name like C:\\Pictures\\whatever into your images.
When you're storing images in your file folders, you don't store images in your databases. If you haven't watched my "How to Deal with Images" video, go watch that. But yes, if you want to store a relative path, that's okay. I recommend storing a relative path based on the current folder that the database is run out of—have it as a subfolder under your database folder. To do that you need something called CurrentProject.Path. That'll give you the path that the current database file is running out of. I cover it in a couple different Extended Cuts, but I also cover it in my Access Developer 33 class. I'm pretty sure I cover it in these Extended Cuts too.
It looks like maybe in my "Check Icon File" video. Let's see here... Yes, I'm pretty sure I check it in there because here we're checking the database icon—the icon picture that goes with the database. And to know and define that, you've got to also know the CurrentProject.Path. So check this video out—I'll put a link down below.
Ramachandran wants to know if you can run a query as part of an If function. In other words, if the user logs on and is not an admin, execute this query; otherwise, execute this other query.
You can't do it as part of an If function, but you could do it in an event with an If Then statement. Go watch my If Then video. You do it right here: If the user is an admin then run this query, else if whatever, run this query, else run that query. So you can certainly do it, just not with a function. Functions are good for returning values, but you can't execute stuff with them.
Pitsi Cool asks if I could include the amount of interest along with the loan payment amount in the Loan Payments video. All I do is show you what that monthly payment should be—we give you the dates and then the amount of the payment, but we don't break down how much of that's principal and how much is interest. That's a little more complicated. I do cover it in my Access Expert Level 30 class. We'll use the PMT function and we'll break down, of that payment, how much is interest and how much is principal—and of course, it changes every month. The amount of interest gets smaller and the amount of principal gets bigger.
I also cover it in my Excel class at Expert Level 6. I do the same thing because the same functions work in Access and Excel. In here, for example, we go through a couple of different scenarios using the PMT function, calculating interest rates, all that good stuff too. So whether you want to learn about it in Access or Excel, I've got you covered.
Jim says he wants to set up a form to do simple calculations regarding the year, but he's getting the syntax wrong. He's got "equals 2023 minus ModelYear"; he gets a proper result but wants to use the current year function. So instead of 2023, it'd be valid. So he was going to try Year, and he tried Year(Date), but not getting a number.
It should look like this: Let's say you want to take the number of years since 1990. Right? "Equals Year(Date)". When in doubt, always put that open and close parentheses around Date. It's a function that doesn't take a value as input, but it returns a value. Now Access will sometimes remove those, but I always type it in anyway just to make sure. Make sure it doesn't put that inside quotes.
Then, from that, either subtract the 1990, or subtract whatever your Year field is if it's another field on a form, like a control for example, and make sure you put that inside brackets. Because again, Access will sometimes try to put that inside quotes for you. So there you go. That should work.
Need more help with this? Check out my Year-Month-Day video for more help on those functions, and another very helpful video as far as working with dates is my Date Math video.
This slightly weird question is presented without comment.
Linda wants to know if in my Invoicing database, if the prices change for a product, for example, will the older orders change? In the way that this video is designed, the way that this database is built—no, because you're just manually typing in the prices for each line item. So you have no product pricing to change.
However, in the Extended Cut for the members, I do create a product table and you can pick the product and hit a button and it adds it to the order, and that also copies the product pricing at that moment. So it will not change old orders.
Now, in my Expert classes, if you take my full course, this is something that I actually did intentionally to teach the users how if you put, for example, a product combo box in the order details (in the line items), and you let them pick a product and just link the price to it, if the pricing changes in the product table it will change in all of your old orders—and that's bad. So we have to use an event like an AfterUpdate event to update the pricing in the order and copy it from the product table so you have it at that moment.
Again, this is all covered in my Invoicing video in the Extended Cut for the members. I do show you how to make a product table, and then you click and you copy over the product information up into the order. We make a little product table with a combo box, you pick the product, you hit the Add button, and it puts it right up here in the order and it copies the information with an event so it's not linked to the original product.
It's the same thing, for example, if you want to have the address of the customer—you want to copy that address to the order so you have their address at that moment in time, and it's not going to change if the customer's address changes.
Richard wants to know—cool name by the way. He's got his company information stored in a table with a query and a form, of course, called Company (it should be CompanyT, but okay). He wants to know how to get that information to appear on his other forms, and I'm assuming reports, like your company's name and address and stuff, so that if you change it you don't have to change it in a whole bunch of other places. I get it.
I show how to do this in a couple of different classes. I have a TechHelp video that would probably be most helpful; I call it the System Defaults table, same thing—you've got some default values, last name, company name in there; you could put your company address, phone number, that kind of stuff and have that show up anywhere you want. Really, it's just a matter of using a DLookup wherever you want to have it. Just DLookup the values from your Company table and display them in a field on a form, on a report, wherever you want it. So DLookup is what you want.
Pre-Rack wants to know if it's possible to change the title "Enter Parameter Value" when you do a parameter query and it pops up the little message box. It says "Enter Parameter Value." No, you cannot customize that text to the best of my knowledge. If you use a query parameter you will always see "Enter Parameter Value" right there; there's no way around that that I know of. If anybody out there knows a way, let me know—I'm always looking to learn.
What you can do, though, is use your own form instead of relying on parameter query values. Make a form that's got whatever fields you want in it, and then that can have whatever title on top you want, and then the query will get the value from that form, and this video explains how to do that. It's a lot more elegant than parameter query values.
Another one posted without comment.
Be very, very quiet.
I get this question a lot. Moses asks: He's DLooked up a value—how do I save that to a table?
You have to use an event of some kind to handle that value. So DLookup gets the value, but you're going to need some kind of event to save it. If you've got a DLookup field just in a form, it'll display the value for you, but it's not going to save it anywhere. You'll have to use some kind of programming event or a macro, like an AfterUpdate event, for example. Or after you update a field, it'll DLookup something and then save it in the table. Or you can use a button with an OnClick event—there are a million ways to do it. It all depends on how you're trying to do it, but you've got to have some kind of code somewhere that says, "DLookup this value and save it in this table for me, or save it in this form field," or whatever.
Robert says he tried to use ChatGPT and received a message saying they were at capacity and to get on a waiting list. Yes, they've been pretty busy ever since they were released to the public. Actually, they have a paid membership and I've been using it a lot. It's 20 bucks a month to become a paid member, and then you get access even when they're full. I use it a ton—for ideas for things and for reformatting lists, like if you've got something in an Excel table and you want to reformat it as a comma-separated list, it's easy just to copy and paste it into ChatGPT and let it do that grunt work for you.
I've also been using it for making titles. I'll give it the text of my video and I'll say, "Make a catchy title out of this and give me some keywords that I can use," and so it's great at things like that. I also just recently got accepted to Bard, which is Google's version of AI. The nice thing about Bard is that it actually can go out to the live internet and look things up—you can ask it questions like, "What's the weather going to be like tomorrow," whereas ChatGPT is limited to the information that it was fed from 2021 and earlier. So Bard's got that. I'm sure they're going to add that to ChatGPT eventually as well.
I've been playing with both of them and the results are kind of mixed between them. Some things I like Bard better for; some things I like ChatGPT for. Microsoft has actually added it to Bing, so this AI is coming, so I'm spending some time learning it so I can see how best to integrate it with Access to teach you guys. It's getting there.
Yes, continuing on with ChatGPT, a lot of people have mentioned to me that they use it for Python. I've never used Python myself, but apparently it's better at writing Python code than VBA.
It does get tripped up on more advanced stuff, but like Garrett says here, it'll get you 90 percent of the way there. It's a great way to get started. Definitely double check the code before you use it in any kind of production environment; make sure that it's working perfectly, but it's a good stepping stone to get you moving in the right direction—to give you a framework at least of code.
I've done some stuff with it where it makes up properties on its own sometimes. It's doing the best it can, but it's good to get you started in the right direction.
Of course, people are asking ChatGPT to write poems about Microsoft Access—that's pretty cool. But if you have not yet heard Karl Donabauer's "Access Song" you must go watch it now. I will put a link down below in the link section. It is awesome.
Stargazer—spelling "Stargazer" like that, that's pretty cool—is saying he's using my tip for graying out fields, but it's not working with continuous forms—everything gets grayed out. Yes, a lot of these techniques that I show you will only work with single forms, because as soon as you switch to a continuous form, then it changes things. If you change the color or the locked property or pretty much any property of a control, it changes that whole group of controls. "FamilySize" is the text box for the entire form on every record.
There are some tricks you can play with, like conditional formatting, to have different colors, but you can't hide, unhide, lock, enable, disable different fields like that for different records in the same control on a continuous form—it just doesn't work. There are some advanced tricks you can play, but pretty much no. There are some advanced VBA techniques that can kind of get around that, and I cover them, I think, in one of my developer classes. If you want to know which class that's in, let me know and I'll try to find it for you.
Philip says—this sounds like a stupid question. First of all, let me stop you there. As Mr. Garrison says on South Park, "There's no such thing as stupid questions, only stupid people." No, I'm just kidding.
He said he's watched many Access tutorials and many of them created a table relationship on the relationship screen in the Database Tools menu. I noticed I've not done this. Are linked tables—well, linked tables...
Linked tables are linking to tables in other database files, which I'm assuming that's not what you're talking about. As far as linking tables—meaning making relationships between them—it's not necessary unless you want to enforce referential integrity.
To get a good understanding of that, watch my Relationships video first and you can see the relationships window right there. I talk about it in this video. Then, go watch this video on Referential Integrity. Basically, referential integrity is when you have two tables where one depends on the other. For example, let's say Customers and Orders, and you want to make it so you don't have any orders that are missing customers, or order details that don't have a parent order associated with them. So, you can enforce referential integrity to prevent that, and you can also create something called cascade deletes where, if you delete a customer, it deletes all of their orders and all of their order details. That can be very dangerous. I don't like that; I try to avoid that, if possible.
Now, the one major downside with creating relationships like that on the system level is: They do not work with linked tables. If you split your database where you've got a front end that's got your forms and reports and your code in it, and you split it so your back end is your table file, referential integrity and global relationships don't work across multiple database files like that—the tables all have to be in one database file. Even if you do something like upscale your database to SQL Server or something like that, these relationships don't work, so that's why I generally try not to rely on them heavily, because once you get to a certain point they're useless. They're good for simple, small databases, but if you do decide to upscale later on, you can't rely on them. You have to actually come up with coding solutions to handle if, for example, "I delete an order"—then my code has to delete all of the child line item records. That's not too hard to do, but you can't rely on cascade deletes for everything.
Hope that answers your question.
BNW says: We homeschool our two children. They want to put together a program to track their students' progress. Would I recommend Access or Excel? Would I be interested in creating this program?
Yes, I get asked this a lot. Lots of people are looking for something to track students' grades, attendance, transcripts, all that stuff. It's on my back burner. I've been wanting to put together a database like this for a while now. I've got a couple of different tip videos that cover some of it—like I've got an old tip video here that covers doing student attendance. I'll put a link to this down below. You basically use an app entry, and tracking grades is an example that I do in a lot of my Excel classes.
It's really up to you. Honestly, if it's just two kids, I would just do it in Excel myself. I tend to start most projects in Excel and work with it a little bit until I've got, you know, 20, 30, 40 records/rows. It's good for templating and for prototyping so I can see what fields I'm going to need, what kind of flow I need, what I want to automate. So I generally will start something in Excel and then port it over to Access, unless you want to learn Access. I mean, this is a good example of a database that I would use in a class to teach someone how to build an Access database. I would suggest start in Excel if you're not already using Excel, and that will give you a good template for starting your Access database.
I see that you took my Access Beginner 1 class because this is where you posted the comment. Just keep going—go to level 2, level 3. By the time you're up into the Expert classes, you'll be able to make relationships and do all that fun stuff. So yes, start in Excel and move up to Access later on.
Two kids—yes. If you have 200 kids, then yes, definitely Access, but that's my two cents.
Nice asks if he can create everything—tables, queries, and so on—with VBA code, but you can't create forms or reports with VBA. Yes, you can. But tables and queries are things you can create using VBA at runtime very easily, and it can be seamless to the user. You can change table structure, you can create whole queries, but in order to modify forms and reports, or to create whole new ones, it can be done using VBA. I don't have a lesson on it because it's something that is very rare, and I think I've done it maybe two or three times in my entire 30-year career, but it's not something I get asked a lot for. But it is possible. It's something that's more of a developer tool, because you have to switch the form into Design View and then you can use VBA to create or manipulate controls, and then you've got to save it and then flip back to Form View, so it's a bit of a pain. But it can be done. If enough of you out there want to see how to do it, post a comment down below. If enough people are interested, I might do a lesson on it.
Ryan, commenting on my Multi-Table Forms video, says he had an issue where he had the Customer table and the Contacts table joined together—probably with a query—and he had that in one form, and if he deleted the contact it would take the customer with it. Yes, that can be a problem when you join two tables together like that. You might not be sure which of the two is going to be deleted if you delete one. That's why, (a) I don't like to delete records, and (b) I have this video—the Multi-Table Forms video—where I explain you shouldn't do that. Don't bring together multiple tables like that into one single form. Do a form with a subform. It's okay to display information, but I'd lock those fields and make sure you're only allowing one of those tables to be edited. Go watch this video for more details on that.
Yann Home over on Facebook, in my Facebook group—yes, I still use Facebook once in a while, not often, I don't like Facebook—asks: What's the problem with using an ACCDR file instead of an ACCDB file? ACCDE actually takes the database file and encrypts it and makes it executable only so that people can't get into your forms and reports.
ACCDR stands for runtime, and you could take a normal ACCDB file and just change the file extension to .dr, and that will make it look like a runtime-only version and it locks a lot of stuff out so the user can't touch it, just like it would in the runtime version. It hides a lot of stuff on the ribbon, locks them out of some stuff. But if they know what they're doing, they can just rename the ACCDR file to ACCDB so then they can go in to modify stuff. So, if you want, make an ACCDE file first—an execute-only file. Then you can rename that to .dr if you want to.
I like to use just .de files, because then you can still give them access to some of the things that you might want to, like more options on the ribbon and stuff like that, without having to make a custom ribbon. I am making a video on just ACCDR files very soon, and I will be covering customizing the ribbon in my upcoming developer classes. So stay tuned. Thanks for the question.
Michael Duncan, one of my Gold members, asked if it's possible to highlight the entire row in a continuous form, like all the way across the row. If you've got a continuous form, that's all you get. Now, you can use conditional formatting to highlight the field that is selected—that's pretty easy to do. You can come into Format here, pick all of these guys, go to Conditional Formatting, New Rule. You could say "Field has focus" and then set it, like, yellow. Then, when you save this, whatever you've got clicked on will now have the focus and will be yellow. But you can't do the whole row across. Not even—I can't even think of a solution with VBA, because when a field has focus, remember that is the field, a whole column. So conditional formatting is the only thing that works across multiple rows in a continuous form like this.
The only thing I could suggest would be to use a list box if you want the whole row to be selected. If you make, let's say, a list box, create a list box for customers, bring in a bunch of fields—there you go, and we'll just finish. Now, with that list box, the whole row gets selected—that's probably what you're looking for. So if you need that, use a list box, and if you want to edit that record, put a double-click event on it. So, you double-click on this and it opens up the customer that you want. I show you how to do that in my OnDoubleClick event video—go watch that one.
Suhad Ask asks if I can create a simple supplier debt list payment in Microsoft Access. Yes, I notice you, I see you, I read all the comments eventually. I mean, I don't always get to them right away—as you can see, as of right now, this is 10 days old and I've got stuff that's years old in there. I go through them when I sit down; I go through the most recent ones first and go backward. I know I should do it the other way, but this is easier because YouTube doesn't give you a way to sort in reverse order.
But a lot of times I get questions from people asking me to help them build a database, but I have no idea what you mean: "a simple supplier debt list payment." What is that? I'm sorry, I need more information in order to help you. It sounds to me like, all right, supplier debt list: Do you want to basically track accounts receivable or accounts payable—one of those? Suppliers are your people you buy stuff from. Debt list payment—so are you... I don't understand which end of the transaction you're on. Accounts payable are things you owe to other people; receivables are things that are owed to you.
Now, as far as tracking accounts receivable—people that owe you money—I have a video for that right here; I'll put a link down below. Accounts payable you can just do in the opposite direction. Build the same thing, but instead of building invoices you're building bills that you have to pay, and then just track those as payables; it's the same thing backwards. I've got a whole seminar on tracking payables—I do aged accounts payable, all kinds of stuff. There's a link for that as well. Both I have them.
I just don't know what you mean by "simple." If you want simple, just make a table with who you owe, what, and then track when it's paid. Right? I need more information, folks. I can't—I wish I could read minds. But yes, I notice you, I see everything.
Fidna Fish asks: I just converted my database. Converted from what? Older version? Excel? What are you converting from? And none of my buttons or dropdown menus are working. Okay, "not working" doesn't tell me anything. What exactly is happening? You click on it and it doesn't fire off code? I don't know; I need more information, like the last guy. Give me some more specifics; screenshots help. I know, unfortunately, you can't post screenshots on YouTube, but you can go to my website, go to the Ask page—I've got a whole page with a whole bunch of different links on ways that you can ask me questions. Even if you're not a student, I have a visitor form you can go to.
The first thing that I always do when there's a weird problem is run down my troubleshooter. There's a video on this page and there's a list of stuff to try in order that will hopefully help you find your problem. If it's a problem that, like, I've seen a million times—like there's a particular error message, or something's happening that's repeatable—then, yes, it probably has an easy solution. But if it's something weird, like just buttons aren't working, run down this: compact and repair, make sure you're not running it online, restart your database, restart your computer, make sure you're running out of a trusted location (especially for you, if you've got VB code in your database and the buttons aren't working, chances are the code's not running because it might not be in a trusted location, so make sure you set that up). Lots and lots of stuff you can try in here; run down this—tons of stuff.
Amita asks if it's possible to open an Access database on a Mac. Nope. Next question.
No, seriously, I get asked this all the time. Yes, there are other versions of Microsoft products like Word and Excel for the Mac, but not Access. You'll have to run some kind of a Windows emulator. I'm not familiar with them; I don't use Macs. I've never used Macs at all—the last Apple product I think I even touched was an Apple II back in high school. So I'm not a Mac fan—sorry, that's just me. I'm a PC guy—PC and Android—so sorry.
If there are any of you out there who are Mac fans and you run Access on a Mac, I'd be curious to hear what you have to say, so post your setup and what software you're using down below in the comments section. Maybe you can teach me something and I can pass it on to everybody else.
This question comes up a lot in my forums on my website—here is someone asked a couple months ago, and yes, the guys... so Alex is my right-hand man. He has Mac stuff, he uses Mac, so he's got some different options and stuff down here. I'll put a link to this thread down below.
Next up, Jeff asks: Are there any situations where an ACCDE file will error and an ACCDB won't?
Yes. ACCDE is basically compiled machine language, so it is a lot less forgiving than ACCDB files are, because the VB interpreter will still be running through the ACCDB code as it runs, and if it doesn't hit a particular spot that's got a problem it might just ignore it. The way to check for this is to compile your database. I've got a whole separate video on compiling your database, and you might need to decompile your database as well. There's a link on this page that explains that. During the compile process, Access might catch any errors in your VB code that aren't necessarily being caught when you create an ACCDE file, and that could be what's causing the problem. If not, run down the troubleshooter.
Hullabaloo gave a bunch of background, and his question down here below is: He's got "active customers only" and an "isActive" query, and he says that in the video I told you not to base forms on queries.
I don't remember ever saying that. If I'm wrong, please tell me which video and at what time index I said that, because it's perfectly fine to base forms on queries. Sometimes I tell absolute beginners not to do that because it can confuse them, but generally, yes, you're going to use queries all the time to base on your forms and reports.
An easy way to switch that, though, if you want to see both active and non-active customers, is to base the form on the table and then just use a filter to either show active or inactive customers. That's the easiest way to do it. There are ways you could do it with code and stuff like that, but filters are the easy way.
But yes, I don't generally recommend not to base your forms on queries; it's perfectly fine. If maybe you're watching a video from 10, 15 years ago, I might have to go yell at younger me.
But here's a video on filtering in case you don't know how to do that. I would do this for what you're doing, instead of using a query, if you just want to switch between active and inactive customers, and you can save the filter state in the form design too. Go to the properties for the form and turn "Filter On Load" on.
Hatham asks if I could do more with ActiveX controls for ImageList and ImageComboBox. Honestly, I tend to stay away from ActiveX controls. I don't like them; they're very hard to distribute if you're sharing your database with other people around a network. If I can do something with just Access, I prefer to do that. There are very few controls that I actually like to use, but there are some that I've done some videos on in the past—like the ProgressBar control. If enough of you want to see me cover these controls, post a message down below in the comments and I will consider making a video on them.
Again, folks, not enough information to answer this question. I get tons of these—I just skip most of them. There's not enough information here for me to help you. I want to help you, but I need more specifics.
Refreshing a form with a table linked to an SQL database caused a problem... I don't know. I don't have your database. I can't see what the problem is. Sorry. Chances are, 9 times out of 10, without being able to actually look at your database I'm not going to be able to tell what the problem is—and I don't do that. So, sorry. Try my troubleshooter.
Anyone want to know if you can still buy Office Professional in retail stores like Best Buy or if they offer it in stores? I haven't been in a Best Buy in years—probably since well before the pandemic. I just don't—I shop online. So I don't know. I really don't want to pay the monthly subscription. I get it. I thought the same thing when all the software vendors started making everything a subscription, but now I actually kind of prefer it because, hey, you can cancel anytime. Microsoft Office is something that I've been using for 30 or 40 years—I'm going to continue to use it, so that's never going to go away.
But some other software I would rather spend, you know, $9.95 a month to use it for a month or two if I need it, and then if I decide I don't like it, I just get rid of it versus paying $200 up front for a piece of software and then I don't like it and don't use it anymore. So, I like the subscription model myself.
A quick search of the Best Buy website—yes, they still sell it. There it is: I see Home and Business, I see Home and Student, I see Personal—I am not sure what else they have... Family... So yes, check it out and see. I mean, this is something you could have googled yourself or gone to Best Buy and asked them if they have it, but I guess you can ask me, that's okay, I don't mind.
I do have a page on my website that explains buying Access, and the guys over at Access Forever just did—because Microsoft has always been very confusing with all of the different models—and their website is taking forever to load up. There it goes. Access Forever—check this site out, lots of good stuff on this site. They have all kinds of up-to-date bug lists and stuff like that. But Microsoft has always been very confusing with all of their different offerings and options and Enterprise and Home and Business. Here you go. Here's my table. They do have Office Professional—there's a link right here, one-time purchase. If you want a one-time purchase, that's up to you. But $8.25 a month—you can't beat that, come on. Use it for two years, that's $200. That's less than the cost of buying Office Pro 2021. You'd have to use it for, what, four years for it to break even, roughly. So I like subscriptions, sign me up for the subscription any day.
Sui says about my Search Form video that it's working fine except it doesn't include in the search any new records entered in the database since the query was built. That does not sound right, that sounds very strange. It almost sounds like, somehow, when you built it, you made a new table, maybe with a Make Table query, and the search form is pulling records from that because you're adding new records into a different table than the search form is looking at. So definitely check the record source of where the search form is getting its data from, because that should not behave like that. A query is basically, when you run the query, it looks at its data source—which is usually a table or another query—and it pulls in the records at that moment, so I have no idea. Again, without being able to see this database, I can't really tell you what's going on.
David says he's working with replication IDs and they're very frustrating to work with because a lot of standard functionalities don't work well. The worst part is you get a type mismatch almost everywhere. Yes, and you're right, there aren't a lot of resources for them. I personally don't use replication IDs that much. Replication IDs are great, but they're these big gigantic long strings; you've got to treat them as string values, whereas autonumbers are long integers.
When you work with replication IDs, they look like that—they're not strings, but there are functions to change the GUID to a string and then go back again (string to GUID). You have to treat them as strings when you're doing anything in your VBA code. They're a pain in the butt. I don't like replication IDs. The thing is, they guarantee almost that you're not going to get a duplicate value, but I personally find that it's better for Access databases to use random autonumbers because the random numbers— they're still long integers and they behave exactly like regular autonumbers do except the number is random instead of sequential. Yes, the chances of you getting a duplicate are a lot higher than with a replication ID, but it's still astronomical, and I cover the math in this video. Unless you're dealing with millions and millions of records, a random autonumber works better in Access and I think it's just fine. So instead of using replication IDs, I use these—that's me personally. But yes, okay, hope this helps. But yes, replication IDs are a pain in the butt, and the only reason I did a video on them is because some people ask me about them, and people do use them. I've used them before, mostly to interface with outside systems that have replication IDs, and they're just a pain.
Malco Robb said that he asked ChatGPT to write a poem about Access, and I'm just going to post this here without comment. Maybe I'll send this over to Karl Donabauer, and he can add this as a verse to another one of his Access songs. This is awesome, I love this.
Add Deta asks if it's possible to add data to different tables at the same time using forms.
You haven't given me enough information to properly answer your question, but if you are adding to related tables, then you can relate them together. I recommend a form with a subform, and then you can add records to both at the same time. But if you've got one standard form—that's like one single form—and you want to take that information and put it into multiple tables, that's going to require some coding. You need to make an unbound form, and then when you hit your Go button, it can take whatever data is necessary from whatever fields and put them in whatever other tables it has to go to, using either a recordset or an SQL statement. Again, with just what you've given me here, I don't know what you're trying to do, so visit the Ask a Question page and give me some more information.
Peter is asking a question related to my Association Database. Now, if you haven't seen this yet, my Association Database is for any group where you want to track anything about a group—whether it's a church, a membership organization, a golf club, bowling league, whatever. That's why I call it an Association; I tried to find a generic name for it.
In that database, I have a Person table where you can track people—members, for example—and then I have a separate Demographic table, which is in a one-to-one relationship with it, and it's got separate information related to a person, like their gender, their marital status, all that stuff.
He wants an opinion; wants to know why don't I just put that stuff in the Person table? It's a matter of preference; it's a personal choice. Personally, for me, I like to keep information that I might not necessarily have on a majority of my people in a separate table so it doesn't clutter the main table. With Customers, for example, if you have this demographic information only on a very small percentage of your customers, I don't like to waste fields in the Customer table by putting stuff in there that I've only got for a tiny percentage of them. I'm assuming with demographics, for example, you'd only have them on a small percentage of your members. If you've got them for 90 percent of your members, sure, put it all in one table—that's fine. But with demographics, especially, I find that you only have them with a small percentage.
Could you put them all in there? Is it wasting space? Access isn't going to waste space if you don't have any data in those fields; I just like less clutter in my main tables, that's all. So things that I only have in a small percentage of records, I put in a separate table. Right, personal design choice, that's all.
Neil wrote to me in an email saying that my De-normalization video broke his mind. That's some serious accusations there, Neil. I don't want to break anybody. He's like, "It broke all the rules of coding that I've lived by since 1994." Yes, denormalization is something that you want to teach new students—how to normalize their tables correctly—and then as you get into more advanced topics and you've been working with databases for a while, then you see that sometimes not doing that is better for performance. That's the whole reason behind this video.
Think about it this way: If you've got a million customers, and in your database those million customers each have 10 orders (that's 10 million orders), and you want to sort those customers based on their total order sum for their whole history, if you run that and you're running over a network so the data's got to come across the line, or even in a database server, it's got to crunch all those numbers, it's got to calculate all of those order totals for all of those orders for every customer—10 million times—versus just, if you were to denormalize and store that total in the Customers record (or even in a temporary table like you suggest, that's fine), that will definitely speed up the performance of that query. So you have to go against proper normalization, which says, "Don't store redundant data," if you're normalizing, but sometimes for performance issues you might want to. That's all I was trying to say. By and large, you do want to definitely stick to the rules of normalization unless you've got a good reason to not do that.
So, I didn't mean to break anything. But, yes, it kind of broke my mind a little bit, too, when I first read about it. I'm like, "Wait a minute, we can do what? Oh, okay," so that worked.
Tammy Tam asks: What do you do if the customer list is long—got 100,000 customers in your combo box, right? Is there a better option? Because, yes, if you've got 100,000 customers that combo box is going to take forever to load up. So, there's a couple things you could do.
What I would do is have a cascading combo box where you can pick something first to filter them—to break that list down. Even if your first combo box is a list of letters, or just like even A through D, E through M, whatever—break that list down, then the second combo box is more manageable. I'd recommend a cascading combo box—check that out and see if that helps you. In this example, I do city and state so you can pick the state first, and then you get a list of cities in that state so you don't get a big, gigantic long list of cities.
And yes, thank you very much for your comments—flattery will get you everywhere.
No idea how to pronounce that, sorry, but they're asking: If after backing up the database the file is a lot smaller than the original, is that fine? Generally, when you back up a database like I show in my backup video, the file should be the same size. Only if you compact the database will it become smaller, and here's a video on compacting. If you just copy the database file using backup, and the file is a lot smaller than the original, that says to me it didn't copy properly, so I would double-check and make sure.
For Rita asks a question, and what I'm commenting on here is your follow-up question—"It's been three weeks waiting for an answer." First of all, I appreciate that you're a member, but being a member doesn't guarantee anyone an answer. Saying, "It's been three weeks waiting for an answer," that's a little rude, sorry.
I go through my YouTube comments maybe once a month, so if you have a question and you want to have a better chance of having it answered, go to the Ask a Question page on my website or post it in the forums on my website. I don't check my YouTube comments that often because I get tons of them. Also, it's on my website very clearly that when you join as a member—even being a Platinum member—doesn't guarantee you an answer. Tech support is not guaranteed. It just gives you a better chance of me making a video out of your question. So don't complain that it's been three weeks and you haven't got an answer—sorry. There are people who've been waiting years and haven't gotten an answer.
I'm not a tech support guy. I make videos, and if your question is good, I'll make a video about it. I've got a fantastic group of moderators on my website that do a great job of helping me by answering people's questions if you ask them politely, and hopefully they'll give you an answer.
Now, as far as your actual question goes, that's not enough information for me to answer the question. So, you've got five rows of a text in your table—can you put them together? Yes, sure, but I need a lot more information than this to answer your question. How are they related together? How does the database know which five rows you want? You can make a field to select them, maybe add a Yes/No field to it, but I need a lot more information than this to answer this question.
Go to the Ask a Question page: you can come down here, and you're a student, so you can go to the appropriate forum and post your question there, and be patient, please. I do want to help everyone, and I do read all the questions—as you can see, it's been a month since I've gotten down this far in my comments list, but there are people who have been in there for years. It's been a month.
I get it; I just get way too many questions. I can't answer them all. That's why I'm very upfront with people—just because you are a member, that's not a guarantee that I'm going to answer your question. I will eventually read it, but I will definitely need more information to help you with this, so visit the website and post your question there. Thank you.
Malco Rub asks: Is there ever a time to use External Data, New Data Source, Excel—append a copy over—append query? Basically, that's like an import, but instead of importing it into a new table, you're just importing it into an existing table. It's just saving a step, basically. Right: External Data, New Data from File, Excel, you pick "append a copy." It's just going to take the data out of that Excel spreadsheet and drop it into this table.
I have never used this, to be honest with you. I prefer to import it and then use an append query to massage the data properly inside the database myself. It's a matter of personal choice. If the original table was created from an Excel spreadsheet and you kept all the same field names, then yes, I could see it saving you a step, but not something that I use regularly, but if it works for you, great, use it.
Azwan asks a really good question about my Tasks database: "Is the notes area for all the tasks combined, or will it change depending on what task you've selected?" Right, here's the Tasks database, and I use a continuous form with a Notes field down here. Now, this is one of those situations where I'm going to tell you: If you want to learn this stuff better, try it yourself and try to figure it out instead of me just giving you the answer. But as you click on each one of these records, you'll notice that this will change. When you put a field in the footer or the header of a continuous form, it's bound to whatever control you set it to, like this is Notes, and it will be for the current record. So if you click on this one, you'll see this guy's notes; if you click on this one, you'll see this guy's notes, and so on. Very good question. Yes, a lot of people ask that question, so there you go.
So, that'll do it for Quick Queries Number 9. I hope you learned something today. Live long and prosper, my friends, and I'll see you next time.
If you've got a question that you want answered, visit the Quick Queries page or the Ask a Question page. You'll find links to all this stuff down below.
Bye!Quiz Q1. What is the main reason Richard suggests using a simple Word document and shared Google Drive over Access for a referee mentor form system?
A. Access cannot store images
B. Access is overly complex for small collaborative tasks
C. Access is incompatible with cloud databases
D. Google Drive charges less money
Q2. What is the benefit of using a Helper table in Access?
A. To increase the number of tables in a database
B. To reduce small lookup tables and centralize list data
C. To replace queries with tables
D. To enforce referential integrity across unrelated data
Q3. Why does Richard prefer using a two-table solution for Helper data (Helper and HelperType tables)?
A. It complicates relationships
B. It is better for normalization and simplifies updates
C. It reduces performance
D. It prevents users from making changes
Q4. What is Richard's primary rule when naming database objects in Access?
A. Only use lowercase letters
B. Use spaces and dashes for readability
C. Use only letters and numbers without spaces or dashes
D. Begin object names with numbers
Q5. What property or function does Richard recommend to use for relative image paths in Access?
A. FileSystemObject.Path
B. CurrentProject.Path
C. GetImagePath()
D. Database.ImagePath
Q6. Can you execute a query as part of an If function in Access?
A. Yes, within the function definition
B. Only in a macro
C. No, but you can use an event procedure with an If Then statement
D. Yes, using a query macro
Q7. What is required to break down loan payments into principal and interest components in Access or Excel?
A. A SUM function
B. An IIF function
C. The PMT function and additional calculation steps
D. The AVG function
Q8. When calculating the current year in Access, what expression should you use?
A. "Year"
B. Year(Date)
C. [Year]
D. Date(Year)
Q9. In the Extended Cut for Invoicing, how are historical product prices preserved for orders?
A. Orders are always linked directly to product prices
B. The pricing is copied to the order at the moment of creation
C. Product prices automatically update every record
D. Historical prices cannot be preserved
Q10. What function does Richard recommend using to display company or default values on multiple forms or reports?
A. DLookup
B. DSUM
C. DCount
D. LookupValue
Q11. How can you customize the "Enter Parameter Value" prompt in Access?
A. By renaming the query
B. By using VBA code to control the prompt text
C. You cannot, but you can use a custom form instead
D. By changing Access settings
Q12. How do you save a value obtained via DLookup into a table?
A. It happens automatically
B. With an AfterUpdate event or similar code/macro
C. By using a calculated field in a table
D. No need to save it
Q13. What is a key difference between ACCDE and ACCDB files?
A. ACCDE files are text files
B. ACCDE removes all queries from the database
C. ACCDE files are compiled and less forgiving of errors
D. ACCDE files allow more user customization
Q14. Why does Richard not rely heavily on referential integrity in relationships for split databases?
A. Referential integrity is only useful in large databases
B. Referential integrity does not work across multiple files or SQL Server backends
C. It makes queries slower
D. It is only available in ACCDE files
Q15. For a small homeschooling scenario, which tool does Richard recommend starting with?
A. Access only
B. Excel, then transition to Access if needed
C. Google Docs
D. SQL Server
Q16. Can you create forms and reports entirely through VBA at runtime in Access?
A. No, it is impossible
B. Yes, but it is rarely necessary and more complex than creating tables and queries
C. Yes, but only reports
D. No, only macros can do this
Q17. When using continuous forms, why might field graying or locking not behave as expected per record?
A. Continuous forms only support one record at a time
B. Changing a control property affects all records in the continuous form
C. Conditional formatting solves all issues
D. You cannot use textboxes in continuous forms
Q18. What is the best practice when handling large lists (like 100,000 customers) in combo boxes?
A. Increase memory allocation to Access
B. Use a cascading combo box to filter the list
C. Link the combo box directly to the entire table
D. Use multiple listboxes
Q19. What is Richard's stance on normalization versus denormalization for performance?
A. Always strictly normalize data
B. Denormalize for performance in specific, justified cases
C. Never denormalize under any circumstances
D. Use random field names to improve performance
Q20. Why does Richard suggest starting new database prototypes in Excel before moving to Access?
A. Because Excel has better security
B. To quickly prototype data structures and workflow
C. Because Access cannot import data from Excel
D. To avoid using queries
Q21. What is the main challenge with replication IDs in Access?
A. They are more efficient than random autonumbers
B. They are easy to handle in VBA
C. They are large, treated as strings, and often cause type mismatches
D. They are required for all tables
Q22. If your Access search form does not find new records, what is a likely cause?
A. The database needs rebooted
B. The search is looking at a Make Table query or the wrong data source
C. The query needs to be refreshed every minute
D. Forms cannot display recent data
Q23. What is the only way to run Microsoft Access on a Mac?
A. Download Access from the Mac App Store
B. Use a Windows emulator or virtualization software
C. Install it alongside Word and Excel for Mac
D. Access is native to iOS
Q24. When adding records to multiple related tables at once in Access, what form design does Richard recommend?
A. One unbound form for all tables
B. Linked forms using queries
C. A form with a subform for the related child table
D. Only using update queries
Q25. Can conditional formatting highlight the entire row in a continuous form in Access?
A. Yes, always
B. No, only a single field at a time can be highlighted
C. Yes, if you use VBA code
D. Yes, but only in ACCDE files
Q26. What is Richard's advice about using ActiveX controls in Access?
A. Use them as much as possible for better performance
B. Avoid them if possible due to distribution issues
C. They are required for all image management
D. Only use them in single-user databases
Q27. What does Richard say about being able to buy Office Professional as a one-time purchase?
A. It is no longer available anywhere
B. It can only be bought online via subscription only
C. One-time purchase versions still exist, but he prefers subscriptions
D. Only available outside the US
Q28. When you backup an Access database and the file size is much smaller than the original, what should you suspect?
A. The backup is successful and normal
B. The backup file did not copy properly; check your method
C. Access automatically compresses files on backup
D. You need to reindex the database
Answers: 1-B; 2-B; 3-B; 4-C; 5-B; 6-C; 7-C; 8-B; 9-B; 10-A; 11-C; 12-B; 13-C; 14-B; 15-B; 16-B; 17-B; 18-B; 19-B; 20-B; 21-C; 22-B; 23-B; 24-C; 25-B; 26-B; 27-C; 28-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 am answering a variety of questions that have come in from students through email, YouTube, Facebook, and my website forums. These are questions that may not require a full, dedicated video, but are still interesting and helpful for anyone working with Microsoft Access.
To start, Matt wrote in because he is a soccer referee and mentor. He wants a form for assigning referees and to share it among mentors, with information and photos collected in a secure, shared database. He asks if Access is the right tool. While Access could handle this, I advised him that it might be overkill for what he described. A simple solution, such as a Word document or an Excel sheet shared via Google Drive, might meet his needs, especially if security is not a major concern. Access is best suited for multi-user databases in a local office network or with cloud or SQL hosting if advanced features or strong security are needed. I suggested he start simple and only step up to Access if he finds those tools limiting.
Next, a different Matt asked about my Helper Data video. He wondered why I used two tables instead of one, as he thought reducing the number of small tables was a goal. He had put a type field right in the Helper table and wondered about potential future issues. My answer focused on normalization. Using two tables supports proper database normalization, which centralizes information and avoids redundancy. It prevents having to update multiple records if, for example, a category name changes. However, a single-table approach may work fine in a small system or if you do not anticipate such changes. Using two tables is generally safer but not strictly mandatory if your current solution meets your needs.
Sirius asked about a reading error in Access, with the message stating it could not find the table or query 'Frame Assignments Summary.' The first step is to check that the table indeed exists and that the name is spelled correctly. Also, I always recommend using only letters and numbers in object names, because spaces and dashes can cause unpredictable problems. If the object exists and is named properly, such an error might indicate a hardware or network problem, especially if the database is stored on a server. I advised running through my troubleshooting guide to isolate and resolve the issue.
Pisc Nikolov wanted to know if images can be referenced by relative path instead of hard coding the full path. The answer is yes: you can store images in subfolders next to your database and use Access's CurrentProject.Path property to create a relative reference to those images. I have covered how to do this in developer classes and some video tutorials, so be sure to check those if you want step-by-step guidance.
Ramachandran asked if you can run a query as part of an If function. While you can't execute a query directly inside an If function, you can use an If Then statement within an event (for example, after a user logs in) to run one of two queries depending on the user's admin status. This logic is best handled in VBA with events, not with Access functions.
Pitsi Cool was interested in displaying the breakdown of loan payments, separating principal and interest, as opposed to just the monthly payment. While my Loan Payment video just shows the overall payment, my Expert Level Access and Excel classes cover breaking down principal and interest using the PMT function and related formulas, which is useful for creating amortization schedules.
Jim wanted to calculate the difference between the current year and another field, like ModelYear, but was having trouble with the syntax. The correct way in Access is to use Year(Date) minus your ModelYear field, making sure to use square brackets around field names and parentheses for the Date function. More details and examples are found in my videos on date functions and date math.
Linda asked if product price changes in the products table would alter prices on previous orders. In my basic invoicing database, order pricing is entered manually and is not linked to the current product price. In my Extended Cut for members, I demonstrate how to create a product table, and when adding a product to an order, the current price is copied over so that old orders remain unaffected by later price changes. This approach is important for historical accuracy.
Richard wanted to share company information, such as address and phone, across multiple forms and reports. The recommended approach is to use a System Defaults table and use DLookup to retrieve that information wherever it is needed, ensuring consistency and making global updates easy.
Pre-Rack asked if it's possible to change the wording of the "Enter Parameter Value" prompt that appears with parameter queries. Unfortunately, this text cannot be customized. However, creating your own dialog form for parameters gives you more control over prompts and lets you design them however you like.
Moses wanted to know how to save a value acquired with DLookup into a table. DLookup by itself just displays a value. To store it, you need some VBA, such as in an AfterUpdate event or a button click, to take the looked-up value and write it to a field or table.
Robert inquired about using ChatGPT for Access solutions and mentioned running into availability problems. Subscription access resolves that. I also commented about using AI tools like ChatGPT and Google Bard for various tasks, from reformatting data to generating video titles. ChatGPT is especially helpful with code examples, although you should always verify its output.
Stargazer reported an issue with conditional formatting and enabling/disabling fields in continuous forms. Properties like Locked, Enabled, or background color apply to all records in continuous forms, so you cannot change these properties for individual records. Conditional formatting may help with colors, but per-record changes are limited on continuous forms.
Philip asked whether creating relationships in the Access relationships window is necessary. The answer is you only need to do that if you want to enforce referential integrity at the database level, such as preventing orphaned records. However, global relationships don't work across split databases (front end/back end) or with linked tables, so I tend to handle referential integrity with VBA where possible.
BNW is homeschooling two kids and wants to track their academic progress. While Access is a good solution for large-scale student tracking, for just two students, Excel is probably easier to start with. Excel works well for small-scale tasks and as a prototyping tool before moving on to Access.
Nice wanted to know if tables, queries, and other database objects can be created using VBA, not just by hand. It's entirely possible to use VBA to create and modify tables, queries, forms, and reports—though it's rarely done for forms and reports. Most development relies on using VBA to alter the data structure or automate repetitive tasks, but form and report creation by code is complicated and not commonly needed.
Ryan had problems with using a single form for two joined tables: deleting a contact led to the associated customer also being deleted. This is exactly why I recommend using subforms for related data rather than combining everything into one form, which confuses Access about what should be deleted or edited.
Yann Home asked about using ACCDR versus ACCDB files. ACCDR files are essentially runtime versions of your database. You can convert an ACCDB to ACCDR by renaming the extension, which locks out some features. However, renaming the file back lets savvy users regain full access. If you really want to protect your design, compiling it to ACCDE is preferable. Customizing ribbons and more are topics covered in my developer classes.
Michael Duncan wanted to highlight entire rows in continuous forms. Unfortunately, with standard Access forms, you can only use conditional formatting on fields, not on rows. If highlighting the full row is a must, use a list box instead, which supports row selection across all fields.
Suhad Ask wanted to create a simple supplier debt list payment system in Access but did not provide enough detail for me to offer advice. Generally, accounts payable or receivable systems can be built in Access, and I have resources covering both. More information about your specific needs would help me suggest a solution.
Fidna Fish converted a database and now the buttons and dropdowns are not functioning. Without more specific descriptions or error messages, I recommend running through my troubleshooting steps, including Compact and Repair, checking for trusted locations, and ensuring that macros or VBA code are enabled.
Amita asked whether Access can run on a Mac. Unfortunately, Access is only available for Windows. You could use a Windows emulator or virtual machine on a Mac, but there is no native Access application for MacOS.
Jeff wanted to know if some situations could cause an ACCDE file to error out when an ACCDB would not. ACCDE files are compiled and less forgiving since they cannot ignore errors that might be skipped elsewhere. Compiling and decompiling your database is the best way to identify and resolve hidden code errors.
Hullabaloo mentioned I once stated not to base forms on queries. If I ever did, it was likely old advice for beginners, but it is perfectly normal to use queries as record sources for forms and reports in Access.
Hatham wanted more about ActiveX controls like ImageList and ImageComboBox. I generally avoid ActiveX controls because they complicate database distribution and compatibility. Most database functions can be accomplished with native Access controls, but if there is enough interest, I might cover more ActiveX examples in future videos.
Someone asked about troubleshooting issues when refreshing forms with a table linked to an SQL database. Without more details or access to the database, it's impossible for me to provide a specific fix. General troubleshooting steps and my troubleshooting resources are recommended.
There was a question about whether you can still purchase Office Professional in stores like Best Buy. It is still available, but options change often. I personally prefer the subscription model for many types of software nowadays, since it allows flexibility and usually costs less over time. For more information on buying Access, visit my website for an up-to-date review of the different versions and licensing options.
Sui brought up an issue in his Search Form where recently added records are not appearing in the search results. This suggests the search form may be referencing the wrong data source—perhaps an old static table instead of the current one. Checking and correcting the record source should resolve the issue.
David wrote about trouble using replication IDs (GUIDs), saying that standard Access functions do not handle them well. Replication IDs are clunky and are treated as strings in Access code. Unless you really need guaranteed uniqueness for integration with other systems, I suggest using random autonumbers, which are easier to work with for most Access databases.
Add Deta asked about adding records to multiple tables at once from a form. To do this, a subform for related data is best. If you need to write new records simultaneously to unrelated tables from a single form, coding a custom save event is necessary.
Peter questioned why, in my Association Database, I keep demographic fields in a separate table rather than in the main Person table. My approach is to only put fields in the main table that are widely used for most records; less common fields go into a related table to reduce clutter. This technique is mostly a matter of preference and convenience.
Neil said my De-normalization video challenged his preconceptions about normalization. In some cases, breaking the rules of normalization to denormalize data can actually improve performance, especially when you have to sum or aggregate massive record sets repeatedly. This is a trade-off that experienced developers sometimes make for speed.
Tammy Tam wanted to know the best practice for handling combo boxes with very large lists, such as 100,000 customer names. The answer is to break the list down using cascading combo boxes. For example, let users select a region or the first letter, which filters the main list to a manageable size.
Another question asked about the backup process causing the Access database file to shrink in size. Normally, a simple backup should not reduce the file size, unless the database is compacted. If you copy the file only and the new file is much smaller, it's possible the copy was incomplete or corrupted. Always double check the backup and consider compacting the database for routine maintenance.
For Rita was frustrated by not receiving a prompt answer to her question, and asked how to combine five text rows in her table. While combining records is possible, I need more detail about how the records are related to give an exact answer. I encourage students to use my website's forums for detailed questions, as I cannot answer all YouTube comments quickly.
Malco Rub asked when one might use the External Data, New Data Source, Excel "append a copy" feature, as opposed to importing and using an append query. This feature lets you quickly import Excel data directly into an existing table. I rarely use it, preferring to import and then process data within Access, but it is an option if your columns match exactly.
Azwan asked if the notes area in the Tasks database applies to all tasks or only the selected one. When using a bound control in a continuous form footer or header, it displays the value for the currently selected record, not all tasks combined.
That concludes this edition of Quick Queries. I hope these discussions have helped clarify some common and not-so-common questions that come up when working in Access. If you have your own question, please visit the Quick Queries or Ask a Question pages on my website for more resources.
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 Choosing between Access and simpler solutions for shared forms
Helper Data tables: single-table vs two-table normalization
Referencing images by relative path with CurrentProject.Path
Executing queries using If Then logic in events
Breaking down loan payments: principal and interest
Using Year(Date) for calculations in Access
Handling price changes in invoicing and order history
Displaying company info from a table with DLookup
Customizing parameter prompt forms in place of query popups
Saving DLookup results to a table via form events
Access runtime and ACCDR vs ACCDE/ACCDB differences
Highlighting rows and fields in continuous forms
Impact of form design on multi-table record deletions
Why and how to use relationships and referential integrity
Access vs Excel for tracking student progress
Creating objects (tables, queries, forms) at runtime with VBA
Continuous forms: limitations of control-level formatting
Conditional formatting and list boxes for record selection
Common Access object naming best practices
Access error troubleshooting and the importance of trusted locations
Options and limitations of running Access on a Mac
Compiling and decompiling ACCDBs vs ACCDE error behavior
Filtering forms by status: queries vs filters
Use and drawbacks of ActiveX controls in Access forms
Continuous forms: editing limitations and workarounds
Access DB split: system relationships and referential integrity
Best practices for backing up and compacting Access databases
De-normalization tradeoffs and performance considerations
Cascading combo boxes for large data sets
Replication IDs vs random autonumber primary keys
Form design for adding to multiple related tables simultaneously
Storing demographic and optional data in separate tables
Handling long lists in combo boxes with filtering
Importing data from Excel: append vs import and query
Notes field behavior on continuous forms
Troubleshooting dropdowns or buttons not working after conversion
Techniques for tracking accounts receivable/payable in Access
Using system defaults table for company info display
Parameter queries vs custom search forms
Access search forms and missing new records
Using ChatGPT/Bard for Access scripting and code suggestions
Form/subform design for adding data to related tables
Switching data sources or file extensions: ACCDR, ACCDE, ACCDB
Using filters to show active/inactive records
Mac/PC differences and Access compatibility options
Highlighting selected rows in continuous forms with conditional formatting
Database size differences: backup vs compacting
Managing user requests and best way to ask for help |
