473,320 Members | 1,825 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Access create multiple records based on a date range

1
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(ShortDate)
  • 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.

Expand|Select|Wrap|Line Numbers
  1. Dim dteIterator As Date
  2.  
  3.     dteIterator = Me!StartDate
  4.  
  5.     While dteIterator <= Me!EndDate
  6.         If Weekday(dteIterator, vbMonday) <> vbSaturday And _
  7.             Weekday(dteIterator, vbMonday) <> vbSunday Then
  8.             DoCmd.RunSQL "INSERT INTO tbl_PDO([Worker],[DateTakenOff], [TimeTaken]) VALUES ('" & _
  9.                 Me!Worker & "', #" & Format(dteIterator, "mm/dd/yyyy") & "#, '" & _
  10.                 Me!Time & "');"
  11.         End If
  12.         dteIterator = DateAdd("d", 1, dteIterator)
  13.     Wend
  14.  
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.
Oct 19 '11 #1
1 4138
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.SetWarnings False
should stop all the "Append 1 Record" message boxes

Thank you for uploading your code; I was working on a similar database.
Jan 11 '12 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: BlackFireNova | last post by:
Using Access 2002 I am writing a report which draws data from several different tables. I can't link all the tables in a query, as some can not be related without truncating the data. I plan...
3
by: Gary | last post by:
Sorry to waste everyone's time with a query which is so simple, but as a new user of Access I have created a client database and one of the fields is "next contact due" as the name implies, this is...
2
by: Adam | last post by:
Hi All, This may be a really obvious thing that I'm missing ... but if anyone can help, I'd appreciate it. I have MS Access 2000: I'm using it for a CRM type database. I have a table with...
1
by: edhead | last post by:
Access newbie here, I am trying to figure out how to set up a query that will return an average from multiple records all created on the same date. Table has the following fields date, weight1,...
1
by: itm | last post by:
I have merged the data from Access. Now I need Word to start a new page every time the sequence number changes. How is this accomplished? Also, is it possible to tell Word to hide duplicates? ...
1
by: davidevan | last post by:
What I'm trying to do is set a players division according to their age. So if age is 8, update division to junior, if age is 9, update division to medium, if age is 10, update division to pee wee,...
5
by: hummer | last post by:
In an Access query, I am trying to do the following: I have this: Account # Issue Problem ------------------------------------------------------------------- 1 a...
5
by: jrodcody | last post by:
Hello: I am trying to create multiple records in a table named tblTravel based on the input of a date range in two form controls. The inputs for the form are LastName, TravelDate, EndDate,...
8
by: mintz87 | last post by:
i have a table that has a beg_date & end_date (3/1/1985 & 3/1/1990) how do i, in access, break out this date range into another table by month. since this date range is a 60 month range it would...
8
by: cehlinger | last post by:
I am trying to add multiple records based on a date range. I found some examples and have tried them but am getting errors and I can't seem to figure out what the problem is. I am not a VBA...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.