Generate forms from tables

- [Instructor] You can have a basic data entry form up and running in as little as two clicks. First, we're gonna select the table that you'd like to create the form from in the navigation pane, we go ahead and choose the Guest table, then on the Create tab of the ribbon, in the Forms group, just click the Form button. Access will go through and set up the form based off of the table that we had selected in the navigation pane, and this form has cells that correspond to a single guest record in our table. Here we have the guest ID: the first name, last name fields, and so on for the address. At the very bottom of the screen, just like with our tables, we have record selectors.

We can page through our different records. For instance, I can go from Katherine Reid's record, number one to Donald Bryant, who's record number two. This corresponds to the same data that we find in the guest table. I open that up and just review that data. Here we have record number one for Katherine Reid, and here we have record number two for Donald Bryant. Let's switch back into the guest form. Now in addition to the text boxes that hold that information from the guest table, Access recognizes the relationship between guests and room assignments and created a sub-data sheet down here at the bottom that displays all of the related records for each guest.

Let me go ahead and just scroll down on our form a little bit to bring that up on the screen. So here we can see Donald Bryant's check-in history with the hotel. He has a total of five different check-ins, and we can see those dates right there. Notice that the sub-data sheet also has its own set of record selectors that appear right here. We can page through these different records, and we can even add a new one by pressing on the starburst button. This illustrates one of the reasons that forms are used for data entry over entering data into the tables directly. We can use forms to enter information into multiple tables simultaneously, plus you don't need to manually manage the relationship between, in our case, the guest and the room assignments.

Because the form is currently displaying a single guest, Access knows that any assignments we make here belong to this guest, and the foreign keys will get added automatically. So let's add a new room assignment and verify that it places it correctly in the table. I'm gonna go down to the very bottom, and I'm gonna page through until I get to record number 10. This is for a person named Barbara Hayes. We can see that she's guest ID number 10, and that she lives in Texas in the United States. Now I want to add a new booking for her at the hotel, so I'm gonna scroll down here into the check-in date area, and we can see that Barbara has been to a hotel about 11 times.

I'll press the starburst icon to add a new blank record at the very end, and we'll type in her new check-in date of 1/11/2020. I'll press Tab to move over to the room ID field, and we're going to assign her to room number V3. This is one of our Victoria suites. The other columns that we have here are calculated to determine the day of the week-- So 1/11/2020 is actually a Saturday. We have a rate code that's being calculated from the room, so once I actually finish the room, we'll see that that rate code updates to a V-WE, which indicates that this is the weekend rate for the Victoria suite, and we gives it a auto ID number of 2250, and this just the assignment ID for this particular record.

So all we have to enter is the check-in date and a room ID, and everything else gets filled in automatically. Now I should note here that not any combination of dates and rooms will work, this table's been created with a constraint that prevents double-booking a room, so we won't be allowed to assign a guest to a room that's already been booked on the same day. This is just something to keep in mind if you start playing around with this interface on your own. Let's go ahead and close this form, and Access is gonna prompt me to save it because I actually haven't saved it since it was created, so save the form Guests. And actually, I'm gonna give it a new name here.

Let's call it Guest Check-ins. Now we can go ahead and close the table called Guests, and let's take a look at our room assignment's table data and see if we can find Barbara's new record. I'm gonna come down to the very bottom, and I'll press the button here to jump us to the very last record. Now because these are in numerical order based off of the date, the date that we entered was 1/11/2020, so we're actually gonna have to come up here a little bit, and I'm gonna find that record that we just entered right there. Here is it, 1/11/2020, in room V3. We have guest ID number 10, which corresponds to Barbara, and we can see that it indeed was a Saturday, that's the weekend rate.

Because the arrangement of the sub-data sheet within the guest form, Access knew that the room assignment that was made belonged to Barbara, and it properly set the foreign key here to the value of 10, and that's not something that we had to type in on our own. So we verified that this data has gotten entered in properly into our room assignment's table. Let's go ahead and close that out. Now as a quick aside, there's one setting that you might need to look at when creating these types of forms with a linked sub form. Let's go ahead and open up the Guest table real quick and actually gonna switch it into Design View. On the ribbon, there's a button here to open up the Property Sheet and this will give us an access to the behind the scenes preferences for this table.

One of the properties here is called Sub-Data Sheet Name and you can find that right down here. Right now it's set to the table room assignments and this is where you would go to tell Access which table to link to in a Sub Form Arrangement. This same property controls a little hidden interface when you're working with a table in Data Sheet view. Let's go ahead and close the Property Sheet and then switch back into Data Sheet view. You'll notice we have these little plus icons to the left of each record. When I extend those open that also displays the Sub Data Sheet here that shows us the room assignments for each customer. So having Access create a basic form for us probably couldn't get much easier.

All you need to do is select the table and then press the Form button. However, there's a lot of additional functionality and customization that can be built into our forms using the Form wizard and the Design views