many to many relationship/with form wizard

[thomasf]

I have been trying to use Microsoft Access to create a database. I keep running into the same problem with a many to many relationship. I create a resolution table that contains both primary keys from the other tables and then link them. My problem is when I try to use the form wizard to create a form where I can enter info from one side of the many to many and enter then enter the corresponding information from the other side. It will not let me update the information on the subform. It gives me an error "unable to update". I look at the linking table and it has not been updated but the information has been saved in the regular table. This is driving me crazy!! Does anyone know any way to get around this, or am I doing something wrong. Any suggestoins would be greatly appreciated. Thank you.

 

[DougP]

Get rid of All Relationships.<br>
Then you will have much better luck.<br>
<br>
See relationships were designed in a time when 486 CPU's were king.<br>
It sets up a key so that data is retreived very fast.<br>
<br>
On a Pentium II or III machine which can find data in milliseconds you don't have to worry about speed.<br>
<br>
I Never use relationships cause they give me more headaches.<br>
<br>
<br>
<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[DougP]

Also take a look at the &quot;Orders.mdb&quot; sample that comes with Access<br>
It shows how a Form and subfrom work together.<br>
this is the correct way to add data to different tables.<br>
<br>
NOW they do use relationships here so this may shed some light on the right way to do that too.<br>
I don't use relationships for many reasons and this is just one of them.<br>
<br>
<br>
<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[elizabeth]

thomasf, the reason this doesn't work is it doesn't make sense. The entire reason for existance for the resolution table is to resolve the many-to-many relationship. In other words, you can go from a one to a many, but not a many to a one. So your form needs to be one of the many tables to the resolution table. If this doesn't make sense tell us a little more about what you are trying to accomplish.<br>
<br>
dougp, please see <A HREF="

http://www.instantweb.com/~foldoc/foldoc.cgi?relational+data+model"

TARGET="_new">

http://www.instantweb.com/~foldoc/foldoc.cgi?relational+data+model</A><br>

for more info about the evolution of the relational database model.

 

[alley]

Been there. If your database can't abide orphans, then you have only 3 choices,<br>
(besides self destruction):<br>
<br>
1. Remove all relationships. If you can live with orphans, this is the way.<br>
Otherwise, add the relations back in 1 at a time until it breaks. You may be able to<br>
determine why it died, but Access won't tell you. If you can't figure it out, leave the<br>
offending relationship out, and live with it. (Dedicated programmers will see the<br>
solution in a dream) <br>
<br>
2. Write your own code to enforce Relational Integrity.<br>
<br>
3. Use 1 and 2 together.<br>
<br>
When its done, have some juice; fruit, veggie, or grain.<br>
<br>
alley<br>
<br>