Introduction

Welcome! Invoices as Quotes & DLookup Tips

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Expert Level 10. In this course we will set up invoices to also function as quotations, add paid or unpaid indicators, and continue working with system default values. We will discuss the DLookup function, which lets you retrieve values from tables or queries, and introduce calculated table fields, including their pros, cons, and when you might use them. Prerequisite knowledge from Access Expert Level 9 is recommended. We will also discuss differences for Access 2007 and earlier versions, and I will explain how this course fits into the overall series and how to use the student forums effectively.

Navigation

Prev: X10
Next: Lesson 01

Keywords

Access Expert, DLookup, invoice vs quotation, paid unpaid invoice status, system default values, calculated table fields, calculated query fields, sales rep lookup, add phone number to order form, company name system default, system defaults form

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Expert Level 10 brought to you by AccessLearningZone.com. I am your instructor Richard Rost.

Today's class focuses on three main topics. First, we will set up our invoices so they can also be quotations. We will also put "paid" or "unpaid" at the top of each invoice. And we will do some more work with those system default values that we started working with in the last class.

Next, we will learn about one of the more powerful functions in Access called DLookup. This function allows you to look up a value from a table or query. For example, in the image here, you can see I can select a sales rep for an order, and the DLookup function will automatically display that sales rep's phone number.

Finally, we will take a look at calculated table fields as opposed to calculated query fields. Calculated table fields allow you to put calculations directly into your tables instead of putting them in queries. There are some pros and cons, but we will discuss them in today's class.

The prerequisite for this class is Access Expert Level 9. The printable invoice that we built in Level 9 will be used in today's class. So if you have not taken Level 9, I strongly recommend you take it before taking today's class.

Today's class was designed to be used with Access 2013. If you are using Access 2010, this lesson will also be fine for you. Most of the material in today's class will work just fine with Access 2007 as well, except the lesson on calculated table fields. It is a very short lesson, and I really do not recommend using calculated table fields at all for most purposes. So you are really not missing much, but the last lesson, lesson 4, on calculated table fields, is not valid for Access 2007 because that feature was added in 2010. The bottom line is do not worry. If you are using Access 2007, you are not missing anything.

If you are using Access 2003 or earlier, you should be able to follow along with lessons 1 through 3, the quota invoice, and the DLookup functions. There is no exact match for this class because I did not use to cover these topics until my advanced lessons, and the material in today's class comes from three or four different lessons from the Access 2003 series.

As I have revised the course, I have decided to move some things around and change the order in which some things were displayed. So this class is a combination of a few different lessons from my Access 2003 series.

My courses are broken up into four groups: Beginner, Expert, Advanced, and Developer. The beginner lessons are designed to give you a basic overview of the simple features of Access. The Expert series, the one you are in now, is designed for more experienced users who are already comfortable with Access. The Advanced lessons cover working with macros, automation, and many more advanced features. The Developer lessons get into programming with Visual Basic for Microsoft Access. Each of the series are broken down into different numbered levels, starting with Level 1. The Beginner series, for example, had Levels 1 through 9.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders, tracking accounts payable, learning the SQL programming language, and lots more. You can find complete details on all these seminars and more on my website at accesslearningzone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you are watching this course using my custom video player software or the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class videos, as long as you have an active internet connection. Here you will see all of the questions that other students have asked, as well as my responses to them, and comments that other students may have made. I encourage you to read through these questions and answers as you start each lesson and feel free to post your own questions and comments as well.

If you are not watching the lessons online, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend that you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually, create the same database that I make in the video, step by step. Do not try to apply what you are learning right now to other projects until you master the sample database from this class. If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forum.

Most importantly, keep an open mind. Access might seem intimidating at first, but once you get the hang of it, you will see that it is really easy to use.

Now let us take a closer look at exactly what is covered in today's class.

In the last class, Access Expert 9, we developed a printable invoice that we could generate off of our order form. In today's class, we are going to add the ability to switch between an invoice and a quotation. And if it is an invoice, mark whether it is paid or unpaid and display that information right on top of the quota invoice. In addition, we are going to take the company name, our company name, PCRESHELL.net, and we are going to add that information to the system default table.

In lesson 2, we are going to move the system default values off the main menu onto its own form. And then we are going to learn about one of the most powerful functions in Access called DLookup, where you can look up a value directly out of a table without having to have the form open.

In lesson 3, we are going to spend some more time working with the DLookup function. We are going to add a sales rep field to our customers and our orders. By picking the sales rep, I am going to use DLookup to display that sales rep's phone number on the order form and on the invoice.

In lesson 4, we are going to discuss calculated table fields, how they are different from calculated query fields, what the problems with them are, why you generally should not use them, and the exception when it is okay to use them. And we are going to use them.

Quiz

Q1. What new feature is added to invoices in this class?
A. The ability to use them as quotations
B. A barcode field
C. Automated email sending
D. Batch printing

Q2. What label is displayed at the top of each invoice to indicate its status?
A. Processed or unprocessed
B. Approved or rejected
C. Paid or unpaid
D. Sent or unsent

Q3. What function in Access is taught for looking up values from a table or query?
A. DSUM
B. DLOOKUP
C. DMAX
D. DCOUNT

Q4. Why were calculated table fields not covered for Access 2007 users?
A. The instructor does not like Access 2007
B. Calculated table fields were only added in Access 2010
C. They are too complex for Access 2007 users
D. Access 2007 handles lookups differently

Q5. What is recommended before applying the concepts from this course to your own projects?
A. Read the course transcript
B. Complete the sample database step by step
C. Watch unrelated Access tutorials
D. Only use printed documentation

Q6. What are the four main groupings of Access courses discussed?
A. Beginner, Expert, Advanced, and Developer
B. Junior, Senior, Manager, Director
C. User, Designer, Analyst, Programmer
D. Basic, Intermediate, General, Professional

Q7. Which lesson covers moving the system default values to their own form and introduces DLookup?
A. Lesson 1
B. Lesson 2
C. Lesson 3
D. Lesson 4

Q8. What advice does Richard give about using calculated table fields?
A. Use them as much as possible
B. Never use them
C. Generally do not use them, except for a few cases
D. Only use them in Access 2007

Q9. What do the student forums provide to students?
A. A way to submit project files
B. Access to lesson questions, answers, and comments from other students
C. Code samples for every lesson
D. Automatic certification upon posting a comment

Q10. What is the main purpose of the DLookup function as described in the video?
A. Submitting data to a web form
B. Looking up a value from a table or query based on certain criteria
C. Sorting records alphabetically
D. Importing external data sources

Q11. Which version of Access was this course designed for?
A. Access 2007 only
B. Access 2013
C. Access 2003
D. Access web app

Q12. If you are using Access 2003, which lessons from this course are NOT possible to follow exactly?
A. Lessons 2 and 3
B. Lesson 4 on calculated table fields
C. Lesson 1 on invoices
D. All lessons are compatible

Answers: 1-A; 2-C; 3-B; 4-B; 5-B; 6-A; 7-B; 8-C; 9-B; 10-B; 11-B; 12-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

Today's video from Access Learning Zone is part of my Access Expert Level 10 course. I'm Richard Rost, and in this class, we'll cover several important topics that will build on what you've learned in previous courses.

We'll begin by updating our invoice setup so it can also function as a quotation. I'll show you how to indicate whether an invoice is paid or unpaid right at the top of the document. We'll also expand on the work we started in the previous class by enhancing the way we handle system default values.

Next, I'll introduce you to one of Access's powerful features, the DLookup function. This tool allows you to retrieve values from tables or queries dynamically. For instance, after selecting a sales rep for an order, DLookup will automatically display that person's phone number. This is a handy feature for simplifying your forms and ensuring that key information is always visible.

We'll also discuss calculated table fields versus calculated query fields. Calculated table fields allow you to embed calculations directly within your tables rather than relying on queries. There are both advantages and disadvantages to this approach, and I will explain when it makes sense to use them and when you should avoid them.

Before you start, make sure you've completed Level 9 of the Access Expert series. We will be building upon the printable invoice that you created there. If Level 9 is not familiar to you, I highly recommend reviewing it first.

This course was designed primarily with Access 2013 in mind, but it will also be appropriate for users of Access 2010. Most of the lessons will work in Access 2007, with the exception of the lesson on calculated table fields, as that feature was introduced in 2010. If you're using Access 2007, you only need to skip the lesson on calculated table fields. Users of Access 2003 or earlier can still follow along with the lessons on quoting invoices and the DLookup function, even though these topics were originally spread across different lessons in my Access 2003 series.

As my curriculum has evolved, I've moved some material around. This means that today's class combines concepts from several earlier Access 2003 lessons, ordered to provide a more logical learning path.

Let me quickly review the course structure for those who are new: I organize my series into Beginner, Expert, Advanced, and Developer tracks. The Beginner lessons introduce Access fundamentals. The Expert courses, like this one, are for users who've mastered the basics. Advanced lessons cover topics like macros and automation. Developer lessons focus on Visual Basic programming within Access. Each track is divided into numbered levels, so you can easily follow your progress.

Beyond my core courses, I offer specialized seminars on topics such as web-based databases, calendars, database security, working with images, managing work orders, handling accounts payable, SQL programming, and much more. Details on these seminars are available on my website.

If you have questions while working through this material, please take advantage of the student forums. If you're watching through my website or custom video player, you'll find the forums integrated alongside the class videos. Here you can read and post questions and see answers from me and other students. If you're viewing the lessons offline, you can still access the forums on my website later.

To get the most benefit from this course, I suggest watching each lesson all the way through before you try to follow along. After your first viewing, go back, play the lesson again, and build the sample database step by step with me. Practice with the sample database before you try using what you've learned on your own projects. If you hit any snags or have questions, rewatch the lesson or ask for help in the student forums.

Remember, be open-minded as you learn. Access can seem overwhelming at first, but as you grow more comfortable, you'll see that it's a powerful and user-friendly tool.

Here's an overview of what's ahead in this class. In Level 9, we built a printable invoice from our order form. Now we'll enhance it so it can switch between serving as an invoice and a quotation. We'll also add the ability to mark invoices as paid or unpaid, with that status displayed at the top. We will update our system defaults, adding our company name to the default settings.

In lesson two, we'll move system default values to their own dedicated form and expand on our use of the DLookup function. This will help us fetch values directly from tables, saving time and reducing clutter on our forms.

During lesson three, we'll take DLookup even further by associating sales reps with customers and orders. By selecting a sales rep, you'll instantly see their phone number on both the order form and invoice, thanks to DLookup.

Finally, in lesson four, we'll talk about calculated table fields. I'll show you the differences between these and calculated query fields, explain some of the drawbacks, and discuss when it's appropriate to use them.

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

Setting up invoices to also function as quotations
Displaying "paid" or "unpaid" status on invoices
Adding company name to the system default table
Moving system default values to a new form
Using the DLookup function to retrieve data
Displaying sales rep phone number with DLookup
Adding sales rep fields to customers and orders
Displaying sales rep phone number on order forms and invoices
Understanding calculated table fields vs query fields
Pros and cons of calculated table fields
When to use calculated table fields

Article

Welcome to this advanced Microsoft Access tutorial, designed for users who want to get the most out of their databases. In this lesson, we will cover several important topics: turning invoices into quotations, marking invoices as paid or unpaid, working with system default values, using the powerful DLookup function, and understanding the differences between calculated table fields and calculated query fields.

Let me walk you through each concept and demonstrate how you can apply it directly to your own Access projects.

To begin, let's look at how to modify your invoices so they can also function as quotations. In most businesses, you may need to provide customers with a price quote, which is not yet a formal invoice. You can use your existing invoice structure to handle both by adding a field, such as InvoiceType, to your Orders table. This field can indicate whether the document is an "Invoice" or a "Quote." In your invoice report, add an expression or use conditional formatting to display the document type at the top. For example, in the control source property of a label in your report, you can put something like:

=IIf([InvoiceType]="Invoice","Invoice","Quotation")

This will display "Invoice" or "Quotation" based on what you have selected for that order.

Next, you might want to indicate whether an invoice has been paid or is still unpaid. To do this, add a Yes/No field to your Orders table, for example "IsPaid." On your invoice report, you can add another label and set its control source to show "PAID" if the IsPaid field is checked, or "UNPAID" otherwise:

=IIf([IsPaid],"PAID","UNPAID")

Now, let's talk about managing your system default values. Often, you have core business information like your company name, address, or default terms that need to appear in different places across your database. Rather than hardcoding these on multiple forms or reports, it is better to store them in a table, such as a SystemDefaults table. Each record in this table can store a different setting. For example, you may have one record with a SettingName of "CompanyName" and a SettingValue of "PCRESHELL.net."

To conveniently edit these values, you can create a simple form called something like "System Defaults." On this form, add text boxes bound to each setting you wish to be editable. This keeps your main menu uncluttered and makes it easier to update your defaults as needed.

A very powerful function in Access, DLookup, lets you grab values from another table or query without having to open those tables explicitly. Let's say you have a SalesReps table with fields for RepID, RepName, and RepPhone. In your Orders table, you can have a SalesRepID field to link each order to a sales rep. On your Order form, when you select a sales rep, you can use DLookup to automatically display the rep's phone number.

Here is how DLookup works. In the control source of a text box, you might use:

=DLookup("RepPhone","SalesReps","RepID=" & [SalesRepID])

This looks up the RepPhone from the SalesReps table where RepID matches the value in the SalesRepID field of your form. This same technique can be used in reports, such as invoices, to automatically show the correct sales rep contact details. DLookup is very versatile but can slow down your database if you use it excessively with large datasets. For occasional lookups or displaying related information, it is ideal.

You will find using DLookup helpful in other cases as well, such as bringing in your company default values for display at the top of reports. For example, to show your company name on every invoice, place a text box on the report and set its control source to:

=DLookup("SettingValue","SystemDefaults","SettingName='CompanyName'")

This will pull the company name straight from your SystemDefaults table.

Now let's explore calculated fields in Access. Calculated query fields are a traditional way to add computed values to your queries. For example, you might want to calculate the total price on each order line by multiplying Quantity and UnitPrice. In a query, you can add a field like:

LineTotal: [Quantity] * [UnitPrice]

However, starting with Access 2010, you can also add calculated fields directly to your tables. In table design view, add a new field and set its data type to Calculated. Enter your expression, such as [Quantity] * [UnitPrice], and Access will keep this value updated automatically.

While calculated table fields seem convenient, you should generally avoid them for most business applications. They are less flexible than queries, harder to troubleshoot, and can cause problems if you later need to change your logic. Queries provide much more control and can easily be adjusted without restructuring your tables. A rare exception might be for storing a simple calculation that you are sure will never change, but even then, you must weigh the risks.

For users with older versions of Access, note that calculated table fields are only available starting with Access 2010. If you use Access 2007 or earlier, you should stick to calculated query fields.

If you have questions or get stuck, remember that you can seek help by posting on forums or looking up reference materials. The key to mastering these techniques is practice. I recommend that you first experiment by implementing these features in a sample database. Make sure you understand how everything works before trying to use them in your own projects.

As you continue to develop your skills in Access, you will find that features like DLookup and using system default values can make your applications much more dynamic and user-friendly. With careful design and a bit of exploration, you can create powerful database solutions tailored to your needs. Remember to always keep your approach organized and avoid unnecessary complexity where possible. Good luck with your Access development!