How to link form and subform w/ Combo

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm new to Access and don't have experience w/ database logic nor coding.

1) I have some tables:

I have the PROJECT table with the Project_ID field and others.

I’ve created the PROJECT ACTIVITIES table, containing Project_ID field and
Activities field.

PROJECT table and PROJECT ACTIVITIES table have a one-to-many relationship
made checking the “enforce referential integrity†box.

2) I have a Form

Form is bound to PROJECT table

In the form I’ve created:

An unbound ComboBox (no control source) that uses “Row source type†equal
to Table/Query

and “Row source†equal to SELECT PROJECT.Project_ID, PROJECT.Project_Name
FROM PROJECT ORDER BY [Project_Name];

I’ve added the PROJECT and PROJECT_ACTIVITIES tables to the query builder
(they show the one to many relationship).

3) I’ve created a subform that has the following properties:

Subform “source object†is “activities subform†(created using subform
wizard, choosing PROJECT ACTIVITIES table fields and defining “show <SQL
Statement> for each record in PROJECTâ€)

Subform “link child fields†is “Project_IDâ€

Subform “link master fields†is also “Project_IDâ€

When I click where the rulers meet on the subform datasheet view, there’s
another property box. It is “record source†field and I set it to

SELECT PROJECT_ACTIVITIES.Project_ID, PROJECT_ACTIVITIES.activities,
FROM PROJECT INNER JOIN PROJECT_ACTIVITIES ON PROJECT.Project_ID =
PROJECT_ACTIVITIES.Project_ID;

Still nothing happens when I pick a project in the combobox. The
corresponding activities (I’ve filled out some on the activities table for
testing) don’t show on the subform.

The only way something happens is when I use the record navigator on the
bottom of the form. But that's not my purpose.

I thought that by doing all of the above, I’d have the subform values to
update automatically when I pick a project in the combobox.

What am I missing? I can’t think of anything.

Thank you for your help.
Antonio Machado.
 
what's the name of the combo? is it Project_ID?
I'm new to Access and don't have experience w/ database logic nor coding.

1) I have some tables:

I have the PROJECT table with the Project_ID field and others.

I’ve created the PROJECT ACTIVITIES table, containing Project_ID field and
Activities field.

PROJECT table and PROJECT ACTIVITIES table have a one-to-many relationship
made checking the “enforce referential integrity†box.

2) I have a Form

Form is bound to PROJECT table

In the form I’ve created:

An unbound ComboBox (no control source) that uses “Row source type†equal
to Table/Query

and “Row source†equal to SELECT PROJECT.Project_ID, PROJECT.Project_Name
FROM PROJECT ORDER BY [Project_Name];

I’ve added the PROJECT and PROJECT_ACTIVITIES tables to the query builder
(they show the one to many relationship).

3) I’ve created a subform that has the following properties:

Subform “source object†is “activities subform†(created using subform
wizard, choosing PROJECT ACTIVITIES table fields and defining “show <SQL
Statement> for each record in PROJECTâ€)

Subform “link child fields†is “Project_IDâ€

Subform “link master fields†is also “Project_IDâ€

When I click where the rulers meet on the subform datasheet view, there’s
another property box. It is “record source†field and I set it to

SELECT PROJECT_ACTIVITIES.Project_ID, PROJECT_ACTIVITIES.activities,
FROM PROJECT INNER JOIN PROJECT_ACTIVITIES ON PROJECT.Project_ID =
PROJECT_ACTIVITIES.Project_ID;

Still nothing happens when I pick a project in the combobox. The
corresponding activities (I’ve filled out some on the activities table for
testing) don’t show on the subform.

The only way something happens is when I use the record navigator on the
bottom of the form. But that's not my purpose.

I thought that by doing all of the above, I’d have the subform values to
update automatically when I pick a project in the combobox.

What am I missing? I can’t think of anything.

Thank you for your help.
Antonio Machado.
Click to expand...
 
By name you mean "control source"?

It's control source is "Project", returning the projects' column on
tblProject (so people can select by project name).

It's row source is defined on my first question.

Thanks for your help.
Antonio Machado.

Ivano via AccessMonster.com said:
what's the name of the combo? is it Project_ID?
I'm new to Access and don't have experience w/ database logic nor coding.

1) I have some tables:

I have the PROJECT table with the Project_ID field and others.

I’ve created the PROJECT ACTIVITIES table, containing Project_ID field and
Activities field.

PROJECT table and PROJECT ACTIVITIES table have a one-to-many relationship
made checking the “enforce referential integrity†box.

2) I have a Form

Form is bound to PROJECT table

In the form I’ve created:

An unbound ComboBox (no control source) that uses “Row source type†equal
to Table/Query

and “Row source†equal to SELECT PROJECT.Project_ID, PROJECT.Project_Name
FROM PROJECT ORDER BY [Project_Name];

I’ve added the PROJECT and PROJECT_ACTIVITIES tables to the query builder
(they show the one to many relationship).

3) I’ve created a subform that has the following properties:

Subform “source object†is “activities subform†(created using subform
wizard, choosing PROJECT ACTIVITIES table fields and defining “show <SQL
Statement> for each record in PROJECTâ€)

Subform “link child fields†is “Project_IDâ€

Subform “link master fields†is also “Project_IDâ€

When I click where the rulers meet on the subform datasheet view, there’s
another property box. It is “record source†field and I set it to

SELECT PROJECT_ACTIVITIES.Project_ID, PROJECT_ACTIVITIES.activities,
FROM PROJECT INNER JOIN PROJECT_ACTIVITIES ON PROJECT.Project_ID =
PROJECT_ACTIVITIES.Project_ID;

Still nothing happens when I pick a project in the combobox. The
corresponding activities (I’ve filled out some on the activities table for
testing) don’t show on the subform.

The only way something happens is when I use the record navigator on the
bottom of the form. But that's not my purpose.

I thought that by doing all of the above, I’d have the subform values to
update automatically when I pick a project in the combobox.

What am I missing? I can’t think of anything.

Thank you for your help.
Antonio Machado.
Click to expand...
Click to expand...
 
no, I mean somithing control name or origin.. sorry but my version of Access
is in Italian and I don't know the translation in English. You should find it
in the control property pannel.
By name you mean "control source"?

It's control source is "Project", returning the projects' column on
tblProject (so people can select by project name).

It's row source is defined on my first question.

Thanks for your help.
Antonio Machado.
what's the name of the combo? is it Project_ID?
Click to expand...
[quoted text clipped - 56 lines]
Click to expand...
 
no, I mean somithing control name or origin.. sorry but my version of Access
is in Italian and I don't know the translation in English. You should find it
in the control property pannel.
By name you mean "control source"?

It's control source is "Project", returning the projects' column on
tblProject (so people can select by project name).

It's row source is defined on my first question.

Thanks for your help.
Antonio Machado.
what's the name of the combo? is it Project_ID?
Click to expand...
[quoted text clipped - 56 lines]
Click to expand...
 
Sorry, quite late in the night for me.. It's not the control source but the
control name: you find it in the control's property pannel.
Another point that you should check is the join that you have in the row
source. You should maybe change it so that it show all record from table
project and only the one from table activities that correspond, because if
you have no activities for one project the query won't show it. So that you
could remove the table activities in point 2). Before doing so, check if the
query is working fine.

What you mean by "The only way something happens is when I use the record
navigator on the
bottom of the form. But that's not my purpose". Is your subform a single or
continous form?
 
In the property name there is a field "Name", which is the name of the
control (combobox in this case). I named it "Combo175", but this is just the
name of the control. It doesn't change anything.

The field "Control source" is what tells where data comes from. I chose
"Project_ID field from Tblprojects.

My subform is not single nor continuous. It's "Datasheet".

By "The only way something happens..." I mean that when I use the buttons
that Access provides on the bottom of the form (the buttons used to navigate
through records) the records change. It changes from one project to the other
and the subform data also changes. But I want to select a project with the
combo and have the subform data automatically updated.

I don't understand what you I have to do exactly when you say: "Another
point that you should check ............... table activities in point 2".

What I wanted to do (and I don't know if I coded the right way) is for
tblPROJECT_ACTIVITIES to show all activities (or blank if there's no
activity) ONLY for the project selected on the combobox.

But the way it is, I choose a project on the combo and the subform doesn't
update, doesn't change anything...

Hope you could understand and help me.
Antonio Machado.
 
You need to name to combo: ID_Project otherwise you have no field in your
mother form that MS Access can recognize as the link field. Regarding the
control source I understand you did the right thing and I believe the
associated column is the one containing ID_Project.

What I mean that you should check in the control source is:
-open the control source from property pannel.
-run the query
-check if all project appear or if only the one with activities appear. In
case that it shows only the project with activities, you can solve this
changing the properties of the join between the project table and the
activities table (select the join with right click of the mouse).

In the property name there is a field "Name", which is the name of the
control (combobox in this case). I named it "Combo175", but this is just the
name of the control. It doesn't change anything.

The field "Control source" is what tells where data comes from. I chose
"Project_ID field from Tblprojects.

My subform is not single nor continuous. It's "Datasheet".

By "The only way something happens..." I mean that when I use the buttons
that Access provides on the bottom of the form (the buttons used to navigate
through records) the records change. It changes from one project to the other
and the subform data also changes. But I want to select a project with the
combo and have the subform data automatically updated.

I don't understand what you I have to do exactly when you say: "Another
point that you should check ............... table activities in point 2".

What I wanted to do (and I don't know if I coded the right way) is for
tblPROJECT_ACTIVITIES to show all activities (or blank if there's no
activity) ONLY for the project selected on the combobox.

But the way it is, I choose a project on the combo and the subform doesn't
update, doesn't change anything...

Hope you could understand and help me.
Antonio Machado.
Sorry, quite late in the night for me.. It's not the control source but the
control name: you find it in the control's property pannel.
Click to expand...
[quoted text clipped - 9 lines]
bottom of the form. But that's not my purpose". Is your subform a single or
continous form?
Click to expand...
Click to expand...
 
Back
Top