Attendance Database 1

[oxicottin]

Majp, in reference to thread702-1723375 that was closed I remember I asked how to highlight the current day and you gave me some code to do so and I never incorporated it into my database at the time. Im wanting to try it and I dug threw the thread and found the vba below. I cant seem to get it to work without some errors. I call it in the on load event and I get an err 2465 saying it cant find 'txtDec33' referred in my expression.

I debug and it takes me to:
Set GetTodaysTextBox = Forms("Frm_YearCalendar").Controls(txtBxName)

the Debug.Print txtBxName is "txtDec33" and Debug.Print offset is "5" it is picking the correct month and day everywhere. Thoughts?

Thanks...

Public Sub TestHilite()
  GetTodaysTextBox.BorderColor = vbYellow
End Sub
Public Function GetTodaysTextBox() As Access.TextBox
  Dim CurrentDay As Date
  Dim strMonth As String
  Dim offset As Integer
  Dim txtBxName As String
  CurrentDay = Date
  strMonth = Format(CurrentDay, "mmm")
  'Debug.Print strMonth
  offset = getOffset(Year(CurrentDay), Month(CurrentDay), vbSaturday)
  Debug.Print offset
  txtBxName = "txt" & strMonth & offset + Day(CurrentDay)
  Debug.Print txtBxName
  Set GetTodaysTextBox = Forms("Frm_YearCalendar").Controls(txtBxName)
End Function

Thanks,
SoggyCashew.....

UPDATE: I changed vbSaturday to vbSunday because my calendar starts on Sun - Sat and now the txtBxName is "txtDec32" which if you count the text boxes its todays day so that's correct. But I still get the same error just now with "txtDec32".

 

[MajP]

That code was written before implementing the 12 subforms. At that time there was a single form with 365 Controls. So there is no longer one control for every day of every month. There is only a single subform that is repeated 12 times. In order to determine which instance of the subform, each subform control is named as follows

SubFormJan, SubFormFeb,...SubFormDec

So
1. Figure out current month
2. The correct subform control is named ("SubForm"&"Dec")
3. To get the correct subform it would be Me.controls("SubformDec").form
4. Figure out the day and the offset for the month
5. Figure out the control name "txt"&(offset + day(currentDay))
6. get the correct control on the correct subforminstance Me.Controls("subFormDec").form.controls("txt32")

Public Function GetTodaysTextBox() As Access.TextBox
  Dim CurrentDay As Date
  Dim strMonth As String
  Dim offset As Integer
  Dim txtBxName As String
  Dim sFrm As Form
  CurrentDay = Date
  strMonth = Format(CurrentDay, "mmm")
  'Debug.Print strMonth
  offset = getOffset(Year(CurrentDay), Month(CurrentDay), vbSaturday)
  txtBxName = "txt" & offset + Day(CurrentDay)
  Debug.Print txtBxName
  Set sFrm = Me.Controls("subForm" & strMonth).Form
  Set GetTodaysTextBox = sFrm.Controls(txtBxName)
End Function

 

[MajP]

To complicate this you will only hilite today's date if the calendar is showing the current year. If you scroll to a different year there is nothing to hilite. You also have to remove the highlighting when you change years. Add the following procedure and call it from all locations after you call the fillallMonthLabels.

Private Sub HiliteDay()
   Dim todayBox As TextBox
   Set todayBox = GetTodaysTextBox
   If Me.cboYear.Value = Year(Date) Then
      todayBox.BorderColor = vbRed
      todayBox.BorderWidth = 3
    Else
      'return to original border style
      todayBox.BorderColor = 14270637
      todayBox.BorderWidth = 0
    End If
End Sub

 

[oxicottin]

Majp, that worked great.... Thank You!

Question, how hard would it be to convert this database to Visual Studio Express? I wanted to start messing around with it so I downloaded it last night and its similar to ms access. Would it be more beneficial to create the attendance software in express then in access? I wouldn't mind trying to learn how to do it... Thanks!

Thanks,
SoggyCashew.....

 

[MajP]

Tough question. Once you start building in VS, the access design capabilities will look extremely primitive. You will be able to do hard things in VS far easier than Access, but some simple Access things will be more challenging. However, you will have to learn ADO.net. This is how VS interfaces with databases. I went on line a bought a couple of used books for a few dollars and actually read them cover to cover. You could use your current backend tables and queries and build a VB.net front end. I would say it would be challenging initial climb, but once you got done you would be a pretty good developer. One good thing there is a lot of code on the web that you can download. For example I downloaded what looks and feels like the Outlook appointment calendar with all of its views and capabilities. I put it into an application running an access backend. So that would almost solve your problem. You could look at each employee and switch between day, week, month views. I do not think it can do a year view.

 

[oxicottin]

Majp, what books would be a good starting point to start reading. Also, in your example I added todayBox.SetFocus because the border was in the background and if I didn't give focus right off the bat you could barely see the border. Oh and once I get started im sure I will have question so what part of the forum would I post under for VS? Thanks....

Private Sub HiliteDay()
   Dim todayBox As TextBox
   Set todayBox = GetTodaysTextBox
   If Me.cboYear.Value = Year(Date) Then
      [highlight #8AE234]todayBox.SetFocus[/highlight]
      todayBox.BorderColor = vbRed
      todayBox.BorderWidth = 3
    Else
      'return to original border style
      todayBox.BorderColor = 14270637
      todayBox.BorderWidth = 0
    End If
End Sub

Public Function GetTodaysTextBox() As Access.TextBox
  Dim CurrentDay As Date
  Dim strMonth As String
  Dim offset As Integer
  Dim txtBxName As String
  Dim sFrm As Form
  CurrentDay = Date
  strMonth = Format(CurrentDay, "mmm")
  'Debug.Print strMonth
  offset = getOffset(Year(CurrentDay), Month(CurrentDay), vbSunday)
  txtBxName = "txt" & offset + Day(CurrentDay)
  'Debug.Print txtBxName
  Set sFrm = Me.Controls("subForm" & strMonth).Form
  Set GetTodaysTextBox = sFrm.Controls(txtBxName)
End Function

Thanks,
SoggyCashew.....

 

[MajP]

Programming Microsoft® ADO.NET 2.0 Core Reference (Developer Reference) (Paperback)
You can get it used for 1 cent and 4$ in shipping. I kid you not. Sure it is not the most current, and things will definately have changed. But the basic concepts are in there.

https://www.amazon.com/gp/offer-listing/073562206X/ref=dp_olp_used?ie=UTF8&condition=used

This is also good
Professional ADO.NET 3.5 with LINQ and the Entity Framework (Paperback) by Roger Jennings
Again get a used version for a few bucks

https://www.amazon.com/gp/offer-lis...p_used_olp_sr?ie=UTF8&condition=used&qid=&sr=

These books are just the ADO.net side. You probably want to get a couple books for building forms and generic VS usage. Again go to Amazon and get a few older used books. I am never going to spend $80 dollars for the most current version if I can get a few years older for a couple pennies.

If you want to do this I would strongly recommend only undertaking it for the academic exercise, you may never get finished. The learning curve is pretty steep and you may give up before completing. Be prepared for this to take a long time, and it will be very slow in the beginning. It is worth it if you are doing it to learn for future activities, but it will not be worth it if your focus is just on completing an application.

If you really want to do it, I will walk you through it and can provide a lot of code modules and examples. I would do it because by teaching you, I will be learning myself. I have built multiple vb.net calendar projects with an access back end for just this purpose of learning; however, I got a ton to learn. So if you want to do it just start a thread in the "Access Other Topics" entitled "Converting an Access application to .Net". The first post should just state that this will be a working educational thread converting the YearCalendar to a VB.net front end with an Access back end. The thread will probably end up being longer than the year calendar which was probably one of the longest threads ever on Tek/Tips.

 

[oxicottin]

The thread will probably end up being longer than the year calendar which was probably one of the longest threads ever on Tek/Tips.

LOL, I think a lot of people used/use that thread for reference. I know I have gone back to it. I'm going to watch some YouTube videos and maybe get one of those books and mess around with it before then and see if its what I'm wanting to do. Thanks again and I really appreciate the help!

Thanks,
SoggyCashew.....

 

[oxicottin]

MaJP, Im thinking im going to stick with MS access since I know a little about it and I think I just want to learn VS for this project and maybe one more so it wont be beneficial, but since I did download it I am going to mess with it!

That being said I have something else I want to alter on this Attendance Database which I never got correct. Right now I have it working sum what but I dont have it working the way I want. I have a subform that calculates each employees vacation days they have accumulated by how long they have worked for us as well as their personal days and sick days using a query but I would like to use a function because there are a lot of instances or what ifs like.

if employee is hired Jan 1st - April 30th = 3 personal days
if employee is hired May 1st - Aug 31st = 2 personal days
if employee is hired September 1st - Nov 30th 1 personal day
if employee is hired during Dec 1st - Dec 31st = 0 personal days
 
1 year Service = 1 week vacation
2 year Service = 2 week vacation
8 year Service = 3 week vacation
15 year Service = 4 week vacation
25 year Service = 5 week vacation
 
Eligible to buy a week of vacation after 4 years service.

Here is what Im using to get the vacation in weeks, YearsOfService is a text box on form that is the employees start date.

TotalVacWeeks: IIf([YearsOfService]<=0,0,IIf([YearsOfService]<=1,1,IIf([YearsOfService]<=7,2,IIf([YearsOfService]<=14,3,IIf([YearsOfService]<=24,4,IIf([YearsOfService]>=25,5))))))+IIf([BoughtVac]=True,1,0)

Thanks,
SoggyCashew.....

 

[MajP]

You can make an award table, so if your company is like mine and decide to change the award then you do not have to change code, just the table. The key with things like this is to have a field for the lower and upper bound

[tt]
minYears maxYears Award
1	1	1
2	7	2
8	14	3
15	24	4
25	100	5
[/tt]

Then you can use a dlookup or a query or a combination. You could make a query like

SELECT 
 tblYearsService.yearsService, 
 tblVacationAward.Award
FROM 
 tblYearsService, 
 tblVacationAward
WHERE 
 tblYearsService.yearsService)>=[MinYears] And (tblYearsService.yearsService)<=[MaxYears]

If you then included an employeeid in the above query you would have a query with years of service for each employee. You then on your form could use a dlookup by employeeID. I assume you do not store years of service but a start date. You could still build a query to get the vacationaward using the above table. Instead of yearsService it may look like
dateDiff("Y",[HireDate],Date())>=[MinYears] ...

 

[MajP]

BTW if you included the field BoughtVac in the above query you could get totaVacation something like

SELECT 
 EmployeeID,
 tblYearsService.yearsService , 
 tblVacationAward.Award + [BoughtVac] * -1 as TotalVacation
FROM 
 tblYearsService, 
 tblVacationAward
WHERE 
 tblYearsService.yearsService)>=[MinYears] And (tblYearsService.yearsService)<=[MaxYears]

 

[oxicottin]

MaJP, Im not quite getting how this works. its basically what I have now, but without the award table. How would I get it to work for personal days because when you start it goes by hire date you get so many days then after a year you always get 3 and same goes for sick days. I cut down a Attendance DB thats basic. you can see how I have to add each year if an employee buys a vacation I have to open the frm_UpdateEmpInfo form using the admin button on frm_YearCalendar and entering in the password "toor" then the form opens. I removed my subform that had the vaction,personal and sick days that was in the footer so I can start over.

NOTE: Shift open the DB or it will open full screen.



Thanks,
SoggyCashew.....

 

[MajP]

I do not have access at home so this is off the top of my head, untested. I interpret your rule. If they are hired in the current year then the personal days equals 4 - quarter they were hired.
Q1 = 4-1 = 3days
Q2 = 4-2 = 2 days
Q3 = 4-3 = 1 day...

If the Year of hireddate <> this year then they get three (assuming you are not putting future hiredates in the system)

Public Function GetPersonalDays(HiredData as variant) as Integer
  dim qtr as integer
  if isdate(hiredDate) then
    if Year(hiredDate) = Year(date) then
      qtr = (Month(hiredDate) + 2) \ 3
      GetPersonalDays = 4-qtr
    else
      GetPersonalDays = 3  
    end if
   end if
end function

You can use that function in a query like

Select
employeeID,
GetPersonalDays([hiredDate]) as PersonalDays,
...
From
someTable

Then you can always do a dlookup on that query and look up personal days by employeeID. BTW I always make my parameters variants if I am calling the function from a query. There is always the chance of try to pass in a null value from a table and a long query would then lock up. The Isdate function is checking to ensure that what was passed in is a valid date.

 

[oxicottin]

Thanks MaJP I will give it a try...

Thanks,
SoggyCashew.....

 

[oxicottin]

MajP, I found an error can you take a look if you get time? If you open the database I posted just above 2 posts back and select the Supervisor (Chad Zandell) and let’s say for the Employee (Chad Zandell) again then for the year it should be automatically be 2017 if not make it that, that way there is no entries. Now this is when I have the error, click the button to print the calendar and you will be greeted with a Run-Time error ‘2455’ you entered an expression that has invalid reference to the property Form/Report. If I click Debug it takes me to the form I named (rpt_YearView). Now this only happens if there are no absences entered yet for this employee for the year chosen after one entry then the print will bring up a print dialog asking me where I want to print ect. with no errors.

Private Sub Form_Load()
    Set sFrmJan = Me.childCalendarMonth1.Form

Thanks,
SoggyCashew.....

 

[MajP]

The problems is you bound the mainform which you do not really need. You do need to pass to the form the employee ID and the Year. If your query returns no records on the mainform the subforms cannot load and that is your current problem. Instead of binding the main form pass in the needed information using openArgs.
OpenArgs is a single string that you can pass to a dialog form. If you want to pass in more than one piece of information pass it as a delimited string and then when the new form opens it can split out the pieces.
“1234;2017;Smith”
The split command allows you to split that string into an array
myArray = split(“1234;2017;Smith”)
myArray(0) therefore equals “1234”
So to simplify your design make your pop up form unbound. What is nice about this is that now you can test the form regardless if the year view is open.

Private Sub Form_Load()
    'Pass in the employeeID and year using openargs in the format  1234;2017
    'DoCmd.OpenForm "rpt_YearView", acNormal, , , , acHidden, Me.cboEmployee & ";" & Me.cboYear
    
    Dim empID As Long
    Dim theYear As Integer
    Dim empName As String
    If Not Trim(Me.OpenArgs & " ") = "" Then
      empID = CLng(Split(Me.OpenArgs, ";")(0))
      theYear = CLng(Split(Me.OpenArgs, ";")(1))
      empName = Nz(DLookup("EmpLName", "tblUEmployees", "EmployeeID = " & empID))
    Else
      theYear = Year(Now)
    End If
…. Rest of your code

Once thing I really do not like is queries that have references to forms and controls on other forms. I would strongly avoid this. It creates a spider web and makes your design not modular. Use openargs where you can, to help.

 

[oxicottin]

Majp, Sorry for the late response I tried several times gave up and tried again and figured I would reply with my results, plus work got me really busy. Here is what I did, on the (rpt_YearView) I removed its Record Source then in the forms "On Load" event I added your code above what was there. To test it I opened the (frm_YearCalendar) and selected a supervisor and an employee then the year 2018 then pressed the button "Print Absence Calendar For 2018". I keep getting a error Compile error: Method or data member not found and it takes me to the....

[highlight #FCE94F] Private Sub Form_Load()[/highlight]

FillAllMonthLabels (Me[highlight #729FCF].Year[/highlight])

Highlights the private in yellow and the year in blue...

Thanks,
SoggyCashew.....

 

[MajP]

That was probably my fault for not explaining it.
We made the form unbound. So there is no longer a field associated with the form called "Year". Since there is no longer a field "Year", "me.year" returns an error. We got around this by passing to the form the empID and the year in openargs. We get theYear in this line of code:
theYear = CLng(Split(Me.OpenArgs, ";")(1))

Your correct code would then be

FillAllMonthLabels (theYear)


BTW "Year" is a BAD name for a field. Any name that can be confused with the name of an object, method, function, property, or event can cause problems. "Year" is the name of a vba function (example year(Now). Most things related to a date have a function name: "now", "weekday", "date", "Year", "Month", "hour", "minute" etc.

 

[oxicottin]

Majp, I have another question about the attendance database. Im asked to supply the number of days an employee worked Mon - Friday and Not including and days he had a attendance or any days from our Holidays in tbl_Holidays for the "YEAR" not "BETWEEN" two dates. So I wanted to use it for a query and be able to have the query use EmployeeID from my main form using a criteria like

[forms]![frm_YearCalendar]![cboEmployee]

And maybe using the year I have already selected on the main form... Is there a Module On Tek-Tips that's something like what I need for the holidays and I can use it in my query? If so and there is what would I use to call the function... There is a copy of the basic database a few posts up....

Thanks,

Thanks,
SoggyCashew.....