Papa_
Exp19_AC_CH10_GRADER_CAP_AS
.zip
Exp19_Access_Ch10_Cap – Drivers 1.1
Exp19 Access Ch10 Cap Drivers 1.1
Access Chapter 10 Capstone – Drivers
Exp19_AC_CH10_GRADER_CAP_AS
Project Description:
You have been asked to modify a database that tracks driver data and insurance details. You will create macros to automate data entry, broadcast an informational message to users, navigate through a form, and to open forms. You will also set an SQL statement to filter a report when it opens.
Start Access. Open the downloaded Access file named Exp19_Access_Ch10_Cap_Drivers. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files.
Open the Insurance table in Design view. Add a new field named RenewTag in the last position of the table with the data type Date/Time.
Create a data macro attached to the Before Change event. Use the SetField Action to populate the RenewTag field in the table. The RenewTag date will always be 21 days before the TagExpiration.
Add the comment Sets the renewal date for tag renewal. to the macro. Save and close the macro. Save the table.
Switch to Datasheet view of the Insurance table. Retype the TagExpiration in the first record (InsuranceID 10001), and then press the DOWN ARROW. The macro will be triggered and automatically fill in the RenewTag field with a date 21 days before the TagExpiration. Close the table.
Open the Insurance form in Design view. Add the RenewTag field to the form so that its label is aligned two dots below and with the left edge of the TagExpiration label. Switch to Form view, and then save and close the form.
Open the Drivers report in Design view. Create an embedded macro in the On Open property box.
Create a message box to display Please upload this report to Web site every Friday. (include the period).
Verify that Yes is in the Beep box. Select Information in the Type box. Type Upload Report in the Title box. Save and close the macro. Save and then print preview the report. Ensure that the message displays, click OK in the message box, and then close the report.
Open the Drivers form in Design view. Add a command button aligned two dots below and with the left edge of the Restrictions label.
Set the button to navigate to the next record in the form and to display Next Record as the button text. Set cmdNextRecord as the button name. Save the form, view the form in Form view and test the button.
Set the Background Color of the button to Light Blue (under Standard Colors) and italicize the button text. Save and close the form.
Open the Drivers report in Design view. Type an SQL statement into the Record Source property of the report. The statement should select all fields (*) for drivers in the Adult class. Save the report. View the report in Report view. Close the report.
Create a standalone macro that will open the Insurance form in Form view and Normal mode. Add an action to open the Drivers form in Form view and Normal mode.
Save the macro as Open Forms and close the macro. Run the macro from the Navigation Pane to test it and then close all forms.
Close all database objects. Close the database and then exit Access. Submit the database as directed.
Exp19_AC_CH10_GRADER_CAP_AS_Instructions x
Grader – Instructions Access 2019 Project
Exp19_Access_Ch10_Cap – Drivers 1.1
Project Description:
You have been asked to modify a database that tracks driver data and insurance details. You will create macros to automate data entry, broadcast an informational message to users, navigate through a form, and to open forms. You will also set an SQL statement to filter a report when it opens.
Steps to Perform:
Step
Instructions
Points Possible
1
Start Access. Open the downloaded Access file named
Exp19_Access_Ch10_Cap_Drivers. Grader has automatically added your last name to the beginning of the filename. Save the file to the location where you are storing your files.
0
2
Open the
Insurance table in Design view. Add a new field named
RenewTag in the last position of the table with the data type
Date/Time.
8
3
Create a data macro attached to the Before Change event. Use the
SetField Action to populate the RenewTag field in the table. The RenewTag date will always be
21 days before the TagExpiration.
10
4
Add the comment
Sets the renewal date for tag renewal. to the macro. Save and close the macro. Save the table.
8
5
Switch to Datasheet view of the Insurance table. Retype the TagExpiration in the first record (InsuranceID 10001), and then press the
DOWN ARROW. The macro will be triggered and automatically fill in the RenewTag field with a date 21 days before the TagExpiration. Close the table.
4
6
Open the Insurance form in Design view. Add the
RenewTag field to the form so that its label is aligned two dots below and with the left edge of the TagExpiration label. Switch to Form view, and then save and close the form.
8
7
Open the
Drivers report in Design view. Create an embedded macro in the On Open property box.
8
8
Create a message box to display
Please upload this report to Web site every Friday. (include the period).
8
9
Verify that
Yes is in the Beep box. Select
Information in the Type box. Type
Upload Report in the Title box. Save and close the macro. Save and then print preview the report. Ensure that the message displays, click
OK in the message box, and then close the report.
8
10
Open the
Drivers form in Design view. Add a command button aligned two dots below and with the left edge of the Restrictions label.
8
11
Set the button to navigate to the next record in the form and to display
Next Record as the button text. Set
cmdNextRecord as the button name. Save the form, view the form in Form view and test the button.
8
12
Set the Background Color of the button to
Light Blue (under Standard Colors) and italicize the button text. Save and close the form.
4
13
Open the
Drivers report in Design view. Type an SQL statement into the Record Source property of the report. The statement should select all fields (
*) for drivers in the Adult class. Save the report. View the report in Report view. Close the report.
8
14
Create a standalone macro that will open the Insurance form in Form view and Normal mode. Add an action to open the Drivers form in Form view and Normal mode.
8
15
Save the macro as
Open Forms and close the macro. Run the macro from the Navigation Pane to test it and then close all forms.
2
16
Close all database objects. Close the database and then exit Access. Submit the database as directed.
0
Total Points
100
Created On: 07/12/2019 1 Exp19_Access_Ch10_Cap – Drivers 1.1
Papa_Exp19_Access_Ch10_Cap_Drivers.accdb
AgentID
InsuranceCo
InsAgentFirstName
InsAgentLastName
InsAddress
InsCity
InsState
InsZIP
InsPhone
mSysRowId
AS2234
AS
William
Smith
7035556657
8bwlzL2j41sVsd36YtsTK2eAk9X1JWZ5a5OvFpW1vFY=-~5jZ1YIQydzrMaZdElrlygA==
AS8842
AS
Anna
Taylor
7035559998
8bwlzL2j41sVsd36YtsTK2eAk9X1JWZ5a5OvFpW1vFY=-~5jZ1YIQydzrMaZdElrlygA==
SF4485
SF
Terri
Jones
7035558876
8bwlzL2j41sVsd36YtsTK2eAk9X1JWZ5a5OvFpW1vFY=-~5jZ1YIQydzrMaZdElrlygA==
SF8567
SF
James
Johnson
7035551243
8bwlzL2j41sVsd36YtsTK2eAk9X1JWZ5a5OvFpW1vFY=-~5jZ1YIQydzrMaZdElrlygA==
SF8870
SF
Darin
Miller
7035550987
8bwlzL2j41sVsd36YtsTK2eAk9X1JWZ5a5OvFpW1vFY=-~5jZ1YIQydzrMaZdElrlygA==
DriverID
StateIssued
Photo
Class
FirstName
MiddleName
LastName
Street
Apartment
City
State
ZIP
HomePhone
Gender
Height
Weight
HairColor
EyeColor
OrganDonor
Restrictions
IssueDate
ExpirationDate
DateOfBirth
mSysRowId
123456789
LA
Adult
Pamela
R
Toliver
7015 Westchester Drive
Baton Rouge
LA
70810
2255552752
F
5′ 7″
135
Blonde
Brown
true
2
2008-07-05
1985-11-22
341012345
LA
Adult
Brian
Andrew
Andrews
132 Blue Street
Baton Rouge
LA
70810
2255553385
M
5′ 10″
200.00
Brown
Brown
true
4
1995-05-05
1975-05-15
341098765
LA
Adult
Victoria
May
Bowman
20 Spruce Avenue
Baton Rouge
LA
70810
2255559876
F
5′ 5″
130.00
Red
Hazel
false
3
1999-06-10
1969-06-20
341123456
LA
Minor
James
Hallard
Barnes
8 Shane Drive
Baton Rouge
LA
70810
2255556712
M
6′ 1″
205.00
Blonde
Brown
false
2
2016-01-04
1999-01-04
341234567
LA
Adult
Richard
Alvin
Bingham
90 Highland Place
Baton Rouge
LA
70810
2255559855
M
6′ 2″
250.00
Brown
Brown
true
1994-01-10
1954-01-19
341678901
LA
Adult
Kirk
Richard
Abelson
226 Valley Street
Baton Rouge
LA
70810
2255552234
M
5′ 10″
170.00
Black
Brown
true
5
2015-05-31
1995-05-30
341789012
LA
Adult
Eric
Thomas
Abelson
226 Valley Street
Baton Rouge
LA
70810
2255555542
M
6′ 2″
225.00
Blonde
Blue
true
2
1990-06-20
1970-06-27
341876543
LA
Minor
Bruce
David
Callander
2 Kron Lane
Baton Rouge
LA
70810
2255557875
M
6′ 1″
195.00
Black
Blue
false
2
2016-08-07
1999-08-07
341890123
LA
Adult
Lawrence
Allen
Alexander
198 Broad Street
#1001
Baton Rouge
LA
70810
2255552530
M
6′ 1″
270.00
Gray
Blue
true
2002-02-01
1952-02-01
341901234
LA
Special
Mark
Wayne
Anderson
1459 River Drive
Baton Rouge
LA
70810
2255553964
M
5′ 8″
160.00
Brown
Brown
false
1
1998-12-02
1948-12-22
341987654
LA
Special
Ibrahim
Isaac
Brown
177 Forest Avenue
Baton Rouge
LA
70810
2255551232
M
5′ 10″
200.00
Gray
Brown
true
2
1995-08-10
1935-08-13
342012345
LA
Minor
Andrew
Edward
JAMES
243 Oak
Baton Rouge
LA
70817
2255555833
M
5′ 10″
180
Brown
Brown
true
2016-06-30
1999-05-30
342098765
LA
Minor
Andrea
Marie
LARRABEE
823 Shyrock Drive
#1540
Baton Rouge
LA
70817
2255556789
F
5′ 6″
150.00
Brown
Hazel
false
2016-06-02
1999-04-01
342123456
LA
Minor
Harold
B
STAUSS
One Front Royal
Baton Rouge
LA
70817
2255552176
M
6′ 1″
185
Blonde
Brown
false
2
2016-08-15
1999-03-15
342234567
LA
Minor
David
N
ANDERSON
805 North Allen
Baton Rouge
LA
70817
2255555589
M
6′ 4″
305
Brown
Brown
true
2016-06-04
1999-01-20
342678901
LA
Minor
Kerry
Eldon
MITCHELL
622 Valley View
Baton Rouge
LA
70817
2255554322
M
5′ 10″
170.00
Black
Brown
true
2016-06-01
1999-05-03
342789012
LA
Minor
John
Eric
LANDERS
1032 North Jackson
#301
Baton Rouge
LA
70817
2255552455
M
6′ 2″
225.00
Blonde
Blue
true
2016-06-02
2000-04-18
InsuranceID
DriverID
InsuranceAgentID
AutoType
AutoYear
TagID
TagExpiration
10001
341012345
SF4485
Buick
2009
4321R
2021-08-01
10002
341234567
AS8842
Nissan
2006
4457D
2021-02-01
10003
341789012
SF8567
Dodge
2003
54687
2021-07-01
10004
341123456
AS8842
Volvo
2010
B3847
2021-02-01
10005
341876543
SF8870
Cadillac
2006
CADME
2021-08-16
10006
342234567
AS2234
Mercedes
2007
MYCAR
2021-06-30
10007
341098765
SF8567
GMC
2008
TR876
2021-10-01
10008
341789012
SF8567
Chrysler
2005
TR887
10009
123456789
AS2234
Toyota
2004
X1234
ID
mSysRowId
1
VhiYvKKNisAV2VYyguGDF9NaQG/FxJ8ch/Z755G753w=-~s3WKqP+w/9gYTEpxEhZ1Eg==
SELECT [Test Drivers].*
FROM [Test Drivers];
PARAMETERS __DriverID Value;
SELECT DISTINCTROW *
FROM Insurance AS Drivers
WHERE ([__DriverID] = DriverID);
SELECT [Test Drivers].*
FROM [Test Drivers];
SELECT [Test Drivers].*
FROM [Test Drivers];
SELECT DISTINCTROW *
FROM Drivers;
SELECT DISTINCTROW *
FROM Insurance;
SELECT DISTINCTROW *
FROM Drivers;
SELECT DISTINCTROW *
FROM Insurance;
SELECT DISTINCTROW *
FROM Insurance;