How to ceate a Leave Tracker Form in MS Access

Sample Leave Tracker Form to Store PTO(Paid Time Off)  details of Employees in MS Access

Let us suppose we have a sample Employee Table as follows :

Now we need to store the PTO Details of the Employees in the below format :

For this , I have designed a Form as shown below :

-----------------------------------------------------------------------------------------------------------------

Code for the Above Form as follows :

-----------------------------------------------------------------------------------------------------------------

Option Compare Database

'Refreshing the Form on Load

Private Sub Form_Load()

Form.Refresh

End Sub

----------------------------------------------------------------------------

'Refreshing the EMP_ID combo values 

Private Sub cmbo_EMP_ID_GotFocus()

Me.cmbo_EMP_ID.Requery

End Sub

----------------------------------------------------------------------------

'Retriving EMP_ID ,EMP_Process values from the Employee Table[MyTeam] based on the selection of E_Name , and loading in to text boxes  EMP_ID ,EMP_Process of Form.

Private Sub Cmbo_EMP_NAME_AfterUpdate()

Dim SQL_Str As String

Dim DB As Object

Dim RS_ID As DAO.Recordset

Dim RS_Process As DAO.Recordset

'Retriving EMP_ID ,EMP_Process values from the Employee Table[MyTeam] based on the selection of E_Name.

E_NAME = Me.Cmbo_EMP_Name.Value

Str_ID = "SELECT EMP_ID FROM MyTeam WHERE MyTeam.EMP_NAME=" & """" & E_NAME & """" & ";"

Str_Process = "SELECT EMP_Process FROM MyTeam WHERE MyTeam.EMP_NAME=" & """" & E_NAME & """" & ";"

Set DB = CurrentDb

Set RS_ID = DB.OpenRecordset(Str_ID)

Set RS_Process = DB.OpenRecordset(Str_Process)

'Getting EMP_ID ,EMP_Process values from the Employee Table[MyTeam] to respective Texboxes of Form based on the selection of E_Name.

Me.txt_EMP_ID.Value = RS_ID.Fields("EMP_ID").Value

Me.txt_Process = RS_Process.Fields("EMP_Process").Value

End Sub

----------------------------------------------------------------------------

'At any moment while inputting into the form , if you want to Reset the form on clicking the Reset button , you can assign the below code to 'Reset' button.

Private Sub cmd_Reset_Click()

Me.Cmbo_EMP_Name = ""

Me.txt_EMP_ID = ""

Me.cmbo_LEAVE_TYPE = ""

Me.txt_Process = ""

Me.txt_COMMENTS = ""

Me.txt_PTO_START_DATE = ""

Me.txt_PTO_END_DATE = ""

End Sub

----------------------------------------------------------------------------

'Validating the Form Input on clicking the Submit button and then submitting/updating the data to the Output Table(PTO_Details) , assign the below Macrp code to 'Submit' button :

Private Sub cmd_Submit_Click()

DoCmd.SetWarnings False

Checking that mandatory input values should not be left blank

If Me.Cmbo_EMP_Name.Value = "" Then

MsgBox "Employee Name Field Cannot be Blank", vbCritical, "Please Enter Employee Name"

Exit Sub

ElseIf IsNull(Me.txt_EMP_ID.Value) Or Me.txt_EMP_ID.Value = "" Then

MsgBox "Employee ID Field Cannot be Blank", vbCritical, "Please Enter Employee ID"

Exit Sub

ElseIf Me.txt_Process = "" Then

MsgBox "Employee Process Field Cannot be Blank", vbCritical, "Please Enter Employee Process"

Exit Sub

ElseIf Me.cmbo_LEAVE_TYPE = "" Then

MsgBox "Leave Type Field Cannot be Blank", vbCritical, "Please Enter Leave Type"

Exit Sub

ElseIf Me.txt_PTO_START_DATE = "" Then

MsgBox "PTO Start Date Field Cannot be Blank", vbCritical, "Please Enter PTO Start Date"

Exit Sub

ElseIf Me.txt_PTO_END_DATE = "" Then

MsgBox "PTO End Date Field Cannot be Blank", vbCritical, "Please Enter PTO End Date"

Exit Sub

'Checking that PTO End Date should be greater that PTO Start Date

ElseIf Me.txt_PTO_END_DATE.Value < Me.txt_PTO_START_DATE.Value Then

MsgBox "The End Date should be Greater Than or Equal to Start Date", vbCritical, "Please Select Correct Date"

Me.txt_PTO_END_DATE = ""

Exit Sub

End If

'After data validation , submitting the Form Input to the Output Table ( PTO_Details):

Update_Query1 = "INSERT INTO PTO_Details ([Employee_ID],[Employee_Name],[Process],[Leave_Type],[PTO_Start_Date],[PTO_End_Date],[Comments])" _

& "Select[Forms]![PTO_Tracker]![txt_EMP_ID] AS [Employee_ID],[Forms]![PTO_Tracker]![cmbo_EMP_NAME] AS [Employee_Name]," _

& "[Forms]![PTO_Tracker]![txt_PROCESS] AS [Process],[Forms]![PTO_Tracker]![cmbo_LEAVE_TYPE] AS [Leave_Type]," _

& "[Forms]![PTO_Tracker]![txt_PTO_START_DATE] AS [PTO_Start_Date],[Forms]![PTO_Tracker]![txt_PTO_END_DATE] AS [PTO_End_Date]," _

& "[Forms]![PTO_Tracker]![txt_COMMENTS] AS [Comments]"

DoCmd.RunSQL Update_Query1

'Clearing the Form Input fields After submitting the Form Input to the Output Table ( PTO_Details):

Me.Cmbo_EMP_Name = ""

Me.txt_EMP_ID = ""

Me.cmbo_LEAVE_TYPE = ""

Me.txt_Process = ""

Me.txt_COMMENTS = ""

Me.txt_PTO_START_DATE = ""

Me.txt_PTO_END_DATE = ""

DoCmd.SetWarnings True

End Sub

----------------------------------------------------------------------------

'Exporting the Form Data when you click on the button 'Export PTO Data' , assign the below Macro code to 'Export PTO Data' button.

Private Sub Exp_PTO_Data_Click()

Dim OP_FileName As String

CurDB_Name = Left(CurrentProject.Name, Len(CurrentProject.Name) - 6)

OP_FileName = CurrentProject.Path & "\" & CurDB_Name & "-" & Format(Date, "DDMMMYYYY") & ".xlsx"

Dim Tbl As Object

Dim DB As Object

Set DB = CurrentDb

Tbl_Name = "PTO_Details"

'Exporting 'PTO_Details table to Excel in same location of Form/Database

DoCmd.TransferSpreadsheet acExport, , Tbl_Name, OP_FileName, True

MsgBox "PTO Data Exported SuccessFully", vokonly, "Export Success"

End Sub

-----------------------------------------------------------------------------------------------------------------

Key Notes :

-----------------------------------------------------------------------------------------------------------------

In the above from :

[Me.Cmbo_EMP_Name]  is a Combo Box

Me.txt_EMP_ID] is a Text Box

[Me.cmbo_LEAVE_TYPE] is a Combo Box

[Me.txt_Process] is a Text Box

[Me.txt_COMMENTS] is a Text Box

[Me.txt_PTO_START_DATE] is a Text Box with Date Picker

[Me.txt_PTO_END_DATE] is a Text Box with Date Picker

My Dear users please try understand that , we have to assign the right Macro code to the right Event/Property of the Form Control.

To understand the above Form structure completely we need to look into the Properties of the each Form Control.

But the above piece of Information will give you an Idea of Form Design and Validating and  Submitting Form Input as well as Exporting Form Data.

-----------------------------------------------------------------------------------------------------------------

Thanks,TAMATAM