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.....

 

[oxicottin]

Majp, after placing the subform on my frm_YearCalendar and linking it to the supervisors combo box like you said I used:

LoadGrid Forms!frm_YearCalendar.cboSupervisor

and then I open my frm_YearCalendar and I get an error '94' invalid use of null. and I see why but how do I fix it? Obviously when I open the form my supervisor combo box is going to be null and every time I change supervisors it will so how do I get it to give no information untill supervisor is selected?

Thanks,
SoggyCashew.....

 

[MajP]

You can use NZ function to change null to 0 or use an if then to do nothing if is null

 

[oxicottin]

Yes that worked I used;

LoadGrid Nz(Me.Parent!cboSupervisor, 0)

and I no longer recieve the error message now I have to toss in a requery for the weekView subform in the after update of the supervisors combo box.... One more question, is there a way we can use =gridClick() in the on click event of the text boxes to open the frm_CalendarInputBox like we did for the calendar? Just asking no bigie if not....


Thanks Again!

Thanks,
SoggyCashew.....

 

[MajP]

No but you can do the same technique and build a new function "=WeekViewClick()".
The function can be similar in how it calls the form. The only difference is how you get the absence date and employeeid. In fact it is much easier. Just copy the grid click. So copy the gridclick and paste it and rename it WeekViewClick. You need to get the date and employeeID differently. In gridclick that came from the main form.

I will give you a hint. In the columns of the form use the tag property. Tag them 0 through 6. Get the tag, and the FirstDayOfWeek, which I conveniently made a public property of the form. Now you should be able to determine the selected date (hint, add). Getting the employeeID will be tough. You can get the employee name by using the split function (with to get the name in last,First. You can split it again with (,) to get the last name and first name. Then you can use a dlookup to get the employeeID. Then the code remains the same.

I

 

[oxicottin]

Ok, I will try or atempt to try "=WeekViewClick()" after I get this requery working. I have tried everything and googled requerying diferent ways for the subform and it just wont requery. I did read and it does work on requerying the weekView subform and here is what the guy explained to the person he was helping.

There is a bug in access that will not requery your subform. If you try to just do something like subForm.SourceObject=subForm.sourceObject, it kicks out an error. However the compiler will allow you to set a SourceObject to a variable and then back. The string is the least memory intensive variable that will hold a sourceobject(a variant will do as well, but will use more memory).

It is a very strange solution to a very unusual problem. But with microsoft finding strange solutions to bugs, is a common place

Dim temp As String
temp = ""
temp = subFormWeekViewGrid.SourceObject
subFormWeekViewGrid.SourceObject = temp

Now why is this working and a requery wont? Ideas?

Thanks,
SoggyCashew.....

 

[MajP]

I think you are over thinking it. Let the subform do the work.

So this assumes that you are using the form as a subform and it is dependant on a supervisor. The supervisor id is not loaded when the parent opens. You have to pick it from a combo. So use the combos afterupdate.

Parent Form

Private Sub cboSupervisor_AfterUpdate()
  If IsNumeric(Me.cboSupervisor) Then
    Me.frmWeekView.Form.UpdateCalendar
  End If
End Sub

When the you change the supervisor, tell the subform to update. (Could also pass in a new date to have it move to that date using the SetStartingWeek property.)

now you could pass in the supervisor from main to sub, or have the subform get the supervisor from the main. Here is the latter.

Public Sub UpdateCalendar()
  SetLabels
  'User must add Code to get supervisor ID. This assumes never a supervisor 0
  LoadGrid nz(Me.Parent.cboSupervisor,0)
  Me.Requery
End Sub

Also that quote you posted looks like garbage to me. I know of no such bug (maybe in access 95).

 

[MajP]

That quote is stupid the more I read it. It is like saying "there is a bug with my screwdriver because I cannot use it to hammer a nail. But if you flip it over and use the handle it works." In other words just because they are doing something wrong does not make it a bug.

To test this I pulled out the requery from the sub form to require the main form to force the requery.

Private Sub cboSupervisor_AfterUpdate()
  If IsNumeric(Me.cboSupervisor) Then
    Me.frmWeekView.Form.UpdateCalendar
    Me.frmWeekView.Form.Requery
  End If
End Sub

No problem. So if done the proper way (using a hammer to drive a nail) it works no problem.

Changing the source object, is used to change the object (form) within a subform control. In certain cases it might trigger it to requery. The reason the temp variable works is that the source object goes out of scope and basically reloads.
so in my case
me.frmWeekView.sourceobject = me.frmweekview.sourceobject
does not work, but would not expect it to.
Do not forget
me.nameOfSubformControl.Form to return a reference to an object inside a subform control

 

[MajP]

I will let you finish the weekViewClick. But here is a helper function for that function. You have to get the employeeID.

I forgot the string will have all the tags. So splitting it up and getting the first and last name takes some manipulation. Replace and Split functions are your friend for this.

Look at the remarks to see how each line of code narrows it down
everything will be in this form

'<div><font color=white style='BACKGROUND-COLOR:#0000FF'>PD: Cat, Bob</font></div>

Public Function getEmpIDFromString(strVal As String) As String
  Dim aVals() As String
  Dim lName As String
  Dim fName As String
  Dim empID As Long
  Dim strWhere
  '<div><font color=white style='BACKGROUND-COLOR:#0000FF'>PD: Cat, Bob</font></div>
  strVal = Replace(strVal, "<", ">")
  '>div>>font color=white style='BACKGROUND-COLOR:#0000FF'>PD: Cat, Bob>/font>>/div>
  aVals = Split(strVal, ">")
  strVal = aVals(4)
  'PD: Cat, Bob
  aVals = Split(strVal, ":")
  strVal = aVals(1)
  'Cat,Bob
  aVals = Split(strVal, ",")
  lName = aVals(0)
  lName = "'" & Trim(lName) & "'"
  'Cat
  fName = aVals(1)
  fName = "'" & Trim(fName) & "'"
  'Bob
  strWhere = "EmpFName = " & fName & " AND EmpLName = " & lName
  'debug.print  strWhere
  empID = DLookup("EmployeeID", "tblUEmployees", strWhere)
  getEmpIDFromString = empID
End Function

 

[oxicottin]

Thanks Majp, your requery works perfect! As for the weekViewClick() I have no clue and its way out of my scope.... lol, I looked at the getEmpIDFromString and I have no clue what to even do with that.

Thanks,
Soggy....

Thanks,
SoggyCashew.....

 

[MajP]

C'mon, you should be able to do it. Look at the forest and not the trees. The gridClick function does 90% already.
So here is the gridClick function we have already.

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
    Dim strWhere As String    '============Added

    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

    strWhere = "AbsenceDate = #" & selectedDate & "# AND EmployeeID = " & empID    '============Added

    If DCount("*", "tbl_YearCalendar", strWhere) Then    '============Added
        'Date/Emp already exists open frm_CalendarInputBox
        DoCmd.OpenForm "frm_CalendarInputBox", , , , , acDialog, Format(selectedDate, "mm/dd/yyyy") & ";" & empID
    Else
        Const cstrPrompt As String = "Absence record does not exist for this date.  Create a new Absence?"
        If MsgBox(cstrPrompt, vbQuestion + vbYesNo) = vbYes Then
            'Yes I want to create an event
            DoCmd.OpenForm "frm_CalendarInputBox", , , , , acDialog, Format(selectedDate, "mm/dd/yyyy") & ";" & empID
            'refill the text grid when frm_CalendarInputBox closes
            FillTextBoxes Forms("frm_YearCalendar"), empID, intYear
            Forms!frm_YearCalendar!cmdTransparentButton.SetFocus    '============Added so cur isnt sitting on text box
        End If
        'Nope lets get outa here
        Forms!frm_YearCalendar!cmdTransparentButton.SetFocus    '============Added so cur isnt sitting on text box
        Exit Function
    End If
    FillTextBoxes Forms("frm_YearCalendar"), empID, intYear
End Function

So what does it do? It figures out the selected date from the controls and figures out the employeeID from the combo.
So the goal of this function is to get the selected date and employeeid so that you can pop open the form to edit it. That is the bottom line.

DoCmd.OpenForm "frm_CalendarInputBox", , , , , acDialog, Format(selectedDate, "mm/dd/yyyy") & ";" & empID

With the new form getting the selected date is easier than in the year view. You know the 1st day of the week because you calculated that already to load the form. The employee ID is hard to get because it is not stored anywhere on the form. But you have a string like with a bunch of tags but also something like "PD: Cat, Bob". So if we can get the first name and last name from that string we can find the employeeID.

So I showed you how to get the employeeID

dim ctrl as access.control
dim EmpID as long
dim strVal as string 'value in the textbox
dim selectedDate as date

'get the string value from the control that you double click
set ctrl = me.form.activecontrol
strVal = nz(ctrl.value,"")

So now you have the value from the textbox. It will either be empty and thus I converted it to an empty string using Nz, or it is going to look something like

'<div><font color=white style='BACKGROUND-COLOR:#0000FF'>PD: Cat, Bob</font></div>

If it is "" then you will want to prompt if they want to add a new record. I will let you code that. Else you want to find the employeeID

empID = getEmpIDFromString(strVal)

Although the code looks complicated all it does is take that string and chops it up until you end up with Bob and Cat.
Now the only thing you need is the date. I said to tag the controls from 0 to 6. And the first day of the week is known

selectedDate = me.FirstDayOfWeek + cint(ctrl.tag)

So now after clicking on the control you have the selected date and an employeeid. Or if it is an empty day you know that there is no record for that date and you can prompt them to add new.

So from the original grid click you had these variables

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
Dim strWhere As String '============Added

Now you only need

Dim ctl As Access.Control
Dim selectedDate As Date
Dim strVal as string
Dim empID As Long
Dim strWhere As String '============Added

See if you can put it all together.

 

[kjv1611]

MajP,

Just wanted to say thanks for sticking this one out, and going into such detail explaining the steps. Should help the person who needs it now, as well as folks later.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[oxicottin]

Majp, I have been experencing some unusual errors with access so I had t reinstall access but after the install Im still stuck with the same Run-Time error as I was before. I am getting a Run-time error ‘13’: Type mismatch when I select the month (December) and december only to add a absence. Debug takes me to;

Public Function getIntMonthFromString(strMonth As String) As Integer
'Assume Jan, Feb..Dec
  getIntMonthFromString = Month("1/" & strMonth & "/2013")
End Function

Then after that nothing works correctly I have to close and reopen. Another thing thats weird is if I dont click debug and click end then the window comes up again so it must come up twice then changing employees gives a Run-Time error '91' Object varable or With block varable not set.

Thoughts?

Thanks,
SoggyCashew.....

 

[SkipVought]

Public Function getIntMonthFromString(strMonth As String) As Integer
'Assume Jan, Feb..Dec
  getIntMonthFromString = Month([b]DateValue[/b]("1/" & strMonth & "/2013"))
End Function

 

[MajP]

With out looking at this, I cannot remember how the month is passed in, but check from where it is being passed.

Public Function getIntMonthFromString(strMonth As String) As Integer
'Assume Jan, Feb..Dec
  'my guess the string is corrupted, maybe with a space
  strMonth = trim(strMonth)
  debug.print strMonth
  'What is passed in?
  getIntMonthFromString = Month("1/" & strMonth & "/2013")
End Function

if that does not work just roll your own function

Public Function getIntMonthFromString(strMonth As String) As Integer
'Assume Jan, Feb..Dec
  select case strMonth
    case "Jan"
      getInMonthFromString = 1
    case "Feb"
      getIntMonthFromString = 2
    ....
    case "Dec"
      getIntMonthFromString = 12
    case else
       Msgbox "Your Passed " & strMonth
   end case
End Function

Bottom line is you are passing in Jan, Feb,...Dec and need to return 1,2...12

 

[MajP]

Then after that nothing works correctly I have to close and reopen. Another thing thats weird is if I dont click debug and click end then the window comes up again so it must come up twice then changing employees gives a Run-Time error '91' Object varable or With block varable not setAlso. I do not think there is a lot of error checking.

So start adding error checking to the application. This thing is pretty complicated for vba, lots of places to get errors.

 

[oxicottin]

Majp, I tried the strMonth = trim(strMonth)and the print came up with "December" and you wrote im wanting a numberic return like 1,2....12 corect? Wounder why something changed I never did anything to change it.


Thanks,
SoggyCashew.....

 

[MajP]

No, that is fine. You are passing in a month that is a string, like January, February.. My guess was that you where not passing in a good month as a string. The function takes that input concatenates it to make a date, then runs the month function on it which should return 12. So try this.

Public Function getIntMonthFromString(strMonth As String) As Integer
  dim strDate as string
  dim intMonth as integer
  strDate = "1/" & strMonth & "/2013"
  debug.print "strDate: " & strDate
  intMonth = Month(strDate)
  debug.print "intMonth: " & intMonth
  getIntMonthFromString = intMonth
End Function

So what do the debugs print? Trying to determine if the string is bad, or the function.

 

[oxicottin]

Majp, I ran the VBA and in the the immediate window:

strDate: 1/Decembember/2013

Thanks,
SoggyCashew.....

 

[MajP]

Is that a typo or is the month passed actually spelled wrong? If it is actually spelled wrong then that is the problem, and that would make sense. Find from where you pass the month. Is it from a combo, tag property, label? I do not remember. Make sure you spell it correctly.

 

[oxicottin]

Good catch.... I didn't even notice that! Nope that was a copy and Paste from the immediate window.

Thanks,
SoggyCashew.....