Find records

- [Instructor] On our check-in form, we have a situation where it's difficult to find a specific guest. Right now I can page forward and backward through our different records, but imagine trying to locate the correct page for someone that's looking to book a room. We can alleviate this by adding a search box to the top of our form right up here. This will allow us to find a record for a specific guest, based off of their last name. To do this, I'm gonna switch our view into Design view. I'll click on the bottom portion of the view button, and then switch to Design view there. First, I'm gonna make my form a little bit wider. So I'll come over here to the far right, and when I get this double-headed arrow, I'll just click and drag it open a little bit.

Then, on the Design tab of our ribbon, I'm gonna grab a control called a combo box which is represented by this icon here. A combo box is essentially a dropdown menu and we've seen these several places throughout Access. I'll go ahead and click that once, and then when I come down into my form you'll notice that my icon has changed to indicate that the next click that I make will drop a combo box right into our form. When I do that, that starts up the Combo Box Wizard. Now there's a couple of different options here, but the one that I want is the bottom one. I want to find a record on my form, based off of a value that I select in the combo box.

So I go choose that option and press Next. Then, we get to select which fields are gonna show up in our combo box's details. I wanna order this by our last name of our guest, so I'm gonna select LastName and move that over, but I also wanna see the guest's first name, just for reference, so I'll select that one and move that one over second. With both of these listed in the Selected Fields section, I'll press next. This'll give me a preview of what the dropdown menu is gonna look like, so it'll have our guest list out this way. I'm just gonna press the next button. And then finally, we get to label our combo box.

Instead of LastName1, let's just call it Search. I'll press Finish, and that gets added here into my form. Now we have a label here that's actually on top of my button right now, and then we have the combo box element itself. Let's actually make the combo box wider, I'm gonna click here and drag to the left a little bit, and I'm also gonna click and drag to the right. I'm gonna make my form a little bit wider here and then we can move this down and give us some more breathing room. If you wanna move the label search on top of the combo box, you'll need to grab it with the gray handle in the upper left-hand corner.

So you click there and drag, it'll move independently of the combo box itself. So, now that that's in position, let's go ahead and test it out. I'll switch my view back into Form view, and if I use the drop-down menu, we should see a listing of all the different people in our database. I come up here to the top and if I type in a name, let's type in Hayes, you can see that we can select Barbara Hayes from the list. Or if I type in Reid, we'll be able to select Katherine Reid from the list. Selecting that record will jump right to those records on the screen. Now, it'd be better if this was in alphabetical order, and we can make that change back in Design view.

Once again I'll switch my view, and with the combo box selected, I need to open up the Property Sheet. Up here on the Design tab, we'll click on the button for Property Sheet. That'll open up over here on the right. On the Data tab for the Property Sheet for the combo box, I wanna look for a property here called Row Source. This controls how the records will appear in the dropdown menu of the combo box itself. And we can see that it starts with the work Select. This is actually a select statement that indicates that this is a query that's actually running the contents of the combo box.

We can edit that query by coming over here and clicking the button with the three dots. Remember this ellipsis button we've seen several times in Access. That'll open us up into a Query Builder. So, this is a query that lives inside of the combo box, and the combo box lives inside of the form here inside of Access. So this query is powering the results of the combo box and all I wanna do is add a sort on the LastName field. So you can come down here into the sort row, underneath LastName, and change it to Ascending. If I want to I can run the results here and I could just verify that this is the way that I want it to appear inside of the combo box.

When we're done, let's go ahead and close the tab, that'll prompt me to save the query, I can say yes to that, then it'll return me back to the Form Design view, I can go ahead and close the property sheet, and then test it out again. Now if I go over here to our search box and open it up, we should see things in alphabetical order. Let's type in Hayes again. Now I can see Barbara Hayes, but we can also have an Emily Hayes and a Nicole Hayes that we could choose. Picking anybody off this list, it immediately jumps to their record here, in which case I can go to RoomAssignments and add them to our hotel directory.

So now we have an easy way to jump to specific guests in our check-in form. Plus, we saw that the list that appears in the combo box is driven from the same query engine that we already know how to work with. So making edits to the combo box's contents or functionality is simply a matter of digging down to its properties and editing the underlying query.