G
Guest
I have 3 tables:
1) Table Projects with autonumber primary key ProjectID, ProjectName (text)
2) Table Activities with autonumber primary key ActivityID,
ActivityName(text), ProjectID (foreign key)
3) Table HoursWorked with autonumber ID field, ProjectID, ActivityID and
Hours (Number)
Tables Activities and Projects are linked. One project can have many
activities. In table HoursWorked users insert ProjectID, ActivityID and
Hours. This third table is linked to a datasheet form.
What I would like to do is to select projects and corresponding activities
by using cascading combo boxes in the datasheet, I wonder if this is possible
at all?
I insert a combobox (cboProject) as the first column of the datasheet bound
to projectID with TableProjects as rowsource.I insert a second combobox
(cboActivity) bound to ActivityID and displaying the ActivityName. This
second combobox must have its rowsource set from the value of the first
combobox: Select ActivityID, Activityname from TableActivities where
ProjectID = Me.cboProject. I set this rowsource on cboproject AfterUpdate
event.
This does not work in my datasheet because all activities in all records are
updated from this event. If I am working on record 5 of the datasheet, the
Activitynames on row 1 to 4 of the datasheet are also updated, although the
data in table HoursWorked are not changed. How can I fix this?
Regards
Tore
1) Table Projects with autonumber primary key ProjectID, ProjectName (text)
2) Table Activities with autonumber primary key ActivityID,
ActivityName(text), ProjectID (foreign key)
3) Table HoursWorked with autonumber ID field, ProjectID, ActivityID and
Hours (Number)
Tables Activities and Projects are linked. One project can have many
activities. In table HoursWorked users insert ProjectID, ActivityID and
Hours. This third table is linked to a datasheet form.
What I would like to do is to select projects and corresponding activities
by using cascading combo boxes in the datasheet, I wonder if this is possible
at all?
I insert a combobox (cboProject) as the first column of the datasheet bound
to projectID with TableProjects as rowsource.I insert a second combobox
(cboActivity) bound to ActivityID and displaying the ActivityName. This
second combobox must have its rowsource set from the value of the first
combobox: Select ActivityID, Activityname from TableActivities where
ProjectID = Me.cboProject. I set this rowsource on cboproject AfterUpdate
event.
This does not work in my datasheet because all activities in all records are
updated from this event. If I am working on record 5 of the datasheet, the
Activitynames on row 1 to 4 of the datasheet are also updated, although the
data in table HoursWorked are not changed. How can I fix this?
Regards
Tore