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.)
- cboWorker(with Record Source tbl_Employee[Worker]
- DateTakenOff(ShortDate)
- TimeTaken (in hours)
- cboWorker(with Record Source tbl_Employee[Worker]
- StartDate
- EndDate
- TimeTaken (in hours)
The following code is the closest I’ve been able to come.
Expand|Select|Wrap|Line Numbers
- 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
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.