Assuming you have a main project form with a schedule subform within it
(whether the subform is in datasheet view, continuous form view or single
form view is immaterial) you can execute some code to insert rows into the
Schedule table and requery the subform ready for the hours to be entered.
The main form would have the start date for the project in a control already,
so you could perhaps put an unbound text box on the main form in which the
number of days you wish to add in the subform, starting with the project
start date, could be entered. You could then put a button on the main
project form (which must be in single form view of course) with code along
these lines in its Click event procedure:
Dim dbs as DAO.Database
Dim strSQL As String
Dim n as Integer
Dim dtmDate as Date
Set dbs = CurrentDb
For n = 0 to Me.Parent.txtDays -1
dtmDate = DateAdd("d",n, Me.Parent.[Start Date])
strSQL = "INSERT INTO [Schedule] ([Project #], [Project Date]) " & _
"VALUES(" & Me.Parent.[Project #] & ",#" & _
FORMAT(dtmDate,"mm/dd/yyyy") & "#)"
dbs.Execute strSQL
Next n
Me.sfrSchedule.Requery
In the above txtDays is the text box on the main project form in which the
number of days to be added is inserted, and sfrSchedule is the name of the
subform control, i.e. the control on the main project form which houses the
subform, not the name of its underlying form object; unless both have the
same name of course. The reason for formatting the date in the SQL statement
is that date literals must be in US short date format, or an otherwise
internationally unambiguous format. You can show the dates in the form in
whatever local format you wish of course.
I've assumed that the Project # column is a number data type. If its text
data type, howver, you'd need to wrap the value in quptes characters like so:
strSQL = "INSERT INTO [Schedule] ([Project #], [Project Date]) " & _
"VALUES(""" & Me.Parent.[Project #] & """,#" & _
FORMAT(dtmDate,"mm/dd/yyyy") & "#)"
A pair of contiguous quotes characters within a string is interpreted as a
literal quotes character.
One other point: the above code would insert consecutive dates, including
Saturdays and Sundays. If you want to skip over the weekend then amend the
code like so:
Dim dbs as DAO.Database
Dim strSQL As String
Dim n as Integer
Dim dtmDate as Date
Set dbs = CurrentDb
dtmDate = DateAdd("d", -1, Me.Parent.[Start Date])
For n = 1 to Me.Parent.txtDays
dtmDate = DateAdd("d", 1, dtmDate)
Do While Weekday(dtmDate, vbSaturday) < 3
dtmDate = DateAdd("d", 1, dtmDate)
Loop
strSQL = "INSERT INTO [Schedule] ([Project #], [Project Date]) " & _
"VALUES(" & Me.Parent.[Project #] & ",#" & _
FORMAT(dtmDate,"mm/dd/yyyy") & "#)"
dbs.Execute strSQL
Next n
Me.sfrSchedule.Requery
Ken Sheridan
Stafford, England
JennKriv said:
Thank-you, I believe you are correct on your better layout. That seems to be
what I was really thinking I needed to do. My example came from an excel
speardsheet which I am trying to get rid of . I wanted to know if I can do
the details in a datasheet subform and if so if there is a code I could use
so that the date of the scheduling can be automatically entered and go on to
the next day.
IE:
March 25, 08 8
March 26, 08 8
March 27, 08 8
and so on..
:
The Schedule table is quite simple, it just needs columns project #, project
date (don't use date as a column name BTW, it’s the name of a built in
function) and hours. To get the layout or a report with the dates per
project horizontally across the page you can have a report based on the
Projects table and a multi-column subreport based on the Schedule table,
using an across-then-down column layout. The subreport would be in the main
report's detail section and positioned immediately to the right of the
Project #, Project Title and Start Date controls in the main report. The
column headings with the would go in the main report's page header. The
first would have a ControlSource of [Start Date], the next of =DateAdd("d",
1, [Start Date]), the next of =DateAdd("d", 2, [Start Date]) and so on
I do see a number of potential problems with the layout you've illustrated,
however:
1. It assumes that all projects included in the report start on the same
date.
2. That all projects included in the report have hours assigned for each
day, with no gaps.
3. That the number of days will fit across the page as one line of values.
Unless the data fits this scenario a better layout might be the more
conventional approach of, rather than using a subreport, base the main report
on a query which joins the tables on Project #. The report would then be
grouped on Project #, with the Project #, Project Title and Start Date
controls in a group header and the Project date and Hours controls in the
detail section. These would then be listed vertically for each project
rather than horizontally in columns, but would allow for different start
dates and number of days per project. It would also allow space for other
data, e.g. the tasks to be undertaken on the project on that day, for which
you'd have a Task column in the Schedule table. If there can be than one
task per project per day then these would appear as separate rows in
projects, having the same Project # and Project date values but different
Hours and Task values. In this scenario you'd group the report first on
Project # and then on Project Date and have two group headers, the Project
Date going in the second header and the Hours and task in the detail section.
The layout might then look something like this:
Project Number: 8001
Project Title: Shop
Start date: Mar 25, 08
Mar 25, 08 Some task 4
Some other task 1.5
Yet another task 2.5
Total hours for day 8
Mar 25, 08 One big task 8
Total hours for day 8
Project Number: 8002
Project Title: Office
Start date: Mar 25, 08
and so on.
The total hours for each day would be in the Project Date group footer using
an unbound text box control with a ControlSource of =Sum([Hours]. If you
wished you could also have a control in the Project # group footer with an
identical ControlSource property to show the total hours per project.
One thing you might have noticed about the above layout is that the first
line of the detail section is on the same line as the Project Date group
footer. To do this you enter MoveLayout = False in the Format event
procedure of the Project Date group footer.
Ken Sheridan
Stafford, England
:
I have a database that I use for projects that our company has. I want to
create a time schedule type calendar so that I can schedule hours per job per
day.
I also want to be able to create a report that shows the schedule, so it
needs to have that capability.
Here is a example of what I was hoping to acheive. I am not sure if this is
even the best way to do this but I am unsure of other ways.
Project # Project Title Start Date Mar 25, 08 Mar 26, 08 Mar
27, 08
8001 Shop Mar 25, 08 8 8 8
8002 Office Mar 25, 08 8 8 8
The Project #, Title & Start Date will be automaticall pulled in from
another table.
Any Ideas? I hope I explained what I was looking for to help you all give
the best answer. If you need more info just let me know.
Thank-you in advance.
