Quick Queries #26
By Richard Rost
12 months ago
Normalize, Distribute New Version, Subform Val, More!
In this Quick Queries tutorial, I'll guide you through tackling common issues in Microsoft Access, such as updating an ACCDE file while preserving user data, resolving Enter Parameter Value errors in queries, and effectively changing interface settings to the classic overlapping windows. Additionally, I'll address referencing issues with subforms and discuss best practices for relational databases, including proper use of IDs over state names.
Prerequisites
Links
Recommended Courses
Up Next
Keywords
TechHelp Access, Quick Queries, Dell laptop purchase, Office 2024 without Access, perpetual license vs subscription, Microsoft 365 model, standalone Microsoft Access 2024, database update distribution, ACCDE file, split database, relink tables with VBA, enter parameter value error, count of person ID error, overlapping windows interface, cascading combo boxes on subforms, datasheet view limitations, store state ID vs name, normalizing data, TechHelp forum, TechHelp membership perks.
Intro In this video, we'll talk about several common Microsoft Access questions, including why Microsoft Access is no longer included with Office 2024 perpetual licenses, the differences between perpetual and subscription models, and how you can purchase Microsoft Access as a standalone product. We'll also discuss secure ways to distribute ACCDE files, strategies for updating databases without losing data, and how to handle table structure changes and relinking tables. Other topics include resolving enter parameter value errors in queries, working with aggregate calculations, switching between tabbed and overlapping window interfaces, correctly referencing fields in subforms, and best practices for storing data using IDs.Transcript Well, it's Friday, folks, and you know what that means. It's time for another TechHelp Quick Queries video brought to you by Access LearningZone.com. I'm your instructor, Richard Rost. Everyone's excited. I'm excited. Are you excited? I know this kid's excited. I love this clip. Every time I see it, I laugh. I just think it's so pumped. I love it.
First off, I got an email from a student of mine, Sam, who said he just bought a new laptop from Dell and he made sure to add on Microsoft Office 2024 only to be shocked that it no longer included Access. Now, even with the older versions of Office, you used to have to make sure you got the professional version. There used to be an Office 2021 Pro, and that included Access. The standard version has never included Access.
However, just recently with this version, Microsoft changed it so that no versions of Microsoft Office include Access if you're doing the perpetual license. We buy the specific version, 2024, 2021, that's called the perpetual version, as opposed to the subscription model. I personally like the subscription model, the Microsoft 365 model. You get one set price every year. You're paying it for the software. You get all the updates. You get all the new features. But I know some businesses prefer the perpetual version because they might have that version for 10 years or whatever. And there's no problem with that.
Just know that if you're looking to buy Access, you have two options. You can either do a subscription model that includes it, like I have the Microsoft 365 apps for business. It's like $8.25 a month. It's not bad at all. Or you have to buy the standalone Microsoft Access 2024. It doesn't come as part of Office anymore. I know this is confusing. Microsoft loves to confuse people with all their different subscriptions and product variations, and they've been confusing people for years. I love Microsoft, but they got to do something to make this easier for everybody.
Anyways, I got a whole separate video on this coming out next week. It may already be available by the time you see this. It's scheduled for release on February 13th. I go into much more detail with all the different options, what versions include what and where and this and that. If you're confused, watch this video. Members, it's online. You can watch it right now.
Next up on the list in the forums on my website, one of my students, Jeff, is making his own software that he wants to release to other people. He's distributing it as an ACCDE file, which is smart. It's encrypted. It's locked up. Good for you. But his question is, he wants to know once his customers already have the database installed and they've been working with it, they got data in it, how does he then distribute updates so that they get the newest version of the database program without affecting their data?
So, of course, we went back and forth, asked some questions and talked about it. Basically, it came down to it after I figured out exactly what he was doing. You've got two options in this case. You can, and this is what I recommend, split the database. This way, you only have to replace your front-end file and they can keep their back-end file. Unless, of course, you make changes to the table structures, in which case, you'll have to update their tables. You can do it with VBA. It's not easy, but you can do it. I think I covered that in one of the classes. But if you don't make any table changes, then all you have to do is give them the new copy of your front-end file. You'll probably have to relink your tables. I got a video for that. That's not hard. You can do that with VBA too. So that when they open the database, if it doesn't find the back-end, it'll just say, oh, hey, I see you updated. I can't find your back-end files. Please point me to them. And then they'll just have to open up and explore, where you can just pick where their back-end file is. Or if you know what it is programmatically, you could set it ahead of time.
The other option is you'd have to have them export their data to a temp file, whether it's another database, or even, you could use a text file or whatever. Give them a whole new database, and then have them import their data back in. This is a pain. You got to be careful. Make sure all your IDs are the same. You can definitely do it. I've done it before. I had a client that didn't want to split the database for whatever reason I forget. So we just made an import-export routine. Again, I've got videos on all this stuff. I'll put links down below. But that's what I would do. I would just give them a new front-end file, and then you're good to go.
Next up, another one of my students, Collins, says that his math is not working. I love it. Here in the US, we call it math. And everybody else around the world, it's maths. I get it. Makes sense. Mathematics is plural. But I just never get used to it. I watch a lot of science and math stuff on YouTube. And I can always tell if someone's from the UK or whatever. Maths. Anyways, Collins is trying to do some math in a query, and he's getting the enter parameter value.
Now, enter parameter value. I got a whole separate video on this. I'll put a link to it down below. He's trying to use the count of personal ID in the query in which the count of the personal ID is being calculated. Yes, I actually realize this should be count of person ID, not personal ID. Let me see. Let me zoom in. Yeah, he's got personal ID. So that is a typo. But even if you got it spelled right, the count of person ID isn't calculated until this query is done running.
You've got to take that. You can see here, there's your count of person ID. I don't think we got more on the bottom here. But basically, you need to take this now. You get these results. Then you feed this into another query after this, where you can then count, subtract that from the count of referral. Because you get both of these. I see what you're trying to do. You want to put that here too, but you can't. Because these aren't calculated until this query is finished.
So a, spell your fields right. That's why it's tough if you got personal data and you got person ID. I know. I used to do something that all the time. But you can't do calculations until this query is finished running. So now I'll just take the results of this aggregate query, feed this into another query, and then you can easily do your math there.
I actually see this a lot. This is a common question about your maths and your queries. I love it.
Let's head on over to YouTube. Bro, how to make a floating table like this. It makes it easier for matching two tables. I think, bro, you're talking about overlapping windows, which is one of the first things that I teach people how to switch in my Access beginner class and in my TechHelp free template of the basic video, where I show how I make my database template.
If you create a new database or if you use any number of database templates that you find online, the default is this. The tab interface, wherever the object that you open is a new tab. I personally don't like this. I never have. I prefer the classic old-school Windows overlapping interface, which looks like this. And then you can open up different objects. You can have them side by side, or you can tile them just like Windows is supposed. Why they call it Windows? These are little windows. With the tab interface, you can't. No, there's no way to. No, you can't. I don't like that.
Want to learn how to change it? Go watch this video. I'll put a link down below.
Next up is a question that was posted on my cascading combo boxes video. This user saying that everything works perfectly if the combo boxes are on a main form. However, if he tries to run them from a subform, then he gets the enter parameter value pop-up. That's because you just need to know how to reference the name of an object on a subform. It's slightly different. Fields on a single form on a standard form are just simply forms, form name, field name. So forms, customer, F, customer ID, easy enough. On a subform, it gets a lot more tricky. It's forms, parent form name, the subform name, dot form, and then the field name. So it's going to be like forms, order form, order detail form, dot form. This is an actual thing you'd have in there. And then the field name. It's a little more complicated. I didn't make this up. I just teach you how it works. But you're in luck. I got another video where I explain it in detail. So go watch this if you have any more questions.
I think what I'm gonna do from now on, because usually I go through the comments in chronological reverse chronological order, because that's the way they're displayed on YouTube for me. But I see there's a bunch more questions going back on this cascading combo boxes one. So let's take some time and just go through all of these then.
Too bad this doesn't work in datasheet view on your form now. Sorry, not much will work in datasheet view. I don't recommend datasheet view. You as the developer, if you're gonna go poke around on it, fine, or I just go straight to the table, that's the case. But I don't like my end users ever having the datasheet view. Form view, that's it. The single forms, continuous forms.
The amazing review posted how to make cascading combo boxes work on continuous forms. Well, they do, if you know the trick, and I do show you the trick in the extended cut, which you can see is right down here, members learn how to make cascading combo boxes in a continuous form. Now this star here means this person is a member. I don't know if you became a member because you wanted to see how to do this, or you were a member before, but either way, I thank you very much. I appreciate all my members who help support my work, and it allows me to keep making all of these videos for all of you guys, which I love doing.
Next up, Tarek is saying that the VBA code to require the city combo box doesn't work. It should. It's real simple. Make sure you've got the combo box name spelled right. It's just one line of code, and I've never had it not work before. City combo, not requery. Check the name of your box, make sure it's spelled right. Nine times out of ten when someone tells me something's not working, it's spelling.
Coach Max says he's got two forms, one for athletes, one for parents, create the parent profile first, then create the athlete profile and connect it using a lookup. If a child drops, it changes the tuition, sounds good. I want to create a command in the athlete form that will take me specifically to the athlete's parent profile to handle this, rather than opening up the general parent form and searching.
So what I would suggest, first of all, if you got two forms, that's a great first start, you want to do this as a form subform. That's the best way to set up a relationship like this. You got your parent form out here and you got your subform in here with all the athletes. You can do totals on the bottom and see exactly what the tuition and stuff should be based on calculating it that way. That's how I would do it. I wouldn't have two separate forms side by side. That makes things difficult. And you got the parent right there, it's easy to find.
CC Sprowrrow1 says the state is being stored because the ID number rather than the name is there a way to get it stored as the actual state name. Yes, you can if you want to. I mean, there's nothing wrong with that, but I prefer using IDs. That's building a proper relational database. Every state, you have a state table and every state's got its own ID. That's the best way to store it. You avoid redundant information in multiple tables because then you got the state listed in the state table and then you got the state listed in the customer table too. But what happens if they change the name of the state? I mean, who knows. The Gulf of Mexico is now the Gulf of America accordingly. To whatever. But you don't want to have to have that state name copied in multiple tables. You store it in one table. And that's why you have the ID stored everywhere else. Go watch this video on relationships between tables, and go watch this video on normalizing data.
For example, you got your customers. You got Jim Kirk, Will Wryker, Mr. Worf. You don't want this information in every single record related to Jim Kirk in his contacts and his orders. So you want to store a customer ID that we can go back and get his name. That's what normalizing data is all about.
That's going to do it for today, folks. That is your Friday Quick Queries, number 26. Hope you learned something. Live long and prosper, my friends. I'll see you next week for another video and I'll see you next Friday for another Quick Queries. I like doing this. I'm going to do this every Friday.
If you enjoyed this video, please give me a thumbs up and post any comments you may have below. I do try to read and answer all of them as soon as I can. Make sure you subscribe to my channel, which is completely free. Click the bell icon and select all to receive notifications when new videos are posted.
Want to learn more? Click the Show More link below the video to find additional resources and links. YouTube does a pretty good job of hiding it. It's right down there. See this part of the description here. The name, the videos up here.There's a little "show more" down there, right at the bottom. It's kind of hard to find. But once you click on that, you'll see a list of other videos, additional information related to the current topic, free lessons, and lots more.
YouTube no longer sends out email notifications when new videos are posted like they used to. But if you'd like to get an email every time I post a new video, click on the link to join my mailing list. You can pick how frequently to get emails from me, either as they happen, daily, weekly, or monthly.
If you'd like to become a paid member of my channel and receive all kinds of awesome perks, click on the "Join" button. You'll see a list of all the different membership levels that are available, each with its own special perks, including my extended cut videos, access to my code vault with lots of VBA source code, template downloads, and lots more. I'll talk more about these perks at the end of the video.
Even if you don't want to commit to becoming a paid member and you'd like to help support my work, please feel free to click on the tip jar link. Your patronage is greatly appreciated and will help keep these free videos coming. I have some puppies to feed. But don't worry, no matter what, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free.
If you really want to learn Access and you haven't tried my free Access Level One course, check it out now. It covers all the basics of Microsoft Access, including building forms, queries, reports, and more. It's over four hours long. You can find it on my website or on my YouTube channel. I'll put a link down below that you can click on. And did I mention it's completely free? The whole thing, free, four hours. Go watch it.
A lot of you have told me that you don't have time to sit through a four-hour course. So I do now have a quicker Microsoft Access for Beginners video that covers all the basics faster, in about 30 minutes. And no, I didn't just put the video on fast forward. I'll put a link to this down below as well.
If you like Level One, Level Two is just a dollar. That's it, one dollar. And that's another whole, like, 90-minute course. Level Two is also free for paid members of any level, including supporters. So if you're a member, go watch Level Two. It's free.
Want to get your question answered in a video just like this one? Visit my TechHelp page and send me your question there. Members get priority, of course. While I do try to read and respond to all of the comments posted below in the comments section, I only have time to go through them briefly a couple of times a month, and sometimes I get thousands of them. So send me your question here on the TechHelp page and you'll have a better chance of getting it answered.
While you're on my website, be sure to stop by my Access forum. We've got lots of lively conversations about Microsoft Access and other topics. I have a fantastic group of moderators who help me answer questions. Shout out to Alex, Kevin, Scott, Adam, John, Dan, Juan, and everybody else who helps out on the site. I appreciate everything you do. I couldn't do it without you.
Be sure to follow my blog, find me on Twitter, and of course on YouTube. Yes, I'm on Facebook too, but I don't like Facebook. Don't get me started.
Now, let's talk more about those member perks. If you do decide to join as a paid member, there are different levels: Silver, Gold, and Platinum. Silver members get access to all of my extended cut TechHelp videos, one free beginner class every month, and some other perks.
Gold members get all the previous perks plus access to download the sample databases that I build in my TechHelp videos. Plus access to my code vault where I keep tons of different functions that I use, the code that I build in most of the videos. You'll also get higher priority if you submit any TechHelp questions. Now, answers are never guaranteed, but you do go higher in the list to meet a rhythm. If I like your question, you have a good chance of it being answered. You'll also get one free expert-level class each month after you've finished the beginner series.
Platinum members get all the previous perks plus even higher priority for TechHelp questions. You get access to all of my full beginner-level courses for every subject, and I cover lots of different subjects like Word, Excel, VBA, ASP, and lots of different stuff, not just Access. These are the full-length courses found on my website. You get all the beginner ones. In addition, once you finish the expert classes, you get one free developer class per month. So lots of training.
Once again, my name is Richard Rost. Thank you for watching this video brought to you by AccessLearningZone.com. I hope you enjoyed it. I hope you learned something today. Live long and prosper, my friends. I'll see you again soon.
TOPICS:
Microsoft Access exclusion from Office 2024
Perpetual vs subscription model for Office
Purchasing standalone Microsoft Access
Distributing ACCDE files securely
Splitting databases for easy updates
Handling table structure changes
Relinking tables using VBA
Exporting and importing data for database updates
Calculating fields in Access queries
Avoiding enter parameter value errors
Using count and aggregate queries correctly
Switching between tab and overlapping window interface
Handling cascading combo boxes in subforms
Referencing fields in subforms correctly
Cascading combo boxes on continuous forms
Storing data using IDs vs actual values in tables
COMMERCIAL:
In today's video, we're discussing a common issue with installing Microsoft Access in the latest Office 2024 version and what options you have if it's missing. We'll also address how to effectively distribute updates for an Access database without affecting existing data. Next, you'll learn how to troubleshoot queries when encountering the 'Enter Parameter Value' error, particularly with calculations that involve aggregated data. We'll briefly touch on the pros and cons of using a tabbed interface versus overlapping windows in Access, and finally, we'll explore how to correctly reference objects on subforms to eliminate nagging parameter prompts. Live long and prosper, my friends.Quiz Q1. What significant change did Microsoft make regarding Access in Microsoft Office 2024?
A. Access is now included in all versions of Office
B. Access is only available through the subscription model
C. Access is available in Office Standard edition
D. Access is free for all Office users
Q2. What is one recommended method for distributing updates to a database without affecting user data?
A. Rewriting the entire database from scratch each time
B. Only distributing updates during scheduled downtime
C. Splitting the database into a front-end and a back-end
D. Keeping tables and queries in Excel spreadsheets
Q3. Why might you encounter an "Enter Parameter Value" error in a query in Access?
A. The database is not installed properly
B. Incorrect spelling of field names in queries
C. Access does not support mathematical operations
D. The software version is outdated
Q4. How should a field be referenced on a subform in Access compared to a standard form?
A. Forms!SubformName!FieldName
B. Forms!FieldName!SubformName
C. Forms!ParentFormName!FieldName
D. Forms!ParentFormName.SubformName.Form!FieldName
Q5. What is a recommended interface style for easier comparison of database objects in Access?
A. Tabbed interface
B. Overlapping Windows interface
C. Fullscreen interface
D. Split-screen interface
Q6. What does Richard recommend using instead of storing state names directly in customer records?
A. Using state abbreviations
B. Storing the full address string
C. Using state IDs as foreign keys
D. Avoid storing state names entirely
Q7. How does Richard suggest setting up forms for managing related data in Access?
A. Using two separate forms without linking
B. Implementing a single, combined form view
C. Using a form and subform structure
D. Keeping data in Excel for ease of access
Q8. According to Richard, what is one advantage of being a paid member on his platform?
A. Access to a private social network
B. Receiving only Silver level perks without upgrades
C. Access to his extended cut videos and sample databases
D. Getting one free course per year only
Answers: 1-B; 2-C; 3-B; 4-D; 5-B; 6-C; 7-C; 8-C.
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone covers some key questions and answers regarding Microsoft Access. I'm your instructor, Richard Rost, here to guide you through today's topics.
To start, a student named Sam recently purchased a new laptop and added Microsoft Office 2024, only to find Access missing. Historically, only the professional versions of Office included Access, like the Office 2021 Pro version. However, Microsoft has now altered its licensing model. In the current perpetual license versions of Office, such as 2024 and 2021, Access is no longer included. If you want Access, you either need to subscribe to a Microsoft 365 plan, which provides Access and regular updates, or buy Access as a standalone product. Though the perpetual versions have their merits, such as longevity without recurring costs, Microsoft likes to keep things complicated with their wide array of products and subscriptions.
I'm releasing a separate video detailing all these options and versions, which will be available by February 13th. If you're puzzled about your choices, that video will clarify things.
Moving on, a student named Jeff is producing software that he plans to distribute in an ACCDE format. His challenge is updating customers' databases without affecting their existing data. The solution is to split the database, allowing clients to update only the front-end file while preserving their data in the back-end. If there are structural changes in the tables, those can be updated using VBA. If table structures remain unchanged, a new front-end copy suffices. Relinking tables is straightforward and can be managed with VBA.
Another student, Collins, encountered a problem with his query calculations returning an "enter parameter value" error. The issue stems from incorrect field spelling and trying to perform calculations before the query has finished executing. Ensuring accurate field names and setting up subsequent queries to handle calculations should resolve this.
There's also a common question about handling overlapping windows versus the tabbed interface in Access. The default tab interface doesn't allow for multiple object displays. Switching to the classic overlapping windows interface allows for more flexibility, which I teach in my Access beginner class.
For those wondering about running cascading combo boxes on subforms and encountering issues, remember that referencing objects on subforms requires a more complex syntax than on main forms. It's important to adjust references appropriately.
To close, plenty of comments and questions about Access improvements are addressed on my YouTube channel. Videos are available on a wide range of topics, from database splitting to combo box configurations and more. You can find tutorials and resources designed to enhance your Access knowledge and skills.
Remember, you can find a detailed video tutorial with step-by-step guidance on my website through the link below. Thank you for taking the time to learn with me. Live long and prosper, my friends.Topic List Microsoft Access exclusion from Office 2024
Perpetual vs subscription model for Office
Purchasing standalone Microsoft Access
Distributing ACCDE files securely
Splitting databases for easy updates
Handling table structure changes
Relinking tables using VBA
Exporting and importing data for database updates
Calculating fields in Access queries
Avoiding enter parameter value errors
Using count and aggregate queries correctly
Switching between tab and overlapping window interface
Handling cascading combo boxes in subforms
Referencing fields in subforms correctly
Cascading combo boxes on continuous forms
Storing data using IDs vs actual values in tables |
