Quick Queries #6
By Richard Rost
3 years ago
Space in Field Name, AutoNumbers, Can Grow/Shrink
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...
Topics Covered
- Spaces in field names
- Autonumbers are not for you!
- Sequential numbers
- Which mic did you choose?
- Open 32-bit database from 64-bit Access
- Relationships
- Invoicing
- Association Database
- Member Perk: Pre-Release Videos
- Can grow/shrink in forms
Addendum
- Cody also needs a space before the word WHERE.
Previous Quick Queries
Links
Keywords
microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, quick queries, qq, Spaces in field names, Autonumbers are not for you, Sequential numbers, Which mic did you choose, Open 32-bit database from 64-bit Access, Relationships, Invoicing, Association Database, Member Perk Pre-Release Videos, Can grow/shrink in forms
Intro In this video, we'll talk about a variety of Microsoft Access topics based on your questions, including troubleshooting VBA SQL statement errors, the use of spaces in field names, how autonumber fields work, issues with opening ACCDB and ACCDE files across different Access versions, tips for joining tables for reports, and when to use Can Grow and Can Shrink in forms and reports. We'll also discuss preferences for microphone setups, updates on the association database series, and general advice for posting questions on the website and getting the most from AccessLearningZone.com.Transcript Hey there folks, it's been like a year and a half since I've done one of these, but welcome to another Quick Queries video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.
For those of you who haven't seen these because it has been a while, the Quick Queries are basically a way where I can put a whole bunch of questions into one video, whereas maybe each of these questions doesn't merit its own video. I put them all together and we'll do a Quick Queries about it. This may or may not contain actual queries about queries. I don't mean queries in the Access sense; I mean queries in the question sense.
Let's get started.
To start us off, Cody says he's got a simple question about VBA coding. You followed all the steps, but his SQL statement isn't working. Can you figure it out? Take a quick look at it and see if you can tell what's wrong with this SQL statement. I'm going to put a small pause in here after each question to give you a chance to pause if you have to, to see if you can figure out what the problem is.
This is why I tell you never to put spaces in your field names or your table names. See right there, "Consumer info" is his field name, he's got a space in there. If you do that, you have to remember to put the "consumer info" inside of square brackets; otherwise, Access doesn't know what you're talking about, so that's the problem. I would go back and get rid of that space in there, and it'll make your life a whole lot easier.
There are often things and little tips and clues that I give in my Access Beginner 1 class and 2 and so on. At the time, you might not see what the big deal is. Why can't I put "first name," "first space name"? Well, that's why. When you start getting into more advanced stuff like VBA programming and query design and SQL, I'm giving you my benefit of 30 years of doing this, so trust me - no spaces. You'll thank me later.
Next up, Jim is adding records in his table, and he's got 84 records right now. When he adds another one, he's expecting the primary key, the autonumber, the ID, to automatically increment to 85, but that's not happening. Why?
If you're using an autonumber, that number is not for you, you shouldn't worry about it. Don't use that as any kind of number that you care what the value is. Autonumbers are not for you; they're for the database, for Access to internally create relationships, make sure you have unique records, and so on. Don't use it for any number where you care what that value actually is or if you want to be able to change it.
If you need sequential numbering, I know the need comes up from time to time, for example, if you want invoice numbers without gaps in them. Then go watch this video. I'll put links to all the videos that I recommend down below in the description so you can click on them and go watch them and learn some stuff.
Next up, Paul wants to know which mic setup I actually went for. If you're new to my channel, I've been doing some different mic tests to get the best sound possible, so I had you guys vote on which microphones you thought sounded best. I had some different stand mics and boom mics and all kinds of stuff.
I finally went with the Tonor mic - this guy - and the boom stick. Not this boom stick. This baby is made in Grand Rapids, Michigan, retails for about $199.95. If you don't know what movie that is, go look for "Army of Darkness," one of my favorite movies of all time. I've got a little desktop boom stand that goes with it, so it works really well. I can swing the mic back and forth, and when I'm not recording, I can just slide it away and it works very nicely. Thanks for asking, Paul, and thanks to everyone who gave your input on the different videos that I posted as far as what sounds good and what doesn't.
Next up, Melania asks, I need your help here. My ACCDB is created with 32-bit version of Access. How to open this in the 64-bit version? Well, normally if you're using an ACCDB file, those will open just fine. The code might not run - we'll talk about that in a second.
If you're using an ACCDE file - that's when you make a version that you compile and you give to someone else so they can't get into your code and your forms and stuff - those have to be specific to whatever version of Access the machine has on it. That's one of the reasons why I stopped putting 32-bit versions of those on my website, because I've upgraded and I don't even have 32-bit running on any of my machines anymore.
I've got a video here to show you how to import objects, if you've never done that before. You can usually just click and drag them, or you can go and do the "Get External Data" and pull them all in at once.
Your code's a little bit different. If you open up your database and it's trying to run and you're getting compile errors and code errors, things like this, that could be because you have to add a little bit to your code; this video talks about it in more detail. You have to change some things to pointer safe - that keyword there - and some things you might have to change to long pointers. Like I said, this video will go over that in a lot more detail, so check that out too.
Next up, Wesley: first comment, really? Seriously, people still do this? That was popular back in forums in 2004. I'm not picking on you, Wesley. You'll be surprised; I see this a lot still today in 2023. People still do "first comment." I think it's time to retire this, guys.
Next up is Steven - or is it Stefan or Steven? I'm not sure about that one. He's saying he's got an order table and a client table and a report. The report is an order number, the quantity from the order table, and the client from the client table. When I print the report, it prints the client number and not the name of the client. So what do you do?
That's just a matter of joining them together in a query first. Watch my invoicing video and also my relationships videos. What you want to do is create a relationship; if this is your client right here and this is your invoice information, let's say you want to link those together in a query, then you can bring together the order information along with whatever details you want to see from the customer table as well.
Go watch relationships, then go watch invoicing. I do this exact same thing in this video. We build an invoice based on customer data and order data.
Next up, Moe's - is it Moe's? How do you pronounce that? You guys have to let me know. Thanks for coming up with this so far as my association database. As of the time he wrote this comment, there were four parts available. Out of curiosity, how many parts will there be in total?
I don't know, honestly. There could be - right now I think I'm up to eight - and I'm going to keep doing it until either I get out of ideas or people stop watching. Then I'll be sad. I can see right now that every one is getting less views than the previous one, so people are falling off. When the number gets down to where it's not getting as many hits as I want it to have, I'll stop doing it.
For those of you who don't know what this is, I'm going to start doing releases of databases that we're going to build together. You're going to watch me from the beginning, from scratch, build a database for a particular type of business or a particular type of need or whatever. The association database is like a membership database, and you can use it for anything that involves people and membership types. It could be your golf club, your HOA, your church - that's a popular request; you want to track donations, tithes, that kind of stuff. I just called it "association" because it's the most generic term that I could think of to fit all of these different types.
How many will there be? I don't know. I'm up to eight, probably at least ten. I have a couple more things I want to do, and I'm looking forward to your feedback to see what you want to see in future videos, so keep watching and I'll keep making them.
Speaking of the association database, Steven says another great video, can't wait for the next video, number five. Well, Steve, parts five through eight are available right now on my website for the members - hint, hint. Whenever I finish a video, I put it on my website immediately that day, and members get access to all of those. They're all queued up for YouTube and for my website for the public. I have them come out one per day. So, for example, if I finish four of them at once, they're all on the website, so if you're a member, you can watch them all now.
Silver membership is only five dollars and ninety-nine cents a month. Well worth the price of admission.
This is one from the forums on my website. If you are a member, you get to post on my website too, and I check the YouTube comments maybe once a week and I don't usually spend a ton of time answering those. If you want to get your question answered and you are a member, come to my website and post it there because not only do I try my best to answer those more often, but I've got a great group of six moderators and they're fantastic, and they usually get to stuff way before I do.
Even non-members - there's a visitor forum on my website too. I'll put a link to that down below.
So if you're not a member - which you should be - you can still post.
Richard on my website asks, does Can Grow and Can Shrink work with forms? I know forms can use reports, but I'm having trouble with it on forms. I have set the form detail to Yes on Can Grow and Can Shrink, because you have to set the text box itself and the detail section or whatever section it happens to be in. But for some reason, it's not working on his forms; it'll work in the reports, but not the forms. Why is that?
Can Grow and Can Shrink only work when you print a form, which we never do. We print reports. You would only use Can Grow and Can Shrink in a report.
Yes, you can print forms. There's a form header/footer and a page header/footer, and if you go into design view, that page header/footer is when you print a form. Some people are lazy; you don't want to take the time to make a separate report. You can print the form if you want something quick for yourself, that's fine, but in a finished production database, I never rely on that. I don't let my users print forms, and there are some other reasons why too, but basically, it'll work on a report but it won't work in a form on the screen. It'll work in a form when you print it, but it won't work on a form on the screen, which is what forms are designed for.
If you want to learn more about Can Grow and Can Shrink, here's a video for you. I'll put a link down below.
That's going to do it for today, folks. If you've got a question and you want it answered, go ahead and post it in the comments down below. It's best to post it on my website, but I do check the YouTube comments as you've seen.
Be sure to check out my website, and if you haven't taken my Access Beginner 1 class, do it now. Even if you've been using Access for a while, I cover a lot of stuff in Access Beginner 1 that I want people to know before they start working with Access, like things like don't put spaces in your field names, that kind of stuff. It's simple beginners' tips, but it'll make your life so much easier in the long run.
I've been doing this for a little while now, folks, so take it from me.
Hope you learned something, and I'll see you next time.Quiz Q1. Why is it generally advised not to put spaces in table or field names in Access?
A. Spaces make field names more readable in queries
B. Spaces require you to use square brackets in SQL statements
C. Spaces make tables easier to sort
D. All databases prohibit spaces in field names
Q2. When using an autonumber field as a primary key in Access, what should you keep in mind?
A. Autonumbers can be freely changed as needed
B. Autonumbers are designed for internal database use, not for user-facing numbering
C. Autonumbers will never have gaps in the sequence
D. You should always display autonumber fields on reports
Q3. What is the best way to create sequential numbers without gaps (e.g., for invoices) in Access?
A. Use the autonumber field as your invoice number
B. Manually enter sequential numbers
C. Use a custom sequencing method discussed in specialized videos
D. Use the record count property of the table
Q4. What potential issue might you face when opening an ACCDB file created in 32-bit Access on a 64-bit version?
A. The file will not open at all
B. The database objects may need to be imported, but most ACCDB files will open fine
C. Microsoft Access will automatically convert all code for you
D. Reports will disappear
Q5. Why might an ACCDE file compiled on a 32-bit version of Access not work on a 64-bit system?
A. ACCDE files are incompatible with all bit versions
B. ACCDE files must match the bit version of Access where they are run
C. 64-bit systems never support ACCDE files
D. The file format is obsolete and unsupported
Q6. If VBA code does not run correctly after migrating to 64-bit Access, what is a likely solution?
A. Switch back to 32-bit hardware
B. Change code to use the PtrSafe keyword and update long pointers as needed
C. Reinstall Microsoft Office
D. Downgrade to Access 2010
Q7. If a report is printing client numbers instead of client names, what should you do?
A. Only change the report design
B. Ensure that you join the order and client tables in a query to pull the client name
C. Add duplicate client name fields to the order table
D. Delete the client number field
Q8. What is the "association database" mentioned in the video designed for?
A. Only tracking golf club memberships
B. Managing any membership-based organization like clubs, HOAs, churches, etc.
C. Creating inventory databases
D. Payroll and human resources tracking
Q9. Why does Can Grow and Can Shrink not appear to work on Access forms?
A. The options are only for reports, not forms on screen
B. Forms have no headers or footers
C. Can Grow/Can Shrink only work in macros
D. You must enable this in Access global settings
Q10. When will the Can Grow and Can Shrink settings on a form actually have any effect?
A. On the screen in Form View
B. When the form is printed
C. Only for subforms
D. When exporting the form to Excel
Q11. What is a key benefit of AccessLearningZone.com membership according to the video?
A. Access to free hardware
B. Ability to post questions on the website and get prioritized help from moderators and the instructor
C. Automatic downloads of Microsoft Access
D. Lifetime email support
Answers: 1-B; 2-B; 3-C; 4-B; 5-B; 6-B; 7-B; 8-B; 9-A; 10-B; 11-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary In today's Quick Queries video from Access Learning Zone, I am taking on a set of questions submitted by viewers. This series is all about addressing those shorter questions that do not really need a full video of their own, so I collect several into one session and answer them together. Keep in mind, these questions do not always relate to Access queries themselves – sometimes the topics vary, and the term "query" just means a question from you.
Let us get started.
First, Cody asked about a problem with his VBA code using an SQL statement. He followed all the normal steps, but something was not working. The issue turned out to be with spaces in the field name – he had a field named "Consumer info" and did not use square brackets around it in his SQL. Whenever you have spaces in your field or table names in Access, you must surround those names with square brackets, like [Consumer info]. However, the best advice is not to use spaces at all in your field and table names. If you avoid them from the start, you will run into far fewer problems when you move into more advanced work, such as VBA programming and query writing. This is one of those tips I cover back in my introductory Access courses, and it really pays off to follow it.
Moving on, Jim says he is adding records into his table and notices that his autonumber field is not incrementing as expected. He had 84 records, added one more, but the next number was not 85. The thing to remember is that autonumbers are for Access, not for you. They ensure that every record is unique and allow Access to build relationships between tables, but they are not intended as meaningful sequential numbers. You should not use autonumbers for values you need to have in a predictable or gapless order, especially in situations like invoice numbers. If you need sequential numbers with no gaps, there are other techniques, and I have videos about that which you will find linked below.
Paul asked which microphone setup I finally chose after testing out several and getting viewer feedback. For those interested, I went with the Tonor mic on a boom stand, which makes it easy to move the mic out of the way when I am not recording. It has worked well for my needs and delivers the quality I was looking for. Thanks to everyone who shared their thoughts and helped me choose.
The next question is from Melania. She created an Access ACCDB database in a 32-bit version and wondered how to open it in the 64-bit version. Generally, ACCDB files can be opened without problem across 32-bit and 64-bit versions of Access, but if you have an ACCDE file – which is a compiled version you distribute to others – then it does need to match the version of Access installed. That is why I no longer offer 32-bit versions for download, since all my current systems are 64-bit. If you want to bring objects from one database to another, you can import them using the Import Objects feature. This is useful if you are migrating between versions. Be aware, if your code does not run after opening the database in 64-bit Access, you might need to update your VBA to make it pointer safe and change the data types on some API calls. There are resources and video links below where I explain these code updates in more detail.
Wesley commented simply, "first comment," and I have to say this is a bit of internet history. People used to rush to claim the first comment spot in forums back in the early 2000s, but it is probably time to retire that tradition.
Next, Steven (or Stefan) asked about getting names on a report instead of numbers. He has an order table and a client table. In his report, he sees only the client numbers instead of their names. The solution for this is creating a query that joins the order and client tables, so you can pull in all of the order details plus the client information you want, such as names. This approach is covered in detail in my relationships and invoicing video tutorials, so check those out for step-by-step instruction.
Moe's question is about the association database series I am currently publishing. He wondered how many parts there will be in total. Truthfully, I do not have a fixed number in mind. I add more parts as long as there is something to teach and people are still watching and asking for more. At the time of answering, we were up to eight parts. As long as the series keeps getting interest from viewers, I will keep producing more. The association database is designed to be a flexible membership database, perfect for clubs, HOAs, churches, and similar organizations. I chose the term "association" to keep it generic.
Steven also asked when upcoming videos (parts five through eight) of the association database project would be available. The answer is that members on my website get immediate access to new videos as soon as I complete them. They roll out to the public on a daily schedule, but members get early access. Membership is currently $5.99 per month, and you will get instant access to all the latest content. As a bonus, members can also post questions in the forums on my website, and I or one of the moderators often answer them more quickly than in YouTube comments.
Even if you are not a paid member, there is a visitor forum available on my website where anyone can post questions. I encourage everyone to join in and participate in the community.
Another question came from Richard, asking whether the Can Grow and Can Shrink properties work in Access forms. These settings allow controls like text boxes to expand or contract to fit their contents, but this only works when printing the form, not when viewing it on the screen. In practice, we almost never print forms – we print reports – so Can Grow and Can Shrink are really intended for reports. If you want more information on this, I have a separate video available.
That brings us to the end of this Quick Queries session. If you have more questions, the best place to ask them is in the forums on my website. I do check YouTube comments as well, though not as frequently.
If you are new to Access or want a refresher, I strongly recommend my Access Beginner 1 class. Even seasoned users will pick up tips to avoid headaches down the road, such as avoiding spaces in field names. These basics make a big difference.
As always, I hope you learned something helpful today. 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 Identifying and fixing SQL errors caused by spaces in field names
Explanation of why not to use spaces in Access field/table names
Proper use of square brackets in SQL statements in Access
Understanding autonumber fields and their intended use
Why autonumber fields can have gaps and are unreliable for user-facing numbers
How to generate sequential numbers without gaps in Access
Opening 32-bit ACCDB files in 64-bit Access
Differences between ACCDB and ACCDE compatibility across versions
Importing objects between Access databases
Updating VBA code for 64-bit Access compatibility (Pointer Safe, LongPtr)
Resolving issues with displaying client names instead of IDs in reports
Creating relationships between Access tables for linked data
Building queries to combine data from multiple tables
Differences between Can Grow/Can Shrink in forms and reports in Access
Why Can Grow and Can Shrink work in reports but not interactive forms
Printing Access forms and limitations compared to using reports |
