ComboBox Query question

[JonnyRotten]

I have two tables
The first has a ID (key) field and enteries for Name
The second has a ID field (tied to the first) and then 6 fields for
activities. Called Activity1, Activity2, etc etc

I would like to make a form that when they selected their Name from a
combo box, another combobox would take its selections from a query tied
to their name and put their 6 Activities into the second combo box.
Writing the query is no problem, I just have all 7 fields it, where the
Name field is "Like NameSelection" on the form
but I cannot seem to make access take the next 6 fields and use their
data to fill the combo box.

Any advice is apperciated. Thank you!

[Rick Wannall]

If the query fueling the combobox is Select ID, Activity1, Activity2...,
this will build a rowsource showing the six activities

dim l as long
dim s as string
me.cboActivitie s.rowsource = ""
for l = 1 to 6
if l > 1 then
s = s & ";"
endif
s = s & me.cboID.column (l)
next l

me.cboActivitie s.Rowsource = s

The real question is why you would use a table with columns like Activity1,
Activity2, etc. Any time you see repeating (usually numbered) field names,
that is a tip-off that the data is not normalized properly. YOu need a
second table that contains the ID and 1 Activity field. Then you can have
as many activities as you want for any name, and listing those activities
doesn't make you do odd things such as I coded above. Then it look more
like this:

me.cboActivitie s.row source = "Select NameActivityID, ActivityID,
ActivityName From NameActivity Inner Join Activity on
NameActivity.Ac tivityID = Activity.Activi tyID Where NameActivity.ID = " &
me.lboID & " Order By ActivityName"

Here, you have a listbox of names (ID, Name), and a combobox of activities
for that name.

If you want to explore this approach more, post back.

[JonnyRotten]

This approach definatly interests me. I am just learning access and
this is my first project with tables that are relational.
So Your saying I would want 2 tables, one with a Key field and a Name
field, and the second table with a non-key number ID field, and a
Activity field
So basically NameTable Row one would be:
1 | JonnyRotten
2 | Joe Blow

TableActivity would be
1 | Made shiney in access
1 | Reseached thing on the net
1 | Had a meeting
1 | Fixed a computer
2 | Swept
2 | Made a widget

etc etc

TableName would have a 1 to many relation with Table activity, correct?

So when it comes to making a combo box, I would take the Key field from
TableName, and run my query for that number in the ID field, so my
combobox would have all that numbered items in it.

Rick Wannall wrote:

If the query fueling the combobox is Select ID, Activity1, Activity2...,
this will build a rowsource showing the six activities

dim l as long
dim s as string
me.cboActivitie s.rowsource = ""
for l = 1 to 6
if l > 1 then
s = s & ";"
endif
s = s & me.cboID.column (l)
next l

me.cboActivitie s.Rowsource = s

The real question is why you would use a table with columns like Activity1,
Activity2, etc. Any time you see repeating (usually numbered) field names,
that is a tip-off that the data is not normalized properly. YOu need a
second table that contains the ID and 1 Activity field. Then you can have
as many activities as you want for any name, and listing those activities
doesn't make you do odd things such as I coded above. Then it look more
like this:

me.cboActivitie s.row source = "Select NameActivityID, ActivityID,
ActivityName From NameActivity Inner Join Activity on
NameActivity.Ac tivityID = Activity.Activi tyID Where NameActivity.ID = " &
me.lboID & " Order By ActivityName"

Here, you have a listbox of names (ID, Name), and a combobox of activities
for that name.

If you want to explore this approach more, post back.

[JonnyRotten]

OK, I am pretty stoked here.
I have the relational tables working. I can make the query work.
Now I have my form with Name as a combobox that pulls its data from the
Names Table
Then I have my Activity ComboBox that should be pulling its information
from the query, which it kind of is.
The problem is, if I change the Name field.. the data in Activity does
not change. How can I make this happen?

[Rick Wannall]

How are you loading the Activity combobox rowsource?

[w_a_n_n_a_l_l_ -]

Pretty close. But it's 3 tables.

TableName
NameID (autonumber)
Name

TableActivity
ActivityID (autonumber)
ActivityName

TableNameActivi ty
NameActivityID (autonumber)
NameID
ActivityID

Make sense?

[JonnyRotten]

I have a query set up that takes the name from one combo box and uses
that to find all the matchine activities from the second table.

Rick Wannall wrote:

How are you loading the Activity combobox rowsource?