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.cboActivities.rowsource = ""
for l = 1 to 6
if l > 1 then
s = s & ";"
endif
s = s & me.cboID.column(l)
next l
me.cboActivities.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.cboActivities.row source = "Select NameActivityID, ActivityID,
ActivityName From NameActivity Inner Join Activity on
NameActivity.ActivityID = Activity.ActivityID 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.