C
Chris
Hi,
I have a timesheet database that stores tasks by date,
duration and projectID. My company has decide that they
would like more detail for each task and have therefore
requested that I add an Activity field to the timesheets.
I have created an activity table which has the following
fields.
ActivityID
ProjectID
ActivityNum
ActivityDescription
etc. (the above fields are all I'm concerned with for the
timesheets)
My users add task through a subform which filters the
records by week. a typical display would look something
like this:
DAY DATE PROJECT ACTIVITY DESCRIPTION DURATION
=========================================================
Mon 12/May/03 1268 ???? Drafting 05:45
Mon 12/May/03 1291 ???? Mapping 03:25
Tue 13/May/03 1268 ???? Setting out 01:00
Tue 13/May/03 1268 ???? Drafting 02:40
I would like to be able to select from a combo box the
relevant activity from a limited list that shows only the
activities for that project. I have set the recordsource
for the activity drop down menu to:
select all records from activities where projectID =
selected projectID on form.
The exact SQL used is as follows:
SELECT A.ActivityID, "A" & Format(ProjectNum,"0000") & "-"
& Format(ActivityNum,"000") AS ACODE,
A.ActivityDescription FROM tblActivities AS A LEFT JOIN
tblProjects AS P ON A.ProjectID=P.ProjectID WHERE
(((A.ProjectID)=Forms!frmTimesheets!sfTimeDiff.Form!
ProjectID));
The problem is that all the fields are empty if the
currently selected record has a project number with no
activities defined. In other words, the list of activities
is limited to the activities of the currently selected
project. I would like to show only the activities for the
selected project.
So for the recordset above I would like the pull down menu
to show the following:
DAY DATE PROJECT ACTIVITY DESCRIPTION DURATION
=========================================================
Mon 12/May/03 1268 PICK Drafting 05:45
A1268-001 GENERAL
A1268-002 UNKNOWN
A1268-101 DESIGN WORK
A1268-102 SURVEY
Mon 12/May/03 1291 PICK Mapping 03:25
A1291-001 GENERAL
A1291-002 UNKNOWN
A1291-101 ANALYSIS
A1291-102 IMPLEMENATION
Tue 13/May/03 1268 PICK Setting out 01:00
A1268-001 GENERAL
A1268-002 UNKNOWN
A1268-101 DESIGN WORK
A1268-102 SURVEY
Tue 13/May/03 1268 PICK Drafting 02:40
A1268-001 GENERAL
A1268-002 UNKNOWN
A1268-101 DESIGN WORK
A1268-102 SURVEY
Can anyone tell me how to setup the combobox so that only
the relavant records are seen for each record?
Thanks in advance for any advice/help.
Regards,
Chris Kelly
I have a timesheet database that stores tasks by date,
duration and projectID. My company has decide that they
would like more detail for each task and have therefore
requested that I add an Activity field to the timesheets.
I have created an activity table which has the following
fields.
ActivityID
ProjectID
ActivityNum
ActivityDescription
etc. (the above fields are all I'm concerned with for the
timesheets)
My users add task through a subform which filters the
records by week. a typical display would look something
like this:
DAY DATE PROJECT ACTIVITY DESCRIPTION DURATION
=========================================================
Mon 12/May/03 1268 ???? Drafting 05:45
Mon 12/May/03 1291 ???? Mapping 03:25
Tue 13/May/03 1268 ???? Setting out 01:00
Tue 13/May/03 1268 ???? Drafting 02:40
I would like to be able to select from a combo box the
relevant activity from a limited list that shows only the
activities for that project. I have set the recordsource
for the activity drop down menu to:
select all records from activities where projectID =
selected projectID on form.
The exact SQL used is as follows:
SELECT A.ActivityID, "A" & Format(ProjectNum,"0000") & "-"
& Format(ActivityNum,"000") AS ACODE,
A.ActivityDescription FROM tblActivities AS A LEFT JOIN
tblProjects AS P ON A.ProjectID=P.ProjectID WHERE
(((A.ProjectID)=Forms!frmTimesheets!sfTimeDiff.Form!
ProjectID));
The problem is that all the fields are empty if the
currently selected record has a project number with no
activities defined. In other words, the list of activities
is limited to the activities of the currently selected
project. I would like to show only the activities for the
selected project.
So for the recordset above I would like the pull down menu
to show the following:
DAY DATE PROJECT ACTIVITY DESCRIPTION DURATION
=========================================================
Mon 12/May/03 1268 PICK Drafting 05:45
A1268-001 GENERAL
A1268-002 UNKNOWN
A1268-101 DESIGN WORK
A1268-102 SURVEY
Mon 12/May/03 1291 PICK Mapping 03:25
A1291-001 GENERAL
A1291-002 UNKNOWN
A1291-101 ANALYSIS
A1291-102 IMPLEMENATION
Tue 13/May/03 1268 PICK Setting out 01:00
A1268-001 GENERAL
A1268-002 UNKNOWN
A1268-101 DESIGN WORK
A1268-102 SURVEY
Tue 13/May/03 1268 PICK Drafting 02:40
A1268-001 GENERAL
A1268-002 UNKNOWN
A1268-101 DESIGN WORK
A1268-102 SURVEY
Can anyone tell me how to setup the combobox so that only
the relavant records are seen for each record?
Thanks in advance for any advice/help.
Regards,
Chris Kelly