I have some tables, trying to create a normalized many to many
situation. I the the following tables:
tblStates (US States, that is)
tblProducts
ProdStates (to link the two tables together)
The tblStates and tblProducts tables have a many to many relationship.
What is the best way to create a from to be able to select all the
applicable US States that pertain to this product? No. I am not using
a multi select field in my tables as of yet. I was avoiding that
option.

However, it seems to boggle me that I cannot wrap my mind around this
concept, relationally.

Sincerely,
WhathaveIdone?

"WhathaveIdone?" <brasus04 (AT) gmail (DOT) com> wrote in
news:12810560-2455-4d9d-b822-44a4ba261212 (AT) g6g2000pro (DOT) googlegroups.com
:

I'd create a form with a combobox to select a Product, two listboxes,
one showing the states that have been assigned to the Product and the
second listing the states not selected for that Product and a pair of
command buttons, the first to add a row of the selected Product and
State to the ProdStates table and the other to delete the row from
the Prodstates table.

I have a little demo database that shows how to code this. If you
want it, email me.

WhathaveIdone? wrote:

The usual way to do that is to have a main form bound to
tblProducts and a subform bound to tblProducts with the
productid fields linking them together.

The subform would have a combo box bound to the state field
and use tblStates as its row source.

--
Marsh

On Aug 10, 3:56*pm, Marshall Barton <marshbar... (AT) wowway (DOT) com> wrote:
Now, what I was trying to avoid is a form with 50 checkboxes that are
tied to the productID. Maybe I shouldn't be avoiding this method at
all. Any thoughts? Pros vs. Cons?
I also should mention that selecting a state means that product is NOT
available there (Its a lot easier than selecting which states it IS
available in. Usually there are only 10 or less). The results, I want
to concatenate in my report like AZ, DE, etc
That listbox idea sounds like it might work. If you do have code on
that, that would be great. I'm not a pro, but I can read the code at
least.

On Aug 10, 3:52*pm, Bob Quintal <rquin... (AT) sPAmpatico (DOT) ca> wrote:
I'd love to see your database and that option. It sounds better than
anything else I've seen.

WhathaveIdone? wrote:

You definitely do not want 50 fields bound to check box
controls. That would grosely denormalize the table or
require a lot of code to keep things straight.

Using a combo box (NOT a list box) is much cleaner and
simpler. Take a look at Bob's demo that take it a step
further by eliminating states already selected.

In case you haven't already picked up on it, using a
continuous subform makes it all look like a list of the
selected states.

--
Marsh

On Aug 11, 8:52*am, Marshall Barton <marshbar... (AT) wowway (DOT) com> wrote:
What do you mean "eliminating states already selected"? Are you
referring to the listbox of states to select from? I would like to
hear your argument on why a combobox is cleaner as well.
Also, I will try your continuous subform to see if I can visualize
what you are referring to on that part. But if you have any more
detail on what you mean, that might be helpful.

BTW, thanks for all the suggestions and input.

-WhathaveIdone?

WhathaveIdone? wrote:

I think you should try using the combo box on a continuous
subform arrangement I described earlier so you can see what
it looks like. I expect doing that will answer your main
questions.

Making the combo box only display the states that have not
yet been selected is a refinement that we can deal with
once you have settled on your user interface design.

--
Marsh