Yearly calendar for attendance 11

[oxicottin]

Hello, I have been using excel for employee attendance and im sick of tabs ect. I have been looking for a MS access database that shows 12 months on a form and on the form your able to select a day and enter a attendence entry ect. Does anyone know of anything free out there Or a sample DB I can get started on? calendar stuff is quite confusing so I want to find a sample to start from.

Thanks!

Thanks,
SoggyCashew.....

 

[SkipVought]

hi,

I'd just say in passing that no one is going to have in their hip pocket an application that suites your needs as you envision it.

Furthermore, when you say, "im sick of tabs ect." that leads me to think that your workbook design is much less than optimum, as I would expect that "sick of tabs" means that you may have tabs for each employee and maybe tabs for each week which means GOBS and GOBS of tabs.

I might envision a workbook with 2 or 3 tabs for a timesheet application. Tabs equate to tables,and tab/table structure is de-normalized, which is what I suspect. So translating Excel to Access would probably cause you a sickness of a different sort, without a significant redesign of your structure & process.

That's my impression from what you have stated and from what I know.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MajP]

You will have to describe this in detail of what you want it to look like and what features it has. I have built a lot of calendar/booking applications. But like Skip said, they are all different. Real estate becomes the big issue. If you display a year on a form then you can only display very basic information per day. So in this one you have a year calendar and only able to display when an order goes out to the shipper in the combo.

You can click on a day and add information for that day. If you wanted more information you could put each month on a tab. The trick for all of these is to enter information into a normalized format and display in a non normal format.

 

[oxicottin]

Majp, my excel workbook almost looks exactly like your Access database can you upload an example of that database and I can work off of that. basicly I need a database that shows the year and each day or square I would be able to click in and add an attendance code like vacation and some hours taken and like in your example the box would turn green showing it is a vacation etc.thanks!

Thanks,
SoggyCashew.....

 

[MajP]

Take a look at thread 702-1705763. Bottom line there are a few ways demonstrated to do this.
1) Build a table of dates and use a crosstab query
2) Build an unbound form
3) Build a bound form and a "grid" table

There are pros and cons for all. I went with an unbound form, but this can be extremely hard to build by hand. That is a whole lot of textboxes and labels. So i wrote some code to add, move, format, and name the controls. Once built, this is the solution that provides the most flexibility.

 

[MajP]

thread702-1705763

 

[oxicottin]

Thank you for pointing me in the right direction much appreciated! This is a great starting point, I googled and googled and couldn't find anything. Thanks again!

Thanks,
SoggyCashew.....

 

[MajP]

In that thread is a link to my solution.

 

[oxicottin]

Majp, I found your upload in the thread but the file on 4Shared isn't there...... Any luck of getting a new link?

Thanks,
SoggyCashew.....

 

[oxicottin]

Majp, I followed your instructions and got an example working to a point. The problems im haveing is in my attached example if I click on a date the year always says 1905. The second problem im haveing is the FillTextBoxes Sub SQL. Simply put I cant get it to work.... Third and last is I want to have an input popup for the text boxes so how would I get the info from the popup to the table? Thanks!

SKYDRIVE ATTACHMANT LINK

Thanks,
SoggyCashew.....

 

[MajP]

Link to code

http://www.4shared.com/file/_M80HSfu/MajPYearCalendar_Ver2.html

 

[MajP]

For the 1905 issue. I was using a date time picker which returns a date, then by taking Year() that would return an int. You are using a combobox which returns a string. If you use the function Year(somestring) you will return 1905. In your case you probably nead Cint(cboYear.value)

 

[MajP]

add the employeeID to your query and then use that. your combo is already bound to it.

Public Sub FillTextBoxes(frm As Access.Form, EmpID As Long, theYear As Integer)
  Dim ctl As Access.TextBox
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim strMonth As String
  Dim intMonth As Integer
  Dim intDay As Integer
  Dim AbsenceDate As Date
  Dim FirstDayOfMonth As Date
  Dim intOffSet As Integer
  Dim AbsenceCode As String
  

  strSql = "Select * from qry_YearCalendar where EmployeeID = " & EmpID
  strSql = strSql & " AND year(AbsenceDate) = " & theYear
 'Debug.Print strSql

 

[MajP]

The click event is already there. The code show how to get the date, so all you need is the empID. I pop up a messagebox, but you have to build the form.

So pop up a form for that person on that date. Probably need to pass the date in openargs so you can set the date for new records. The easiest solution is when the popup closes you clear and repaint the entire form, instead of trying to just update the selected date.

Public Function gridClick()
  'This just demoes a single function that fires when any of the grid text boxes are clicked
  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 empID As Long
  
  Set ctl = Screen.ActiveControl
  Set frm = ctl.Parent
  strMonth = Replace(Split(ctl.Tag, ";")(0), "txt", "")
  intCol = CInt(Split(ctl.Tag, ";")(1))
  intYear = CInt(frm.cboYear.Value)
  intMonth = getIntMonthFromString(strMonth)
  intDay = intCol - getOffset(intYear, intMonth, vbSaturday)
  selectedDate = DateSerial(intYear, intMonth, intDay)
  empID = Nz(frm.cboEmployee, 0)
  'Since you know the date you could now open a form to
  'add, edit, or delete a value for that date and that empID
  MsgBox selectedDate & " EmpID" & empID
End Function

 

[oxicottin]

Sorry MajP, Im learning so im getting stuck alot. I created a popup data entry form (frm_CalendarInputBox) and fixed the sugested changes you replyed but am still having trouble.

First, I notice the days are incorrect for the year AND when I change the year it stays the same.

Second, I am getting an 3061 Too few parameters. Expected 1. when I select an employee and I made the changes to the (Public Sub FillTextBoxes) SQL as instructed.

Third, I added some VBA to (Public Function gridClick) to open the input popup and show the date and employees name but how to I add the entered data to the correct date and empID and refresh calendar and show absenceCodein calendar.

Thanks!

SKYDRIVE ATTACHMANT LINK 2



Thanks,
SoggyCashew.....

 

[MajP]

http://www.4shared.com/office/Fdq2v5xw/MajP_YearCalendarUpdate.html

Here is a working demo. There is probably a lot of ways to pop the form open. I used openargs and passed the date and employeeid. If the record exists then move to that record, if not set the default values.

 

[oxicottin]

Majp, this is exactly what I was looking to do thanks a million. I don't understand the code below could you explain or link me to what exactly it does? Thanks!

If Me.OpenArgs & "" <> "" Then
    selectedDate = Split(Me.OpenArgs, ";")(0)
    empID = Split(Me.OpenArgs, ";")(1)

Thanks!

Thanks,
SoggyCashew.....

 

[PHV]

When in the VBE put the cursor inside the Split word in the code and press the F1 key.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

That is a lot of shorthand. I chained together a few functions.

If Me.OpenArgs & "" <> "" Then
    selectedDate = Split(Me.OpenArgs, ";")(0)
    empID = Split(Me.OpenArgs, ";")(1)

you could spell it out a little more using some variables.

'Through OpenArgs I am passing in a string of the form "12/05/2014;16"
  dim datePassed as string
  dim empID as string
  dim aArguments() as string
  'check to make sure something got passed in by openargs in the form date;employeeID
  If Me.OpenArgs & "" <> "" Then
    'Split takes a string and creates an array based on the delimeter
    aArguments = split(Me.openArgs,";")
    'so aAruments(0) is the first part of the split, the date
    'aArguments(1) is the second part
     selectedDate = aArguments(0)
     empID = aArguments(1)

 

[oxicottin]

Majp, I have been messing with your example and Im trying to find a way to set the Date label (FillMonthLabels) to visible = false when there isnt a date. I tried the code below:

If intDay < 0 And intDay <= DaysInMonth Then 'added
         ctl.Visible = false
         End If 'added

But it did nothing.... Im I looking in the correct place? Thanks!

Public Sub FillMonthLabels(frm As Access.Form, theYear As Integer)
  Dim ctl As Access.Label
  Dim i As Integer
  Dim amonths() As Variant
  Dim theMonth As Variant
  Dim FirstDayOfMonth As Date   'First of month
  Dim DaysInMonth As Integer    'Days in month
  Dim intOffSet As Integer      'Offset to first label for month.
  Dim intDay As Integer         'Day under consideration.
  Dim monthCounter As Integer
  Const ctlBackColor = -2147483616 'Used for Holiday shading/Unshading

  amonths = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")
  For monthCounter = 1 To 12
    FirstDayOfMonth = getFirstOfMonth(theYear, monthCounter)
    DaysInMonth = getDaysInMonth(FirstDayOfMonth)   'Days in month.
    intOffSet = getOffset(theYear, monthCounter, vbSaturday) 'Offset to first label for month.
     For i = 1 To 37
       Set ctl = frm.Controls("lbl" & amonths(monthCounter - 1) & i)
       ctl.Caption = ""
       ctl.BackColor = ctlBackColor  'reset the backcolor
       intDay = i - intOffSet        'Transforms label number to day in month
       If intDay > 0 And intDay <= DaysInMonth Then
         ctl.Caption = intDay
         
         If intDay < 0 And intDay <= DaysInMonth Then 'added
         ctl.Visible = false
         End If 'added
         
         If isHoliday(FirstDayOfMonth + (intDay - 1)) Then ctl.BackColor = 16776960 'Color holiday backcolor Blue
       End If
     Next i
  Next monthCounter
End Sub

Thanks,
SoggyCashew.....