Quick Queries #4
By Richard Rost
5 years ago
Quote Invoice, Building Database at Work, 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...
- Changing a Quote to an Invoice
- Combo Box Missing Find Record Option
- Does Access have AutoFill like Excel
- Change Individual Row Height
- DAO.Database vs. just Database
- Importing Data from a PDF File
- Short Text Field Size Property
- What is an LACCDB File and Can I Delete It?
- Can a Corrupt Database be Recovered?
- Is There an Online Replacement for Access?
- Ownership of a Database Built at Work
- Checkboxes Not Showing Up in New Records
Previous Quick Queries
 Links
Keywords
microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, quick queries, qq, quote, invoice, combo, find record, autofill, excel, row height, dao, dao.database, import pdf, short text, field size, laccdb, corrupt, compact, repair, access online
Intro In this video, we'll discuss a variety of common questions about Microsoft Access, including best practices for storing quotes and contracts, using combo boxes to search records, creating sequence numbers, adjusting form and report layouts, and the differences between DAO and ADO programming methods. We also cover topics like compacting and repairing databases, field size optimization, the purpose of LACCDB files, recovering corrupted databases, the future of Access versus cloud solutions, ownership of databases created at work, and troubleshooting default values for check boxes in forms. This is Quick Queries 4.Transcript Welcome to another TechHelp Quick Queries brought to you by AccessLearningZone.com. My name is Richard Rost, your instructor. The Quick Queries videos are quick answers to queries about Microsoft Access queries in the generic sense of questions. They may or may not contain actual information about Access queries. That's kind of why I picked the name "Quick Queries" because it has a double meaning.
Anyway, what do we have today?
The first question comes from Carl, one of my Platinum members. Carl says: "Once a quote becomes an order, I want to convert the quote to a contract but keep the original quote. I want to click a button which would copy the quote to a contract, which would then store the information in the contract table. The reason I want to is the contract may change, but I want to retain the original quote."
Carl, I get asked this one a lot. I know you've already purchased Access Developer 24, and that's the one that I use to show people how to copy an order. Because you don't only have to copy the order table itself, you have to copy all the related details.
Now, it's possible to do it manually. You can copy the order and then paste it as a new order, then go back to the original order, copy all the details, go to the new one, and paste all those. Or you can do it with a macro or some programming. I don't like that. In order to do it properly, you really need to use a recordset. That's why I covered it in my Developer series.
But I wouldn't use two separate tables. One table is plenty. If you want to differentiate between a quote and a contract or a quote and an invoice or whatever, just have a Yes/No field. All the fields are the same. You don't need to duplicate all that data in different tables. Just use the same table and have a Yes/No field in there to differentiate between quote or invoice or, in your case, quote or contract, or contract and job, or whatever. If it's all the same data, put it in the same table.
The only time I ever really split up tables like that is if it's for archiving purposes. If you've got ten years' worth of invoices in your system and you almost never have to pull up anything older than, let's say, three years, you can dump that stuff to a backup table. You might have to have a way to get to it, like if you want to search for older orders, you could use a union query or put them together somehow with a make table or append, or whatever. I usually use it for things like email blasts. I don't usually keep email blasts longer than six months. I just dump them to an archive table where I can go back a couple of years and see, and if it's over three years old, I dump those. Incoming email I do the same way. It comes into my database. I want to keep old correspondence, but if it's older than ten years, I dump it to an archive table. So if I really want to go back and find something, I can, but I almost never do, and that email table can get pretty big.
But anyway, when it comes to quotes and invoices, just have a Yes/No field. I covered that in Access Expert 10, and I also show you how to do things like, when you print it out, have the word on top change from "quote" to "invoice" or "quote" to "contract" or whatever.
Alright, so you've already got Developer 24. Watch that; that showed you how to copy an order to another record if you want to keep it. I get it: sometimes you want to have the original quote and then make changes to it, but still have the original in the system.
The next question comes from Alan, a Gold member. I actually get this one once in a while. I've got a tip for this one on my website. This is from like seven years ago, so it was before I was doing the TechHelp videos, but I'll point you to that in a second.
Alan says: "When I use the combo box wizard, I don't get the third option to find a record on my form based on the value I selected in my combo box." It's one of the combo box wizard options. This was in regard to one of my older tips that I had on using a combo box to search records. I covered it in Access 2010 Beginner 8. The version is meaningless; it works on all versions of Access, but basically you use a combo box to search for records.
The problem is that if your form isn't based on a table or a real simple query, not an SQL statement, then that third option doesn't show up. You can't have a form based on an SQL statement. I explain the details on the "No Third Option" link—I'll put a link down below. If you open up the form's record source property and you see something like that, that's an SQL statement. Make sure it's like CustomerT or CustomerQ or something simple. If not, you won't get that option. Go to this page, I'll explain it in more detail.
Next up, Rohit from YouTube: "Is there a way to have a sequence number like Excel? I put 1 and 2 in a column and drag to create up to 100."
That's called autofill in Excel, where you put, like, 1 and 2 into adjacent cells, then select them both, and then autofill down. No, Access does not have something like that. You could do it with some VBA, but it would involve some programming. If you wanted to do a number sequence like 5, 10, 15, 20, or something like that, you could do it with a loop. You could probably do it with a macro or some VBA. But without programming, no, you cannot do this in Access.
Next up, Nela from YouTube: "I'm new to Access and found a database template already available for task list. I'm using a description column which has varying content length. I'm sure I'm going to find a way to adjust the row height individually to fit the content. Every time I change one row width, it changes all of them. Any help or direction to a video on this would be greatly appreciated."
Nela, make sure you get your terminology straight when you're talking about Access or Excel: rows have height and columns have width. There's no such thing as a row width. A row has height; columns have width. So when you change a row's height, for one row, Access changes them all—this is in Excel. In Excel, you can have different heights for different rows, but not in Access. They're all uniform. Same thing with the columns: if you change the column width, they're all going to be the same.
That's with a form. In a report, you can have variable row heights. So if one field, for example, is multiple paragraphs, you can set something called the CanGrow/CanShrink property. Normally, CanGrow is set to On. That video there, "CanGrow," will explain how that works.
Public service announcement: everyone check out this page, the Terminology page. It talks all about the context of asking questions—like, I might not understand your business. So try not to use custom terms that I might not know. I know Access. I know databases. I might not know underwater basket weaving, so if you've got specific terms that are unique to your business, you might need to explain them generically to me. When you ask a question, also try to remember Access terms. Access uses records, not rows. Rows are in Excel. I mean, yeah, you can talk about rows in a table, but it's records for Access. Fields, not columns. Combo boxes, not drop downs. Don't call them drop downs. I know you drop it down, but it's called a combo box in Access. Remember, forms are for the screen; reports are for printing.
People always tell me, "I have to reproduce this form." Why? If they want it to look like that on the screen for data entry, sometimes people do. I need to know that. Generally, people are talking about printed forms—things that you're going to be printing out to look a certain way. Those are reports, not forms. Read more on that page.
Next up, Adam—hey Adam, how are you doing? One of my Gold members and an Access veteran. I've singled out a bunch of my students who are my Developer students. They're really great. They're awesome. They help out other people in my forums, which helps me out a lot. I appreciate them tremendously, so they're called my Access veterans.
Adam asked a very relevant question lately: "I see some code—if you Google search and you find some code that has 'Dim x as DAO.Database' and some just says 'Dim x as Database' like your code. Just curious if there's any difference, with or without the Database object?"
Here's the bottom line. There are two ways to access data in your database programmatically (well, there are more than two, but the two big ones are ADO and DAO): ActiveX Data Objects and Data Access Objects.
DAO was around first, and it is specific to Access. It allows you to get in there and modify the table structure, read and write recordsets, and all that stuff, but it's Access-specific and was the initial type of recordset that Access had available. All you had to say was "Dim db as Database," then "Dim rs as Recordset."
Then, a few years later, ADO came along—ActiveX Data Objects. ADO is more generic. You can use it to talk to Access tables, SQL Server, or many other types of servers. Microsoft actually switched the default programming method to ADO. I forget what version it was, but it messed up all my code because, in my code, I didn't specify ADO or DAO. I just said "Dim db as Database" because the default was DAO. So I had to go and change all my code to say "DAO.Database."
For those who don't know programming, this might be over your head. I apologize—the Quick Queries is a mixture of all kinds of different questions.
After ADO, a few versions later, Microsoft switched the default back to DAO, which is where it is now: the Access-specific Data Access Objects. To answer your question, Adam: you only need to specify if you have both of them installed in your code editor Tools/References. These are the defaults now. DAO is included by default, I believe, in the Microsoft Access 16 Object Library. It's the default and already installed; you don't have to do anything.
If you want to use ADO—maybe you're connecting to SQL Server or something like that—you have to scroll down, find "Microsoft ActiveX Data Objects." Here's the ADO stuff. And yes, I'm going to have some lessons coming up for the Developer students on how to do that because it makes talking to other databases a lot easier if you use ADO.
ASP, by the way, uses ADO, and the way that you talk to your recordsets and stuff is very similar but slightly different. It actually annoys me sometimes because I wish they had just used one language.
That's the difference. Normally, you can just say "Dim db as Database," and that's the default. But if you want to be certain, you can come in here and say "DAO.Database," and that'll make sure your code is set. If you are going to possibly add ADO to your database in the future, now you don't have to go through all your code and change all your database references.
I'm pretty sure ".as ADO.Database"—yes, it's not installed, so that's the default there. It's kind of like how you don't have to say, for example, "FirstName equals Me.FirstName." That "Me" is unnecessary. I see this in a lot of people's code. Technically, it should be Me!FirstName to reference a field because a dot is a property, although Access makes all the fields properties. It's weird how they do that. I spend a whole other video on this. You don't really need the "Me" unless you're accessing a property like "Me.Caption" (property of the form) or "Me.Width" or something like that.
I could go on and on, but that should answer your question.
Next up, Shane from YouTube: "After watching many of your videos, I would like to know if it's possible on an Access database to be able, after importing two or more PDF files into a report, to then be able to email them all out as PDF files for the end person to be able to open the PDF files again."
Let me see if I understand what you're talking about here. You want to import PDF files into Access and then be able to re-export them again. As far as I know, you cannot import a PDF directly into a report unless you make a screenshot of it and use the image or store the PDF in an Access attachment, which I don't recommend. I don't like the attachment fields at all. I don't know any way to import a PDF into Access; Access does not directly read PDF files. You'd have to somehow save that PDF data as a text file, CSV, or something else that Access can import. If you've got a PDF that's got a spreadsheet of data in it, you can copy and paste it.
I don't have the paid version of Adobe Acrobat, so I don't know what it can do. I did have a paid version way back before Access was able to create PDF files by itself. I did buy the version, and we're talking late 90s, so I haven't really used it in a long time.
Once you get the data inside Access, yeah, you can do whatever you want—you can create as many PDFs as you want. But I don't know how to import a PDF into a report. If anyone else knows, please, by all means, share. Personally, it's not something I've ever had to do. If I come across something like this, if I have a client who really needs to know how to do it, I'll look it up. I'll try to figure it out. I'll Google it myself. As far as off the top of my head, I do not know any way to import PDF data into Access.
Got another question from Adam—and yes, my Access veterans can ask as many questions as they want: "Kind of curious about your opinion on compacting and repairing, or rather, how often should you do it? Is there any harm in just doing it every chance you get?"
For those who don't know what compacting and repairing a database is, go watch this thing right here, the Compact and Repair video. Compacting and repairing is basically—even if your database isn't broken—over time, it gets cluttered and bloated with empty space. Every time you delete a record, for example, Access marks the record deleted, but that space is still wasted until you compact your database. It can slowly grow over time and get bigger with all that empty space, and of course, a big database is a slow database, especially if you're running it over the network.
So you should compact and repair your database. My suggestion: at least once a month. Here's what I said to Adam. It all depends on the activity of the database. A moderately used database, once a month, is fine. I've got one ACCDB file that holds all my incoming and outgoing emails that I compact weekly because as emails are added and deleted, it grows. Normally when I compact it, it's like 200 MB (could be more, I save all the old stuff too), and over the course of the week, it'll grow to five or 600 MB, so it'll get out of hand because it's a lot of incoming and outgoing traffic.
There's no harm in doing it daily if you want to—just make sure you have good backups. I've seen over-compacting corrupt the database, especially if it's over the network. The best way to compact the database is to do it on the machine that the database is actually on. If it's a big file, like a 1.5 GB ACCDB file, I suggest you go to the server and do your compacting there, or at least run a routine on that machine. If you do it over the network, it just increases the chances of that file getting corrupted. I've seen it happen.
I don't like the option that says "Compact on Close" unless it's a tiny database and it's on your computer. Don't use that, especially if it's a multi-user database because you could compact and close it, and someone else has it open and it just causes problems. It's supposed to not let you, but it does. Access isn't perfect.
I've seen some databases get corrupted by clients that I told, "Don't do the auto compact," and they did it anyway. I said, just go in once a week or once a month even and compact your database, and they don't listen.
Once a month is usually fine unless you know that you have a file that gets crazy big. Once a month is good enough unless you're approaching that 2 GB limit, and if that's the case, break it up, split your tables. Put some of your tables in one file, some of your tables in another. Try to identify the tables that get used all the time, as far as records getting added and deleted—put those in their own table, and then you can compact that one on a more frequent schedule if you want to.
Go watch that video on compacting.
William from Dallas, Texas: "I notice that you don't change the size of the fields when creating tables. I was taught decades ago to change the size of text fields to keep the table as small as possible, such as a phone number needing 14 characters instead of the default 255, or does the compact feature in Access eliminate the issue of space requirements?"
Yes, William, I was taught that way too when I was first learning Access back in the 90s. I think I started using Access version 2.0 in 1994. When I was learning Access, that's how it behaved. Whatever space you set aside for a text field was reserved on the drive, so if your largest first name field is 20 characters and you reserved 255 for all of them, it wasted all that space. Back in the 90s, space was a lot more expensive per megabyte, so you were taught to optimize your field sizes, and that's actually how I taught my Access classes up until recently.
I recently did a video—it's at the bottom, "Text Field Sizes"—where I did some testing and ran a whole bunch of crazy loops to see, and yeah, Access doesn't have that problem anymore. Whatever size you put in a short text file is the max length allowed, but only the actual used space is reserved on the disk. You don't have to worry about optimizing it. The only time you have to worry about that now is if you want to set a hard maximum limit. For example, phone numbers: 10 characters. If you want to force it so the user cannot add an 11th character, set the maximum field size to 10. Aside from that, don't worry about it. I leave all my fields at 255 now just to let people type in whatever they want to type in. You can drop it down: first name, I can't think of one being more than 50 characters, so feel free to do that, but it's no longer necessary to do this now.
Yeah, I know I taught my classes like this up until 2013, the last time I did my beginner level one class.
Next up, Henry from YouTube (or is it Henri?): "What is the LACCDB file that Access creates, and is it safe to delete?"
That's a record locking file. Whether or not you have record locking on in your database, Access will create that whenever you open a database. It'll open it in the same folder and with the same file name as your database file. Even if it's a single-user database, it still creates this file. If you exit Access, it's supposed to delete that file automatically. Let Access do its thing.
However, sometimes if Access crashes or if you shut down your computer unexpectedly (the power drops or whatever), that file might get left behind. It's perfectly safe to delete it. It should be a really tiny file, not very big, and yes, you can delete an LACCDB file. Of course, make sure you have backups of everything, but don't worry about it if you have to delete that file.
Next up, Mylan from Georgia, one of my longtime Access students. She asks: "Can a corrupted database be recovered? I have attempted compact and repair but to no avail. I might need paid support."
Sorry to hear that, Mylan. If it goes beyond compact and repair, it's beyond what I can do for you. There are database recovery services and programs out there that I've seen advertised, but I haven't had the need to use one since the 90s, so I don't know what to recommend. This isn't a service that I provide. Like I said, if it's beyond compact and repair, it's beyond what I can do.
Back in the 90s there was a service that I ended up using for a client of mine. They had a really bad corrupted database, and the tool was able to recover about 90% of their data. The database file itself was corrupted. They had a software program you could purchase for a couple hundred bucks. It went through, sector by sector, trying to reconstruct the file, and it did a good job. It got most of their data back.
But my suggestion is, don't get to that point. This is why I stress the importance of proper database backup every night automatically. I've got multiple videos on the subject and a template for it you can get. It's a template you can install right in your database. It'll copy all your files to a backup folder, set up Google Drive. I show you how to do that in the videos. Go watch my backup video. There's no excuse for not setting up your database backup; if your database runs your business, back it up.
I've got mine to backup to a different computer in my office. It backs up off-site to Google Drive, up in the cloud. It rotates every day; I keep the last four days, I keep the last month, every month. At most, you should lose at most a day's worth of data. The way my stuff is set up, I can also pull it out of my logs. So even if my database is corrupted, I save a log file. It keeps a transaction log in a separate database file.
If any of you have any database repair software that you know of, or an Access repair tool that you've used and can personally vouch for, email me and let me know. I haven't used one in probably 20 years, and I don't know what's out there. I could do a Google search too, but I don't personally recommend any of them. So you guys let me know. If you have something, post in the comments or send me an email.
The good news is that, in the last 20 years or so, I haven't had a database that was corrupted so badly that Access couldn't fix it itself. So they've gotten a lot better with the file format and the repair technology inside Compact and Repair, and I just haven't had it happen. Mylan, yours is the first email I've gotten in probably five or six years about someone asking about this. It's very hard nowadays to corrupt an Access database so badly that Compact and Repair won't fix it.
Next up, Ken from YouTube: "There seems to be the view that we are going to move to an online version of replacement database on Microsoft 365. What is your view? Should I ignore it and go ahead with Access because it will be here for a long time?"
I get asked this question at least once a month: Is Access going to be around? It's getting old, are they still working on it? Go watch this video, "Access Going Away." Every year, in the Access DevCon developer conference for us nerds, the Microsoft people reassure us every year: "Nope, Access isn't going anywhere." They're going to continue developing it; it's got a long-term roadmap. It's going to be here for at least the next bunch of years—which I hope so because that's all I do is Access these days.
A lot of databases are moving online. Your average small business still wants an in-house, desktop-based database, and there's nothing better for that than Access. A lot of big corporations are putting all their data on the web, in the cloud, on big servers. Individuals may use a cloud database. Small businesses—which, when I was doing consulting and database design, were 99% of my business, and small businesses make up most of the businesses in this country and around the world—your average small business where you've got five to twenty computers on a network: if you want to use a database, Access is still your best solution for an in-house, desktop-based database.
If anything, you are going to see companies start to move their backend tables to SQL Server. If you've got people on the road and they want to be able to get into the customer records or the order history, you can store your data online. I have a seminar that I'm working on right now that's going to show you how to migrate your data from Access up to SQL Server on the web. Then you can access it from a web portal, your Android phone, your iPhone, whatever. You can get to some of the data online. You can share your Access frontend with people in other locations. You can go home and then connect to the database online using Access. Access is basically the frontend tool. Access is your forms, your reports, your VBA code. That's not going anywhere. That's not going to change anytime soon. How the data is stored in the backend—yes, you'll start to see that migrating up to SQL Server online or on premises in your office. But Access as a frontend—there's no substitute for it. It's the best thing out there.
All the web-based tools that I've looked at, they're okay. If you just want to get in there and have a simple customer list, look up an order, that's fine. But as far as the power and functionality that Access has with Visual Basic and all that, you're not going to see that replaced anytime soon. For the next three to five years, easily, Access will be on top for that.
If you have a small team, right now I have a SharePoint seminar that shows how to take your data from your tables and move them up to SharePoint. SharePoint is good for small teams: three to five users, maybe ten users, wanting to share their data in different locations. You can use SharePoint lists—medium flexibility. SQL Server takes a little more setup, a little more work. You need a web host, but it's a much better solution, especially for a bigger office or if you want to have public-facing data, like your product list up on your website. That's coming; that seminar should be out within the next week or so.
Next up, I received this email from one of my Developer students, and I wanted to share it—but I'm going to leave his name anonymous for obvious reasons:
"I need advice. I'm the owner of a database that I created at work. What would you do if a new employee is hired, and she wants to learn what I do and wants to get a copy of my project? If my manager tells me I have to teach her about my database, do I look bad if I say that bothers me? I do not really want to mix someone else's database with mine. Please help."
I will start out by saying that I am not a lawyer. You should speak with one before making any decisions, but this is my understanding of how the law works:
If this is a database that you built at home on your own time, then it's your property, and you can do whatever you want with it. If that's the case, you can sell it or license it to your employer however you want, and if you don't want anyone else in the design or source code, then you don't have to. Of course, your boss can also terminate your employment at any time for any reason or for no reason at all because you live in an at-will state.
If, on the other hand, this is software that you built on company time, on company premises, or using company computers, then your employer can argue that the software belongs to them. You have no say in whatever is done with it. They can demand that you give them copies of the source code and/or teach anyone else how to use it. You, of course, can choose to quit and not provide the training, but the software belongs to them.
That is my understanding of the legal ramifications of software development at work. In the real world, however, things are often more complex. What I would do is explain to your employer, "Look, I built this software from the ground up. I know it inside and out. I really don't want someone else tinkering with it. I don't want them introducing bugs that I have to fix. I'm happy to teach them about it on company time, of course, but I don't really want to let anyone else mess with my program." If your employer is smart, they'll understand. I've been in that situation as an outside developer. I had a very strict policy with databases that I built for my clients: if I install a locked ACCDE file (the encrypted file) and nobody has access to make modifications, then I will warranty the software. If you find a bug, I'll fix it on my own dime.
If you want me to give you the source code (the ACCDB file), then I will, but if any changes are made, it voids your warranty. If you want me to troubleshoot problems that someone else created, I'm going to charge you twice. Here's the source code—have fun with it. Tear it apart, look at it. If you install this and you have problems, I'm going to restore my copy. If that's not good enough for you, I'll fix it on your dime.
In addition to not wanting someone else messing with your code, I understand your reluctance to give up the program—it's job security for you; it makes you indispensable. But just be aware that if you built the database at work, your employer technically owns it.
Coming from the other side as a former employer myself, I understand why your employer wants you to teach someone else about how the database is built. They don't want you to be the only person that knows what's going on with it. It's probably not that they're going to try to get rid of you, but put yourself in their shoes. What happens if you get hit by a bus tomorrow? Who's going to maintain their business-critical software? The more they rely on your database to run their business, the more important a backup developer becomes.
I can't tell you what to do. Like I said, I'm not a lawyer; every situation is unique. But I hope this gives you something to think about. It's certainly a concern for a lot of people.
As an update, I was emailing with this student of mine about a week ago, and he has since gotten back to me and said that everything is fine—they worked it out. He agreed to let the new employee take a peek at the code, but not actually get in and modify stuff. She was just kind of curious. It's a good thing; you don't want the newbie—especially if they're not an Access expert—messing with your stuff and breaking what's working. But it's not a bad idea to have someone else on hand who is learning it as well, from the employer's standpoint.
Every situation is unique. If you like your employer, if it's a good company, share, have someone else help you. If not, if your boss is a jerk like some I've had, then no, you aren't getting it. I'm just kidding.
Last up today, Alexandra from my Access Learning Zone Facebook group—yes, I have a Facebook group. I started it a while back. I hate Facebook—I tell this to everybody. I'm very public about it. Their community standards are outrageous. I've gotten 30-day blocks and bans for stupid stuff. I don't even want to get into it. I hate Facebook. I will not promote Facebook. I don't like Facebook, but I do have a group that I started a while ago and I've got a lot of people in it, so I still post my stuff there and I still answer questions in it.
I have a form that contains bound check boxes defaulted to True. However, when I run a query, they do not show up. I have to manually uncheck and recheck each box. How can I avoid this, especially for a front end user?
There's a link to the Facebook group, by the way, if you guys want to go there. If you're on Facebook and you want to join it, fine. I still check it out like once a week or so.
It took me a minute to understand what you're saying, Alexandra. I think I got it. If your form consists of just those check boxes and they're defaulted to True, you still have to change something on that form for the record to be created. If you go to a blank new record, they're all going to show up as checked (True), but that record doesn't exist yet—that's a new blank record. Make an Autonumber field or something else on the form so you can see that the record has some data in it. If there's no Autonumber assigned, then that record doesn't exist—it'll say "New" in it. As soon as you check one of those boxes on or off—well, it'll be off because they're defaulted to on—then the record is created and you should see an Autonumber appear.
So, if it's literally just those boxes, you're going to have to check (uncheck and check a box again) for the record to be created, but then all the rest will be defaulted to True. Some field has to have a value set. If you want to, on a button that opens that form, you could put some code in the button to open the form and then create a new record—if you manually set a value to True in the button, then the record gets created. I would still throw an Autonumber up on the screen so you can at least see that the record exists. I think that's what you were asking. If not, let me know.
That's it. Those are some Quick Queries for you—number four in the can.
If you have any questions, put them in the comments below. If I deem them worthy, then I will include them in a future Quick Queries, or you might even get a full TechHelp video about it.
How do you become a member? Click the Join button below the video. After you click the Join button, you'll 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 much more.
But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more.
If you liked this video, please give me a thumbs up and feel free to post any comments 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'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, so if you'd 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 1 course, check it out now. It covers all the basics of building databases with Access. It's over three hours long, and you can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just one dollar, and it's 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 1 course, more of my TechHelp videos, or to subscribe to my channel.
Thanks for watching this video from AccessLearningZone.com.Quiz Q1. What is the recommended way to differentiate between quotes and contracts in Microsoft Access?
A. Use separate tables for quotes and contracts
B. Use a Yes No field in the same table to indicate type
C. Store quotes and contracts in different databases
D. Use color coding in forms to tell them apart
Q2. When should you consider splitting tables or using a backup table in Access?
A. For archiving old data you rarely access
B. For each new data entry
C. Whenever a new user joins the team
D. At the end of every business day
Q3. Why might the "third option" not appear in the combo box wizard in Access?
A. The form is not based on a table or simple query
B. The database is set to read-only mode
C. There are more than three fields in the source table
D. The Access version is not up to date
Q4. Can you use Excel-style autofill functionality (like dragging to fill sequences) in Access tables?
A. Yes, Access has built-in autofill identical to Excel
B. No, but you can simulate it with VBA programming
C. Only in Access 2016 and later
D. Only using macros, not VBA
Q5. In Access forms, what happens when you adjust a row's height?
A. Only the selected row changes
B. All rows on the form change height uniformly
C. Only the column width changes
D. The font size adjusts automatically
Q6. How can you achieve variable row height for varying content in Access reports?
A. Use the CanGrow and CanShrink properties
B. Change the table's default view
C. Adjust the row width in the form
D. Use Excel for reporting instead
Q7. What is the main difference between DAO and ADO in Access?
A. DAO is for web development, while ADO is for desktop
B. DAO is Access-specific, ADO is more generic and can connect to various databases
C. ADO is older than DAO
D. ADO cannot be used with SQL Server
Q8. What is the safest way to compact and repair an Access database?
A. Over the network using the "Compact on Close" option
B. On the machine where the database is stored, not over the network
C. While multiple users have the database open
D. By deleting old tables manually
Q9. Why do you no longer need to reduce the field size of short text fields in modern versions of Access?
A. Only the actual data used is stored, not the maximum defined size
B. Field sizes are fixed at 255, regardless of setting
C. Reducing size makes the database incompatible with Excel
D. Access compresses all text fields automatically
Q10. What is the LACCDB file that Access creates?
A. A backup of the database
B. A record locking file created when the database is open
C. A file containing database settings
D. An encrypted copy of your data
Q11. What should you do if Compact and Repair cannot fix a corrupted Access database?
A. Nothing, Access will always fix it eventually
B. Use a database recovery service or specialized software
C. Reinstall Microsoft Access
D. Simply delete the database
Q12. Which is the best practice to minimize data loss from corruption in an Access database?
A. Backup the database nightly and store copies off-site or in the cloud
B. Only backup when major changes are made
C. Never backup, as Access is always reliable
D. Use only wired internet connections
Q13. How does Microsoft currently view the future of Access?
A. Access is being phased out for Microsoft 365
B. Access will continue to be developed and supported with a long-term roadmap
C. Access is only for legacy systems
D. Access will become web-only software
Q14. What should you do if your employer asks you to share your Access database project source code, and it was built on company time with company resources?
A. Refuse and keep the code to yourself
B. Understand that the employer typically owns the software
C. Sell them the license
D. Hide the code to ensure job security
Q15. When do default values in form controls (such as check boxes) actually get recorded as data in Access?
A. As soon as the form opens
B. Only after a value in the record is changed or saved
C. When the database is compacted
D. Upon closing the form, regardless of changes
Answers: 1-B; 2-A; 3-A; 4-B; 5-B; 6-A; 7-B; 8-B; 9-A; 10-B; 11-B; 12-A; 13-B; 14-B; 15-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary In today's Quick Queries video from Access Learning Zone, I am answering a range of common questions that Access users have submitted, covering a variety of topics from database design best practices to troubleshooting specific features.
The first question comes from someone who wants to convert a quote to a contract but keep the original quote intact. They want a button to copy the quote to a contract table, so changes to the contract won't affect the original quote. I encounter this request frequently, and although you can do it manually, it is much better to handle the process with recordsets in VBA, as I describe in my Access Developer 24 course. However, I advise against maintaining separate tables for quotes and contracts. Instead, use a single table with a Yes/No field to differentiate between a quote and a contract, or other statuses like invoice or job. This avoids unnecessary duplication and keeps the structure clean. Multiple tables should only be used for archiving old records that you rarely access, which is helpful for managing very large databases or keeping email blasts for only a limited time. For differentiating current statuses, a simple field is best, and I cover changing status labels (such as printing "quote" or "invoice" at the top of a document) in Access Expert 10.
Another user asked about the combo box wizard and not seeing the option to "find a record on my form" when setting up a combo box for searching records. This option only appears if your form's record source is based on a table or a simple query, not on a complex SQL statement. Make sure the record source references something straightforward like CustomerT or CustomerQ. I have a more detailed explanation about this issue and how to resolve it on my website.
A YouTube viewer wanted to know if it's possible to fill a sequence of numbers in Access the same way you can in Excel by dragging to autofill. Unfortunately, Access does not have a built-in autofill feature like Excel. You can achieve similar results with VBA code or macros, but not without programming.
For those trying to adjust row heights in Access forms to fit content, remember the terminology: rows have height, columns have width. In Access, table and form views use uniform row heights and column widths – you cannot adjust individual heights or widths as you can in Excel. On reports, however, you can use the CanGrow and CanShrink properties to allow fields to dynamically increase in height if there is more content. Make sure to use Access-specific terms when asking questions: in Access, we call them records and fields (not rows and columns), and combo boxes instead of dropdowns. Forms are for screen entry; reports are for printed output.
There was a question about the difference between writing code that declares database variables as DAO.Database versus just Database. There are two major database access libraries in Access: ADO (ActiveX Data Objects) and DAO (Data Access Objects). DAO is Access-specific and was the original default, but Microsoft changed the default to ADO for a few versions before switching back to DAO. If you reference both libraries, you need to be specific to avoid errors, otherwise you can just declare Database or Recordset using the default DAO library. For those connecting to SQL Server or other data sources, you might want to enable the ADO library as well.
Another question was about handling PDFs: can you import PDF files into Access reports and then email them as PDFs? Access cannot directly import PDFs into reports. You could possibly store them as attachments, but I do not recommend this approach. Access does not read PDF content natively. If you need to work with PDF data, consider converting it to a format Access can read, such as text or CSV.
I was also asked about how often you should compact and repair your Access database. This maintenance helps reclaim unused space and keep your database running smoothly. The recommended frequency depends on how much activity your database gets — monthly is usually fine for most cases. If your files grow rapidly, you might do it weekly or more often. Never enable auto-compact in multi-user databases, and try to compact files from the local machine to avoid corruption over the network. Don't forget to keep regular backups before performing this action.
A question came up about changing the size of text fields to optimize storage. While older versions of Access required you to set field sizes for efficiency, this is no longer necessary. Modern Access only reserves as much space as is actually used, regardless of the field's maximum size setting. However, use shorter field lengths if you want to place a hard limit on what users can enter.
Someone asked about the LACCDB file that appears when Access databases are open. This is a locking file that helps coordinate record access; it is safe to delete it if, for example, Access crashes and leaves it behind.
If a database is corrupted and compact and repair doesn't fix it, you're probably out of luck. Modern versions of Access are much more resilient, and it's rare that compact and repair cannot recover a file. However, this underscores the importance of regular backups. There are paid database recovery tools, but I cannot recommend a specific one as I haven't used any in recent history.
I also get frequent questions about the future of Access given the push toward web/cloud databases. Microsoft assures us at every Access developer conference that Access will be around for a long time. It's still the best choice for desktop database development, especially in small to medium business environments. Moving back-end data to SQL Server or SharePoint is an option for cloud storage, but Access as the front-end tool is not going anywhere.
One student asked for advice about sharing a database with a coworker if the database was created at work. If you built it on company time or with company resources, the employer likely owns the source code and has the right to access it. If you built it outside the company, then you keep ownership. Employers often want more than one person to understand the system in case something happens to the original developer. It's reasonable to explain to your employer your concerns about maintaining the quality of the system, but remember that ownership usually follows the circumstances of development.
Finally, there was a question about why defaulted check boxes in a new record don't appear in queries unless you uncheck and recheck them. In Access, records are only created once at least one field is actively changed on a new record, even if default values are set. Until the record exists, queries will not display these "default" values because the record itself has not yet been saved to the table. Adding an Autonumber field can help make it more obvious when a record has been created.
That wraps up another set of Quick Queries. If you want your own question answered, feel free to leave a comment, and it might appear in a future episode. Membership in the site or YouTube channel unlocks additional courses, sample databases, and my code vault, but I will continue to make free TechHelp videos for everyone.
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 Converting quotes to contracts while retaining original quotes
Copying records and related details using VBA recordsets
Using a single table with a Yes/No field to differentiate record types
Archiving old data in Access using backup tables
Combo box wizard's missing "find a record" option explanation
How form record sources affect combo box wizard options
Autofill capability comparison between Access and Excel
Row height and column width adjustments in Access forms
Using CanGrow and CanShrink properties in Access reports
Clarifying Access versus Excel terminology for forms and tables
Understanding DAO vs ADO in Access VBA
When to specify DAO.Database versus Database in code
Adding ActiveX Data Objects (ADO) library reference
Importing and exporting PDF files in Access
Limitations of storing and handling PDFs in Access
Best practices for compacting and repairing Access databases
Database compaction frequency recommendations
Risks of "Compact on Close" and network compaction
Optimizing field sizes in Access tables
Impact of text field sizes on database storage
Understanding and safely deleting LACCDB locking files
Options for recovering corrupted Access databases
Importance of automated database backups
Backup strategies and file rotation in Access
Future of Microsoft Access and migration to cloud backends
SharePoint and SQL Server as Access backend alternatives
Software ownership for databases built at work
Advice on sharing source code and training coworkers
Handling default values in checkboxes on Access forms
Triggering record creation when using defaulted checkboxes |
