Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2012
    Posts
    2

    Post Unanswered: Automatic repeat of entry for the following day

    Hi,

    My first post and I apologise if my terminology isn't 'industry standard'.

    Using Access 2003. (also have a B&W TV and a pet dinosaur).

    I have an attendance db for recording 'last minute' leave, such as if someone is sick or has to care for thier child.
    The db records the usual name date etc but sometimes people will be away for 2,3,4 even 5 days.
    My Boss wants a report daily.
    The form has a date picker for the first day of leave (usually today) and then a dropdown with options 1-5 for the number of days off.

    If I record on Monday that somebody will be sick for 3 days this shows up as a total of 3 sick days for Monday.

    Is there a way to have access interpret the number 3 (days off) as an instruction to create a record for 1 day-off on 3 consecutive days?

    Any assistance is appreciated.

  2. #2
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Ok, so you must have a way to add a record to the database with the userId and the leave date.

    So, in your form code simply create a loop and repeat this database call for every day they request (3 day leave - add 3 records). And between each call use the DateAdd function to add 1 day to the original leave date.

    Steve

  3. #3
    Join Date
    Aug 2012
    Posts
    2

    Question Follow up

    Quote Originally Posted by sps View Post
    Ok, so you must have a way to add a record to the database with the userId and the leave date.

    So, in your form code simply create a loop and repeat this database call for every day they request (3 day leave - add 3 records). And between each call use the DateAdd function to add 1 day to the original leave date.
    What if the next day day is a w/end? We don't work weekends

    Also, How do I create a 'loop'?

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Check the Access Help file for Do loops and For...Next loops.

    The Weekday and Format functions can help you with identifying which day of the week a date value is - again, the Help file is your friend!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  5. #5
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Loops are pretty straight forward, but as weejas recommended, the help files (or google) will help you understand the syntax.

    Here's an example with a nested loop that is somewhat relevant to your situation:


    Code:
    Private Sub recordDays(startDate As Date, dayCount As Integer)
        Dim i As Integer
        
        For i = 1 To dayCount
            
            'Use a custom function to test for valid workdays
            Do Until (isWorkday(startDate))
                'If not a valid workday add a day and retest
                startDate = DateAdd("d", 1, startDate)
            Loop
        
            'Call another custom function to add this record to the database
            recordDayOff (startDate)
            
            'Move to the next day
            startDate = DateAdd("d", 1, startDate)
        
        Next i
    
    End Sub
    This uses two kinds of loops, the For Loop will execute a certain amount of times and a Do Until loop will execute until a specific condition is met. In this example the Do Until loop starts by testing if the startDate is a valid workday. If it is, then the code inside the loop is skipped. Otherwise, it will continue to add one day and test again until a valid workday is found.

    I put the workday test into a custom function because you need to handle weekends and whatever holidays your company offers. There are many ideas for handling this out on google.

    Steve

Tags for this Thread

Posting Permissions

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