Combo boxes on a form

[Kevin]

I need some help figuring this one out. I'm sure I did something like it
awhile back

but I can seemed to find that database or remember how I did this. Any way,
the

database tables are currently as follows...

tblClients tblProperties tblProjects

ProjectID
PropertyID 1----------8 PropertyID
ClientID 1----8 ClientID |-8 ClientID
Client | PropertyLocation | Project
|__________________________| ...

The form I am working on is a project worksheet. The control source a query
of the

projects table. At the top is a combo box that is to contain all of the
client names

from tblClients. Once the user selects a client, a second combo box becomes
active

for the user to select a property. cboProperties is to only contain the
properties

associated with the selected client. At this point, the user can enter all
of the

appropriate project information.

As you can see from the diagram above, there is a one-to-many relationship
between

tblClients.ClientID and tblProperties.ClientID; a one-to-many relationship
between

tblProperties.PropertyID and tblProjects.PropertyID and I currently have a

one-to-many relationship between tblClients.ClientID and
tblProjects.ClientID. I am

thinking that putting ClienID in tblProjects is redundant but I don't think
that this

is necessary creating my problems. I could be wrong, however, but I don't
want to delete it from the table just yet if it is not necessary.

How should I go about setting up this forms RecordSource so that I can
accomplish this. I have tried setting it as a query the uses the three
tables with their inherent relationships but I do not get all of the
records...

SELECT tblClients.ClientID, tblClients.Client, tblProjects.ProjectID,
tblProjects.Project, tblClientProperties.PropertyID,
tblClientProperties.PhysicalAddress
FROM tblClientProperties INNER JOIN (tblClients INNER JOIN tblProjects ON
tblClients.ClientID = tblProjects.ClientID) ON (tblClients.ClientID =
tblClientProperties.ClientID) AND (tblClientProperties.PropertyID =
tblProjects.PropertyID);

I have tried to alter the join properties on each of the joined fields but
if I change them I get an error telling me that I have ambiguous outer
joins.

[tina]

okay, so one client may have many properties, and each property belongs to
one client. one property may have many projects, and each project belongs to
only one property. correct so far?

if so, then you're correct in that you should not have a ClientID foreign
key field in tblProjects. since each project belongs to a specific property,
and each property belongs to a specific client, each project is already
*indirectly but clearly* linked to a specific client.

to set up a data entry form to add/edit projects for existing properties,
you don't need to link any of the tables in a query, and you only need one
small line of code in a form. suggest the following:

an unbound main form; i'll call it frmMain. add an unbound combobox control,
with tblClients (or a query based on tblClients) as its' RowSource; i'll
call the control cboClients. make sure that the BoundColumn is the ClientID
field (if you're not familiar with combo box controls, read up on them in
Access Help). add a second unbound combobox, with a query based on
tblProperties as its' RowSource; i'll call it cboProperties. add criteria to
the ClientID field in the query, as

Forms!frmMain!cboClients

make sure the BoundColumn is PropertyID. add code to cboClient's AfterUpdate
event procedure, as

Me!cboProperties.Requery

now when you choose a client from cboClients, the properties list in
cboProperties is automatically filtered to match that client.

create a second form, bound to tblProjects; i'll call it frmProjects. in
frmMain Design view, add a subform control (from the Toolbox toolbar); i'll
call it ChildProjects. set the subform control's properties as

SourceObject: frmProjects
LinkChildFields: PropertyID
LinkMasterFields: cboProperties

now when you choose a property from cboProperties, the subform will be
automatically populated with only the project records for that property.

hth
"Kevin" <no**@email.comwrote in message
news:tL*********************@fe05.news.easynews.co m...

I need some help figuring this one out. I'm sure I did something like it
awhile back

but I can seemed to find that database or remember how I did this. Any

way,

the

database tables are currently as follows...

tblClients tblProperties tblProjects

ProjectID
PropertyID 1----------8 PropertyID
ClientID 1----8 ClientID |-8 ClientID
Client | PropertyLocation | Project
|__________________________| ...

The form I am working on is a project worksheet. The control source a

query

of the

projects table. At the top is a combo box that is to contain all of the
client names

from tblClients. Once the user selects a client, a second combo box

becomes

active

for the user to select a property. cboProperties is to only contain the
properties

associated with the selected client. At this point, the user can enter all
of the

appropriate project information.

As you can see from the diagram above, there is a one-to-many relationship
between

tblClients.ClientID and tblProperties.ClientID; a one-to-many relationship
between

tblProperties.PropertyID and tblProjects.PropertyID and I currently have a

one-to-many relationship between tblClients.ClientID and
tblProjects.ClientID. I am

thinking that putting ClienID in tblProjects is redundant but I don't

think

that this

is necessary creating my problems. I could be wrong, however, but I don't
want to delete it from the table just yet if it is not necessary.

How should I go about setting up this forms RecordSource so that I can
accomplish this. I have tried setting it as a query the uses the three
tables with their inherent relationships but I do not get all of the
records...

SELECT tblClients.ClientID, tblClients.Client, tblProjects.ProjectID,
tblProjects.Project, tblClientProperties.PropertyID,
tblClientProperties.PhysicalAddress
FROM tblClientProperties INNER JOIN (tblClients INNER JOIN tblProjects ON
tblClients.ClientID = tblProjects.ClientID) ON (tblClients.ClientID =
tblClientProperties.ClientID) AND (tblClientProperties.PropertyID =
tblProjects.PropertyID);

I have tried to alter the join properties on each of the joined fields but
if I change them I get an error telling me that I have ambiguous outer
joins.