Desperate - Combo to link form/subform

[AntonioMachado]

I have 2 tables:

tblProject (ProjectID, ProjectName fields)

tblActivities (ProjectActivityID, ProjectActivity, ProjectID)

This last table was made using table wizard and telling it that there was a
relationship with tblProject.

question 1) The wizard replicated the ProjectID field in tblActivities. Is
this what we call a "foreign key"?

I used Form wizard to create a form that has a subform. User navigates
through Projects on the main form and the subform shows the ProjectActivity
field. Then, user can input/delete activities. Form is based on tblProjects
and suform is based on tblActivity. Ok, this works perfectly.

BUT, what I want to do is the following:

Currently, project field is a textbox on the main form and the user navigates
through projects using the form record navigators (Access arrow buttons on
the bottom of the form).

I want the user to select the project from a combo and automatically bring
the subform info (activity(ies)).

That's where I'm stuck. I was able to change the textbox to combobox. BUT now
when I choose a project on the combo, instead of going to that project record,
Access substitutes the current project with the project I chose on the combo.


Example: Let's say I have Project1 (ID = 1) and Project2 (ID = 2).

When I open the form, it shows Project1 on the form and Project1 activities
on the subform.

When I use the combo and select Project2, instead of going to that project's
record (ProjectID = 2 and ProjectName = Project2) it CHANGES Project1 info.
Now ID = 1 became Project2 on the ProjectName field.

Database looses all consistency. I "kill" Project 1 and now I have two
"Project2".

question 2) How to make this combo work?!?

Thanks
Antonio Machado

 

[Steve]

I am glad you implemented the tables I gave you!

Yes, ProjectID in TblActivities is the foreign key.

Open your main form in design view. Click on the subform control (the border
around the subform). Open Properties and go to the Data tab. Set both the
LinkMaster and Linkchild properties to ProjectID.

Re: Combobox

Open your main form in design view. Go back and create a textbox for the
project field. Now select the combobox and make the control source property
blank. Make the rowsource property TblProject. Make the Bound Column
property = 1. Go to the Format tab. Set Column Count to 2 and Column Width
to 0;2. Enter the following code in the AfterUpdate event of the combobox:
Dim Rst As DAO.Recordset
Set Rst = Me.RecordsetClone
Rst.FindFirst "[ProjectID] = " & Me!NameOfYourCombobox
Me.BookMark = Rst.Bookmark
Rst.Close
Set Rst= Nothing

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

 

[AntonioMachado via AccessMonster.com]

Steve, thank you for your help and time.

I did everything you said. Now it works, but partially.

When I click on the arrow in the combo Project, it shows all projects from
tblProjects (perfect). When I pick, let's say, "Project 4", it moves the form
to record number 4 (perfect) and brings "Project 4" activities in the subform.


BUT it misteriously changes the "Project 1" name in the combo Project.
"Project 1" name changes to "4".

I guess I understand WHAT is happening: When the form is opened, its on
record 1 ("Project 1"). When I pick Project 4 on the combo, its Project ID
(equal to "4") moves to Project 1...

I don't know WHY this happens. Has anything to do with Bound Column?

Thanks

I am glad you implemented the tables I gave you!

Yes, ProjectID in TblActivities is the foreign key.

Open your main form in design view. Click on the subform control (the border
around the subform). Open Properties and go to the Data tab. Set both the
LinkMaster and Linkchild properties to ProjectID.

Re: Combobox

Open your main form in design view. Go back and create a textbox for the
project field. Now select the combobox and make the control source property
blank. Make the rowsource property TblProject. Make the Bound Column
property = 1. Go to the Format tab. Set Column Count to 2 and Column Width
to 0;2. Enter the following code in the AfterUpdate event of the combobox:
Dim Rst As DAO.Recordset
Set Rst = Me.RecordsetClone
Rst.FindFirst "[ProjectID] = " & Me!NameOfYourCombobox
Me.BookMark = Rst.Bookmark
Rst.Close
Set Rst= Nothing

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

I have 2 tables:

[quoted text clipped - 52 lines]

Thanks
Antonio Machado

 

[John W. Vinson]

I don't know WHY this happens. Has anything to do with Bound Column?

No; it's the Control Source. That property should be blank. Otherwise it will
overwrite the Control Source field with the selection from the combo. If the
purpose of the combo is to find a record, it must be unbound - blank control
source.

John W. Vinson [MVP]

 

[Steve]

You missed the third sentence under Re: Combobox --
"Now select the combobox and make the control source property blank. "

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Steve, thank you for your help and time.

I did everything you said. Now it works, but partially.

When I click on the arrow in the combo Project, it shows all projects from
tblProjects (perfect). When I pick, let's say, "Project 4", it moves the
form
to record number 4 (perfect) and brings "Project 4" activities in the
subform.


BUT it misteriously changes the "Project 1" name in the combo Project.
"Project 1" name changes to "4".

I guess I understand WHAT is happening: When the form is opened, its on
record 1 ("Project 1"). When I pick Project 4 on the combo, its Project ID
(equal to "4") moves to Project 1...

I don't know WHY this happens. Has anything to do with Bound Column?

Thanks

I am glad you implemented the tables I gave you!

Yes, ProjectID in TblActivities is the foreign key.

Open your main form in design view. Click on the subform control (the
border
around the subform). Open Properties and go to the Data tab. Set both the
LinkMaster and Linkchild properties to ProjectID.

Re: Combobox

Open your main form in design view. Go back and create a textbox for the
project field. Now select the combobox and make the control source
property
blank. Make the rowsource property TblProject. Make the Bound Column
property = 1. Go to the Format tab. Set Column Count to 2 and Column Width
to 0;2. Enter the following code in the AfterUpdate event of the combobox:
Dim Rst As DAO.Recordset
Set Rst = Me.RecordsetClone
Rst.FindFirst "[ProjectID] = " & Me!NameOfYourCombobox
Me.BookMark = Rst.Bookmark
Rst.Close
Set Rst= Nothing

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

I have 2 tables:

[quoted text clipped - 52 lines]

Thanks
Antonio Machado

 

[AntonioMachado via AccessMonster.com]

You guys are geniuses! now it works perfectly. I was trying for weeks and
your help was essential. Now I know a little bit of Access.

Another question:

1) I noticed that if I insert a new project in tblProject, it won't be
inserted in tblActivity's ProjectID field. But when I open the form, choose
this project in the combo and fill out the activity field in the subform, it
automatically associates it to the project chosen in the combo. Is the code
responsible for this?

thanks

You missed the third sentence under Re: Combobox --
"Now select the combobox and make the control source property blank. "

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Steve, thank you for your help and time.

[quoted text clipped - 51 lines]

 

[John W. Vinson]

1) I noticed that if I insert a new project in tblProject, it won't be
inserted in tblActivity's ProjectID field.

Why on Earth would you expect it to be? There are presumably many records in
tblActivities; each one is related to some specific project. When you add a
new project, you surely don't want it arbitrarily assigned to all - or even
any particular - activity!

But when I open the form, choose
this project in the combo and fill out the activity field in the subform, it
automatically associates it to the project chosen in the combo. Is the code
responsible for this?

The Subform's Master/Child link field takes care of this. It's presumably
ProjectID; the way the subform works is that the value of the Master Link
Field on the mainform controls which records are displayed in the subform
(just those for that project) and what value is assigned to the Child Link
Field when a new record is created in the subform.

John W. Vinson [MVP]

 

[AntonioMachado via AccessMonster.com]

No, I don't want the new project to be assigned to any or all activities. I
just wanted that the new project be available in the tblActivity. But as you
said, it's not necessary.

I noticed that when I create a project in the tblProject, then choose it in
the combo and type an activity name in the subform, the tblActivity already
associates the activity to the new project.

I learned that the master/child link is not the only thing I need. It's also
necessary to type a code in the After Update event of the combobox in order
to link the fields .

Thank you!