Creating a form to handle insert multiple ids into a single table 1

[DSaba10]

I'm not terribly great with access, so I don't know if I'm even starting this off correctly. Here is the relationship of the tables:

What I'm wanting to to is create a form that will update the "PropertyPieces" table with the id from Property and id from Pieces.

The pieces table contains a small list of about 20 or items. A single "property" could have many "pieces" it's related to. I really don't know if I set the joins properly to achieve this either.

Anyway, the next thing I'd like to do is create a form that will show a single property, the list of all possible pieces, have the user select which pieces the property is related to and then have it update the "PropertyPieces" table with the appropriate ids.

Let me know if I'm totally off my rocker on my approach here, and if there are any resources you can point me to if it's just too complex.

Thanks!

 

[MajP]

Looks correct. You did the right thing by creating a "link table" PropertyPieces. This will allow you to do a many to many (A property can relate to many pieces, and A piece can relate to many properties.)

You would make the form with a main form and a subform. The main form's recordsource would be a query based on the Property table. The subform is based off the PropertyPieces table. When you add your subform the properties for subform control linking are
Link Master FieldsropertyID
Link Child FieldsropertyID

Where the master is the PropertyID from main form and Child is propertyID from subform.
If you are using the subform wizard you will be prompted for this information.

In your subform you only really need to show your piece field using a combobox.. The trick is you want to show the name but store the ID.

Rowsource: Select PieceId, PieceName from Pieces ORDER By PieceName
Number of Columns: 2
Column Widths: 0";1"
Bound Column: 1

This will hide the first column only showing the Piece name, but the bound column (PieceID) is what is stored in your subform. The propertyID is stored automatically by the subform link.

 

[DSaba10]

Thank you so much! I'm getting close, I think, but i'm getting this error:
"The LinkMasterFields property setting has produced this error: 'The object doesn't contain the Automation object 'Property.''

I thought it might have been because I had just used a "select Property.*" type of start, but removing that and listing the individual fields produced the same result.

I've tried a few things unsuccessfully. I'm so close!

 

[DSaba10]

Quick update, I fixed the automation object issue. Now it's just not saving the value(s) selected by the combo box. I do have it bound to pieceid, but the only thing it's adding is the propertyID to my propertypieces table. I'm also seeing that when I move to the next record on the main form, the selections I've made in the subform for the previous "property" are still highlighted rather than none.

 

[MajP]

Now it's just not saving the value(s) selected by the combo box. I do have it bound to pieceid, but the only thing it's adding is the propertyID to my propertypieces table.

If it is creating a record with the propertyID than it sounds like you have setup the Main form subform link correctly. But when you say this

the selections I've made in the subform for the previous "property" are still highlighted rather than none.

That says it is not working so I am confused.

If the linking is correct. When you add a new record into the subform it will automatically add the PropertyID from the main form into the PropertyID field of the subform (if you show it or not). When you move to a new property it will filter the subform to only the pieces associated to that property.
Since all the pieces are showing and not filtered that tells me your link master / child is not correct.

 

[MajP]

Take a look at the demo. Not pretty but works.

 

[DSaba10]

That was it. That was all I needed. I really appreciate your help.