Homework answers / question archive / Access Business Unit 5 Capstone Problem Solve 2 Student data files needed: a05Hotel

Access Business Unit 5 Capstone Problem Solve 2 Student data files needed: a05Hotel

MS Access

Share With

---

Access Business Unit 5 Capstone
Problem Solve 2

Student data files needed:
a05Hotel.accdb

¢ a05HotelLogo.jpg

You will save your file as:
a05Hotel_LastFirst.accdb

New Forms for Hotel Database

¢ Production & Operations
The hotel at Painted Paradise Resort and Spa offers accommodations ranging from individual rooms to a grand villa suite.
The hotel must track all aspects of a reservation including special requests for items such as a crib. The hotel also has to track
room charges that guests have made. Room rates vary according to size, season, demand, and discount. The hotel has
discounts for organizations such as AARP. The database that is used to track charges needs some forms to make it easier to
use; you will provide these.

a. Open the Access file a05Hotel. Save your file as a05Hotel_LastFirst using your last and first name. If necessary,
enable the content.

b. Create a form that includes all fields from tblGuests, uses a Columnar layout, and is named frmAddGuest

¢ Change the title of the form to Add a Guest and then add the date and time with default settings to the header.
Anchor the date and time to the top right of the form.

e Change the form detail back color to White, Background 1, Darker 15%, and then change the header back color
to White, Background 1. Move the Detail bar below the header to the 1.5" mark on the vertical ruler. Resize and
move the title by changing the Width property to 3.8", the Height property to 0.5", and the Left property to 0.1".

¢ Insert the logo a0SHotelLogo.jpg in the form header, delete the blank header box next to the logo, and then
change the Width property to 3.8", the Height property to 0.75", the Left property to 0.1", and the Top property
to 0.70".

¢ Disable the GuestID text box.

¢ Save and close the form.

c. Create a form using the GuestFirstName, GuestLastName, and Phone fields from tblGuests and the CheckInDate,
NightsStay, NumberOfGuests, Crib, Handicapped, RoomRate, DiscountType, and Room Type fields from
tblReservations. Accept the default view and default layout. Change the title of your form to frmAddReservation and
the title of the subform to frmAddReservationSubform

¢ Add a default value of 1 to Nights Stay, add a default value of 1 to NumberOfGuests, and then add a default
value of One King to Room Type.

¢ Delete the subform label. Drag the left edge of the subform to the .2" mark on the horizontal ruler. Drag the right
edge of the subform to the 8" mark on the horizontal ruler.
¢ Change the title in the header to Add a Reservation
¢ Insert a combo box to the right of the title in the header that finds a record on your form based on the value you
select. Select the GuestLastName and GuestFirstName fields. Sort in ascending order by GuestLastName and
then change the label of the combo box to Find Guest
e Align the Combo Box label so that the left-edge is on the 3” mark on the horizontal ruler and move the Combo
Box and label so that their top edge is on the .5" mark on the vertical ruler.
d. Create a button to go to the previous record.
¢ Show the text Previous Guest and name the button cmdPreviousGuest
e Change the Left property to 5" and the top should line up with the top of the GuestLastName field.
e. Create a second button to the right of the Previous Guest button to go to the Next record.
¢ Show the text Next Guest and name the button cmdNextGuest
¢ Change the Left property to 6.25" and align the button to the top of the Previous Guest button.
f. Create a third button to open frmAddGuest and show all the records.
¢ Show the text Add a Guest and name the button cmdAddGuest
¢ Align the button below the Previous Guest button.
¢ Edit the On Click event macro of the cmdAddGuest button so that the Data Mode action is set to Add.
g. Create a fourth button to close the form.
¢ Show the text Close Form and name the button cmdCloseForm
¢ Align the button below the Next Guest button.
h. Align and resize all four buttons.
¢ Top-align the Add a Guest and Close Form buttons.
¢ Select all four buttons and set the size to the widest.
i. Remove the Record Selectors and Navigation Buttons from the main form and subform, and then save and close the form.

j. Create a Blank form for use in a Query by Form and make the following changes in Layout view.
e Add a Guest Lists title to the form.
e Add a Text Box control to the Detail area.
¢ Edit the label to be Search Guest Name
e Edit the Name property of the text box control to be searchGuestName and edit the Width property to be 2
e Add a second Text Box control below Search Guest Name with a label of Search Guest State and name the text
box searchGuestState
¢ Create a custom Input Mask in the searchGuestState text box so that the field only allows two capital letters.
e Save the form as frmGuestListsQBF
k. Edit the qryGuestList query so that the criteria can be specified in the frmGuestListsQBF form.
¢ Modify the Criteria row of the GuestName field so that it uses the value in the search-GuestName text box
control. Incorporate wildcards on either side of the value.
¢ Modify the Criteria row of the State field so that it uses the value of the search-GuestState text box control. Allow
for the field to be left blank if desired.
¢ Save and close the query.
1. Create Mailing Labels using the name and address fields from qryGuestList.
¢ Use Avery C2160 labels with a Calibri font, size 8, and Font Weight of Normal.
e Add the GuestName field to the first line and Address to the second line.
¢ Add City, State, and ZipCode to the last line, with a comma and space between City and State and a space
between State and ZipCode.
¢ Sort by GuestName.
¢ Name the report rptGuestLabels
m. Add buttons to frmGuestListsQBF to run the query and mailing labels.
¢ In Layout view of the frmGuestListsQBF form, add a button under the labels that will run the qryGuestList query.
¢ Use View Guest List as the button text and name the button cmdRunQuery
¢ Add another button to the form that will Preview the rptGuestLabels report.
¢ Use Mailing Labels as the button text and name the button cmdPreviewReport

n. Test the Query by Form.
¢ Switch to Form view, type AK in the Search Guest State, and then click View Guest List to view all guests from
the state of Alaska. Close the query, click Mailing Labels, and then close the report.
¢ Delete AK from the Search Guest State field and type S in the Search Guest Name box. Test both buttons to see
all guests with an S in their name.
o. Close the database, exit Access, and then submit the file as directed by your instructor.