Quick Queries #3
By Richard Rost
5 years ago
Quick Queries #3 - Maximize, Home/Office Copy, et al.
Welcome to another Quick Queries video brought to you by Access Learning Zone. I am your instructor, Richard Rost. Quick Queries videos are designed to let me answer as many of your questions as possible! In today's video...
- Maximize a Form OnLoad with Docmd.Maximize
- Use an AutoNumber if I have another Key Field?
- Working with Access between Home and Office
- How to rename a database
- How do I record my videos?
- How do you store someone's age in the table?
- How to remove AutoNumbers from the table?
- Database startup options: AutoExec or Startup Form
- Output All Fields property in a Query
Previous Quick Queries
 Links
Keywords
microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, quick queries, zoom, how to make screen recordings, how to record tutorials, how to make videos, docmd.maximize, onload, autonumber, calculate age, autoexec, startup form, output all fields, query
Intro In this video, we will talk about a variety of Microsoft Access topics including how to maximize forms on open, choosing between autonumbers and natural keys for primary keys, moving your Access database between computers, renaming an ACCDB file, handling calculated fields like age, issues with autonumbers, setting startup objects, displaying all fields in a query, and some behind-the-scenes info on how I record my videos. This is TechHelp Quick Queries 3.Transcript Greetings and welcome to TechHelp Quick Queries number three, brought to you by Access Learning Zone. My name is Richard Rost.
The Quick Queries videos are to answer all of the tons of questions that I get every week that do not necessarily merit their own video, or I have maybe covered them kind of before, or they are follow-ups or similar. Very informal, just to have fun. Not everything has to be a formal video. I like to goof around from time to time too. But if you want to learn a little bit more about Access, get your Access fix in there. You might learn a thing or two.
Before we get started, I just want to give a quick shout out to Winhost. I ended up switching internet providers. I had been with GoDaddy for about 11 years or so, and they have just been slowly getting worse and worse over the last couple of years. I had a problem. My site went down, and their tech support has become increasingly difficult to work with. Very unhappy with them. I feel like I am talking to people who have no idea how the internet works. They should not be working in tech support. I am having to explain things to them about their job, which is frustrating. I have been a loyal customer for over 11 years. I have dealt with slowdowns and problems at my site, and I just thought I had enough.
So I switched over to Winhost. They've been very helpful in getting me up and running. This is not a paid advertisement, by the way. I only endorse when I am really, really happy with something. But I just wanted to give a shout out to Winhost. If you are looking for a good internet provider, I've been with them for almost a month now and have had no problems whatsoever. Everything is running smooth. My site is running a whole lot faster. GoDaddy was trying to tell me that my SQL server was crashing their server. No, no. I moved the exact same website over to Winhost, and everything is running perfectly. It is not my coding. I am a database expert. I am not crashing your SQL server. I am sorry. My code is solid. So if you want to read the whole story, I'll put a link down below in the link section.
First question today comes from Emma from Indianapolis. She says, I want to force overlapping forms to resize to the largest size inside of MS Access. Because if I close the form and reopen it, it becomes small. How can I force it to become the largest every time it should be? Every time?
I just copy and paste these folks. If you have typos, they make it into the videos. Sorry about that. Sometimes I'll fix it if I notice it. I didn't catch that one.
The answer is, if you want to, when you open a form, have it maximize itself inside of Access, just issue a DoCmd.Maximize command. One line of VBA code. Put it in the On Open event for the form. If you don't know how to use an On Open event, I cover that in, let me look at my outline here, Access Developer 1. There is a link there. You can put it in the DoCmd.OpenForm too. If you are using DoCmd.OpenForm from another form like a button, you could do a DoCmd.OpenForm and then a DoCmd.Maximize. That will open the form and immediately maximize it. Or you can put it in the form's On Open event or On Load event. Any of those things will work.
There is, unfortunately, not a setting in the form itself without using VBA to maximize the form. I hate the tabbed interface. It gives you the look and feel of having everything as full screen, but sometimes I want side-by-side forms. I do not like that tabbed view. I like overlapping forms, traditional Windows forms.
Next up, Brent via YouTube. You should change the name of your Quick Queries videos to make it sound like you are teaching about queries.
No. I like Quick Queries. It is a double entendre, a little play on words. Queries are queries in Access, but they are also queries like questions. Plus, I am a huge fan, I think I have said this before, of Neil deGrasse Tyson and StarTalk. He does a thing called Cosmic Queries. So I like this as Quick Queries. It is Access. So I like it, Brent. Too bad. I am not changing it for you. You did not even ask nicely. You shot it at me.
Next up, Aslam from YouTube. Great new idea, and I love to watch these TechHelp Quick Queries. See Brent? People like Quick Queries. So I am leaving the name.
I am designing a database for an automobile trading house. We have automobiles as products, and their chassis numbers are universally unique. Chassis numbers consist of a code, and after a dash, it is a variable numeric number. Codes are usually unique for that particular automobile series. For example, NZ121-xxxxxxxx. It is a Toyota Corolla, but at the same time, that may also be a Toyota Corolla. Suggest if I make this chassis number as primary key in my purchase and sell table or should I create an autonumbering primary key as you often suggest.
You answered the question yourself. I like primary keys to be autonumbers. Sometimes if I take the time to actually reply, because I replied to Aslam in the YouTube comments, so if I actually take the time to type it out, I'll put it here. I always use autonumbers as primary keys. They're just better for Access to handle internal relationships with. By all means, add your value as a key field. You can put your special custom number, whatever that is, in your table. Keep that there. You can index it. You can make it duplicate or no duplicate. You can make it a key field. But I like to use autonumbers for the relationships internally to track unique items that way and for relationships. There is a link right there. Go watch my autonumbers video. It is a good long video where I give the arguments for and against using autonumbers. Lots of people, other database developers, will argue with me. You do not always have to use an autonumber. Well, no, you don't. You can use natural keys if you want to. But I believe, in my personal opinion, and I present all of the facts as to why in that video there. So go watch that.
So yes, I would make a second field. That is your autonumber. That is an ID for your internal relationships. And then you can keep whatever other numberings that you have for those partner numbers. That is fine. Nothing wrong with that. In fact, many databases do that. If you have products, I used to sell computer parts, and I would have the vendor product number in there as well as my ID.
Next up, Richard from Detroit, Michigan. Richard, cool name. I like it. Like a lot.
Richard says he's got a database on his home computer and when he goes to work, he wants to continue working on that same database. The only solution he has right now is the USB memory stick.
There are a lot of things you can do. I have got an article on my website that I will post. I will put a link up to it. People ask me this a lot. Copying your database back and forth.
It all depends on how big the database file is and what kind of internet speeds you have. Since you are the only user and you do not have to share it with other people, you could use a cloud backup solution like Google Drive, for example. They have got a backup and sync utility, Dropbox, OneDrive, etc., to copy the file back and forth. If the database is relatively small and your internet speed is decent, this might work for you.
This is not a good solution though if you share the data with people. In that case, see my Access on the Web page, which I will put links to down below. Google Backup and Sync, I cover in my new PC setup video. That is on the website. I will put a link to that down below too. And then, Access on the Web talks about other kinds of solutions.
I used to do this myself for a time because I used to have an actual office outside my house and I had about a half an hour commute. I was the only person working on the database. So I had everything so it backed up using the Google Drive backup. I had pretty decent internet speeds at the office and at the house. So if I finished working in the office and I put the database file in the Google backup drive, and then I got home, it synced up because there's half an hour there. But again, this only works if you are the only person. Everything you put in your Google Drive shared folder will show up on both systems.
If your database is small enough and your internet speed is fast enough and your commute time is long enough (a lot of variables, I know), then by the time you go from the office to the home, your file should have synced up. If you have a 100 megabyte file, gigabit internet, 20 minute commute, you are good.
I suggest copying the file out of your Google Drive folder to your desktop or wherever to work on it, because Google will keep trying to sync it while it is open. Alright, so what I would do is you have got your Google Drive backup folder, you take your database file, you copy it onto your desktop, work on it, make your changes, do whatever you are going to do, and then when you are done, put it back in the Google Drive folder and Google will sync it. Do not work out of that folder. Do not open the database up while it is in that Google Drive folder, because Google is going to keep trying to copy it, and if it is open, it is going to either not copy it right, or get it corrupted, or whatever.
Again, this only works with small databases. Most of the time, if you have a small contact/customer database solution, whatever, that's okay. If you have a big database, then you should look into some other solutions, like the stuff I cover in my Access on the Web.
Yeah, when you are done, copy it back. Optionally, rename it so you have got three or four copies, rotating backups, and so on and so forth. And I said it and I did not update the autonumbers link right there, did I? Let me fix that one. There is the link for the new PC setup where I talk about using Google Drive, and there is the link to my Access on the Web where I talk about different solutions for using a Microsoft Access database on the web. There are lots of different solutions. Go check those pages out. I will put links down below in the link section. Click on them. I just put them in the video in case people are watching this and they cannot see the comments down below the video.
Next up, Alan from YouTube. How can I rename a database I have created?
Sometimes I do not think to answer the most basic questions because I just assume people using Access know things like basic file management. However, Access does not give you an easy way to rename the ACCDB database file. Close it. Go to the folder where you have it saved with Windows Explorer and then rename the file. Let me show you real quick.
Most people, when you are working with Word or Excel or Notepad, you get files. You save your files. When you create a database in Access, you do not always know where it is. So let's say I created a database here. It's in my ABCD folder. Let me open this up.
Here is the database right there: ABCD Core 32 bit, whatever, ACCDB. If you do not see file extensions in there, you can turn those on. That is a whole separate lesson though. That is a Windows thing. You should see the file extensions. All you do is come in here and just click on it and type in a different name. That is it. Unless you have got your database linked to a backend, then you do not really have to worry too much about what that file name is. It can be whatever you want. It is meaningless to the actual operation of the database itself. And if you know how to create backend linked tables, then you probably know how to rename your file.
Hope that answers your question. And yeah, I am not very good about changing this link. I think I am just going to get rid of this unless I need it. Let me just move this out of here. Why keep it there? I keep messing up. I will bring it in if I need it. I do not need it on every slide.
Next up, how do I record your videos? I actually get asked this a lot, so I decided to include it here. Lots of people ask me.
Nothing fancy. I use a program called Bandicam for the screen capture recording. I use Bandicut to piece the pieces together. So I will record in short segments, like 10, 15, maybe 30 seconds at a time, I will pause. If I go for a sneeze or there is a noise in the background, I do not want to lose a huge segment, so you can go back in and edit it using Bandicut and snip out a piece. You can cut pieces out and push the pieces around. For me, it is easier just to record short segments, and pause liberally, and then if I goof, I just re-record that segment. I almost never go back and edit something.
But that is why I like my TechHelp videos, and I like my advanced and developer videos, because if I goof, I just leave it. I want my advanced students to see my goofs. If I make that mistake, chances are you are going to make the same mistake, so I leave my mistakes in the advanced classes. Whereas my beginner classes, like Beginner Level One, I am re-recording Beginner Level One right now for Access and it takes a long time because I like to make sure the videos are perfect, or as close to perfect as possible. Mostly because I want to look more professional, obviously. But I do not want to confuse beginner users by showing the mistakes, unless the mistake is intentional. Sometimes I will put an intentional mistake in there, and then I will be like, but wait, we want to really do it this way. Because sometimes, by making a mistake, you will appreciate the right way to do something if I show you the wrong way accidentally first or intentionally first.
That is more along the lines of my recording methodology.
But yeah, Bandicam for recording. Bandicut pieces together those. I create a bunch of small videos and I piece them together. I used to have my own software years ago that I wrote to do that. What was the name of that? I used to use a shareware utility that I got, and I wrote my own software to use the shareware utility. I cannot remember what it was called. Anyway, we are going back. I have been using Bandicam now for the past five years or so. I am pretty happy with it. It is a good piece of software.
This is just a PowerPoint slide. All the stuff like this, this is just PowerPoint. All of this type of stuff. This whole thing was done in PowerPoint. I grabbed their logo, but anything that is not an actual screen recording of Access or Excel or whatever I am teaching is just a PowerPoint.
And that is it. It is not that hard. I have got a simple 30 dollar headset that I use for recording. I have gone through many different headsets over the years. Some were good, some were pretty crappy. I switched to a wireless headset for a while because I wanted the freedom of not being attached to a cord, but the sound quality was poor. It was a relatively cheap wireless headset because I wanted it to be light on my head. I wear it for six, seven hours at a time, sometimes, when I am recording. I spent the money, I got a 300 dollar professional quality, nice, heavy, wireless headset that sounded great, but it was just so heavy and thick on my head, I did not like wearing it for hours at a time. It made my ears sweat. So I have just got a light little USB plug-in headset. It works great. The sound is good, I think.
I record at 720p, which is HD. It is not 1020p, but you do not need that. I think this is a big enough window. My older videos had smaller windows because back in the day, the video had to be compressed a lot more.
That is it. I hope that answers your question, Peter. If anyone really wants to see like a whole behind the scenes of how I actually record, let me know. I will do something. I did not think that was that interesting, but a lot of people want to know because they want to make their own tutorials, and I am happy to share. There is always room for more people doing tutorials, even Access tutorials. I welcome them. I watch a lot of other people's tutorials. There is plenty of space for everybody, and that is one good thing. If you have information to share, I like to share for free. I like to just put these free videos out there. I enjoy interacting with people. I like answering questions. I think even if I was independently wealthy, I would still do this just for fun. I love Access and I have a good time doing it.
Next up, Deb from YouTube. Thank you for the video, it is just what I needed. She is talking about the How to Calculate Someone's Age video. How can I get the calculated age to update in the table field?
Well, Deb, you really do not want to. You do not want to update that date in the table because it changes constantly. Every time you open up that customer's record, it is going to be different. Calculate it when you need it. Do it on the fly, either directly in the form text box itself or in an underlying query.
However, if you do want to store it, you will have to calculate it using an After Update event when the birth date is changed and whenever the record is opened. So, the After Update event for their date of birth, and whenever that record is viewed, which when you move from record to record or open up a new record, it is the On Current event. You have to calculate it every time you open up the customer's record.
I can see why you might want to store that value because it can speed up some calculations. If you are running records with thousands and thousands of people in it, it might speed things up. But generally, you want to calculate this value on the fly every time you need it. Because if you open it up a year from now, it is going to be different.
Next up, Rajashri. Still remember, is there any way to remove autonumbers in the table? Thousands of autonumbers got appended to the backend table. I want to remove them.
I seriously wish I had a nickel for every time someone asked me about modifying, deleting, changing autonumbers, etc. Delete whatever records that you do not want in your table and just move on. You do not need to worry about what those autonumbers are. You are not going to run out of them. There is an unlimited supply. Realistically, you are never going to have to worry about what those autonumbers are. They are not for you. They are for internal use only, so Access can make relationships between the tables and make sure that all the records are unique. It does not matter if your autonumber is one or if it is a million and one.
If you do want to get rid of the autonumbers at the end of the table, let us say you have one to a thousand and you delete 200 records off the end, your next record is going to be a thousand and one. But if you compact and repair the database, you will get those numbers back. But do not rely on it. Do not worry about it. Go watch my autonumber video that I just posted earlier. Those numbers are not for you. You should not be using them for anything that a human will see.
Adrian from the UK writes that he creates some tables, queries, and forms in Access, and when he is opening the main menu, Access is automatically opening a table, which he does not want. That table is assigned to a button created with the option group. Could you help me with this issue, please?
I am not sure. I have never seen a database just open a table on its own. When you open up a database unless you specify the startup object, which is usually a form, then all you will see is the navigation pane.
There are two ways you can open stuff up when the database starts. You can either use an On Open event in your main menu form. When the database itself starts, one of two things will happen. It will look for an autoexec macro, a macro actually named autoexec. If you do not see a macro named autoexec, then the only other thing that will happen is that you could specify a startup form in your Access options. I cover this in my free template--my blank template. I will put a link to that down below. In that video where I make the blank template, I show you how to create a startup form. I create a main menu form that you can put buttons on and such.
If you do not have an autoexec macro, look in your startup form. Whatever that startup form is, if there is some code or another macro in there to open up a table, then that is what is causing it. Otherwise, I cannot think of any reason why a table would start up with your database automatically. So if you are saying the database just opens and there is a table, something is calling it. Access does not normally behave that way. I would need to see more. I would need to see a screenshot or something of what is happening, or a copy of your database--which I normally do not accept database submissions, but you could send me a screenshot.
This next one is a good one. Russ from WebStream, Minnesota. Why is it that every time I make a query, it will display all fields in the table and not just the ones I want? Did I checkmark something in the settings or options area?
To which I responded, did you bring down the star from the table? I am not sure. One of the reasons why I like to know what level my student is, one of the reasons I added badges to my website, is so I could see if you are a beginner or an expert. Because I do not know how to answer the questions sometimes. I do not know if you are a developer student or a beginner student, so I just ask if you brought down the star. I did not know if Russ knew how to make queries. Because if you bring down the star, it brings down all the fields. That is the first thing. I usually always go with the simplest solution first, and if that is not it, then I move on.
The star represents all the fields. And then, of course, Russ gets back to me. He figured it out. The show all output fields option was checked. I completely forgot about that option. I have almost never used it. I do not think I have ever used it in the past 20 years.
It is one of those really hidden features that you forget about. But here, let me show you. Here is my TechHelp database. Open this guy up. I just opened up a second copy of it, so let me close that one. Here is a query: customer last first, QA. It just puts last name and first name together, two fields. There is the customer table. I have just used the customer ID, and then there is one calculated field. If you look in the properties for the query, open up the property sheet, click on the background, you will see the query properties. Right there: output all fields. The default is No. If you set that to Yes, then no matter what you bring into the query, if you run the query, you get everything. So he had that checked on. Thanks, Russ. Thanks for reminding me that feature exists. I do not think I have ever seen that actually used.
That is going to do it for Quick Queries number three. I have a whole ton--I have probably, I do not know, 300 more questions. They are decent little questions, they are just not good enough for a whole TechHelp video, but I am going to keep plugging through them. I am going to try and do these more often. I have not done one of these in about a month. But I am going to try and do one Quick Query a week. If you like it, let me know. Post a message, post a comment. Tell me if you like these or not. They are just nice and simple and quick and easy to knock out.
I will see you next time.
How do you become a member? Click the Join button below the video. After you click the Join button, you will see a list of all the different types of membership levels that are available.
Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more.
Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use.
Platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses. These are the full length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more.
But do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more.
If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select all to receive notifications when new videos are posted.
Click on the Show More link below the video to find additional resources and links. You will 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, so if you would like to get an email every time I post a new video, click on the link to join my mailing list.
If you have not yet tried my free Access Level One course, check it out now. It covers all the basics of building databases with Access. It is over three hours long. You can find it on my website or on my YouTube channel. If you like Level One, Level Two is just one dollar, and it is also free for all members of my YouTube channel at any level.
Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there.
Click here to watch my free Access Beginner Level One course, more of my TechHelp videos, or to subscribe to my channel.
Thanks for watching this video from AccessLearningZone.com
Thanks for watching this video.Quiz Q1. What VBA command should you use to maximize a form when it is opened in Access?
A. DoCmd.Maximize
B. DoCmd.OpenForm
C. DoCmd.Minimize
D. DoCmd.ResizeForm
Q2. Where should you place the DoCmd.Maximize command to make sure a form opens at its maximum size every time?
A. In the On Close event
B. In the On Open or On Load event
C. In a table macro
D. In the property sheet only
Q3. What is Richard's preferred method for primary keys in Access tables?
A. Use an autonumber field as the primary key
B. Use the product code as the primary key
C. Always use natural keys only
D. Do not use any primary key
Q4. Why does Richard recommend using autonumbers for primary keys?
A. They are better for internal relationships and tracking unique items
B. They look nicer in reports
C. They are required for all Access tables
D. They make data entry faster
Q5. What is the recommended way to work on an Access database on multiple computers if you are the only user?
A. Use a USB memory stick only
B. Use a cloud sync solution like Google Drive, Dropbox, or OneDrive
C. Email the database back and forth
D. Share the file on Facebook
Q6. What is important to remember when using Google Drive or similar syncing tools with an Access database?
A. Always open and edit the file in the cloud folder
B. Only work on a copy OUTSIDE the sync folder to avoid corruption
C. Syncing is never necessary
D. You must share the folder with everyone
Q7. How do you rename an Access database file?
A. Open the database and use File > Save As
B. Use Windows Explorer to rename the file when Access is closed
C. Use the Rename button in Access
D. It is not possible to rename an Access database
Q8. What programs does Richard use to record and edit his videos?
A. Camtasia and Audacity
B. Bandicam for recording, Bandicut for joining segments
C. Only the Windows Game Bar
D. OBS Studio only
Q9. What is a reason Richard records short video segments and then joins them together?
A. He likes making lots of small files for no reason
B. It allows him to easily re-record mistakes without editing long clips
C. It makes uploading to YouTube faster
D. He cannot use long video files on Bandicam
Q10. What resolution does Richard record his videos in?
A. 4K
B. 1080p
C. 720p
D. 480p
Q11. What is the recommended approach for calculating and displaying someone's age in Access?
A. Store the calculated age in the table permanently
B. Calculate the age on the fly in a form or query when needed
C. Manually update the age field every year
D. Never calculate age in Access
Q12. Why is it NOT a problem if autonumber values in a table have gaps or large numbers?
A. Because humans need to identify records easily
B. Because autonumbers are only for internal use for relationships
C. Because Access limits how high autonumbers go
D. Because you should always renumber them
Q13. What are two database startup behaviors in Access mentioned in the video?
A. File > New triggers a table to launch
B. A macro named autoexec and specifying a startup form
C. Setting a table as default view and always opening it
D. Compile database opens first
Q14. If a query displays all fields from a table even though you only selected some, what could be the cause?
A. The physical table is corrupted
B. The Output All Fields query property is set to Yes
C. You did not write any SQL
D. The table is too large
Q15. Why should you NOT use access autonumbers for anything a human will see?
A. They are sometimes negative numbers
B. They are only for internal tracking of records and relationships
C. They do not sort properly
D. The numbers are always hidden
Q16. If you want your calculated fields (such as someone's age) to update frequently, what events should you consider using?
A. Only the On Click event
B. After Update and On Current events
C. Report Footer event
D. Table Design event
Q17. What is Richard's advice about sharing free Access tutorials?
A. He discourages it because he wants all the views
B. He welcomes more people making tutorials
C. Only experts should make tutorials
D. You must buy membership to create tutorials
Answers: 1-A; 2-B; 3-A; 4-A; 5-B; 6-B; 7-B; 8-B; 9-B; 10-C; 11-B; 12-B; 13-B; 14-B; 15-B; 16-B; 17-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'm going to tackle several questions that I've received about Microsoft Access. These Quick Queries sessions are intended to address all those smaller questions that come in each week—questions that may not require their own full-length video, or perhaps have already been covered in a similar fashion, or are good follow-ups to previous topics. The format here is very relaxed and informal. I think sometimes it's just fun to keep these things light. Not every tutorial needs to be formal and scripted. If you want to pick up some new Access tips or just get your Access fix, this is a great way to do that.
Before I get into the questions, I want to mention a recent experience with web hosting. After being with my previous provider, GoDaddy, for over 11 years, I noticed things were getting worse, slower, and less reliable, especially with technical support. During a major issue, I felt like I was instructing their support staff about fundamental concepts, which became too frustrating given my loyalty as a customer. Eventually I switched over to Winhost, and the difference has been night and day. My website runs much smoother now, and their support was truly helpful getting me set up. This is not a paid plug—just an honest recommendation for anyone looking for solid hosting. There's a more detailed write-up on my site if you're interested.
Now for today's questions.
Emma from Indianapolis asks about resizing overlapping forms in Access so that when you reopen a form, it always maximizes automatically, instead of opening small. The solution is pretty straightforward. You'll want to use a simple line of VBA code—specifically, issuing a command to maximize the form when it opens. Place this command in the form's On Open event. If you're not sure how to work with the On Open event, I cover it in my Access Developer 1 course. You can also maximize the form with VBA right after opening it from another form, for example via a button. Unfortunately, there isn't a built-in form property you can set in Access to have a form always maximize without VBA. I personally dislike the tabbed interface for this reason, because overlapping windows give you more flexibility to arrange your forms.
Next, Brent on YouTube suggested changing the name Quick Queries because it might sound like I'm only talking about Access query objects. But I like the double meaning: "query" also means question, not just the Access object. Plus, it's a bit of a nod to Neil deGrasse Tyson's "Cosmic Queries" on StarTalk, which inspired the name. So, Brent, the title stays!
After that, Aslam from YouTube shared that he is building a database for an automobile trading business and asked whether he should use the unique chassis number as the primary key, or use an autonumber as I generally recommend. I replied that while you can keep your unique field in the table and index it, I always suggest using an autonumber for primary keys in Access. It's simply easier for Access to handle relational integrity and it's more reliable for establishing relationships between tables. You can have both: an autonumber for the primary key and your custom chassis number as an indexed field. This is the practice I use for products as well—my own product IDs sit alongside vendor or manufacturer numbers. There's a comprehensive video on my website where I explain in more detail why I favor autonumbers.
Richard from Detroit wants to work on his database both at home and at work, and currently uses a USB stick to transfer it. There are several alternatives. If the database is not too large and you are the only user, cloud solutions like Google Drive, Dropbox, or OneDrive can sync your file between computers. This works best if you have good internet speeds and your commute gives time for files to sync. However, do not open or work on your database file while it is in the cloud-synced folder, as services like Google Drive may attempt to sync it while open, which could cause corruption. Instead, always move the file out before working on it and then move it back when done. If your database is larger or if you need multi-user access, you'll want to look into more sophisticated solutions, and I have articles and additional videos about running Access on the web for that. Links to those resources can be found on my site.
Alan from YouTube asked how to rename an Access database file. This is a common question, and the answer is simple. Close the database, then locate the file in Windows Explorer and rename it just like you would any other file. If your database is linked to a backend, keep in mind you may need to relink tables if you change file locations or names. For basic single-file databases, though, the name doesn't matter to Access itself.
People often ask about my recording setup. I use Bandicam for screen recording because it is reliable and straightforward, and Bandicut to edit and connect short segments. Recording in brief clips allows me to easily fix mistakes and keep the process efficient. For most basic tutorials, I aim for a polished presentation, but for more advanced courses, I leave in occasional mistakes—this helps learners see how errors occur and how to resolve them. For slides and visuals, I use PowerPoint. My audio setup is just a simple $30 wired headset. I've tried a range of headsets over the years and prefer something lightweight for comfort during long recording sessions. My videos are recorded in 720p HD, which I find is more than adequate for tutorial purposes. If there's enough interest, I might do a full behind-the-scenes tutorial on my recording process.
Deb on YouTube referenced my video on calculating a person's age and asked how to store the calculated age in a table field. Generally, you don't want to store calculated ages because they change continuously. Instead, calculate the age as needed in your forms or queries. If you do find it absolutely necessary to store the age for performance reasons (for example, in a very large table), you'll need to update that value whenever the related birthdate changes or whenever you open the record—this can be handled by the After Update or On Current events for forms. But best practice is to calculate age on-the-fly.
Rajashri asked about removing autonumbers from tables after many have accumulated. Many people ask about managing autonumbers. The best advice is not to worry about them. Just delete any records you wish, and let Access manage autonumbers on its own. Autonumbers are there to ensure unique records for internal use and are practically unlimited. They're not for users to see or worry about. If you do need to reset them after removing records, compact and repair might reclaim values at the end, but don't depend on it. For a fuller discussion on why and how autonumbers work, I suggest watching my autonumber video, also on my site.
Adrian from the UK stated that every time he opens his database, a table opens automatically, even though he wants to start with a main menu. Normally, Access doesn't open tables by default unless you have set up a startup object or an autoexec macro to do so. If a table is popping up immediately, check your startup settings in Access Options and see whether there's a macro or code that instructs Access to open the table. If not, it could be getting opened by code in your main menu form. I recommend reviewing your startup processes. I have a blank template video where I explain how to create a proper startup form—there's a link on my site.
Russ from Minnesota had an issue where all the fields appear in his query output, not just the ones he selected. This is likely due to the "output all fields" property being set to Yes for the query, or possibly because the asterisk (star) was added to the query design grid. The star pulls in every field from the table. If you only want selected fields to appear, make sure you only bring down those specific fields into your grid and check that the "output all fields" option is set to No in the query property sheet. Thanks to Russ for reminding me that property even exists—it's something I almost never use.
That wraps up Quick Queries number three. I have many more questions queued up, and I'll continue these sessions regularly. Let me know if you find this format helpful by leaving a comment or feedback—these are quick to produce and a great way to share knowledge.
If you're interested in supporting the channel further, you can become a member. Membership unlocks various benefits: Silver members get extended cut TechHelp videos and live sessions, Gold members have access to downloadable sample databases and my code vault, and Platinum members have access to my complete courses, covering not just Access but also Word, Excel, Visual Basic, ASP, and more. Either way, my free TechHelp videos will always continue to be available as long as there's interest.
If you enjoyed this session, please like and subscribe to the channel for more content. For a list of related videos, resources, and additional details, visit the links on my website. If you'd like notifications by email, you can also join my mailing list there.
If you haven't already tried my free Access Level One course, it covers all the basics of building databases and is over three hours long, available on both my website and YouTube. Level Two is just one dollar, and it's included for all channel members.
To have your own question answered in a future Quick Queries video, submit it via my TechHelp page.
You can find the 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 Maximizing forms with DoCmd.Maximize in Access
Using On Open and On Load events to maximize forms
Autonumber vs natural key for primary keys in Access tables
Storing custom product or chassis numbers alongside autonumbers
Syncing Access databases across computers with cloud services
Safely working on Access databases with Google Drive or Dropbox
Avoiding file corruption when accessing databases in sync folders
Renaming Access database files (.accdb) in Windows Explorer
Recording tutorial videos using Bandicam and Bandicut
Choosing headsets for recording tutorial videos
Recording at 720p resolution for tutorial content
Updating calculated age fields in Access tables and forms
Using After Update and On Current events for calculated fields
Managing and resetting autonumbers in Access tables
Understanding autonumbers' internal purpose in Access
Using Compact and Repair to reset autonumbers
Suppressing automatically opened tables on Access startup
Configuring startup forms and autoexec macros in Access
Resolving queries showing all fields due to output all fields property
Adjusting the Output All Fields query property in Access |
