Option Compare Database
Option Explicit
Public Function gridClick()
'This a function that fires when any of the grid text boxes are clicked on the Frm_CalendarYear
Dim ctl As Access.Control
Dim strMonth As String
Dim intCol As String
Dim intMonth As Integer
Dim intDay As Integer
Dim frm As Access.Form
Dim intYear As Integer
Dim selectedDate As Date
Dim stLinkCriteria As String
Dim StDocName As String
Set ctl = Screen.ActiveControl
Set frm = ctl.Parent
strMonth = Replace(Split(ctl.Tag, ";")(0), "txt", "") 'get the month from the text box
intCol = CInt(Split(ctl.Tag, ";")(1))
intYear = CInt(frm.CmbYear.Value) 'get the year from the combo box
intMonth = getIntMonthFromString(strMonth)
intDay = intCol - getOffset(intYear, intMonth, vbSaturday)
selectedDate = DateSerial(intYear, intMonth, intDay) 'make the date
'selectedDate = Format(selectedDate, "dd/mm/yyyy")
'Since you know the date open the form to
'view the TSR/RMA
If frm.CmbView = 1 Then 'TSR
stLinkCriteria = "[DateOpened] = #" & selectedDate & "#"
StDocName = "Frm_Tickets"
'MsgBox stLinkCriteria ' just checking
ElseIf frm.CmbView = 2 Then 'RMA
stLinkCriteria = "[DateRaised] = #" & selectedDate & "#"
StDocName = "Frm_Repair_Main"
'MsgBox stLinkCriteria
End If
DoCmd.OpenForm StDocName, acNormal, , stLinkCriteria
End Function
Public Function getOffset(intYear As Integer, intMonth As Integer, Optional DayOfWeekStartDate As Long = vbSunday) As Integer
'If your calendar starts on Sunday and the first day of the month is on a Monday
'Then everything is shifted one day so label 2 is day one
'If the first day was Saturday then everything shifts 6 days. So label seven shows 1
Dim FirstOfMonth As Date
FirstOfMonth = getFirstOfMonth(intYear, intMonth)
getOffset = Weekday(FirstOfMonth, DayOfWeekStartDate) - 1
End Function
Public Function getFirstOfMonth(intYear As Integer, intMonth As Integer) As Date
getFirstOfMonth = DateSerial(intYear, intMonth, 1)
End Function
Public Function getDaysInMonth(FirstDayOfMonth As Date) As Integer
getDaysInMonth = Day(DateAdd("m", 1, FirstDayOfMonth) - 1) 'Days in month.
End Function
Public Function getIntMonthFromString(strMonth As String) As Integer
'Assume Jan, Feb..Dec
getIntMonthFromString = Month("1/" & strMonth & "/2014")
End Function