How to Use the value selected in one field to limit the selectable values in another field in the sa

[Shane]

I hoping somebody can tell me how to do this or that it is not possible
in Access.

I have table (tblIssues) which references two tables, one of clients
(tblclients) and the other a reference to Projects (tblProejcts).
Clients can have multiple projects but the issue is only associated
with one client and one of their projects.

IN the tblIssues table I have a field called ClientDBID (foreign key to
tblClients) and another field called ProjectDBID (foregin key to
tblProjects). Both fields use a lookup query to select the client and
the project. This works fine as long as you know which projects belong
to which clients.

To reduce possible errors where the wrong project is selected for the
choosen Client, I want to be able to have the ProjectDBID field lookup
query automatically list only the valid projects for the choosen Client
i.e. use the value of the ClientDBID field as the filter criteria for
ProjectDBID

In the ProjectDBID field lookup query, I have tried the following
enteries in the critieria field in the query designer. It accepts the
syntax but doesn't work as expected.

[tables]![tblIssues]![ClientDBID] and [tblIssues]![ClientDBID]

The resulting SQL gives you the basic flavour of the lookup query

SELECT tblProjects.ProjectDBID, tblProjects.ProjectName
FROM tblProjects
WHERE (((tblProjects.ClientDBID)=[tblIssues]![ClientDBID]));

So what am I missing here?

Your help greatly appreciated

 

[Duane Hookom]

You state "Clients can have multiple projects" but have avoided the question
of a project having multiple clients. If a project is only for a single
client then you do not need to store the Client ID in the issues table.

 

[Shane]

Duane,

Yes your point is correct. The relationship is that a project can only
belong to 1 client and therefore the client ID actually doesn't need to
be in this table as you have pointed out. So that removes the need for
the field, but it was originally added to reduce the list of visisble
projects to the person entering the information.

I was trying to avoid building forms for this database hence the
approach.

For my own interest though and anybody else who might have a reason to
want to filter a field within a table based on the contents of an
existing field, is there a way to do this or it is it not supported.

Thanks for the prompt response.
Shane

You state "Clients can have multiple projects" but have avoided the question
of a project having multiple clients. If a project is only for a single
client then you do not need to store the Client ID in the issues table.

--
Duane Hookom
MS Access MVP

I hoping somebody can tell me how to do this or that it is not possible
in Access.

I have table (tblIssues) which references two tables, one of clients
(tblclients) and the other a reference to Projects (tblProejcts).
Clients can have multiple projects but the issue is only associated
with one client and one of their projects.

IN the tblIssues table I have a field called ClientDBID (foreign key to
tblClients) and another field called ProjectDBID (foregin key to
tblProjects). Both fields use a lookup query to select the client and
the project. This works fine as long as you know which projects belong
to which clients.

To reduce possible errors where the wrong project is selected for the
choosen Client, I want to be able to have the ProjectDBID field lookup
query automatically list only the valid projects for the choosen Client
i.e. use the value of the ClientDBID field as the filter criteria for
ProjectDBID

In the ProjectDBID field lookup query, I have tried the following
enteries in the critieria field in the query designer. It accepts the
syntax but doesn't work as expected.

[tables]![tblIssues]![ClientDBID] and [tblIssues]![ClientDBID]

The resulting SQL gives you the basic flavour of the lookup query

SELECT tblProjects.ProjectDBID, tblProjects.ProjectName
FROM tblProjects
WHERE (((tblProjects.ClientDBID)=[tblIssues]![ClientDBID]));

So what am I missing here?

Your help greatly appreciated

 

[Duane Hookom]

Sorry, I wouldn't build an applicagtion without forms. You can't filter a
field or combo box in a table view. You would need to use some code in a
form.


--
Duane Hookom
MS Access MVP

Duane,

Yes your point is correct. The relationship is that a project can only
belong to 1 client and therefore the client ID actually doesn't need to
be in this table as you have pointed out. So that removes the need for
the field, but it was originally added to reduce the list of visisble
projects to the person entering the information.

I was trying to avoid building forms for this database hence the
approach.

For my own interest though and anybody else who might have a reason to
want to filter a field within a table based on the contents of an
existing field, is there a way to do this or it is it not supported.

Thanks for the prompt response.
Shane

You state "Clients can have multiple projects" but have avoided the
question
of a project having multiple clients. If a project is only for a single
client then you do not need to store the Client ID in the issues table.

--
Duane Hookom
MS Access MVP

I hoping somebody can tell me how to do this or that it is not possible
in Access.

I have table (tblIssues) which references two tables, one of clients
(tblclients) and the other a reference to Projects (tblProejcts).
Clients can have multiple projects but the issue is only associated
with one client and one of their projects.

IN the tblIssues table I have a field called ClientDBID (foreign key to
tblClients) and another field called ProjectDBID (foregin key to
tblProjects). Both fields use a lookup query to select the client and
the project. This works fine as long as you know which projects belong
to which clients.

To reduce possible errors where the wrong project is selected for the
choosen Client, I want to be able to have the ProjectDBID field lookup
query automatically list only the valid projects for the choosen Client
i.e. use the value of the ClientDBID field as the filter criteria for
ProjectDBID

In the ProjectDBID field lookup query, I have tried the following
enteries in the critieria field in the query designer. It accepts the
syntax but doesn't work as expected.

[tables]![tblIssues]![ClientDBID] and [tblIssues]![ClientDBID]

The resulting SQL gives you the basic flavour of the lookup query

SELECT tblProjects.ProjectDBID, tblProjects.ProjectName
FROM tblProjects
WHERE (((tblProjects.ClientDBID)=[tblIssues]![ClientDBID]));

So what am I missing here?

Your help greatly appreciated

 

[John Vinson]

I was trying to avoid building forms for this database hence the
approach.

That's a little like "I'm trying to reduce the use of expressions in
my Excel spreadsheet", or "I'm trying to reduce the complexity of
driving by not using the steering gear".

Forms *ARE* the basic tools used to interact with data. Table
datasheets (or query datasheets) are not designed for that purpose and
are, frankly, inadequate.

John W. Vinson[MVP]

 

[Shane]

Guys,

Thanks for the feedback and yes forms will have to be used. It was
simply a design question and yes, from a users perspective, forms will
have to be used so I'm "hearing" all your comments and I agree with
them (like the excel example - LOL )

The short answer is it can't be done!

THanks for taking the time to comment.

Regards

Shane