Access create multiple records based on a date range

[kffacs]

Multiple rows based on a date range

I have an MSAccess 2007 DB to record our employees Personal Days Off (PDO). Until now I have only had a form to record each single day taken. This results in time consuming repetitive entry when an employee takes multiple consecutive days or weeks off.

My database consists of two tables:

• tbl_PDO (to hold the Worker, DateTakenOff and TimeTaken)
• tbl_Employees (containing the Employees contact info, Name, Address, etc.)

A form for Single date data entry with tbl_PDO as the record source and the following fields: (This is the original entry method one day at a time and works as it should)

• cboWorker(with Record Source tbl_Employee[Worker]
• DateTakenOff(Sh ortDate)
• TimeTaken (in hours)

A form for Date Range entry with tbl_PDO as the recored source and the following fields:

• cboWorker(with Record Source tbl_Employee[Worker]
• StartDate
• EndDate
• TimeTaken (in hours)

I have been trying to work with bits and pieces of code I’ve found online to create multiple rows in a table based on a date range.

The following code is the closest I’ve been able to come.

Dim dteIterator As Date

    dteIterator = Me!StartDate
  
    While dteIterator <= Me!EndDate
        If Weekday(dteIterator, vbMonday) <> vbSaturday And _
            Weekday(dteIterator, vbMonday) <> vbSunday Then
            DoCmd.RunSQL "INSERT INTO tbl_PDO([Worker],[DateTakenOff], [TimeTaken]) VALUES ('" & _
                Me!Worker & "', #" & Format(dteIterator, "mm/dd/yyyy") & "#, '" & _
                Me!Time & "');"
        End If
        dteIterator = DateAdd("d", 1, dteIterator)
    Wend

PROBLEMS:
The code is not creating a record for the first date in the range i.e. 10/3/2011 to 10/5/2011 only adds (2) records for 10/4/2011 and 10/5/2011 in tbl_PDO

The code does not loop automatically through each date in the range. Instead the “append 1 record” message box comes up for each date. I’d prefer a way to bypass the append records message or a way to append the entire group of rows.

I can upload the entire database if it would be helpful in resolving the above issues.
Any help would be appreciated. Thanks.

[Rbacker320]

I have re-created your database, and I believe that I have solved both issues:

1) Changing vbMonday to vbSunday should create your desired records

2) Adding the code:
DoCmd.SetWarnin gs False
should stop all the "Append 1 Record" message boxes

Thank you for uploading your code; I was working on a similar database.