Welcome

Welcome! Build Reservation System & Copy Orders

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

Welcome to Microsoft Access Developer Level 24. In this course we will build a reservation system using the rental database from Level 23, adding logic for future reservations with specific date restrictions and implementing conflict resolution. We will discuss how to copy an order along with its detail records using both basic manual methods and, later, a proper method with recordsets and looping. We will also cover how to remove child records when deleting an order, and how to identify orphan records if any are missed. This course requires a solid foundation from previous levels in the Developer series.

Navigation

Prev: D24
Next: Lesson 01

Keywords

Access Developer, reservation system, order copy with details, rental database, conflict resolution, minimum rental period, maximum rental period, delete child records, find orphans, recordset loop, future proof code, GoToControl, GoToRecord, RunCommand

Subscribe to Welcome
Get notifications when this page is updated

More Information

Transcript

Welcome to Microsoft Access Developer Level 24 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

In today's class, we are going to build a reservation system, and we are going to learn how to copy an order along with its details to a new order.

This class follows Access Developer 23. It is strongly recommended that you take that class before this one, as well as all the classes before it in the Developer, Advanced, Expert, and Beginner Series.

I will be using Access 365. I strongly recommend its subscription. You have always got the latest and greatest version of Access. Access 2019 is the most recent version of the retail edition. The lessons in this class should work all the way back to about Access 2007 or so; not much has changed.

If you have any questions about the material covered in today's class, just scroll down to the bottom of the page that you are on and post them right there. Also, make sure to take a look through any of the other questions that have been posted because your question might have already been answered.

Of course, make sure you click that big red subscribe button and you will get notified anytime a question is posted about this class. If you have any Access questions not related to this class, go ahead and post them in the forum.

Let's take a closer look at exactly what is covered in today's class.

In lesson one, we are going to build a reservation system. We are going to go back to the rental database that we built in Developer 23 and we are going to add reservations to it. We will put in some logic for our fictitious business, some rules. For example, it has to be a future reservation, but no more than one year in the future. It has to be a minimum one day rental, but a maximum two week rental. And then the hard part, conflict resolution, making sure this new reservation does not conflict with someone else's existing reservation. That is the top part and we will cover that in this lesson.

In lesson two, we are going to learn how to copy an order with its details. We know how to make a copy of a simple record, even with a record set. However, when you copy a record, you have to also make sure to copy the detail items, the child records. I am going to show you the messy way to do it in this lesson using GoToControl, GoToRecord, RunCommand, Copy, and Paste, and a bunch of other stuff.

I will also show you how to delete the child records if you delete an order, and then we will learn how to find orphans in case you forget to do that.

In lesson three, we are going to copy the order and the order details the right way, the proper way using a record set. We are going to loop through the fields in the record set. This is the first time we are learning how to do this, so we do not have to say copy order date, copy order ID, copy product description, and all that stuff. We will just loop through them in a For Each loop.

This will future proof the copy because this way, if we add new fields later to either of these tables, we do not have to update the code. That is going to be in lesson three.

Intro

Quiz

Q1. What is the main project being built in this Access Developer Level 24 class?
A. A reservation system that can copy an order with its details
B. A customer feedback survey form
C. An inventory tracking spreadsheet
D. A payroll processing tool

Q2. Which earlier Access Developer class must be taken before this one?
A. Developer 22
B. Developer 23
C. Advanced Level 10
D. Beginner Level 5

Q3. What is the recommended version of Access to use for this course?
A. Access 2000
B. Access 365 (subscription)
C. Access XP
D. Access 2010 Starter Edition

Q4. What is the first key rule for reservations covered in lesson one?
A. Reservations must last at least two weeks
B. Reservation can only be made for past dates
C. Reservations must be in the future, but no more than one year ahead
D. Reservations must have no time limit

Q5. What is the minimum and maximum duration allowed for a reservation in this project?
A. 1 hour minimum, 1 day maximum
B. 1 day minimum, 2 week maximum
C. 2 weeks minimum, 1 month maximum
D. No duration restrictions

Q6. What needs to be checked to ensure new reservations are valid?
A. If it is the customer's birthday
B. If payment has been received
C. If the reservation conflicts with an existing reservation
D. If the item is part of a special promotion

Q7. What is the "messy way" demonstrated to copy an order with its details in lesson two?
A. Using SQL joins exclusively
B. Using GoToControl, GoToRecord, RunCommand, Copy, and Paste
C. Exporting to Excel and re-importing
D. Manually writing each field to a file and reading it back

Q8. What important step is demonstrated when deleting an order in lesson two?
A. Change the customer name
B. Notify the customer by email
C. Delete the child (detail) records as well
D. Archive the order for ten years

Q9. What is the proper way to copy orders and order details covered in lesson three?
A. Manually copy and paste each field individually
B. Use the built-in Access duplicate button
C. Loop through fields in a record set with a For Each loop
D. Export data to Word and re-import

Q10. Why is looping through fields in a record set considered future proof?
A. It generates automatic backup copies
B. It ensures discounts are always applied
C. If new fields are added later, the code does not need to be updated
D. It always emails a summary to the manager

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

Summary

Today's video from Access Learning Zone is Microsoft Access Developer Level 24. I am Richard Rost, and I will be your instructor for this class.

In this lesson, our primary focus will be creating a reservation system and learning how to duplicate an order along with its related order details to generate a new order.

This level is a direct continuation of Access Developer 23. If you have not yet completed that class and the previous courses in the Developer, Advanced, Expert, and Beginner series, I strongly suggest you do so, as we build on those concepts here.

I am using Access 365 for the demonstrations in this course, and I highly recommend the subscription model because it keeps your software up to date with all the latest features. Access 2019 is the most recent standalone version. However, if you are using an older version, such as Access 2007 or later, you should still be able to follow along easily, as not much has changed in the core functionality we will be working with.

If any questions come up regarding today's material, you are encouraged to scroll to the bottom of the page and submit your questions there. Be sure to review the existing questions and answers as well, since you might find that your topic has already been discussed.

Remember to subscribe so you receive notifications whenever someone posts a new question about this course. If you have general Access questions not related to this class, post those in the forum.

Now, let me walk you through what we will cover in today's class.

In the first lesson, we will construct a reservation system. Returning to the rental database we set up in the previous class, we will add new features to handle reservations. This includes programming some business logic and validation rules. For example, we will require reservations to be no more than a year in advance, and they must be for at least one day but are limited to a maximum of two weeks. The most challenging aspect will be managing conflicts, ensuring that any new reservation does not overlap with existing reservations. That will be the main focus of the opening lesson.

In the second lesson, I will teach you how to copy an order along with all its detail records. You may already know how to duplicate a single record with a recordset. However, copying parent records with their related child records (in this case, an order and its order items) is more involved. I will show you an approach using various commands such as GoToControl, GoToRecord, RunCommand, Copy, and Paste to handle the process, even though it can seem a bit messy.

Additionally, I will demonstrate how to handle deletion scenarios, so that if you remove an order, its detail records are also deleted. We will also discuss how to identify and clean up orphaned detail records if you forget to delete them along with the parent order.

In lesson three, we will tackle copying orders and order details in a more robust way using recordsets. Rather than writing separate code to copy each individual field, we will set up a loop through the fields of the recordset so they are handled automatically. This will make the code more flexible. If you add new fields later to either table, you will not need to update the code because the loop will take care of them automatically. This modern approach to copying records is what we will work on in the third lesson.

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

Building a reservation system in Access
Adding reservations to a rental database
Implementing business rules for reservations
Validating reservation dates (future, max one year out)
Enforcing minimum and maximum rental duration
Checking for reservation conflicts
Copying an order record in Access
Copying order details (child records)
Deleting child records when an order is deleted
Finding orphaned child records
Copying orders and details using recordsets
Looping through fields in a recordset with For Each
Future-proofing copy operations for added fields

Primary Topics

reservation system, conflict resolution, record copying, order details, recordsets, child records management

Secondary Topics

rental database, deletion of child records, orphaned records, VBA automation