Quick Queries #11
By Richard Rost
3 years ago
Separate Tables for Years, Ambiguous Name Detected
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
- Zoom In Out in Design View
- Macros vs VBA Pros and Cons
- Separate Tables for Years
- Ambiguous Name Detected
- Change Font in Zoom Box
- Macro Recorder
- Convert Macros to Visual Basic
- Read the Description Below Videos
- Type in a Report?
- Confusing Membership Options
- Date Math Calculator
- Opinion on Dataverse
- Change Direction of Language
- Securing Your Tables
- Cascading Combo Boxes in Split Forms
- Creating Global Relationships
- Display Date Only Month/Year
Previous Quick Queries
Links
Keywords
microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, quick queries, qq
Intro In this video, I answer a wide range of viewer questions about Microsoft Access, including whether it is possible to zoom or resize windows in design view, the pros and cons of using macros versus VBA, best practices for organizing data across multiple tables and years, how to resolve the "ambiguous name detected" error, font size settings in the zoom dialog box, macro recording options, working with trusted folders, updating reports dynamically, and basic Access security concerns. I also discuss topics like split forms, global relationships, formatting dates, and Access compatibility with Dataverse and SharePoint.Transcript This is number 11. I can't believe I've done 11 of these already. It's quick answers to queries about Microsoft Access, which may or may not contain actual queries about queries.
This is where I answer the questions that I have gotten over the last couple weeks that maybe do not merit an entire video amongst themselves, but they are still good questions on the list that people ask, so I would like to answer them.
First up we've got Stephen, who asks: in design view, my windows seem super zoomed out, requiring some pretty precise maneuvering of the mouse. Is it possible to resize the windows, zoom in and out?
No, you can't, sorry. That is one of the things in design view - you are stuck with what they give you. It is one of my pet peeves with Access, with Microsoft I should say, because with Word, with Excel, with PowerPoint you can zoom in and out while you are working. You can't do that with Access. No, sorry, you are stuck. Access once again is the red-headed stepchild of the Microsoft Access and Microsoft Office family.
Now with that being said, in design mode you are just stuck. Sorry. I mean, there is Windows Screen Magnifier, there are a whole bunch of different tools you can use to zoom in and out. I know - I have really crappy vision myself, so I sometimes use a zoom magnification tool, but when you are working with your database, when your users are working with your database, there are some things you can do. For example, you can use the zoom dialog box to zoom in on a specific field and make that text bigger. You can resize forms for different screen sizes - as you make these bigger or smaller, you can make these fields larger, you can make the font sizes larger.
I even put together something called the form profile template where you can specify different screen sizes for different devices. You can have the same database used on a cell phone, a tablet, a desktop PC, a big screen, small screen, whatever. Again, all of these things are for using the database, and not necessarily designing the database.
So hopefully, eventually, one of these days Microsoft will give us the ability to zoom in and out of a form while you are designing it, and I will look forward to that day. But no, Stephen, you are not crazy. You just can't do it. Sorry.
Next, we've got Carrie who asks: I want to learn VBA, but since I haven't started your developer classes yet, I've been getting pretty creative with macros. I never thought about it much for this video but now I'm curious: what are the pros and cons of doing things one way or the other? Obviously one pro of using VBA is that it is probably a million times more versatile, whereas macros can be constructed more easily by novices. But for all tasks that can be accomplished by a macro, I worry that there is a cost involved, like for example, is it slower, will there be problems when a DB gets scaled up to SQL Server?
Yeah, well, you nailed a couple of things on the head. Obviously macros are easier for people who are not programmers because you can visually pick commands from a list, and there are safe commands that will work even if you share the database with someone else - things like open form, so you don't have to worry about the warnings disabling stuff like that, whereas VBA gets straight disabled unless the user specifically puts it in a trusted folder or allows those things to run.
VBA definitely is a lot more versatile, and once you learn VBA and get proficient with it, it's much, much faster, I think, to type in code than it is to sit there and click and drop down those combo boxes and find the commands you are looking for. I get frustrated with the menu options myself - I can't stand them anymore. On the other side of that, you have to learn the commands yourself. I have been doing this for many, many years, so I know them all mostly by memory, but you need to have reference material for a lot of that kind of stuff if you are not already familiar with it.
As far as speed goes, I really don't think that macros are that much slower than VBA. I mean, both will get compiled to machine code when the database is running, so I don't think that's an issue, especially not with modern computers. The speed is fast for both things.
Now upscaling to SQL Server - well, you are not going to use your VB code or your macros in that case, so that's a whole different can of worms that gets opened when you do that, so I wouldn't worry too much about that. But if you are going to upscale your database later on, you can still use your front end, your Access front end, with SQL Server. So whether you built your database with macros or VBA code, that really doesn't matter, because you are just going to connect your tables to SQL Server and still use Access as your front end, so that doesn't matter there.
I have been planning, it's been on my to-do list for years, to do a more in-depth video on the pros and cons of macros versus VBA. I got a bunch of stuff on there, but I don't really use macros that much anymore. Since Microsoft killed off web apps - I was gearing up to get all excited for macros back in 2013, but then they killed the web apps, so I was like, why bother? I stick with VBA now pretty much exclusively.
But I usually recommend, for beginners, start with macros and see if that's something that you enjoy doing, and if you want to move into VBA, then upscale to that.
This one was submitted on my Facebook group - yes, I still have the Facebook group, even though I hate Facebook - by Jenna. Jenna is looking for help with the database, and she says she has different tables set up for different years, so not everything is clogged up in one table.
I'm going to say, personally, I think you should put everything in one table. Have a field for year or an actual date. But I see this a lot. I see this so much, in fact, I have another video coming up in a couple of days where someone sent me an Excel workbook that's got a whole bunch of stuff in it - different sheets, each sheet represents a different year, and they want to get it all together in one table properly, and I'm going to show you how to do that. But this is one of those things where I see this all the time - don't do things like a separate table for each year or a separate table for each client. No, you should put all that stuff in one table.
That's why you have a database with a customer table for all your customers, or an order table for all of your orders. If you want to archive older stuff, that's fine, and I have videos to show you how to do that, but save that for stuff that you are pretty much sure you are no longer going to need, like 10-year-old orders and stuff.
Now, I did answer Jenna, and I said you could use a union query if you want to. You can use a union query to put data together from those multiple tables. If you want to consolidate that all in one recordset, basically one query that's got all that information in it, take that and use an append query and put it all together in one table like it should be. But I definitely don't recommend storing data like that in multiple tables - right, one table, one table.
I'll put links to these things down below if you want to learn more about union queries or archiving records.
Next one comes from John. John says he's getting "ambiguous name detected." What does that mean? Well, it's exactly what it says it means. Ambiguous means "I can't tell what you are talking about" - it could be one of many things. He is using a function called getSetting, which comes from some of my code that I produced. Generally, I see this when people are copying and pasting code into their database, like if you are taking something out of my code vault or something else you found online and you paste it into a module in your database, and now Access says "ambiguous name detected" - what does that mean? You have got getSetting in there twice.
Do a global search and replace, try to find that, and I guarantee, I'm not going to say 100 percent, but 99.9 percent, you have two getSetting functions in your database. That is what that means; you have to change one of them.
Next up is Pat. Pat sent me an email and he said he knows you can click on Shift-F2 when you are in a field; it will pop up that zoom window, and he says it typically comes up size eight font, which is very tiny. He would like to default it to about size 12.
Then I responded with, "Yeah, there is a little font box in the bottom left corner where you can change the font." Some people don't know that. Now, Pat got back to me after that, and I forgot to save a screenshot of the email, I don't feel like going to find it right now, but Pat was really asking if you can change that in the database itself, so when you go ahead and give a copy of that front end to someone else, if that will save, if that setting will stick anywhere, kind of like when you design a form, you can specify the font size.
I don't think so. I'm not sure. I mean, I'm pretty sure that Access saves that in the registry key somewhere or some kind of a settings file, but I don't know where it is. I spent a few minutes trying to look for it in the registry, and I didn't see it. I don't know. So if anyone out there knows where that setting is kept, tell me, let me know. What I would suggest is, if that's the case, you could very easily make yourself your own zoom box. Just build a form, set it to whatever settings you want, and then you hit whatever key you want to launch it, it opens up. If you want to see how to build something like that, let me know, I'll show you.
But no, Pat, to answer your question, I don't know how to do that, so that would be new to me.
Here is another question/comment about macros from Jose. Jose was asking my opinion if macros are good for beginners, which we just talked about a minute ago, but then Shadow Dragon came up and said something about the Macro Recorder. Now, the Macro Recorder is something that exists in Excel and in Word, but again, Access is the red-headed stepchild, we don't get a Macro Recorder.
If you are not familiar with the Macro Recorder in Excel, you can start recording a macro, give it a name, Macro1, whatever. Now everything you do, right - select something, bold it, change the font, do whatever, stop recording - all that gets saved in a macro in the Visual Basic Editor. Go over here to macros, there is your macro, Macro1, go to edit, and there it is. That would be cool to have in Access, don't you think? Open form, go to this field, do it. We don't have that.
You can take macros in Access and convert them to VBA, though. If you have got a macro, like here I have got this Open Main Menu one - all it literally does is open the main menu, and I use that for my button up top here. If you go into design view, you'll see right here there is this "Convert Macros to Visual Basic." Click on that, and conversion finished, and there it is. It just basically created a Visual Basic version of that, and that window wasn't it, here it is down here, convert to macro, there we go, that's what it created, and it adds error handling functions for you, and there is the one line, the one command that's in my macro. That is a decent way to learn some VB. I learned a lot from that myself back in the day when I was first learning VB, so check it out.
This next comment comes from Nick, and he says "I'll still do it in VB code, don't like macros at all." Now, this was a comment on my Is Trusted video, where I show you how to make a macro that can tell if the database is running from a trusted location, because if it is not, you get the little security risk warning which disables your VB code. The problem, Nick, is that you can't do anything in VB code because they get disabled, so you have to use a macro to tell if the database is not in a trusted folder, and then you can give the user a warning that says "You're not in a trusted folder." So it's like a catch-22. Can't use VB code in that case.
This was another comment on that same Is Trusted video from FOTEOS, and he said "Live long and prosper, Richard. Live long and prosper." Obviously YouTube, he said, "How do you set a folder as trusted? Can you point me again to the explanation video, viewers, as I missed your advice?" This is something I get from a lot of people. When I mention other videos, I always put links in the description down below the video.
I know YouTube does a good job of hiding this stuff down here, but see this gray box? It might change by the time you see this, but this used to be different. Now it is a gray box, click right here where it says "Show More." There are all the links and stuff down there. There is the Is Trusted - that'll take you to my website. Here is the prerequisite video on trusted locations - just click there, it'll take you to my site, there's the trusted location video. See, it's always down there. YouTube does a good job of hiding that description box - I wish it was bigger, but it's not; you have to go down there and click on it and find it.
A lot of people miss that, you are not alone. Once you open it up, though, it's right there, there's all the stuff.
Caroline wants to know if you are able to have a report open whilst you type - I love that word "whilst" - whilst you type into the text box and dynamically update the report. Reports are just for viewing, printing, emailing, or making PDFs. You can't actually type into a report. That is what forms are for; forms are for your data entry.
You could - and this is what I said in the comment that I made to her - you could theoretically have it so that when you type into a form, then the After Update in that form could refresh the report. Is it possible? Yes, you could, but it would involve some coding. You are better off just to look at the report, if you don't like it, close it, make your changes, open it up again. Could you automate that? I'm sure you could, but that's up to you if you want to or not. If anyone really wants to see how to do that let me know, and if enough of you do, then I'll make a video about it.
This is a comment from Darren, and I'm not going to read the whole thing, but basically he said that he was looking to buy some stuff off my website but after a couple clicks he just got confused and lost because there are so many different options. He is a little bit right. My website has evolved over many years, and I used to just sell just videos - you would come to the website, buy a video, that's it. Then I started adding memberships, and then I started adding different types of memberships. It's evolved over the last 20 years.
Basically, the order form is still there. If you want to buy beginner level one, developer level three, whatever, you can still go to the order form and buy whatever lesson you want - you don't have to be a member if you don't want to. Then I have got my TechHelp memberships, which is what you see a lot on my YouTube channel, the silver, gold, platinum members - that's if you want the extended cuts and the code vault and all that stuff. With those memberships, you also get some free monthly classes. Expert users get whatever; there are all kinds of different options, it's all explained on the join page.
Bottom line, if you have any questions about what memberships cover what, just contact me. That's customer service stuff. I don't always have the time to answer technical questions personally, but I do answer all the customer service-related questions personally. That is my number one priority every morning - if you've got a problem with your order, if you have a problem with your account, you can't log on, something's not working, then let me know and I'll take care of you and I'll answer your questions.
Maybe one of these days I'll streamline all those different options, but like I said, I didn't want to alienate any of my existing members, so I've just kind of added stuff on as time has gone on, and it's gotten, yeah, sometimes it confuses me.
Nathaniel commented on my calculator video. I said you could use the lessons learned here to build a date calculator that works out the date in a set number of weeks or days, and I just commented, I said, well, if you know your date math it's very easy to do. I have a whole video on date math and lots of different functions for how to do stuff with dates, months, calculations, equations, and things of that nature.
John wants to know what my opinions on Dataverse are. Honestly, I don't consider myself an expert on Dataverse. I have not really spent a lot of time with it yet, so I'm going to reserve judgment until I do consider myself an expert on it. I don't like to give my opinion on things that I'm not very well versed in. Like, back 20 years ago I used to know a lot about Photoshop, but if you ask me any Photoshop questions today, I'm going to be like, hmm, I don't know.
I do agree with you on SharePoint; I have had some issues with SharePoint. I do have a SharePoint seminar that I released to show people who are currently already on SharePoint how to connect their Access database to it, but if you are not already set up with SharePoint, I do not recommend using it. I prefer using SQL Server online, which is much better. If you want to get your database online, this is what I recommend: you can use your Access front end that you've built and just connect it to an SQL Server backend, move all your tables up there, and you're good to go. That is what I recommend right now.
As I mentioned in a post on my forum on my website, it is still relatively new. I like to let someone else figure out all the problems; it still feels very 1.0-ish to me. I know it is going to be great eventually; I can definitely see the potential. As of right now, I'm still kind of waiting until it matures a little bit before I jump into it.
One of our users, Kevin, who is extremely helpful on the website, has posted a lot of information. He has been working with Dataverse and has posted a pretty good review on here - a whole bunch of stuff, issues he has come up with. If you want to read more about Kevin's experience with Dataverse, check it out. He has posted a lot of good information on my website, so yeah, mind blown, he has some crazy stuff. Check it out; I'll post a link to this down below and you can go check out all the stuff that Kevin has had to say, which has been very informative to me as well.
I get questions like this every now and then, questions about people who want help with different languages in Access, or different date formats that are from different countries and all that stuff. I have to apologize to everyone, I only speak English, I only really work with the US standard, so if you have any questions about using Access in different languages, I really can't help you, I'm sorry. I took Latin in high school, so I recognize the root words in some different Latin-derived languages, but as far as Arabic and so on, I'm sorry, I'm not your guy. I apologize.
Jim says that he wants to know if it is a good option for split databases where users have the ability to open up the back end which is located on a shared drive - in other words, because they could conceivably edit the back end table. That is always a concern. Remember, Access is not 100 percent secure. Even with all the tips and tricks I show you in my security seminar, the only foolproof way to secure an Access database is to use SQL Server as your back end.
With all of the tips and tricks and techniques I can show you, I can only show you how to secure your front end - your forms, your reports, your VB code - and even that, theoretically, can be reverse compiled. There are services out there that can do it. As far as your data goes, the tables, if you are going to give people access to work with that data, they have to have full read/write access to the folder that file is in. Yes, they could theoretically open up the table directly. You could put a password on it, but you are going to put one password on the entire file. I cover that in a couple of different videos, but they could delete the file, they could copy the file.
The only way to secure your data is to use SQL Server. Access is not secure when it comes to your data. You can encrypt it, you can scramble it so that if they do copy it, it looks like gibberish, but there are only so many things you can do. If you want to learn more about securing your database, start with my simple security video. I go over all the basics, and this page will have more links to other information you can learn about securing your database.
This question comes from Carl. Carl says, "Is it the real Carl, second from beyond?" Carl asked me if I can give tips on using cascading combo boxes in split forms. No, I can't, because I don't use split forms. I don't like split forms. I have had lots of problems with split forms. I refuse to use split forms. I talk about this in several different videos. Instead of a split form, I would use a continuous form with single form elements in the form footer or header. I don't like split forms, and I cover that in the extended cut for the cascading combo boxes video, so go check that out.
Stane left this comment on one of my videos where I made a customer table and a separate city table that had cities and states in it, and you can link them back and forth, and I just bring them together in a query - what's called an ad hoc query. I don't generally make global system relationships using the database window. The only time I usually do that is if I absolutely need to enforce referential integrity. If you want to learn what referential integrity is, check out this video.
Most of the time I'm working with split databases where the tables are in a different file, and sometimes multiple back end files, and when you do that you lose the ability to have global relationships, so you have to have other ways of maintaining referential integrity between your tables in your code, instead of relying on Access to handle them for you by itself. There are all kinds of reasons why I generally don't use global relationships, and I cover that in my more advanced developer lessons.
Augustin wants to know if you can format a date to display just the month and the year. There you go, right there, use that format right there. I love that trick. Use the format function, put your date in there, and then use mm/yyyy, and there you go. You can tell that I'm using PowerPoint.
Check out my format property and function video for more details on how to use that thing.
All right, folks, that's going to do it for quick queries number 11. I hope this was helpful to someone out there, and if you have any questions, feel free to post them - you can post them down below in the YouTube comment section, although I probably check that maybe once a week.
If you really want a good chance of getting your question answered, visit my website and you'll find links down below. Post them in the forums or send them to me that way. That is the best way to get questions to me.
Live long and prosper, folks. I'll see you next time.Quiz Q1. In Microsoft Access Design View, what is true regarding zooming in and out?
A. You cannot zoom in or out; you are stuck with the default view.
B. There are built-in zoom controls available in Design View.
C. The zoom level can be set by right-clicking the background.
D. You can use Ctrl+Plus/Minus to change the zoom.
Q2. What is a suggested workaround for users who have trouble seeing small details in Access Design View?
A. Use the Windows Screen Magnifier or other external zoom tools.
B. Change the zoom in Access settings.
C. Resize controls using built-in zoom buttons.
D. Switch to PowerPoint for database design.
Q3. When designing databases, what is recommended for storing records spanning multiple years?
A. Store all records in one table with a year or date field.
B. Create one table per year.
C. Create separate databases per year.
D. Use only Excel and not Access.
Q4. What is the purpose of a union query in Microsoft Access?
A. To combine data from multiple tables into one recordset.
B. To remove duplicate records from a table.
C. To split a table into multiple years.
D. To create a new table with unique fields.
Q5. What does the error "ambiguous name detected" usually mean?
A. There are two procedures or functions with the same name in the database.
B. A required reference is missing from the project.
C. A table is missing a primary key.
D. The database file is corrupted.
Q6. What is a major difference between macros and VBA in Access?
A. Macros are easier for beginners, while VBA offers more flexibility and power.
B. Macros run significantly faster than VBA.
C. Macros are not available in Access.
D. VBA is only for advanced database security.
Q7. When upscaling your Access database to use SQL Server as a backend, what happens to your macro or VBA code?
A. You can continue using your VB code or macros in the Access frontend.
B. Macros are automatically converted to SQL stored procedures.
C. VBA code will not work at all after upscaling.
D. You lose all your forms.
Q8. What tool allows automatic creation of VBA code based on UI actions in Excel or Word, but not in Access?
A. Macro Recorder
B. Design View
C. Union Query
D. Power Query
Q9. How can you convert a macro in Access to VBA code?
A. Use the "Convert Macros to Visual Basic" option in design view.
B. Open the macro in Notepad and save as a .vba file.
C. Use the Macro Recorder.
D. Export as a CSV.
Q10. What is the only foolproof way to secure your data in an Access database environment?
A. Use SQL Server as your backend.
B. Set an Access password.
C. Encrypt the front end.
D. Use split forms to hide tables.
Q11. What is usually not possible with Access Reports?
A. Type directly into reports to update data dynamically
B. Print reports
C. Export reports as PDFs
D. View reports on screen
Q12. For customizing the default zoom font size (Shift-F2) for all users in Microsoft Access, what is true?
A. There is no known way to set this in the database for everyone.
B. It can be globally set by an Access property.
C. It is stored in the table settings.
D. Changing it once applies to all users automatically.
Q13. If you want to display only the month and year of a date in Access, what can you use?
A. The Format function with "mm yyyy"
B. MonthName property
C. DateDiff function
D. Only the Year function
Q14. What is the presenter's recommendation regarding split forms in Access?
A. Avoid split forms and use continuous forms with header/footer for single form elements.
B. Use split forms for best performance.
C. Always use split forms for cascading combo boxes.
D. Only use split forms in the backend.
Q15. What is the limitation of Access security, according to the presenter?
A. Front-end objects and code can be somewhat protected, but the data in tables is not fully secure.
B. Access offers full encryption for all users without issues.
C. No one can access the backend without a password.
D. Access blocks all reverse engineering attempts.
Q16. What is said about maintaining referential integrity in split database environments?
A. It often needs to be handled by code when global relationships are not available.
B. Global relationships always work in split databases.
C. Referential integrity is not needed in Access.
D. You must always use database window relationships.
Q17. What feature that exists in Excel and Word for macros does not exist in Access?
A. The ability to record a macro with a macro recorder
B. The ability to print macros
C. The ability to export macros as PDF
D. The ability to encrypt macros
Q18. If you want to find links or further information referenced in an Access video on YouTube, where does the presenter suggest looking?
A. The video description box under "Show More"
B. At the beginning of the video
C. As pop-ups in the video
D. In the comments section only
Q19. What does the presenter suggest for learning VBA if you are currently using macros?
A. Start with macros, then move to VBA if you want more power and flexibility
B. Avoid macros completely and start with VBA
C. Use only macros—VBA is outdated
D. Macros and VBA cannot be used in the same database
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A; 16-A; 17-A; 18-A; 19-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, I'm answering a wide variety of your questions about Microsoft Access. This is the eleventh installment in this series, and I'm always impressed at the quality and diversity of questions I receive from viewers like you. These questions are often too specific for a whole standalone video, but still deserve clear answers, so I like to gather them up and discuss them here.
The first question comes from someone dealing with design view in Access. If you've ever found that your design windows appear very zoomed out and small, and wondered if you could zoom in or resize them, I have to admit this has frustrated me as well. Unlike Word, Excel, or PowerPoint, Access simply doesn't allow you to zoom in or out while designing forms or reports. Unfortunately, you're stuck with the default sizing. There are system-level solutions like Windows Screen Magnifier and similar tools if you need to see larger text, which can help if, like me, you have less-than-perfect vision. However, these are workarounds rather than real solutions in Access design itself. When your database is running, there are ways to resize forms, increase field and font sizes, or use my form profile template to adapt to various screen sizes and devices. But none of these apply to design view specifically. One day, I hope Microsoft will finally address this limitation for us.
Next, a common question comes up about the difference between using macros and writing VBA code in Access. If you're just starting out, macros offer a simpler, safer way for non-programmers to automate tasks. They're easy to build and share, with less risk of security warnings disabled by user settings. As you improve, VBA becomes much more versatile and faster—especially for those comfortable with coding. In terms of performance, both macros and VBA run efficiently on modern computers, so speed isn't much of a factor. If you plan to move your data to SQL Server later, you'll still connect through Access using either approach, so that's not a major concern in most cases. Generally, I advise beginners to use macros at first and then learn VBA as they grow more comfortable.
A frequent issue I see is having different tables for each year of data rather than one consolidated table. Even though it seems convenient at first, it's best to keep all similar data—like orders or customers—in a single table with a date or year field. This makes managing and querying your data much easier in the long run. If you already have multiple year-based tables, you can use union queries to combine them for reporting, and append queries to consolidate everything into a master table.
If you ever encounter the error "ambiguous name detected," this generally means you've accidentally included two procedures or functions with the same name—often by copying and pasting code from online sources or my code vault. To fix this, search your project for duplicate names and change or remove one of them.
Pat brought up a question about the Shift-F2 zoom window in Access. If you adjust the font size in this little window, it helps for your session, but unfortunately Access doesn't save this as a preference across copies of the front end or for other users. As far as I know, this setting isn't stored in any easily accessible location like a registry key or config file. One solution is to build your own custom zoom form, which gives you full control over appearance and usability.
On the subject of macros, someone wondered if Access has a Macro Recorder like Excel or Word. Sadly, it does not. The Macro Recorder in those applications makes it easy to record actions and generate code, which would be fantastic in Access, but we're out of luck. However, you do have the option to convert macros into VBA using built-in commands—this can be a helpful way to start learning VBA, as you'll see what the macro looks like in code form.
Nick wrote to say he prefers coding in VBA rather than macros, but sometimes you must use macros for things like checking trusted locations. That's because if your database isn't in a trusted folder, VBA code execution is disabled by Access's security features, making macros the only way to inform users or perform some checks in that scenario.
Many viewers also ask how to set up trusted folders or access links mentioned in my videos. On YouTube, all those links are always in the description box below the video. You may need to click "show more" to see the full list—YouTube does like to keep it hidden, unfortunately.
Caroline wanted to update a report in real time as she types into a text box. In Access, reports are for viewing, printing, or exporting—they can't be used for data entry or live updates. That's what forms are for. You could, with some coding, refresh a report whenever data changes in a form, but usually it's faster and easier just to close and reopen the report after making edits.
A viewer named Darren shared his frustration with the complexity of my website's order and membership system. Over the years I've added multiple product types and memberships, which has made things less streamlined than I'd like. You can still buy individual lessons or sign up for various membership tiers, depending on whether you want extra content, like extended cuts or the code vault. If you have questions about what membership covers, please contact me directly—customer service is my top priority.
Nathaniel commented on building a date calculator in Access, which is quite straightforward if you understand date math. I have comprehensive resources covering all the functions and calculations you might need for working with dates in Access.
There was also a question about Dataverse. I'll be honest—I haven't spent enough time with it to give an expert opinion yet. I'm waiting for it to mature, as it still feels early in its development. For now, I recommend SQL Server as the backend if you're moving your Access database online. Kevin, a frequent contributor to my website, has done some very detailed write-ups on Dataverse from his own experience, so his posts are a great resource if you're interested in the latest developments.
Some viewers have asked about working with different languages and international date formats in Access. Unfortunately, I primarily work with English and US standards, so I can't offer much advice for other locales.
On the topic of security, Jim wondered about users accessing a shared backend database. Access, unfortunately, isn't fully secure. Anyone with folder access could open, copy, or tamper with your backend tables. You can add passwords or use other tricks, but nothing is truly bulletproof, except moving your data to SQL Server. For more on this, see my simple security video and related resources.
Carl asked about using cascading combo boxes in split forms, but I have to admit I don't use split forms. I've had too many issues with them in the past and prefer using continuous forms with single form elements. I discuss this in more detail in my developer videos, especially the extended cut for cascading combo boxes.
Stane left a comment about setting up relationships between tables. I usually don't define global system relationships unless absolutely necessary. When working with split databases or multiple backends, global relationships are often not feasible, so I enforce referential integrity with queries and code instead.
Augustin asked about formatting a date to show just the month and year. You can easily do this using the Format function in Access—just specify "mm yyyy" as your format string.
That wraps up Quick Queries number 11. I hope these answers help you with your own Access projects. If you have further questions, the best way to reach me is through my website—the YouTube comment section is fine, but I check it less frequently. There are links to all relevant resources down below.
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 Limitations of zooming in Access design view
Using Windows Screen Magnifier for Access
Using the Zoom dialog box on fields in Access
Resizing forms and adjusting font sizes in forms
Using a form profile template for multiple devices
Pros and cons of macros vs VBA in Access
Macros limitations and strengths for beginners
Enabling and running VBA in trusted locations
Speed comparison between macros and VBA
Upscaling Access databases to SQL Server
Consolidating yearly data tables into one table
Using union queries to combine data from multiple tables
Appending data from multiple tables into one table
Resolving "ambiguous name detected" errors in VBA
Managing duplicate function names in Access VBA
Changing font size in the Access zoom box
Limitations of saving Zoom box font settings in Access
Building a custom zoom form in Access
Lack of Macro Recorder in Microsoft Access
Converting Access macros to VBA code
How to detect if a database is in a trusted location
Macro use when VB code is disabled for security
Finding video links in YouTube video descriptions
Differences between forms and reports in Access
Dynamically updating reports from form input
Access website navigation and membership options
Performing date calculations and date math in Access
Handling different languages and date formats in Access
Securing split database back ends on shared drives
Limitations of Access security for table data
Using SQL Server for Access backend security
Password protecting and encrypting Access files
Alternatives to split forms in Access
Using continuous forms with form headers/footers
Relationship management in split Access databases
Maintaining referential integrity without global relationships
Formatting dates to show month and year in Access
Using the Format function for date display |
