Hi,
I have a database that stores hours for each employee by week.
Something like this

EmployeeiD ProjectID Store_Hrs Cleaning_Hrs Cashier_Hrs Date
1 1 2 1
3 01/02/2006
1 1 1 3
2 01/09/2006
2 2 2 2
1 01/02/2006
2 1 0 1
0 01/09/2006

I have several employees and that are assigned work for each week
through out 2006.
I would like to be able to enter the same hours for several consequtive
weeks.
I currently have a form where the employees can enter a week date (like
01/15/2006) and the hours for each category (store_Hrs etc).
I would like to have a form where the employee enters a begin date and
an end date in two text boxes. If the end date is greater than the max
date available for that employee in the database, then the dates should
be added by increments of weeks until the end date and the hours
entered for a particular category for every date. This is because the
employee is assigned to work a fixed 'x' hours each week for a project
for say some 4 months.
For example if employee 2 enters a begin date of 01/01/2006 and an end
date of 05/31/2006 and enters 2 for store__hrs, the code checks for the
latest date for employee 2 available in the database which is
01/09/2006 < 05/31/2006 (end date) in this case and then the code adds
dates from 01/09/2006 until 05/31/2006 in the increments of 7 like
01/15/2006, 01/22/2006 etc until 05/31/2006 and then inputs the number
2 in the store_hrs category for each date created.
Basically, this is inputting blanket hours (the same hours for a
particular category over a period of time) instead of creating a new
record each time and then inputting the same hours for each week
manually.
In case the end date is less than the latest date in the database for
that particular employee, only the hours for that particular category
need updated for each date.

Is this something that can be done using VBA? Or Am I pushing my luck?

Any help would be greatly appreciated.

Thank you.

Sorry I see the formating is screwed up
Here is a better format of the table I'm talking about
EmployeeiD ProjectID Store_Hrs Cleaning_Hrs Date
1 1 2 1
01/02/2006
1 1 1 3
01/09/2006
2 2 2 2
01/02/2006
2 1 0 1
01/09/2006

Thanks again for thr help.

A better format...sorry!
EmpiD ProjectID Store_Hrs Clea_Hrs Date
1 1 2 1 01/02/2006
1 1 1 3 01/09/2006
2 2 2 2 01/02/2006
2 1 0 1 01/09/2006

On 4 Sep 2006 09:11:55 -0700, "Pragv" <(E-Mail Removed)>
wrote:

>I would like to have a form where the employee enters a begin date and
>an end date in two text boxes. If the end date is greater than the max
>date available for that employee in the database, then the dates should
>be added by increments of weeks until the end date and the hours
>entered for a particular category for every date. This is because the
>employee is assigned to work a fixed 'x' hours each week for a project
>for say some 4 months.

One way you can do this is with the help of a general-purpose
auxiliary table. I'll routinely create a table Num with one Long
Integer field N, filled with values 0 through 10000 or so.

You can create an Append query like:

INSERT INTO Schecule(EmployeeiD, ProjectID, Store_Hrs, Cleaning_Hrs,
Cashier_Hrs, [Date])
SELECT [Forms]![FormName]![cboEmployeeID],
[Forms]![FormName]![cboProjectID],
[Forms]![FormName]![txtStore_Hrs],
[Forms]![FormName]![txtCleaning_Hrs],
DateAdd("d", 7*[N], [Forms]![FormName]![txtStartDate]
FROM Num
WHERE DateAdd("d", 7*[N], [Forms]![FormName]![txtStartDate]) <=
[Forms]![FormName]![txtEndDate];


John W. Vinson[MVP]

Thank you very much for the reply.
Sorry for the repost. I only did it because I felt my messy formatting
would drive away viewers. My apologies.

I haven't tried you code yet but would it be posible to check for the
latest available date in the database and add the increments from
there?
I mean many times, the start date can be less than an existing date in
the table and so can be rewritten.
I'm also planning to allow users to chose only one category of hours at
a time when making bulk updates.
Would that help with?
I'll try this code.
Thank you for the help!

John Vinson wrote:
> On 4 Sep 2006 09:11:55 -0700, "Pragv" <(E-Mail Removed)>
> wrote:
>
> >I would like to have a form where the employee enters a begin date and
> >an end date in two text boxes. If the end date is greater than the max
> >date available for that employee in the database, then the dates should
> >be added by increments of weeks until the end date and the hours
> >entered for a particular category for every date. This is because the
> >employee is assigned to work a fixed 'x' hours each week for a project
> >for say some 4 months.
>
> One way you can do this is with the help of a general-purpose
> auxiliary table. I'll routinely create a table Num with one Long
> Integer field N, filled with values 0 through 10000 or so.
>
> You can create an Append query like:
>
> INSERT INTO Schecule(EmployeeiD, ProjectID, Store_Hrs, Cleaning_Hrs,
> Cashier_Hrs, [Date])
> SELECT [Forms]![FormName]![cboEmployeeID],
> [Forms]![FormName]![cboProjectID],
> [Forms]![FormName]![txtStore_Hrs],
> [Forms]![FormName]![txtCleaning_Hrs],
> DateAdd("d", 7*[N], [Forms]![FormName]![txtStartDate]
> FROM Num
> WHERE DateAdd("d", 7*[N], [Forms]![FormName]![txtStartDate]) <=
> [Forms]![FormName]![txtEndDate];
>
>
> John W. Vinson[MVP]

On 4 Sep 2006 13:13:06 -0700, "Pragv" <(E-Mail Removed)>
wrote:

>Thank you very much for the reply.
>Sorry for the repost. I only did it because I felt my messy formatting
>would drive away viewers. My apologies.

I saw the two formatting corrections - that was find - I was replying
about the third repeat posting, two hours after the first. Don't worry
about it!

>I haven't tried you code yet but would it be posible to check for the
>latest available date in the database and add the increments from
>there?

Sure, you can use DMax() to find the maximum existing date in a range.

>I mean many times, the start date can be less than an existing date in
>the table and so can be rewritten.

That I don't understand. The query I suggest will add new records to
the table; it will NOT update existing records. To do so would require
a different query, an Update query without using the Num table.

>I'm also planning to allow users to chose only one category of hours at
>a time when making bulk updates.

I do not know what a "category" is in your database but I would
presume that you could have an additional control on your Form to
select the category, just as my suggestion would use combos for the
ProjectID.


John W. Vinson[MVP]

Thank you Mr. Vinson for your help.
I tried using the following code to update my hours based on a begin
date and an end date entered by the user.
I have a combo box to select the workhrs ategory (like clea_hrs or
store_hrs) then text boxes to enter begin date, end date and the hours
for the selected category on my form. I also have text boxes that
obtain the person id and project id from a different form
My requirement here is that if a user enters a begin date that is
greater than the maximum date in the database for this particular
criteria (projectid, personid..), then the code creates dates by
incrementing them by 7 days at a time and inputting the hours entered
for all the dates. If the begin date is less than the max date and the
end date is less than the max date too, only update hrs takes place. If
the end date is greater than max date an dthe begin date is less than
max date, update hours takes place until the max date and the dates and
hours are added from then on.
But my code gives me compile errors. I guess it has to do with the SQL
statements spanning multiple lines. I tried using the _ at the end of
each line but no use.
Can you please help me here?

Dim db As DAO.Database
Dim dbegindate As Date
Dim denddate As Date
Dim dmaxdate As Date
Set db = CurrentDb()
myField = cbadminhrs.Value
dbegindate = Txtbegindate.Value
denddate = Txtenddate.Value
dmaxdate = DMax(Wdate, qryAdmindata)
stadminhrs = Forms![frmbulkhrs]![cbadminhrs]
If DMax(Wdate, qryAdmindata) <= dbegindate Then
strqryinsert = "INSERT INTO tblworkhrs(stadminhrs, [WDate])" & _
"SELECT Forms![frmbulkhrs]![Txthrs]" & _
"DateAdd("d", 7*[N], dbegindate" & _
"FROM Num" & _
"WHERE personid=forms![frmbulkhrs]!personid AND
projectid=forms![frmbulkhrs]!projectid" & _
"AND DateAdd("d", 7*[N], dbegindate) <=" & _
"denddate;"
db.Execute strqryinsert, dbFailOnError
Else
If DMax(Wdate, qryAdmindata) >= denddate Then
RunSQL ("Update [tblworkhrs] SET [" & myField &
"]=Forms![frmbulkhrs]![Txthrs]"& _
"WHERE personid=forms![frmbulkhrs]!personid AND
projectid=forms![frmbulkhrs]!projectid" & _
"AND WDate>=dbegindate AND Wdate<=denddate");
Else
If DMax(Wdate, qryAdmindata) < denddate Then
RunSQL ("Update tblworkhrs SET ["& myField
&"]=Forms![frmbulkhrs]![Txthrs]" & _
"WHERE personid=forms![frmbulkhrs]!personid AND
projectid=forms![frmbulkhrs]!projectid" & _
"AND WDate>=dbegindate AND denddate<=Wdate");
RumSQL ("INSERT INTO tblworkhrs(stadminhrs, [WDate])" & _
"SELECT Forms![frmbulkhrs]![Txthrs]" & _
"DateAdd("d", 7*[N], dmaxdate" & _
"FROM Num" & _
"WHERE personid=forms![frmbulkhrs]!personid AND
projectid=forms![frmbulkhrs]!projectid" & _
" AND DateAdd("d", 7*[N], dmaxdate) <=" & _
"denddate;"
End Sub

Thank you very much....

I noticed I did not have End If's . I added the End If's but I get a
syntax error where the SQL statements are...

Thank you

On 10 Sep 2006 08:09:08 -0700, "Pragv" <(E-Mail Removed)>
wrote:

>I noticed I did not have End If's . I added the End If's but I get a
>syntax error where the SQL statements are...


Please post your current actual code.

John W. Vinson[MVP]

Here is my current code..

I have compile errors where ever there is a SQL statement in the code..
Thank you for the help..

Dim stadminhrs As String
Dim stqryinsert As String

Option Compare Database

'
Private Sub cmdupdate_Click()
Dim db As DAO.Database
Dim dbegindate As Date
Dim denddate As Date
Dim dmaxdate As Date
Set db = CurrentDb()
myField = cbadminhrs.Value
dbegindate = Txtbegindate.Value
denddate = Txtenddate.Value
dmaxdate = DMax(Wdate, qryAdmindata)
stadminhrs = Forms![frmbulkhrs]![cbadminhrs]
If DMax(Wdate, qryAdmindata) <= dbegindate Then
strqryinsert = "INSERT INTO tblworkhrs(stadminhrs, [WDate])" & _
"SELECT Forms![frmbulkhrs]![Txthrs]" & _
"DateAdd("d", 7*[N], dbegindate" & _
"FROM Num" & _
"WHERE personid=forms![frmbulkhrs]!personid AND
projectid=forms![frmbulkhrs]!projectid" & _
"AND DateAdd("d", 7*[N], dbegindate) <=" & _
"denddate;"
db.Execute strqryinsert, dbFailOnError
Else
If DMax(Wdate, qryAdmindata) >= denddate Then
RunSQL ("Update [tblworkhrs] SET [" & myField &
"]=Forms![frmbulkhrs]![Txthrs]"& _
"WHERE personid=forms![frmbulkhrs]!personid AND
projectid=forms![frmbulkhrs]!projectid" & _
"AND WDate>=dbegindate AND Wdate<=denddate");
Else
If DMax(Wdate, qryAdmindata) < denddate Then
RunSQL ("Update tblworkhrs SET ["& myField
&"]=Forms![frmbulkhrs]![Txthrs]" & _
"WHERE personid=forms![frmbulkhrs]!personid AND
projectid=forms![frmbulkhrs]!projectid" & _
"AND WDate>=dbegindate AND denddate<=Wdate");
RumSQL ("INSERT INTO tblworkhrs(stadminhrs, [WDate])" & _
"SELECT Forms![frmbulkhrs]![Txthrs]" & _
"DateAdd("d", 7*[N], dmaxdate" & _
"FROM Num" & _
"WHERE personid=forms![frmbulkhrs]!personid AND
projectid=forms![frmbulkhrs]!projectid" & _
" AND DateAdd("d", 7*[N], dmaxdate) <=" & _
"denddate;"
End If
End If
End If

End Sub