Quick Queries #103
By Richard Rost
7 hours ago
Whats Design View? Understanding Different Views In today's Quick Queries we will explain the different views in Microsoft Access, including Design View, Datasheet View, Form View, Layout View, SQL View, and Print Preview. We will also discuss a VBA Exit Sub versus Exit Function error, preventing Excel imports from truncating long text, manually splitting and linking a database, organizing the Navigation Pane, setting expectations for larger database projects, and the difference between SQL written in a query and dynamic SQL built in VBA. Peter from Denver, Colorado (a Platinum Member) asks: I just started working with Microsoft Access, and I get confused when the screen changes. Sometimes I'm typing customer information into rows, then I accidentally end up moving boxes and seeing all kinds of settings. People mention Design View, Form View, and other views. What are they all for, and how do I know which one I should be using? PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp QQ Quick Queries, Access Design View, Form View, Datasheet View, Query Design View, SQL View, Table Design View, Report Design View, Layout View, Print Preview, QBE grid, Exit Sub not allowed in Function, Excel import Long Text, split database linked tables
More InformationTranscript Ever open something in Microsoft Access and wonder why the screen suddenly looks completely different?
Maybe you just want to enter some data, but Access has you staring at a bunch of properties in some tiny little boxes.
Welcome to another TechHelp Quick Queries video brought to you by Access Learning Zone. I'm your instructor, Richard Rost.
Today's first question is for the absolute beginners, but don't worry, we'll get to some more advanced stuff a little bit later.
We're going to start by clearing up what all those different views in Microsoft Access are for, especially Design View. Once you understand the difference between building an object and actually using it, you'll know where to go when you need to change a table, build a query, work with a form, or make a report that prints the way you expect it.
Access isn't changing the database whenever you switch views; it's just changing what tools are on the workbench.
In addition to that, we've got answers to questions from YouTube comments, my forums, emails, and lots of other stuff coming up.
All right, let's get to it.
Today's first question comes from Peter in Denver, Colorado, one of my Platinum members.
Peter says, "I just started working with Microsoft Access, and I get confused when the screen changes. Sometimes I'm typing customer information into rows, and then I accidentally end up moving boxes and seeing all kinds of settings. All I hear mentioned is Design View, Form View, and other views. What are they all for, and how do I know which one I should be using?"
Okay, Peter, well, this is something that sometimes confuses beginners.
You open up a program like Excel or Word, and you just go to work, but Access has different tools that have different views that can change the way you're working with what you see on the screen.
Let's talk about them.
All right, before we talk about Design View specifically, we need to understand what a view is.
A view is just a different way of looking at or working with the same Access object. A table might have one view for defining its fields and another view for entering records. A query might have one view for building the query and another for seeing its results.
You're not creating multiple versions of the object when you switch views; you're just changing the workspace that Access gives you.
And this is important because Access separates building the database from using the database. Some views are intended for the developer, the person building and designing the database. Other views are intended for the person entering data, reading results, printing reports, the end user.
So whenever you're not sure what view you need, ask yourself: am I building the subject, or am I just using it?
So what is Design View?
Well, Design View is where you build the object. It's the blueprint view.
If you're working with a table, Design View is where you define the fields, things like Customer ID, First Name, Last Name, Email, and so on. You choose the data type for each field, you decide what field is the primary key, and you set all the rules that help keep the bad data out. That's Design View. You're designing the table.
If you're working with a form, Design View is where you work with your text boxes, your labels, your buttons, your combo boxes, and the other controls.
If you're working with a report, it's where you arrange your headings, your logos, your totals, and all that stuff.
In other words, Design View is about the object's structure and setup.
Now, that doesn't mean that Design View is scary or it's reserved for nerds and programmers wearing lab coats. It's simply the place where you go when you need to change how something is built.
So how do you switch views?
Well, usually you can use the View button in the upper left corner of the ribbon. Access will show you what views are available for the object you currently have open.
You can also right-click on an object in the Navigation Pane before opening it.
Or my favorite method: you'll see me right-click on the title bar of the object if it's open.
Now, not every object has every possible view. Tables don't have a Form View because a table isn't a form. Forms don't have an SQL View, and so on.
So if you see a tutorial say, "Switch to this view," but you don't see that view in the menu, first make sure you have the right kind of object open.
And when you leave Design View after making changes, Access may ask you whether you want to save the design. Read that prompt carefully.
Saving design changes saves the structure of the object, but not necessarily any data inside it.
Now let's start with tables because tables are the foundation of an Access database.
In Table Design View, you build the structure of the table. This is where you create fields: Customer ID, First Name, and so on. You decide whether a field stores text, date and time, currency, or number.
Now, Datasheet View, on the other hand, looks more like a spreadsheet. This is where you see records and rows and fields and columns. This is where you can directly type in data, edit data, sort it, and filter it.
For a small personal database, entering data directly into a table can be perfectly fine. But if you're building a database for other people to use, I always say, keep your end users out of the tables and queries directly. Make them work with forms.
Forms can be much friendlier and guide users through the process of data entry, and can prevent some hair-pulling on your end when they break things by working directly with your tables.
So Table Design View builds the filing cabinet, and Datasheet View lets you look through the files and folders that are inside it.
Now, Query Design View is where you can go to build queries. You add the tables or other queries that you need, where you're getting your data from. Choose the fields you want to see, add sorting, specify criteria, and all that stuff.
This is called the Query By Example grid, the QBE grid you'll sometimes see. It sounds fancier than it is. It's just a visual way to tell Access what question you want answered.
For example, you might want to create a query that shows all the customers in Florida, sorted by last name, that kind of thing.
And then, when you're done building the query, you can switch to Datasheet View in the query to see the query's results. This is basically the answer to your question, the list of records Access found.
Now, there's also this thing called SQL View. SQL stands for Structured Query Language. It's the language databases use to request data.
Now, Access writes SQL behind the scenes when you build a query with the QBE grid. SQL View lets you see or edit the statements directly.
Now, as a beginner, you don't need to know SQL to build awesome queries. In fact, personally, I didn't learn SQL myself for years after I started working with Access, because the query graphical designer is perfectly fine.
So if you see this pop up on your screen and you're just a beginner, don't worry about it. Switch back to Design View, and you'll be just fine.
Now, forms are where most users should spend most of their time once you're up and working with the database and using the database to enter, edit, and look up data.
A form is designed to make working with data easier. So instead of looking at a big grid of rows and columns, you see a nice clean customer form with labeled boxes: the customer's name, address, phone number, and so on.
Form View is the normal working view. This is where you can browse through records one at a time, add new customers, edit information, and make buttons do stuff.
And this is where you can build the user experience. This is really what separates a database like Access from something like Excel, where you're just looking at rows and columns.
Now, forms can have subforms inside them, which is a form inside of a form that shows related records. For example, you might have a parent form that's got the order information, and then the subform has all the little stuff inside that is related to that order, like the line items.
You can also have a Datasheet View for your forms, which, again, just like tables and queries, shows your rows and columns.
There are single forms that show one record, then there's continuous forms that show a whole bunch of records.
Then you got this thing called Form Layout View, which lets you make some visual changes to the design of the form while there's live data still visible. Some people love it. Personally, I don't like it.
When it first came out, it was kind of buggy, so it just turned me off. So personally, I almost always build forms directly in Design View because it gives me the most control and predictable results.
All right, old habits die hard.
There is one exception. Layout View can be handy for certain sizing and anchoring tasks. Anchoring is pretty cool. I got a whole separate video on anchoring. That's where objects will stay in the same spot or move when you stretch the form, when you make the form bigger.
But for me personally, just start with Form View for using forms and Design View for building them, and that's it. That's 99% of everything you'll ever need.
Now, reports are similar to forms in that they have a Design View and a Layout View, but their purpose is different.
Forms are for working with data on the screen. Reports are for presenting data. Think invoices, mailing labels, things you print out or email to people, or export as a PDF.
Forms are for working with the data on your computer. Reports are for basically printing stuff out or sending that data to other people that don't have your database.
Now, reports have a Report View that lets you look at the report on the screen, but it's not always the best final test of what your report is going to look like when you print it.
Reports are often intended for paper or PDFs where page breaks and margins and headers and photos and that kind of stuff matters. So that's why I usually use Print Preview, which shows reports as actual pages, and that's a different view you can switch to.
So my rule of thumb is: I build reports in Design View and then I inspect the finished product in Print Preview. If it looks good there, it'll probably look good when you print it or send it as a PDF.
Now, there are two other Access objects worth mentioning. There's macros and modules.
Macros are a way to automate tasks, and then VBA code obviously lets you write programming that goes behind your database.
Now, these are more advanced features, but I have to mention them. They have their own views as well. Macros basically just have a Design View, and VBA modules open up into a VBA editor.
And these are tools for adding automation and custom behavior once your database starts growing up a little bit.
So at this point, you don't need to memorize every single view in Access for every single object. Just match the right view to the right job.
If you're building the structure of a table, query, form, or report, you should probably be in Design View.
If you're entering or editing data, I suggest using Form View with a form.
Checking query results, you're going to get a Datasheet View. Or you can click the little red Run exclamation point that basically runs the query. You go from the QBE grid to a Datasheet View and back.
And remember, the words Design View don't always mean the same thing for every object. Table Design View looks totally different from Form Design View, which looks totally different from Query Design View. But they all have the same general purpose: where you build and configure that type of object.
So once you get that, Access becomes much less mysterious, and you stop wondering why the screen changed. And you start choosing the screen that helps you get the job done.
So Peter, I hope that answered your question.
And if you'd like to learn more about how to use all of these different objects, well, most of these different objects, be sure to check out my Access Beginner Level 1 class.
It's about four hours long. It teaches you all the basics of how to use most of these objects. We build tables, queries, forms, and reports. We go through Design View. We go through entering and editing data. And it's good for beginners.
So check it out.
I say most of the objects because in Beginner Level 1, we don't do anything with macros and modules, but you don't have to. You don't have to learn any of that advanced stuff to make really cool stuff with Microsoft Access.
That's one of its strengths. It's good for users of all skill levels.
All right, let's move on and see what other questions we have in the grab bag today.
All right, next up, I got an email from one of my other Platinum members, Steven from Seattle, Washington.
He says, "I'm getting this 'Exit Sub not allowed in Function or Property' error message. I can't figure out why. Any ideas?"
I'm going to leave this one up on the screen for a minute. Pause the video if you have to. See if you can figure out what the problem is here.
I was going to save this for one of my video quizzes, but I figured I'd put it in a Quick Queries. Give you guys a chance to figure it out.
Anybody?
Killer, do you see it?
Yeah, it is a function. You can't put Exit Sub in a function. You got to put Exit Function in a function. Exit Sub goes in a sub.
Now, one of the things that I can't stand about the VB Editor is that if you change this, let's say this is all right. Let's do a real one here. Let's say I put down here Exit Sub. That's all good.
Now, if I change this to Function, it changes the other ones for you. It's smart enough to make that change. Like Michael Jackson. Make that change.
But if you come in here and just type Exit Sub, and then you Debug Compile, why can't it be smart enough to say, "Oh, hey, let me do that for you," as soon as you type it in?
I mean, come on. That's just those little things.
You just got to read the prompt carefully. And once you see it, you know it.
If it pops up like that, and it looks all scary, and you can't figure it out, you just got to read the whole error message.
"Exit Sub." Okay, that's what I got. "Not allowed in Function or Property." Well, it's definitely not a property. Oh, I must have a function.
Once you decode it, the error messages that Access gives could be a little more verbose.
I'm curious what that help does. Let's go click on it. Let's Debug Compile.
All right, Exit Sub not allowed.
Let's hit Help.
Okay. Yeah. Okay, I don't often hit those Help buttons because sometimes Microsoft's website is not very descriptive, but this one looks pretty friendly.
"Use Exit Sub or Function in a Property procedure, or Exit Sub in a Function or Property procedure. Use the proper Exit statement for this type of procedure."
It could be a little better.
Microsoft, if anyone's listening, I'm willing to put little videos on here to explain all this stuff for you. I got decades of videos in my library if you want to license them. And I'll make custom ones for you. Just saying.
Okay, next up, head north to my website.
Hussein, one of my Gold members, was importing data from Excel into Access and noticed that his Procedures field was getting cut off during the import. "I don't know why Access was truncating the text and how to prevent it."
Well, this one's actually pretty common.
Most of the time, Access is creating that field as a Short Text field, and Short Text is limited to 255 characters. Anything beyond that gets chopped off.
So the easiest fix is to create your destination table first and make that a Long Text field. Then import by appending your Excel data into that table instead of letting Access build a new one automatically.
Also remember that Access guesses the data type by looking at the first few rows of the spreadsheet. So if those early rows are all short, Access assumes the whole column is Short Text, even if there are longer values further down.
So if you're importing long notes, procedures, or descriptions, define your table yourself first. So those little gotchas are easy to avoid when you know they're there.
And also, a bit of trivia. Short Text, as we know, is limited to 255 characters, so everything after that will get chopped off, and that's most likely the cause of your problem, Hussein.
Now, Long Text can store up to about 64,000 characters if you import them from Excel this way, or if you enter them in like in the Access interface, like you copy and paste into a form field or directly into the table, you're limited to 64K.
Even though modern ACCDB databases can effectively store about one gigabyte of text.
So, in other words, you can actually store a lot more in a Long Text field than Access allows you to through either importing it or even copying and pasting it directly into a field.
The only way you can actually take full advantage of that is by using a file I/O import. You can read it in directly from a file and store it that way.
Although, if you're storing that much database or that much text data in your database, you might want to consider just storing them as files and saving links to the files. I do that most of the time.
So, just some nerdy trivia for you, which is why we watch this Quick Queries in the first place.
First up, we got Kenneth, one of my Silver members. He decided to split his Access database into a front and a back end, but instead of using the Splitter Wizard, he copied his tables into another database manually, and then suddenly none of his forms would open, and he thought he might have to rebuild everything from scratch.
Well, fortunately, John, no, your forms weren't broken. They were just looking for tables that weren't there anymore.
John jumped in with the right idea, and once Kenneth moved the tables back, everything immediately started working again.
Now, if you want to split the database manually, you don't have to recreate your forms. Just link the front end to the back end where the tables are.
And that's exactly what linked tables are for.
You put your tables in one database, and then you link to them from the front end. And that's all covered in my Split Database video.
And one thing that came up later in the discussion, and I think is even more important than this, is Kenneth said he wanted to split the database because he had so many objects that his Navigation Pane was getting crowded.
Splitting the database doesn't actually solve that problem. The forms, queries, reports, and code all stay in the front end.
So if your goal is simply to organize your objects better, then you can use custom Navigation Pane groups and good naming conventions. And that's probably what you're looking for.
And I should probably make a video about it because I don't think I have a video about that.
Yeah, I just checked. I don't have a custom one. I've got videos on how to use the Navigation Pane, but I should probably put them together to show some tips and tricks for organizing and getting the most out of the Navigation Pane because there's a lot of cool things you can do with it.
And I've really only scratched the surface.
Next up, let's head over to YouTube.
I got Brandon. He wants to know if I can show him how to build a complete sales and inventory management database with forms, reports, queries, and everything else.
And I said, "Yeah, sure. About 5,000 videos on my website that do that."
First off, Brandon, I was just trying to be funny with my first reply. That's why I had to come back and say, in all seriousness, that's a pretty big ask.
That's kind of like walking into an auto mechanic shop and saying, "Can you teach me how to build a car from the ground up?"
That's going to take a while.
And these Quick Queries videos and YouTube comment sections, even with the forums on my website, are all designed for short-answer questions and answers.
I can't explain how to do that in the YouTube comments section. I can't even make a single short video on how to do something like that.
You're talking hours and hours and hours of lessons.
If you're stuck on one specific thing, like, "Why is my combo box blank?" or "Why won't this query work?" Yeah, that's great for helping with a short video or even a post in a comment.
But if your question is, "How do I build an entire inventory management system?" Yeah, that's exactly what my full courses are for.
I start at the beginning, and I build everything step by step over several hours of lessons.
I mean, look at my fitness database series. We got dozens of lessons just to teach you how to build a simple little database for tracking calories and workouts. And that's not even done yet.
Plus, without knowing your current skill level, there's no way I can answer that question.
I don't know. Do you need the basics? Do you know VBA? Are you a developer?
The more specific your question is, the better answer I can give you. And if you ask a vague question, you're going to get a vague answer.
So to answer your question, can I give you examples of all that? Yes, I can. And that's the answer.
It's like if someone says to me, "Could you go to the store?" Sure, I could. Am I? That's a different question. May I? Will I? Those are all different questions.
But in all seriousness, if I had to point in one place to learn what you want to do, it probably would be my Barcode and Inventory seminar that I have.
It teaches you all the stuff about printing and scanning barcodes. But more importantly, behind that, under the hood, behind the scenes, is how to actually manage inventory: inventory in, inventory out.
And yes, there's barcodes involved. You might not need barcodes. But actually, the barcode printing and scanning part is a real small part of that because if you're working with any barcode scanner, all a barcode does is it converts that barcode into text. It's like typing in a Product ID.
But the rest of everything I cover in the seminar will teach you how to handle the actual inventory part of it.
And so, again, we're talking, this is 12 hours of video. And that assumes you already know VBA. That assumes you're at the developer level.
So you've taken my beginner and expert classes and see how to use Access, because we start right off with some pretty intense VBA.
So I hope that answers your question. I wasn't trying to be dismissive. It's just a very vague question.
All right, next up, also asked a great follow-up question to my BOF and EOF video.
All right, next up, we got Satya.
And he wants to know why sometimes you write SQL like this with MPID equals one, and sometimes you write it like that, building SQL strings with ampersands and variables. And when do you use each approach?
And that's a good question.
It all depends on where the SQL is being executed. SQL executed. Try saying that 10 times fast.
And you posted this in my Access Beginner 1 video, which I'm assuming, since you're doing this kind of stuff with SQL, you're not a beginner. You probably just picked that video at random.
So I'll assume you understand SQL.
Now, if you're writing a normal Access query, like right in the query, you can refer directly to table fields and form controls. And Access understands those references because it's the one executing the query.
But if you're building SQL in a string in VBA, you're just basically creating a piece of text. So VBA has to replace your variables with their actual values before SQL ever gets sent to Access.
And that's what the ampersands are doing. They're concatenating, or joining together, the pieces of text to build one complete SQL statement.
If you don't know what concatenation is, go watch this video.
And another thing: absolute reference that you mentioned right here, that's an Excel term. In Access, we talk about field references, variables, or control references.
Now, if you're just starting out with Access, if you were really watching my Access Beginner 1 video, don't worry too much about dynamic SQL just yet. Learn the regular queries and all the basics like that, and then you'll get to building VBA and SQL once you're more comfortable with the basics.
And another thing I see here: if you're just sending an ID, which is a number, then you don't need the extra quotes here.
You don't have to put it inside of this. This is basically putting quotes inside of other quotes, which you don't need to do for a number value. That's only for strings, for text strings.
And that's a whole other video about double quotes and double-double quotes, and when you need those.
So there's all kinds of different rules. Lots of videos on my website to explain all this stuff for you.
All right, so before we're done today, make sure you hit that Like and Subscribe.
Stop by my website. You can see all the cool stuff that I post, and what's new, and lots of video quizzes and more videos and other videos and other stuff and things and stuff.
And of course, stop by and check out my Captain's Log for my daily musings about whatever it happens to be, whether it's website stuff or financial stuff or penguin facts or Star Trek or whatever.
Stop by the merch store, pick up a mouse pad or a coffee mug or whatever. Grab a copy of my book off of Amazon.
Stop by my forums. If you got questions, you can post them there. I could spend a lot more time answering questions in the forum on my website.
And there's lots of great moderators. I got an awesome team of moderators that are fantastic. Second to none.
And we could spend a lot more time there. YouTube comments, I can only do so much with that. But the forums are where you can usually get your questions answered faster.
But there you go. We know all about the different views in Microsoft Access, Design View and Report View and The View on TV.
I watched that years ago. I don't watch that, though.
"A View to a Kill." Very good James Bond movie. Excellent Duran Duran song.
I don't know. There's lots of views.
Anyhow, that's going to be your TechHelp Quick Queries video for today.
I hope you learned something. Post a comment down below. Let me know what you think, and if you have any questions for next week's Quick Queries.
But that's going to do it for today, folks.
Live long and prosper, my friends. I'll see you next time, and enjoy your weekend. Intro In today's Quick Queries we will explain the different views in Microsoft Access, including Design View, Datasheet View, Form View, Layout View, SQL View, and Print Preview. We will also discuss a VBA Exit Sub versus Exit Function error, preventing Excel imports from truncating long text, manually splitting and linking a database, organizing the Navigation Pane, setting expectations for larger database projects, and the difference between SQL written in a query and dynamic SQL built in VBA. Quiz Q1. What is a view in Microsoft Access?
A. A different way to look at or work with the same object
B. A separate copy of a table or form
C. A database backup
D. A security setting for users
Q2. What is the main purpose of Design View?
A. Entering records into a table
B. Building and configuring an object's structure
C. Printing reports
D. Running macros automatically
Q3. In Table Design View, what would you normally do?
A. Define fields, data types, keys, and validation rules
B. Enter customer records into rows
C. Preview a printed invoice
D. Write VBA code
Q4. Which view is normally used to enter data directly into a table?
A. Design View
B. SQL View
C. Datasheet View
D. Print Preview
Q5. Why are forms generally better than tables for end users to enter data?
A. Forms can provide a friendlier interface and guide data entry
B. Forms store more data than tables
C. Forms do not require fields
D. Forms automatically eliminate the need for queries
Q6. What is Query Design View primarily used for?
A. Building a query by selecting fields, criteria, sorting, and source tables
B. Entering data into a report
C. Editing VBA modules
D. Changing database passwords
Q7. What does Datasheet View show when used with a query?
A. The query's SQL code
B. The query design grid
C. The records returned by the query
D. The form controls used by the query
Q8. What is SQL View used for in a query?
A. Viewing or editing the SQL statement behind the query
B. Viewing the query as a printed report
C. Changing table field sizes
D. Editing form labels and buttons
Q9. Which view is normally used by someone working with records one at a time in a form?
A. Form View
B. Table Design View
C. Print Preview
D. SQL View
Q10. What is a subform commonly used for?
A. Showing related records within a parent form
B. Storing backup copies of a form
C. Replacing a table primary key
D. Running SQL statements without a query
Q11. What is Layout View for forms mainly useful for?
A. Making visual design changes while live data is visible
B. Writing VBA code behind a form
C. Creating table relationships
D. Exporting a form to Excel
Q12. What is the primary purpose of a report in Access?
A. Presenting data for printing, PDF export, or distribution
B. Entering records into a table
C. Replacing all queries
D. Storing VBA functions
Q13. Which view is usually best for checking how a report will look when printed?
A. Print Preview
B. Form View
C. Datasheet View
D. SQL View
Q14. A VBA procedure begins with Function. Which statement should be used to exit it early?
A. Exit Function
B. Exit Sub
C. End Sub
D. Stop Function
Q15. Why might text from an Excel column be cut off when imported into Access?
A. Access created the destination field as Short Text
B. Excel cannot export text fields
C. The table has too many records
D. The query is missing a primary key
Q16. What is the best way to import long notes or descriptions from Excel without truncating them?
A. Create the destination table first and use a Long Text field
B. Import the spreadsheet into a new table every time
C. Change the field to Number before importing
D. Store the data in a primary key field
Q17. Why can Access sometimes choose the wrong data type when importing an Excel spreadsheet?
A. It guesses the data type by examining the first few rows
B. It always imports every Excel column as Number
C. It only imports the last row of the spreadsheet
D. It ignores the spreadsheet contents completely
Q18. If you manually move tables to another database, why might forms in the original database stop working?
A. The forms can no longer find the tables they were using
B. Forms cannot be used with linked tables
C. Access deletes forms whenever tables are moved
D. The forms must be converted into reports
Q19. What should you do after moving tables into a back-end database as part of a split database?
A. Link the front-end database to the back-end tables
B. Rebuild every form and report from scratch
C. Convert all tables into queries
D. Put all VBA code into the back end
Q20. Will splitting a database reduce the number of forms, queries, and reports shown in the Navigation Pane?
A. No, those objects normally remain in the front end
B. Yes, splitting automatically hides all non-table objects
C. Yes, splitting converts forms and reports into tables
D. No, because splitting deletes forms and reports
Q21. What is a good way to organize a crowded Navigation Pane?
A. Use custom Navigation Pane groups and good naming conventions
B. Move all forms into a table
C. Delete all queries after using them
D. Store reports inside VBA modules
Q22. When writing SQL directly in a normal Access query, what can Access understand?
A. References to table fields and form controls
B. Only hard-coded numeric values
C. Only VBA variables
D. Only text enclosed in double quotes
Q23. Why are ampersands used when building an SQL statement in VBA?
A. To join text pieces and insert variable values into the SQL string
B. To create a new primary key
C. To convert a form into a report
D. To open a table in Design View
Q24. When building SQL in VBA, when are quotes generally needed around a value?
A. When the value is text
B. When the value is always a numeric ID
C. When the value is a table name
D. When the value is a primary key field type
Answers: 1-A; 2-B; 3-A; 4-C; 5-A; 6-A; 7-C; 8-A; 9-A; 10-A; 11-A; 12-A; 13-A; 14-A; 15-A; 16-A; 17-A; 18-A; 19-A; 20-A; 21-A; 22-A; 23-A; 24-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. Summary In today's Quick Queries video, I am answering a variety of Microsoft Access questions, beginning with one of the most common beginner topics: understanding the different views available in Access and knowing when to use each one.
If you are new to Access, it can be confusing when the screen suddenly changes from a grid where you are entering customer information into a window full of controls, rulers, and property settings. This is normal. Access gives you different views depending on whether you are building an object or using it.
A view is simply a different way of looking at or working with the same Access object. Switching views does not create a second copy of the table, query, form, or report. It only changes the tools Access gives you for working with that object.
The easiest way to decide which view you need is to ask yourself whether you are building the object or using it. Design View is generally for building and configuring an object. Datasheet View, Form View, Report View, and Print Preview are usually for using or reviewing the object.
For tables, Design View is where I define the structure of the table. This is where I create fields such as CustomerID, FirstName, LastName, EmailAddress, and other pieces of information I want to store. I select the data type for each field, decide which field is the primary key, and establish validation rules and other settings that help protect the integrity of the data.
Table Datasheet View is where I see the actual records. It looks somewhat like a spreadsheet, with records in rows and fields in columns. This is where I can enter, edit, sort, and filter data directly in the table.
For a small personal database, entering data directly into tables can be perfectly acceptable. However, when I build databases for other people, I prefer to keep users out of the tables and queries whenever possible. Forms provide a much friendlier interface for entering and editing data. They can guide users through the process, make important fields easier to find, and reduce the chances that someone will accidentally change something they should not.
Think of Table Design View as building the filing cabinet. Datasheet View is where you open the drawers and look through the files.
Queries also have multiple views. Query Design View is where I build a query using the graphical query designer. I add the tables or other queries I need, select the fields I want to display, set sorting options, and specify criteria.
The lower section of Query Design View is often called the Query By Example grid, or QBE grid. Despite the fancy name, it is simply a visual tool for telling Access what information I want to retrieve. For example, I might create a query that shows all customers in Florida, sorted by last name.
Once the query is built, I can switch to Datasheet View to see the results. Datasheet View shows the records that Access found based on the criteria in the query.
Queries also have SQL View. SQL stands for Structured Query Language, which is the language databases use to request and manipulate data. When I create a query using the graphical designer, Access writes the SQL behind the scenes. SQL View lets me see or edit that SQL directly.
Beginners do not need to know SQL right away. The graphical query designer is more than enough for building many useful queries. If SQL View appears and you are not ready to work with SQL yet, simply switch back to Design View and continue using the QBE grid.
Forms are where most users should spend their time when they are actually working with a database. A form provides a cleaner, more controlled interface for entering, editing, and finding information.
Instead of looking at rows and columns, a user might see labeled boxes for a customer's name, address, phone number, email address, and other information. Forms can also contain buttons, combo boxes, check boxes, images, calculated controls, and many other features that make the database easier to use.
Form View is the normal working view for a form. This is where users browse through records, add new records, edit existing data, and use buttons to perform tasks.
Forms can also include subforms. A subform is a form inside another form that displays related records. For example, an order form might show the main order information at the top, while a subform displays the individual line items associated with that order.
Forms can also be displayed as datasheets. A datasheet form shows multiple records in rows and columns, similar to a table or query. A single form generally displays one record at a time, while a continuous form displays multiple records in a more customized layout.
Form Layout View allows you to make some visual changes to a form while live data remains visible. Some developers like Layout View, but I personally prefer working in Design View because it gives me more control and produces more predictable results.
Layout View can be useful for certain tasks, including resizing controls and working with anchoring. Anchoring determines how controls behave when a form is resized. For example, a control can remain in the same position, stretch wider, or move along with the edge of the form.
In most cases, though, I recommend using Form View to work with forms and Design View to build them. That covers the vast majority of what most Access users need.
Reports are similar to forms in that they have Design View and Layout View, but their purpose is different. Forms are intended for working with data on the screen. Reports are intended for presenting data, usually as printed pages, PDFs, invoices, mailing labels, summaries, or other output that will be shared with someone else.
Report View allows you to look at a report on screen, but it is not always the best way to verify how the report will look when printed. Reports often depend on page breaks, margins, headers, footers, page numbers, grouping, and other layout details.
For that reason, I usually build reports in Design View and inspect the final result in Print Preview. Print Preview shows the report as actual pages. If it looks correct in Print Preview, it will usually look correct when printed or exported to PDF.
Access also includes macros and VBA modules. These are more advanced tools used to automate tasks and add custom behavior to a database.
Macros have their own design interface. VBA modules open in the Visual Basic Editor, where I can write VBA procedures, functions, and other programming code. You do not need to learn macros or VBA to create useful Access databases, but they become valuable as your projects become more sophisticated.
The main point is that you do not need to memorize every possible view for every Access object. Just match the view to the task.
If I am building the structure of a table, query, form, or report, I will usually use Design View.
If I am entering or editing data, I will generally use Form View.
If I am checking the results of a query, I will use Datasheet View.
If I am reviewing the final printed appearance of a report, I will use Print Preview.
Design View does not look the same for every type of object. Table Design View looks very different from Query Design View, and both look different from Form or Report Design View. However, they all serve the same general purpose: they are where I build and configure the object.
When leaving Design View after making changes, Access may ask whether I want to save the design. That means Access is asking whether I want to save changes to the structure or setup of the object. Saving design changes is not necessarily the same as saving data entered into a table or form.
Next, I addressed a VBA error message that says Exit Sub is not allowed in a Function or Property procedure.
This error occurs when the type of exit statement does not match the type of procedure. Exit Sub is used inside a Sub procedure. Exit Function is used inside a Function procedure. Property procedures have their own rules as well.
The error message may look intimidating at first, but it is actually telling you exactly what the problem is. If you see Exit Sub not allowed in Function or Property, check whether you are currently inside a Function. If so, replace the incorrect exit statement with the appropriate one.
The Visual Basic Editor can automatically update the ending statement when you change a procedure from a Sub to a Function. However, if you manually type the wrong exit statement later, the editor will not always correct it for you. This is one of those situations where carefully reading the entire error message can save a lot of time.
Another question involved importing data from Excel into Access when a field named Procedures was being cut off.
The most likely cause is that Access created the destination field as Short Text. A Short Text field is limited to 255 characters. Any text beyond that limit will be truncated.
The best solution is to create the destination table yourself before importing the Excel data. Set the field that will contain long notes, procedures, descriptions, or similar content to Long Text. Then import the Excel data by appending it to that existing table instead of allowing Access to automatically create a new table.
Access guesses field data types during an import by looking at the first few rows of the spreadsheet. If the early rows contain only short values, Access may assume that the entire column should be Short Text, even if longer values appear later in the spreadsheet.
Long Text fields can store far more information than Short Text fields. When entering or importing data through the normal Access interface, the practical limit is around 64,000 characters. However, modern ACCDB databases can store much more text in a Long Text field under certain circumstances.
If you are trying to store extremely large amounts of text, such as lengthy documents, it may be better to store the documents as separate files and save links to those files in the database. That approach can make the database easier to manage and can reduce unnecessary database growth.
Another question involved splitting an Access database manually. Someone copied the tables into a separate database and then discovered that forms no longer worked.
The forms were not actually broken. They were simply looking for tables that no longer existed in the original database file.
When you split a database, the tables are stored in the back-end database, while the forms, queries, reports, macros, and VBA code remain in the front-end database. The front end must contain linked tables that point to the tables in the back end.
If you move tables manually, you do not need to rebuild all your forms. You only need to link the front-end database to the tables in the back-end database. That is the purpose of linked tables.
However, splitting a database will not solve the problem of a crowded Navigation Pane. The forms, reports, queries, macros, and modules will still be in the front-end database, so they will still appear in the Navigation Pane.
If your main goal is to organize objects better, consider using custom Navigation Pane groups along with a good naming convention. Organizing objects into logical groups can make a large database much easier to maintain without changing the actual database structure.
I also addressed a broad request for a complete sales and inventory management database with forms, reports, queries, and everything else included.
That is a large project. Building a complete inventory system involves many separate topics, including table design, relationships, forms, queries, reports, inventory transactions, stock levels, purchasing, sales, and often VBA programming.
Short questions are ideal for Quick Queries videos, YouTube comments, emails, and forum posts. For example, I can easily help with a specific question such as why a combo box is blank or why a particular query is not returning records.
However, a question like how to build a complete inventory system requires a full course or seminar. The answer depends heavily on your current skill level and your exact requirements. Do you need basic Access instruction? Do you already understand relational database design? Do you know VBA? Are you building a personal database or a multi-user system?
The more specific the question, the more specific and useful the answer can be.
For people who want to learn how to manage inventory in Access, my Barcode and Inventory seminar is a good place to start. Although it includes barcode printing and scanning, the barcode itself is only a small part of the process. A barcode scanner generally works like a keyboard, converting a barcode into text such as a ProductID.
The important part is the inventory management behind the barcode system. That includes tracking inventory received, inventory sold, inventory adjustments, stock quantities, and related records.
That seminar assumes you already have a solid understanding of Access and VBA, because it begins with fairly advanced programming concepts. If you are still new to Access, start with the beginner and expert lessons first, then work your way up to the developer-level material.
Finally, I answered a question about why SQL is sometimes written with a fixed value and other times built dynamically with variables and concatenation.
The answer depends on where the SQL is being executed.
When I create a normal saved Access query, Access can understand references to table fields and form controls directly. Access is running the query, so it knows how to interpret those references.
When I build SQL in VBA, I am creating a text string. VBA must insert the current value of a variable or control into that text before the SQL statement is executed. Concatenation, using the ampersand character, joins text and values together to create the final SQL statement.
If the value being inserted is numeric, such as an ID number, it generally does not need text quotes around it. Text values do require quotes, and dates have their own formatting rules as well.
Also, the term absolute reference is generally an Excel term. In Access, I would normally refer to field references, control references, or variables.
If you are just beginning with Access, do not worry too much about dynamic SQL yet. Learn how to build regular queries first. Once you are comfortable with tables, queries, forms, and basic expressions, then you can move on to VBA, dynamic SQL, and more advanced techniques.
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 Understanding Access object views
Using Design View for tables
Using Datasheet View for table data
Building queries in Query Design View
Viewing query results in Datasheet View
Understanding SQL View for queries
Using Form View for data entry
Form Design View versus Layout View
Using forms and subforms for related data
Designing reports and using Print Preview
Fixing "Exit Sub not allowed" errors
Preventing truncated Excel text imports
Using Long Text fields for imported data
Splitting databases with linked tables
Organizing objects with Navigation Pane groups
Building SQL strings dynamically in VBA Article Microsoft Access uses different views to let you either build database objects or use them. Switching views does not create a new version of a table, query, form, or report. It simply changes the tools and workspace Access provides for the object you have open.
A useful question to ask whenever the screen changes is whether you are building the object or using it. Design-oriented views are for defining how an object works and looks. Working views are for entering data, reviewing results, or viewing finished output.
Design View is the main blueprint view in Access. It is where you define the structure and behavior of an object. The exact appearance of Design View varies depending on whether you are working with a table, query, form, or report, but the purpose is always the same: you use it to build and configure the object.
For tables, Design View is where you create and modify fields. You decide whether a field stores text, dates, currency, numbers, or other data types. You can set a primary key, define validation rules, specify required fields, and establish other settings that help keep bad data out of the database. If you need to add a new field such as EmailAddress, change a field from text to date/time, or make a field required, Table Design View is where you do that.
Datasheet View is the normal working view for tables. It resembles a spreadsheet, with records displayed in rows and fields displayed in columns. You can enter, edit, sort, and filter data there. Entering data directly into a table can be acceptable for a small personal database, but it is usually better to have other users work with forms. Forms provide a friendlier interface and can guide people through data entry while reducing the chance that they will accidentally change something they should not.
For queries, Design View is where you build the question that Access will answer. You choose the tables or queries that provide the data, select which fields to display, add sorting, and enter criteria. The grid used to build a query is often called the Query By Example grid, or QBE grid. It is a visual way to tell Access what records you want.
For example, you might build a query that shows customers in a particular state, displays their contact information, and sorts them by last name. Once the query is built, switch to Datasheet View to see the results. Datasheet View for a query shows the records that match the query criteria.
Queries can also be viewed in SQL View. SQL stands for Structured Query Language, which is the language databases use to request and manipulate data. Access creates SQL behind the scenes when you build a query using the graphical design grid. SQL View allows you to inspect or edit that statement directly. Beginners do not need to learn SQL immediately, because the graphical query designer can handle many common tasks. However, SQL becomes useful when queries become more advanced or when you need to generate a query dynamically from VBA.
Forms are usually the primary interface for entering and editing data. A form can show one customer, order, product, employee, or other record in a clear arrangement of labeled controls. Instead of asking users to work in rows and columns, a form can provide text boxes, combo boxes, command buttons, check boxes, and other controls that make data entry easier.
Form View is the normal view for using a form. This is where users browse records, add new records, edit existing records, and use buttons to perform tasks. A form may show one record at a time, or it may show many records in a continuous layout. Forms can also contain subforms, which are forms inside other forms. A common example is an order form with customer and order information at the top and a subform below it showing the related order line items.
Forms can also be displayed in Datasheet View if the form was designed to allow it. This can be useful when users need to see many records at once in a grid-like layout.
Layout View allows you to make some visual adjustments to a form while live data remains visible. It can be useful for simple resizing, positioning, and anchoring tasks. Anchoring controls how objects behave when a form is resized, such as whether a control remains fixed in place or stretches with the form. For most form development, however, Form View is best for using the form and Design View provides the most control for building it.
Reports are different from forms because reports are intended for presentation rather than data entry. A report is appropriate for invoices, mailing labels, summaries, printed lists, PDF exports, and other output intended for viewing or sharing.
Report Design View is where you arrange report sections, labels, headings, logos, calculated totals, and other printed elements. You can control where information appears on the page and how it is grouped and formatted.
Report View lets you see a report on the screen, but Print Preview is usually the best way to inspect the final result. Print Preview shows the report as pages, including margins, page breaks, headers, footers, and page layout. If a report looks correct in Print Preview, it will generally look correct when printed or exported to PDF.
Macros and modules are additional Access objects used for automation. Macros provide a way to automate tasks without writing full VBA code. Modules contain VBA procedures and functions for more advanced automation and custom behavior. These tools are generally used after you are comfortable with tables, queries, forms, and reports.
When making design changes, pay attention to Access prompts asking whether you want to save. Saving design changes saves the structure of the object, such as field definitions, form controls, query criteria, or report layout. It does not necessarily mean that you are saving data records.
You can usually change views using the View button on the ribbon. You can also right-click an object in the Navigation Pane before opening it, or right-click the title bar of an open object. The available views depend on the type of object. Tables have Datasheet View and Design View, while forms have Form View and Design View. Queries can have Datasheet View, Design View, and SQL View. Reports have Report View, Design View, Layout View, and Print Preview.
A simple rule of thumb is to use Design View when you need to change how an object is built. Use Form View when entering or editing data through a form. Use Datasheet View when reviewing table data or query results. Use Print Preview when checking a report that will be printed or exported.
When importing data from Excel, it is important to define your destination table carefully, especially when importing notes, procedures, descriptions, or other long text. Access may guess the field type based on the first several rows of the spreadsheet. If those rows contain short values, Access may create a Short Text field, which is limited to 255 characters. Any text beyond that limit can be truncated during the import.
To prevent this, create the destination table first and set the appropriate field to Long Text. Then append the Excel data into that existing table rather than allowing Access to create a new table automatically. Long Text fields can store much larger amounts of text than Short Text fields, although storing extremely large documents inside a database may not always be the best design. In many situations, it is more practical to store documents as files and save the file path or link in the database.
When splitting an Access database, remember that the purpose is to separate the tables from the rest of the application. The back end contains the tables and data. The front end contains forms, queries, reports, macros, and VBA modules. The front end uses linked tables to connect to the data in the back end.
If tables are manually moved to another database without creating links from the front end, forms and queries may stop working because they cannot find their original tables. The forms do not need to be rebuilt. The front end simply needs linked tables that point to the tables in the back-end database.
Splitting a database does not reduce the number of forms, queries, reports, or other objects shown in the Navigation Pane. If the Navigation Pane is crowded, use meaningful object names and custom Navigation Pane groups to organize objects. Splitting is mainly for data management, multi-user use, maintenance, and deployment, not for Navigation Pane organization.
When working with SQL, there is an important distinction between a saved Access query and SQL assembled through VBA. In a normal Access query, Access can directly understand references to table fields and controls on an open form. In VBA, SQL is initially just text. The code must build a complete SQL statement by inserting the current values of variables or controls before Access executes it.
For example, if code needs to run a query for a particular customer ID, the code should create a SQL statement that includes the actual customer ID value. Text values need to be handled differently from numeric values because text must be enclosed in quotes in SQL, while numbers generally do not. The code's job is to assemble a valid statement, account for the data type of each value, and then send that completed statement to Access for execution.
If you encounter an error stating that an Exit Sub statement is not allowed in a Function or Property procedure, check the type of procedure you are in. A Sub procedure and a Function procedure use different exit statements. The error means that the procedure type and the exit statement do not match. Read the full error message carefully, because it often identifies both the incorrect statement and the type of procedure where it is being used.
The most important skill is learning to recognize whether you are designing an object or using it. Once that becomes familiar, the different Access views stop feeling confusing. You can choose the view that matches the task: build in Design View, enter data in forms, review results in Datasheet View, and inspect reports in Print Preview. Primary Topics
Access Design View, table Datasheet View, query Design View and SQL View, form views, report views and Print Preview, VBA Exit Sub versus Exit Function, Excel long-text import truncation, split databases and linked tables, dynamic SQL in VBA
Secondary Topics
Layout View, subforms, QBE grid, Navigation Pane organization, barcode inventory systems, SQL concatenation, text versus numeric SQL values
|
