|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
Access Developer 17WelcomeDeveloper 17 spends more time on List Boxes, Recordsets, exporting single customer reports as individual PDF files, and creating a new function called DLookupPlus. See below or watch the intro video to the right for details. ResourcesTopics Covered
In Lesson 1, we will tackle the difficult task of removing items from unbound multi-select list boxes.
In Lesson 2, we will learn how to use a recordset to loop through all of our customer records and export individual reports for each of them as a separate PDF file.
In Lesson 3, we will build a function called DLookupPlus which will allow us to use a recordset to return an entire list of values from a table or query, just like DLookup does, but instead of being limited to one record.
Enroll TodayEnroll now so that you can watch these lessons, learn with us, post questions, and more.
Questions?Please feel free to post your questions or comments below. If you are not sure as to whether or not this product will meet your needs, I'd rather help you before you buy it. Remember, all sales are final. Thank you.
IntroIn lesson 17, you will learn how to work with multi-select Listboxes and Record Sets in Microsoft Access. I will show you how to remove items from an unbound multi-select Listbox, export personalized PDF reports for each customer using a Record Set, and create a custom function called dlookupplus to gather a list of values from a table or query. This lesson builds on previous developer-level topics and covers practical techniques you can use in your Access databases.TranscriptWelcome to Microsoft Access Developer Level 17, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's class, we're going to continue working with Listboxes and Record Sets. We're going to remove items from a multi-select Listbox, export individual PDF files from records in our database, and we're going to create a function called dlookupplus. We're going to continue working with multi-select Listboxes, like we have been for the last couple of classes. In the first lesson today, we're going to cover removing items from an unbound multi-select Listbox. That's where you've got a list of items in the box, not tied to a table or query. They're just stored in the box, and we're going to learn how to select multiple items and remove them. In lesson two, we're going to continue working with Record Sets, and we're going to see how to export a customer report that is specific to each customer to a folder on your hard drive. So customer one gets his own PDF, customer two gets his own PDF. This is great for sending out statements. You can send these out in email attachments. You can do whatever you want. Each customer gets their own customer report generated by looping through the customers in a record set and exporting each one using a report to a PDF file. Finally, in lesson three, we're going to write our own function called dlookupplus. Now the standard dlookup function lets you look up one value from a table or query. dlookupplus lets you look up a whole list. You can bring back a list of items. For example, in the example here, you've got a table that's got your sales rep in it, and a list of what states they cover. So you've got Joe, California. The next record of course would be Joe, Maine, Joe, New Jersey, Joe, New York. That's how a properly set up relational set of tables would have them. However, for reporting purposes you might want to bring back a list, a comma-separated list of all of the states that Joe covers. So dlookupplus will say go out to the table and look up all of the values that meet a specific set of criteria. That's dlookupplus. This class is recorded to work with either Microsoft 365 or Access 2019, but it should work going back to all versions back to, I think, about 2007. You'll be fine. 2016, 2013 - those are all good. You should, of course, have taken my beginner, expert, advanced, and developer classes up to at least Developer 1. I strongly recommend taking from Developer 13 on because I do cover some stuff that is reliant upon those lessons. The multi-select list boxes, of course, go back a few lessons. So just take all the developer classes and you'll be fine. Now it's time to sit back, relax, and watch Developer 17. QuizQ1. What is one of the main topics covered in Developer Level 17?A. Working with tab controls B. Removing items from a multi-select Listbox C. Creating SQL Server databases D. Designing macros from scratch Q2. What kind of Listbox are students taught to remove items from in lesson one? A. Bound multi-select Listbox B. Unbound multi-select Listbox C. Single-select Listbox D. Value List Listbox only Q3. In lesson two, what is exported for each customer using Record Sets? A. A text document B. An Excel spreadsheet C. An individual PDF customer report D. A CSV file Q4. What is one use mentioned for the exported PDF reports? A. Uploading to a website B. Printing automatically C. Sending as email attachments D. Importing into another database Q5. What is the purpose of the dlookupplus function described in the video? A. Finds the maximum value in a table B. Looks up a single field value from a table C. Returns a list of values matching criteria, not just one D. Counts the number of records in a table Q6. How does dlookupplus improve upon the normal dlookup function? A. It sorts records automatically B. It updates multiple records at once C. It returns a comma-separated list of matches D. It creates tables dynamically Q7. For what versions of Access is this course designed to be compatible? A. Access XP and earlier only B. Microsoft 365 and Access 2019, back to 2007 C. Access 2016 only D. Only the latest version of Access Q8. What prerequisite is strongly recommended before taking Developer Level 17? A. Only Advanced Level classes B. Only Beginner Level classes C. All Developer classes, especially from Developer 13 onward D. Macro and SQL workshops only Answers: 1-B; 2-B; 3-C; 4-C; 5-C; 6-C; 7-B; 8-C 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. SummaryToday's video from Access Learning Zone covers the material in Microsoft Access Developer Level 17. I am your instructor, Richard Rost.In this lesson, we will continue our work with list boxes and record sets. Our focus will be on several advanced Access techniques, starting with managing multi-select list boxes, exporting reports for individual records as PDF files, and introducing a new function called dlookupplus. To begin, we will revisit multi-select list boxes like the ones we have explored in recent classes. The first lesson demonstrates how to remove items from an unbound multi-select list box. This means that the items in the list are not connected to a table or query, but are manually listed within the box itself. You will learn how to select multiple items at once and remove them from the list altogether. The second lesson takes us further with record sets. We will look at exporting customer-specific reports as PDF files, saving each report to a folder on your computer. For example, every customer will receive their own PDF document—customer one gets a file specifically for them, customer two gets theirs, and so on. This is a very practical tool for situations where you want to distribute statements or individualized reports as email attachments or save them for recordkeeping, all automated by looping through customer records and exporting individualized reports. The third lesson introduces dlookupplus, a custom function that extends the capabilities of Access's standard Dlookup function. While the standard Dlookup retrieves a single value from a table or query, dlookupplus allows you to return an entire list of values matching your criteria. Imagine you have a sales reps table, where each state covered by a rep appears as a separate record; for instance, Joe covers California, Maine, New Jersey, New York, and so forth. Although this is the correct way to design relational tables, sometimes you want to produce a report that shows all the states a rep covers in a single, comma-separated list. Dlookupplus lets you do exactly that: it collects all matching values and returns them as a grouped list. The material in this class is recorded using Microsoft 365 and Access 2019, but everything presented should be compatible with versions as early as Access 2007. If you are using Access 2016 or Access 2013, you should be all set as well. Before jumping into this class, I recommend that you have completed my beginner, expert, advanced, and at least the first Developer class. If you have taken Developer 13 and onward, you will be perfectly prepared, since some of the techniques rely on those earlier lessons—especially the work with multi-select list boxes. Now it is time to get started with Developer Level 17. 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 ListRemoving items from an unbound multi-select ListboxExporting individual customer reports to PDF files Looping through record sets to export reports Creating the dlookupplus function Returning comma-separated lists from table fields with dlookupplus |
||||||||||||||||||||
|
| |||
| Keywords: access developer 17 Remove Items Unbound Multi-Select List Box Single Column Box Multiple Column Box Export Individual Customer PDF Files Create Customer Report Remove Alternating Background Color Force New Page Export Report acFormatPDF Open Report to Sp Page Tag: whatsnew PermaLink Microsoft Access Developer 17 |
