Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rating: Thread Rating: 22 votes, 4.91 average. Display Modes
Old 09-05-2019, 05:29 AM   #106
Mr. Southern
Newly Registered User
 
Join Date: Aug 2019
Location: United States
Posts: 71
Thanks: 11
Thanked 0 Times in 0 Posts
Mr. Southern is on a distinguished road
Re: Yearly Attendance Tracker/Calendar

Quote:
Originally Posted by MajP View Post
This is the code that loads the table for the week view
Code:
Private Sub LoadGrid(SupervisorID As Long)
    Dim RsRead As DAO.Recordset
    Dim RSGrid As DAO.Recordset
    Dim strSql As String
    Dim i As Integer
    Dim FieldDate As Date
    Dim InputText As String
    CurrentDb.Execute "Delete * from tbl_WeekViewGrid"
    Set RSGrid = CurrentDb.OpenRecordset("tbl_WeekViewGrid", dbOpenDynaset)
    For i = 0 To 6
        FieldDate = Me.FirstDayOfWeek
        FieldDate = FieldDate + i
        strSql = "Select * from qry_FillTextBoxes where absenceDate = " & SQLDate(FieldDate) & " AND " & SQLDate(Me.FirstDayOfWeek + 6) & " AND SupervisorID = " & SupervisorID
        Set RsRead = CurrentDb.OpenRecordset(strSql)
        If Not (RSGrid.EOF And RSGrid.BOF) Then RSGrid.MoveFirst
        Do While Not RsRead.EOF
            InputText = RsRead!AbsenceCode & ": " & RsRead!EmployeeName
            InputText = WrapText(InputText, RsRead!AbsenceColorTag, 25) 'Num of spaces in text box
            If RSGrid.EOF Then
                RSGrid.AddNew
                RSGrid.Fields("Day" & i + 1) = InputText
                RSGrid.Update
            Else
                RSGrid.Edit
                RSGrid.Fields("Day" & i + 1) = InputText
                RSGrid.Update
            End If
            If Not RSGrid.EOF Then RSGrid.MoveNext
            RsRead.MoveNext
        Loop
    Next i
    RSGrid.Close
End Sub
You would have to add an additional AND to this line to include only certain choices
Code:
 strSql = "Select * from qry_FillTextBoxes where absenceDate = " & SQLDate(FieldDate) & " AND " & SQLDate(Me.FirstDayOfWeek + 6) & " AND SupervisorID = " & SupervisorID
MajP,

I think im missing something because I am getting a syntax error.

Code:strSql = "Select * from qry_FillTextBoxes where absenceDate = " & SQLDate(FieldDate) & " AND " & SQLDate(Me.FirstDayOfWeek + 6) & " AND SupervisorID = " & SupervisorID & "AND AbsenceCode =("PTO","FMLA", "VAC")"

Mr. Southern is offline   Reply With Quote
Old 09-05-2019, 05:54 AM   #107
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,892
Thanks: 38
Thanked 574 Times in 539 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Yearly Attendance Tracker/Calendar

Code:
AbsenceCode =("PTO","FMLA", "VAC")
not correct sql. Try
Code:
AbsenceCode IN ('PTO','FMLA', 'VAC')
MajP is online now   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
Mr. Southern (09-05-2019)
Old 09-05-2019, 06:51 AM   #108
Mr. Southern
Newly Registered User
 
Join Date: Aug 2019
Location: United States
Posts: 71
Thanks: 11
Thanked 0 Times in 0 Posts
Mr. Southern is on a distinguished road
Re: Yearly Attendance Tracker/Calendar

Quote:
Originally Posted by MajP View Post
Code:
AbsenceCode =("PTO","FMLA", "VAC")
not correct sql. Try
Code:
AbsenceCode IN ('PTO','FMLA', 'VAC')
Seems to be working!

Thanks!

Mr. Southern is offline   Reply With Quote
Old 10-01-2019, 04:38 PM   #109
oxicottin
Newbie
 
oxicottin's Avatar
 
Join Date: Jun 2007
Location: West Virginia
Posts: 495
Thanks: 16
Thanked 43 Times in 11 Posts
oxicottin is on a distinguished road
Send a message via AIM to oxicottin
Re: Yearly Attendance Tracker/Calendar

Quote:
Originally Posted by Mr. Southern View Post
Hi oxicottin,

I am trying to add one thing to the yearly attendance tracker. I am trying to add a total section in the Absences_For_Year Report. For example, when the report displays each type of absence Description/Code it will also show the total next to it. What would be the best way to do that?
Is this what you wanted? I made the changes to rpt_AbsencesForYear

IMAGE: https://imgur.com/yHqmIh1
Attached Files
File Type: zip V2-Mr. Southern_Attendance DB v6.xx.zip (342.8 KB, 24 views)
__________________
If guns cause crimes, spoons make you fat....
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
oxicottin is offline   Reply With Quote
Old 10-01-2019, 05:39 PM   #110
Mr. Southern
Newly Registered User
 
Join Date: Aug 2019
Location: United States
Posts: 71
Thanks: 11
Thanked 0 Times in 0 Posts
Mr. Southern is on a distinguished road
Re: Yearly Attendance Tracker/Calendar

Quote:
Originally Posted by oxicottin View Post
Is this what you wanted? I made the changes to rpt_AbsencesForYear

IMAGE: https://imgur.com/yHqmIh1
Looks good, thanks!
Mr. Southern is offline   Reply With Quote
Old 10-02-2019, 06:58 AM   #111
Mr. Southern
Newly Registered User
 
Join Date: Aug 2019
Location: United States
Posts: 71
Thanks: 11
Thanked 0 Times in 0 Posts
Mr. Southern is on a distinguished road
Re: Yearly Attendance Tracker/Calendar

Quote:
Originally Posted by MajP View Post
Code:
AbsenceCode =("PTO","FMLA", "VAC")
not correct sql. Try
Code:
AbsenceCode IN ('PTO','FMLA', 'VAC')
MajP,

What would I need to add if I wanted to only show specific AbsentReasonID(s)? Oxicottin added a combobox to the AbsentReasons and now I have 1,2,3,4 as AbsentReasonsID(s) that I want to display in the WeeklyGrid.

Would it be something like

strSql = "Select * from qry_FillTextBoxes where absenceDate = " & SQLDate(FieldDate) & " AND " & SQLDate(Me.FirstDayOfWeek + 6) & "AND SupervisorID = " & SupervisorID & "AND AbsenceCode IN('PTO','FMLA', 'V') AND AbsentReasonID IN ('1','2','3','4')
Set RsRead = CurrentDb.OpenRecordset(strSql)

Last edited by Mr. Southern; 10-21-2019 at 06:52 PM.
Mr. Southern is offline   Reply With Quote
Old 10-21-2019, 06:51 PM   #112
Mr. Southern
Newly Registered User
 
Join Date: Aug 2019
Location: United States
Posts: 71
Thanks: 11
Thanked 0 Times in 0 Posts
Mr. Southern is on a distinguished road
Re: Yearly Attendance Tracker/Calendar

Quote:
Originally Posted by Mr. Southern View Post
MajP,

What would I need to add if I wanted to only show specific AbsentReasonID(s)? Oxicottin added a combobox to the AbsentReasons and now I have 1,2,3,4 as AbsentReasonsID(s) that I want to display in the WeeklyGrid.

Would it be something like

strSql = "Select * from qry_FillTextBoxes where absenceDate = " & SQLDate(FieldDate) & " AND " & SQLDate(Me.FirstDayOfWeek + 6) & "AND SupervisorID = " & SupervisorID & "AND AbsenceCode IN('PTO','FMLA', 'V') AND AbsentReasonID IN ('1','2','3','4')
Set RsRead = CurrentDb.OpenRecordset(strSql)
The correct code is
Code:
strSql = "Select * from qry_FillTextBoxes where absenceDate = " & SQLDate(FieldDate) & " AND SupervisorID = " & SupervisorID & " AND AbsenceCode IN ('PTO','FMLA','V') AND AbsentReasonID IN (1,2,3,4)"
        Set RsRead = CurrentDb.OpenRecordset(strSql)

Mr. Southern is offline   Reply With Quote
Old 11-14-2019, 12:38 PM   #113
HollyB
Newly Registered User
 
Join Date: Nov 2019
Location: Illinois
Posts: 1
Thanks: 1
Thanked 0 Times in 0 Posts
HollyB is on a distinguished road
Re: Yearly Attendance Tracker/Calendar

Quote:
Originally Posted by MajP View Post
If Oxicottin does not reply you can ask me most questions. I built the calendar controls for him and wrote most of the code modules.
I love the database! I'm really new to this and I need to make a few changes to fit my company's attendance program to move out of Excel trackers.

1. We use an accrual for available paid time off and wouldn't be using the "bought vacation" option. What's the best choice to remove that and replace it with the accrual?

2. Department/Branch vs Supervisor selection. Is there a way to set this up using department/branches instead of Supervisor?

I love the ability to list more than 1 attendance item per day since we allow our employees to make up time to remove the attendance points for unscheduled absences.
HollyB is offline   Reply With Quote
Old 11-14-2019, 01:16 PM   #114
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,892
Thanks: 38
Thanked 574 Times in 539 Posts
MajP has a spectacular aura about MajP has a spectacular aura about
Re: Yearly Attendance Tracker/Calendar

Quote:
Department/Branch vs Supervisor selection. Is there a way to set this up using department/branches instead of Supervisor?
That would be easy. Change the supervisor table to the Department_Branch table. Now you will have to update the supervisor combo to use the new table and new fields. You will have to check throughout the DB for any place that previously used the supervisor ID and replace with DepartmentID.Once you replace the old table with the new table, the code should break in a few places identifying what needs to be updated.

I will have to look at the accrual for changes.

MajP is online now   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
HollyB (11-15-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Access Attendance Register? Wingeronside General 1 01-25-2007 07:24 PM
Two foreign Keys in One table. Kelemit General 21 04-25-2006 09:45 PM
Using a list box in a form to record attendance Bigideaguy Forms 0 04-06-2006 11:08 AM
Attendance Form - Need to auto update names and dates Surfette Forms 2 03-28-2006 02:00 AM




All times are GMT -8. The time now is 02:51 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World