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!

 

[Al Camp]

Jonny,
The problem is in your design. There should only be one activity field in the "many"
table, rather than fields 1 thru 6.
A combo box selects the value from only one column. It can not select Col 1 in one
instance and Col 4 in another.
Here's how your tables should be set up
tblNames tblActivities
CID Camper CID Activity
12 Bob 12 Swimming
16 Mary 12 Hiiking
23 Joe 16 Canoeing
12 Canoeing
16 Swimming
23 Baseball

Now it's a simple matter to filter cboActivities by the the CID in cboCamper, and select
one of the displayed activities.

 

[JonnyRotten]

This is my first time using a relational table set up, so I am learning
as I go.
Thank you for your guidance.
How can I make an easy form for a user to update only their activities?
In your example, how could bob see all of his activities, and add,
delete, or change one in a easy manner?

 

[Al Camp]

Jonny,
Use a main form and a subform. The main form, based on my example tblCampers would
provide all the details about each Camper (name, address, phone, etc...), the subform
based on tblActivities would show any number of activities associated with Camper
indicated on the Main form. Each of those subform records can be edited, deleted, or
added to at any time.
The Main form is the ONE (Camper) side of the relationship, and the Subform
(Activities) represents the MANY side, related to each ither through the CamperID.

 

[JonnyRotten]

OK. I did this, but how can I make the subform refresh when the main
combo box is chosen?
Thanks for all your help!!!

 

[Al Camp]

Jonny,
I think your misunderstanding me. (still using the "camping" example)...
You create a form that displays all the Camper information. You might have say 80
records, one for each camper.
On that form you have a subform for entering one, or many, activities that camper is
involved with. When you want to change the activities for a camper, you go to that
campers record. The subform (associated to the camper vis the CamperID) will always
dusplay only those records associated with that camper.
You simply go into the subform, for that camper, and add, delete, or edit any of the
activity records.

That's a basic data entry form for (One) Campers and their (Many) activities...

 

[JonnyRotten]

Ok, but say I have 50 campers each with 8 activities. Thats 400
enteries they will have to seach through to find theirs. I would like
to have a combo box where they could select their name, and it would
display all of their activites in the subform.
I'm having a hard time making this happen.

 

[Al Camp]

Jonny,
You wrote...

Ok, but say I have 50 campers each with 8 activities. Thats 400
enteries they will have to seach through to find theirs.

Not true... you'd only be involved with searching the 50 Camper records. You only have
to find a Camper... the associated activities
are automatically tied to that name.

OK, if you've constructed the forms as I suggested, we can now add "convieniences" to
that main form to make finding a camper... and their activities easier.
On my website below, under Access Tips, I have a 97 and 2003 file called Combo Quick
Find.
It demonstrates how to add an unbound combo box to your form that will allow you to
find (and go to) any camper's record quickly.