Combo box Question

[bmoses]

I have three tables (one a junction table). With a form I want to add a new
member with a combo box of 'category'. The category field is on my third
table. How can I add a new member and select the category from the other
table?

Table 1 Member
MemberID

Table 2 Junction
Has MemberID
Has CatID

Table 3 Categories
CatID

of course there are a lot of other fields.

Thanks for any assistance!

 

[Klatuu]

Use Table 3 as the row source for your combo box.
I am not sure why you need Table 2. A junction table is only useful for
resolving many to many relationships between tables. Unless there is
someting I am not seeing, it looks to me that Table 3 should be a child table
to Table 1.
What I see is a member may be assigned to 1 or more categories and a
category can be assigned to 0 or more members.

 

[bmoses]

I agree...lol I have several many to many situations but this one would be a
one to many. Many Categories to one Member. For example a member may be a
Firefighter (as a category).

My form is based on the Member table. When I try to add a combox box based
on the category table I get a 'can't update file bound to...' error.

Again, I am trying to assist data entry with this field. In the future it
will be a search item (all Firefighters in x location), but that's down the
road.

Thanks again! Any thoughts?

 

[Klatuu]

Your row source for the combo should be table 3; however, there should not be
a control source. This needs to be unbound. Since you have a one to many
between tables 1 and 3, you can't bind the category to form, because it does
not exist in your table 1. What really needs to happen, is you should have a
subform based on table 3 that shows all the Categories for the current
member. Now, how you manipulate the subform becomes a little different.
Maybe you don't want the combo box at all, rather handle it in the subform.