Row source combo box limiting by previous field?

[Jaazaniah]

Is this possible? Here's the scenario, the tracking system is meant to
track time spent on client/project/activity with each of those being
heirarchal. That is to say I have 4 tables that are linked similar to
this

Clients:
ClientID
Texts

Projects:
ProjectID
Client>Clients.ClientID
Texts

Activities:
ActivityID
Project>Project.ProjectID
Texts

Hours:
Date/StartTime/EndTime
Client>Clients.ClientID
Project>Projects.ProjectID
Activity>Activities.ActivityID

My question is, is it possible to use the Combo Box lookup option to
filter the Row Source for Project/Activity based on the value in the
previous field? or is this more of a form relations issue?

Technically I could ONLY store the ActivityID, and use the forms as
user aid to filter out combo boxes, but wondered if there was a way to
do this on the table level?

Thanks.

 

[Guest]

I think I understand what you want.
Use the AfterUpdate event to set the default for the Combo Box lookup.

 

[Jamie Collins]

I think I understand what you want.
Use the AfterUpdate event to set the default for the Combo Box lookup.

Tables fo not have AfterUpdate events

Jamie.

--

 

[Jamie Collins]

Is this possible? Here's the scenario, the tracking system is meant to
track time spent on client/project/activity with each of those being
heirarchal. That is to say I have 4 tables that are linked similar to
this

Clients:
ClientID
Texts

Projects:
ProjectID
Client>Clients.ClientID
Texts

Activities:
ActivityID
Project>Project.ProjectID
Texts

Hours:
Date/StartTime/EndTime
Client>Clients.ClientID
Project>Projects.ProjectID
Activity>Activities.ActivityID

My question is, is it possible to use the Combo Box lookup option to
filter the Row Source for Project/Activity based on the value in the
previous field? or is this more of a form relations issue?

Technically I could ONLY store the ActivityID, and use the forms as
user aid to filter out combo boxes, but wondered if there was a way to
do this on the table level?

I'm having trouble figuring out your schema.

Remember the design principle that a table models either an entity or
a relationship but not both.

I think you have omitted some tables: you have entity tables for
'clients', 'projects', and 'activities' but have omitted the
relationship tables between 'clients' and 'projects' ('contracts'?)
and between 'projects' and 'activities' ('deliverables'?), so we don't
know the relationship types (1:1, 1:0..1, 1:m, m:m, etc).

The following SQL (ANSI-92 Query Mode syntax) assumes m:m throughout:

CREATE TABLE Clients (
ClientID INTEGER NOT NULL UNIQUE
)
;
CREATE TABLE Projects (
ProjectID INTEGER NOT NULL UNIQUE
)
;
CREATE TABLE Activities (
ActivityID INTEGER NOT NULL UNIQUE,
ActivityDescription VARCHAR(30) NOT NULL UNIQUE
)
;
CREATE TABLE Contracts (
ProjectID INTEGER NOT NULL
REFERENCES Projects (ProjectID),
ClientID INTEGER NOT NULL
REFERENCES Clients (ClientID),
UNIQUE (ProjectID, ClientID)
)
;
CREATE TABLE ProjectScope (
ProjectID INTEGER NOT NULL
REFERENCES Projects (ProjectID),
ActivityID INTEGER NOT NULL
REFERENCES Activities (ActivityID),
UNIQUE (ProjectID, ActivityID)
)
;
CREATE TABLE Deliverables (
ProjectID INTEGER NOT NULL,
ActivityID INTEGER NOT NULL,
FOREIGN KEY (ProjectID, ActivityID)
REFERENCES ProjectScope (ProjectID, ActivityID),
StartDate DATETIME NOT NULL,
EndDate DATETIME,
CHECK (StartDate < EndDate),
UNIQUE (ProjectID, ActivityID, StartDate)
)
;

In this schema, a 'deliverable' for a project may only be one of those
pre-defined in 'ProjectScope'. Is this what you meant by "do this on
the table level"?

Jamie.

--

 

[Yvonne Anderson]

What it is this group, and why is it appearing on my computer?

Yvonne Michele Anderson
(e-mail address removed)

 

[Guest]

Silly me, I was assuming that the data entry was using a form/subform.

 

[Jamie Collins]

Silly me, I was assuming that the data entry was using a form/subform.

Agreed, when the OP states, "I could use forms but wondered if there
was a way to do this on the table level" in the tablesdbdesign group,
it probably is a bit silly to post a forms-related answer

Jamie.

--