Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2004
    Posts
    4

    Unanswered: Incrementing Dates

    Hello. I need some help in trying to insert a number of records into a table by incrementing the date by one day. We track employee absences on a day-to-day basis. The database works great for adding one or two days of consecutive absences, but if an employee takes a full week vacation or is on a leave of absence, it becomes time consuming to add each day in manually.

    What I have in mind is a request for leave form that will allow a user to choose the employee, the type of leave (vacation, LOA, FMLA, etc.), the startDate, and endDate and then click on a button that will add each day of an absence starting with the startDate and ending on the endDate. The button will insert these records into a table called tblEmployeesAbsences.

    Because I don't want to muddy the waters, the users will only be allowed to add up to 5 working days (Mon-Fri) at a time (I don't want to think about weekends and holidays). If a particular employee is on a Leave of Absence for a month or better, we will use this form for that employee on a week by week basis.

    I've seen a sample of what I'm after that puts the information in a calendar report, but I need this to go directly into the absences table by consecutive date.

    Can someone help me write a code or function that will do this?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Ok, so you want to get a range of dates from a user, then you want to make one entry into an "absences" table for each business day occuring within the range provided by the user, is that about right?

    could you describe how your normal absence tracking method works currently? As it sounds like you're attempting to mimick your current method, it would help a lot to know exactly what that is.

    In general terms, you will need to write a function with two primary parts: one to determine which weekdays are in the range specified by the user, and the second to actually execute update statements inserting records into your absences table.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Dec 2004
    Posts
    4
    Quote Originally Posted by Teddy
    Ok, so you want to get a range of dates from a user, then you want to make one entry into an "absences" table for each business day occuring within the range provided by the user, is that about right?

    could you describe how your normal absence tracking method works currently? As it sounds like you're attempting to mimick your current method, it would help a lot to know exactly what that is.

    In general terms, you will need to write a function with two primary parts: one to determine which weekdays are in the range specified by the user, and the second to actually execute update statements inserting records into your absences table.
    I'm using a subform in continuous mode, and in the header, I have unbound text boxes that the user enters in the appropriate info and clicks a button called Add New. The procedure for this cmd button is as follows:

    Code:
    Private Sub cmdAddNew_Click()
    On Error GoTo Err_cmdAddNew_Click
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tblEmployeesAbsences", dbOpenDynaset)
        rs.AddNew
            rs!EmpID = Me.txtEmpID
            rs!ABDate = Me.txtABDate
            rs!LookupID = Me.cboABCode
            rs!ABHours = Me.txtAbHours
            rs!ABPoints = Me.txtPoints
            rs!ABComments = Me.txtABComments
            rs.Update
          
        Me.cboABCode = Null
        Me.txtAbHours = Null
        Me.txtPoints = 0
        Me.txtABComments = Null
        Me.txtABDate = Date
         
        Me.Parent.cboEmpID.SetFocus
        Me.Requery
        Me.Refresh
        
       
    Exit_cmdAddNew_Click:
        Exit Sub
    
    Err_cmdAddNew_Click:
        If Err.Number = 3315 Then 'zero-length string
            Resume Exit_cmdAddNew_Click
        Else
            MsgBox Err.Description
            Resume Exit_cmdAddNew_Click
        End If
        
    End Sub
    What I'm thinking is that I should add another button to call up another form which I made already that has:

    cboEmployee
    txtStart
    txtEnd
    txtHours=defaulting to 8 (we do use 10 on some employees)
    txtPoints=defaulting to 0
    txtComments=memo

    then add a button that will apply the same information to the absences table for the number of days between the start and end dates (up to 5, Mon-Fri). I will convince the users that they can only add 1 full week at a time to avoid weekends and holidays. So if an employee is on leave for a full month, they will have to use this form 4 times to add in the entire month (if the month in question has four full weeks).

    Unless you have a better idea? I'm open to any suggestions. Thank you again for your time and sorry it took me so long to respond--had to deal with a slight power outage!

  4. #4
    Join Date
    Jan 2005
    Posts
    31
    An absence has a start date and an end date.

    If you record all absences this way, then an extended absence doesn't involve entering multiple dates, just updating the end date. That way, each absence of a particular type also only takes up only one row.

  5. #5
    Join Date
    Dec 2004
    Posts
    4
    Quote Originally Posted by chris_n_osborne
    An absence has a start date and an end date.

    If you record all absences this way, then an extended absence doesn't involve entering multiple dates, just updating the end date. That way, each absence of a particular type also only takes up only one row.
    Thank you for your response Chris. I appreciate it. It's a good concept, but the database wasn't set up that way. While I understand that I could modify parts of it to incorporate this, the absence calendar that we use is too technical for me to modify in such a way that it would continue to work. I realize this could be easy for someone experienced in Access, but I'm not. I really need a way to add incremental days for a week at a time.

    Thank you for your time.

  6. #6
    Join Date
    Feb 2005
    Location
    Peoria, IL, USA
    Posts
    9
    I agree that the database design you are working with can be improved. However, we've all run into situations where we're stuck with less than ideal technical constraints.

    If my understanding is correct, you need to propogate one record for each business day of absence. A "dirty" but reliable way to do this without requiring the user to enter data more than once for the same leave of absence follows.

    Create a table or code in an array with the business days in your year. There is a way to have Access figure out which days are weekends/weekdays, but holidays (and whether or not they are observed by your company) are a different matter. A draw-back is that you will have to update this table as time goes by.

    Use your form to input the elements you need in your table, including the start date and end date. Either provide a button to "update and save" or use an event procedure to run the code -- In this situation I'd advocate the button, giving a "reminder" message if the user tries to close the form or move to another record without saving.

    You're on the right track with your code, just create a DateToAdd variable, and add in a loop to step through from the StartDate to the EndDate. Use an IF statement to check and see if DateToAdd is in the business day recordset. If yes, then add a record to your absence table as you have in your code. If not, then increment DateToAdd by one and continue with the loop.

    I hope this helps. Good luck!

    Bonnie

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    write a function to write records to the same table
    pass the function the dates you want to write eg
    private funtion RecordSickies(datStartDate as date, datEndDate as date + any other parameters you need to pass[such as employee, reason code etc])


    in the function

    clone the forms recordsource
    set up a loop to process the correct number of records
    write the records to the recordset
    requery the form
    exit function

  8. #8
    Join Date
    Dec 2004
    Posts
    4
    Thank you all so much! That helps a lot. I technically already have a Holiday table and a Networkdays function already in the database that is used for determining various probation dates, so with a little tweaking, I should be able to figure something out based on your replies. I appreciate all your help! Have a great day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •