Use ComboBox to show and fill data in a subform

[AntonioRio]

I have a table named PROJECTS that contains data related to projects, such as:

Project_ID (Primary Key)
Project_Name
Phase
Region…………and so on.

I want to:

1) Build a form with a ComboBox where the user will be able to select the
project in this ComboBox

2) Have a subform in datasheet view where users should be able to input
activities for that project (each project has more than one activity) in such
a way that each new activity is automatically saved AND related to the
project selected in the ComboBox.

A) Since I have many activities for each project

- should I build a new table (PROJECTS_ACTIVITIES) containing all the
projects (should I use the field Project_ID or Project_Name?) and its
activities?

- should I make a 1 to “infinite†relationship from table PROJECTS to table
PROJECTS_ACTIVITIES?

- What is the best way to make this new table?
(I was thinking of using a make-table query, using Project_ID instead of
Project_Name. Then I'd use the form’s ComboBox to pick a project and input
activities in the subform. Doing so, I would populate the projects’
activities.)

B) The ComboBox that selects the project shouldn’t be bound to the table, I
just want to use it as a command to choose projects and automatically bring
its activities

- Should this ComboBox be populated through a query?
- Should it be based on PROJECT table or on PROJECT_ACTIVITIES table?
- And Most Important: When I pick a project in this ComboBox, does it
automatically update the subform, relating them to the selected project?

I'm having trouble to code all of this!

Thank you for your time and assistance!
Antônio Machado.

 

[Arvin Meyer [MVP]]

Answers in line:

I have a table named PROJECTS that contains data related to projects, such
as:

Project_ID (Primary Key)
Project_Name
Phase
Region....and so on.

I want to:

1) Build a form with a ComboBox where the user will be able to select the
project in this ComboBox

2) Have a subform in datasheet view where users should be able to input
activities for that project (each project has more than one activity) in
such
a way that each new activity is automatically saved AND related to the
project selected in the ComboBox.

A) Since I have many activities for each project

- should I build a new table (PROJECTS_ACTIVITIES) containing all the
projects (should I use the field Project_ID or Project_Name?) and its
activities?

Yes build a new table. Use Project_ID as the foreign key.

- should I make a 1 to "infinite" relationship from table PROJECTS to
table
PROJECTS_ACTIVITIES?
Yes

- What is the best way to make this new table?
(I was thinking of using a make-table query, using Project_ID instead of
Project_Name. Then I'd use the form's ComboBox to pick a project and input
activities in the subform. Doing so, I would populate the projects'
activities.)

Just make the table and create the subform.. You do not need to populate the
many-side table with data. As your users enter their activities it will be
populated.

B) The ComboBox that selects the project shouldn't be bound to the table,
I
just want to use it as a command to choose projects and automatically
bring
its activities

- Should this ComboBox be populated through a query?

A saved query is slightly easier, but a select statement will work just as
well.

- Should it be based on PROJECT table or on PROJECT_ACTIVITIES table?

The combo box is based on the Project table. It needs to fields: Project_ID
and Project_Name

- And Most Important: When I pick a project in this ComboBox, does it
automatically update the subform, relating them to the selected project?

Use the unbound combo box on the main form to find the project with as many
details as you need to see on the main form. There is a combo box wizard
that will write the code for you. Link the Project_ID on the main form to
Project_ID on the subform.

I'm having trouble to code all of this!

As previously mentioned, there is a combo box wizard that will write the
code for you.

 

[AntonioRio]

I’m new to Access and this is all difficult for me.

Here’s what I was able to do:

1) 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) 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
(the one to many relationship appears).

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. I've set its “record source†field 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.