Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

DSaba10

Programmer
Nov 15, 2007
35
US
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:

getfile.aspx


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!
 
 https://files.engineering.com/getfile.aspx?folder=46130101-a411-4cbd-b942-ef3a29d71285&file=relationship.png
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 Fields:propertyID
Link Child Fields:propertyID

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.
 
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!

 
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.
 
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.

 
That was it. That was all I needed. I really appreciate your help.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top