469,902 Members | 1,950 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,902 developers. It's quick & easy.

Access 2000 Inserting multiple rows based on a date range

5
I have a form that records dates of unavailability for a worker.
Based on what is entered in the simple table behind the form, many calculations take place to create employee "load" balance. Each day needs to be inserted as a separate row on the table.
Right now the users enter each date as a separate row.
They want to just be able to enter a range.
How do I add the multiple rows from the date range entered on the form AND exclude weekends?
Oct 10 '06 #1
4 4668
PEB
1,418 Expert 1GB
Hi Joanie,

you need to use a VB code to perform this task! Do you want to continue with the code? If yeah, can you tell me the names of your start date and your end date and when do you want that the dates are appended... And how do you see if once the specified range is entered... Maybe it's better to use a single form that it's not based on the form that you mention!

So do you want to proceed with VB code?

Best regards
Oct 14 '06 #2
Joanie
5
Yes, VB code would be great.
So, here is how I see it working...
They have a form which has a person's name and the dates they will be absent: (begin & end date) October 14th -29th and hours gone per day (because some people will just be absent 4 hours on one day).

The code should insert rows into a table as follows:
John Smith, 10/14/2006, 8 (for 8 hours)
the next row would be
John Smith, 10/15/2006, 8
etc....
THANK YOU!!!!
Hi Joanie,

you need to use a VB code to perform this task! Do you want to continue with the code? If yeah, can you tell me the names of your start date and your end date and when do you want that the dates are appended... And how do you see if once the specified range is entered... Maybe it's better to use a single form that it's not based on the form that you mention!

So do you want to proceed with VB code?

Best regards
Oct 16 '06 #3
Joanie
5
p.s. and it should exclude weekends.
Oct 16 '06 #4
PEB
1,418 Expert 1GB
Yes, VB code would be great.
So, here is how I see it working...
They have a form which has a person's name and the dates they will be absent: (begin & end date) October 14th -29th and hours gone per day (because some people will just be absent 4 hours on one day).

The code should insert rows into a table as follows:
John Smith, 10/14/2006, 8 (for 8 hours)
the next row would be
John Smith, 10/15/2006, 8
etc....
THANK YOU!!!!
Ok let's do it! :)
Imagine that on your form you have an Ok bouton to that is attached the function. On Click event, you choose event procedure.. Appears a white scrren of VB in which you have to place the following code:
Expand|Select|Wrap|Line Numbers
  1. Sub Ok_onclick()
  2. Dim i
  3.  
  4. For i = int(Cvdate(Me!StartDate)) to int(Cvdate(Me!EndDate))
  5.     if  (DatePart("w", i,vbMonday) <> 6) And (DatePart("w", i,vbMonday) <> 7) then 
  6.       docmd.runsql "INSERT INTO Hours(Worker, Date, Time) VALEUS ('"+Me!WOrker+"',"+Format(i,"dd/mm/yyyy")+",8 );"
  7.   End if
  8. Next i
  9.  
  10. End Sub
The only thing you have to do is to replace The fields and the table name....
Workers, Worker, Date, Time, StartDate End Date with the appropriate ones!

:)
Oct 21 '06 #5

Post your reply

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

Similar topics

17 posts views Thread by Mell via AccessMonster.com | last post: by
1 post views Thread by evenlater | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.