'1-f


Option Explicit

'Declare global variables
Public strObjectName As String
Public strShortName As String
Public intResponse As Integer
Public strSQL As String

'General procedure to close a form
Sub CloseForm(ObjectName, ShortName)

	intResponse = MsgBox("Do you want to close the" &ShortName & _
		"form?", vbYesNo + vbCritical, "Close Form?")

	If intResponse = vbYes Then
		DoCmd.Close acForm, ObjectName
	End If
End Sub

'General Procedure to close a report
Sub CloseReport(ObjectName, ShortName)

	intResponse = MsgBox("Do you want to close the" &ShortName & _
		"report?", vbYesNo + vbCritical, "Close Report?")

	If intResponse = vbYes Then
		DoCmd.Close acReport, ObjectName
	End If
End Sub	

'2-b

Sub cmdClose_Click()
	strObjectName = "Employee"
	strShortName = "Employee Records"
	CloseForm strObjectName, strShortName
End Sub

'2-e

Private Sub cmdClose_Click()
	strObjectName = "History"
	strShortName = "Contribution History"
	CloseForm strObjectName, strShortName
End Sub


'2-h

Private Sub cmdCloseReport_Click()
	strObjectName = "Retirement Summary"
	strShortName = "Retirement"
	CloseReport strObjectName, strShortName
End Sub


'3-b

Private Sub cmdHistoryForm_Click()
	'Display the form
	DoCmd.OpenForm("Employee"), , , , acFormEdit
End Sub

Private Sub cmdEmployeeForm_Click()
	'Display the form
	DoCmd.OpenForm("History"), , , , acReadOnly
End Sub

Private Sub cmdExit_Click()
	'Exit the application
	intResponse = MsgBox("Do you want to exit the Compensation" & _
		"application?", vbYesNo + vbCritical, "Exit Application?")

	If intResponse = vbYes Then
		Application.Quit acQuitPrompt
	End If
End Sub

'3-d

Private Sub cmdSummaryReport_Click()

'This procedure defines an SQL query as the record source
'for the "Retirement Summary" report; the SQL statement
'returns all records

'Assign the report name to the strObjectName variable
strObjectName = "Retirement Summary"

'Declare a variable to store the SQL statement, define the
'SQL string

strSQL = "SELECT Employee.LastName, Employee.FirstName, " & _
"Office.Region, Contribution.PayDate," & _
"Contribution.[401KEmployee], " & _
"Contribution.[401KMatch], Contribution.[401KTotal], " & _
"FROM (Office INNER JOIN Employee ON Office.[OfficeNumber] = " & _
"Employee.[OfficeLocation])" & _
"INNER JOIN Contribution ON Employee.[SSN] = Contribution.[SSN];"

'Open the Address List Report
DoCmd.OpenReport strObjectName, acViewReport
'Assign the SQL query as the record source for the report
Reports(strObjectName).RecordSource = strSQL

'clean up the public strSQL variable
strSQL = ""

End Sub


'3-f

Private Sub cmdContribByRegion_Click()

'Assign the report name to the strObjectName variable
strObjectName = "Retirement Summary"

strSQL = "SELECT Employee.LastName, Employee.FirstName, " & _
"Office.Region, Contribution.PayDate," & _
"Contribution.[401KEmployee], " & _
"Contribution.[401KMatch], Contribution.[401KTotal], " & _
"FROM (Office INNER JOIN Employee ON Office.[OfficeNumber] = " & _
"Employee.[OfficeLocation])" & _
"INNER JOIN Contribution ON Employee.[SSN] = Contribution.[SSN]" & _
"WHERE (((Office.Region) = [Enter Region Name]));"

'Open the Address List Report
DoCmd.OpenReport strObjectName, acViewReport
'Assign the SQL query as the record source for the report
Reports(strObjectName).RecordSource = strSQL

'clean up the public strSQL variable
strSQL = ""

End Sub