Form for a many-to-many relationship

[cnstarz]

I'm trying to wrap my head around how to best implement a form for this very simple database (attached). I have a database that keeps track of just first names and colors. Not every first name needs a color assigned to it, and vice versa.

So, I have three tables:

tbFirstnames
First_ID (PK)
First_Name

tbColors
Color_ID (PK)
Color

jctbFirstColor (Junction Table)
First_ID (FK)
Color_ID (FK)

I want my continuous form (fmFirstColor) to show all first name records and have a drop-down to assign a color for each record individually. That's it -- two fields. With this form, I want to be able to create new first name records with or without colors assigned to them, and I want to be able to edit first names and change what colors are assigned for each name.

Again, very simple and small database, but I'm not sure where to run with this. I want to keep everything normalized and within best practice procedures. How should I go about doing this? Is the junction table necessary or can it even be used in this instance? Thanks!

 

[MarkK]

You create a form with a recordsource of the join table. You put a combobox on the foreign key. The value of the combo is the value of the foreign key, but elements in the combo are drawn from the related table such that you select the item from the related table, effectively assigning that value to that foreign key for that record. Does that make sense?

The combo should have two columns, one is the bound column and that is the ID, and you use the columnwidths property of the combo to hide that column.

The critical properties of the combo to make this work are ColumnCount, ColumnWidths, ControlSource, RowSource, RowSourceType, BoundColumn.

 

[cnstarz]

Thanks MarkK, I already have that going for choosing the color in my attached database in the first post. But what about the first name? I wanted the first name form field to be a text box, not a combo box. The Control Source I have for the text box is "=[tbFirstNames]![First_Name]", but since the form is based off the junction table, the field just displays "#Name?". I thought that establishing a relationship between the tables and having foreign keys would allow this?

 

[MarkK]

The relationship of the color to the first name is exactly the same as the relationship of the first name to the color. The relationship is completely symmetrical. I don't see why you'd handle them differently.

I very commonly use the same subform on both parent forms, and then just conditionally hide one of the combos--the one that shows the same data as the parent.

 

[cnstarz]

The relationship of the color to the first name is exactly the same as the relationship of the first name to the color. The relationship is completely symmetrical. I don't see why you'd handle them differently.

I very commonly use the same subform on both parent forms, and then just conditionally hide one of the combos--the one that shows the same data as the parent.

If I'm using a combo box for the first name that is configured the same as the colors combo box, then the control source for the combo box will have to be First_ID. This wouldn't allow me to type in a name as it would only accept numbers. Unless I'm not following you correctly?

 

[MarkK]

I can't open the db you posted, it's too new. Save it as an older version and I'll just do what I'm talking about, and you can take a look.

But there are two foreign keys in the join table, right? So each of those keys can be treated exactly the same as the other in respect it's related table. So one is a combo on the one parent table, the other should be a combo on the other table. Then, if the form is a child of the FirstName main form, hide the FirstName foreign key in the subform, it will be the same as the record in the main form. Similarly, when the subform is a child of the Color mainform, hide the color foreignkey/combo, because it will be the same as the record in the parent.

In this way the FirstName main form shows a subform of colors selected for that name. Similarly, and symmetrically, the Color main form should show a list of first names selected for that color. When one join record is created, it points, by definition, to one first name AND one color, and thus should appear equivalently as a child of both.

 

[cnstarz]

I was able to mostly accomplish what I want by creating a query (quFirstColor) that has the FKs from the junction table, and the First_Name and Color fields from tbFirstNames and tbColors.

However, the query doesn't let me see all first names, it only lets me see first names that have colors assigned. I've solved one problem but ran into another one.

MarkK, I'm only using one, single form (continuous) with this database. I reattached the new database (Access 2010). I tried saving it as a 2002-2003 Database (.mdb) but it says "The specified sort order, General, is not supported for the target database format." I changed the database sort order to "General - Legacy", but I still get that error. Sorry, I dont know what else to do to convert it to an mdb.

 

[MarkK]

There's a database in post 8 of this thread http://www.access-programmers.co.uk/forums/showthread.php?t=263871 that shows the pattern I'm trying to describe to you. See if that makes sense.

 

[cnstarz]

I see what you're doing with that example, but I'm trying to implement just one continuous form. I'd like to avoid using subforms and keep this as basic as possible. I was able to save it as an .mdb for you to see.

 

[MarkK]

you can take a look at this, it has a datasheet, with a combo on each foreign key. I added an index to the table to not allow duplicates, so one first name can't be entered with the same color more than once . . .
Is that what you mean? If not, how does this fail or fall short?
Cheers,

 

[cnstarz]

you can take a look at this, it has a datasheet, with a combo on each foreign key. I added an index to the table to not allow duplicates, so one first name can't be entered with the same color more than once . . .
Is that what you mean? If not, how does this fail or fall short?
Cheers,

I wouldn't mind this datasheet solution, but you can't add brand new first names through the datasheet. I think that's my only complaint with this. Thanks for your help so far!

Edit: If you look at the form fmFirstColor, it's exactly what I need, except I want it to show all records in tbFirstNames, not just the ones that have colors assigned.

 

[MarkK]

but you can't add brand new first names through the datasheet

Yeah, and that's where the approach I would take, and the one I originally recommended, is use this datasheet as a subform, and then you can use it on the Firstname mainform, AND on the Color mainform. Then, when you add a new FirstName, the subform is empty, because no colors will have yet been related to that new FirstName. This is the most common way to solve that dual problem in the many-to-many relationship of editing the object itself, and editing it's relationships to the "many" other side.

Cheers,