Access 2000 Inserting multiple rows based on a date range

[Joanie]

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?

[PEB]

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

[Joanie]

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

[Joanie]

p.s. and it should exclude weekends.

[PEB]

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:

Sub Ok_onclick()
Dim i
 
For i = int(Cvdate(Me!StartDate)) to int(Cvdate(Me!EndDate))
    if  (DatePart("w", i,vbMonday) <> 6) And (DatePart("w", i,vbMonday) <> 7) then 
      docmd.runsql "INSERT INTO Hours(Worker, Date, Time) VALEUS ('"+Me!WOrker+"',"+Format(i,"dd/mm/yyyy")+",8 );"
  End if
Next i
 
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!

:)