Many-to-Many Updating Problem 1

[toddOne]

I have a tblLoan in a many-to-many relationship with tblCoManager. tblLink contains both primary keys, LoanNo and CoManagerID respectively. In my main form when creating a new record my subform (where CoManager is selected) is NOT updating the tblLink. There is a cboCoManager in this subform (that is linked based on LoanNo) that is created using both tblLink.CoManagerID and tblCoManager.CoManager by way of a query. Is there any way to have this comboBox selection update the tblLink when a new LoanNo record is created? Thanks!

 

[sko]

Please clarify. Can a loan have more than one CoManager assigned to it?

 

[MichaelRed]

You probably beed to requery/refresh the form after the 'commit' of the new record to the db/table. You may also need to reuery/refresh the cbo.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[toddOne]

Yes,a Loan can have more than one CoManager. That is why I have creted a many-to-many relationship.

 

[lestermadoc]

I had the exact same problem. Did you find a solution yet?

 

[Alcar]

to manage a many to many relation ship between 2 tables I have done this:
I have a tblAllTasks that contains taskID TaskName
the TaskName can be the name of a company, a name of it's customers and the name of the tasks.

the other table is called tablejoin where I have:
joinID, lEntOne_ID, lEntTwo_ID

the customer of one company can also be the customer of another company. Two different customers can have the same tasks.

How would I manage this?

well to add new tasks for a customer I have created a form with two listbox two << >> buttons (add remove in the middle) and a combo box at the top.

in the combobox I select the customer, this will make appear on the right list the tasks linked to him, I select one and remove or select one from the other list and add it with the buttons. Now.. what does two button do? here:

one stored procedure to add the task to the customer
INSERT INTO dbo.tblJoin
(lEntOne_ID, lEntTwo_ID)
VALUES (@myCustID, @myTaskID)

one stored procedure to delete the task from the customer
DELETE FROM dbo.tblJoin
WHERE (lEntOne_ID = @myCompID) AND (lEntTwo_ID = @myTaskID)

after calling one or the other procedure, I refresh the right listbox.

HTH
Alcar

 

[Guest_imported]

I found an easy way to make a many-to-many relationship:

Use a third table:

Here's a one-to-many between DocPages and Changes:
[tt]
____________
____________ | Changes |
| DocPages | |----------|
|----------|1 <><>|ChangeID |
|DocPageID |================|DocPageID |
|PageNumber| |ChangeName|
|DocName | ------------
------------
[/tt]

In this example each DocPage can be associated with multiple Changes, however each DocPage cannot be designated to have multiple Changes

Now if I wanted to assign multiple Changes to each DocPage and also show that multiple DocPages could be effected by each Change, I can add another table BETWEEN the relationship of these two to store the each table's multiple possibilities:

[tt]
____________
____________ |DocChanges|
| DocPages | |----------| ____________
|----------|1 <><>|DocChngeID| | Changes |
|DocPageID |==========|DocPageID |<><> 1|----------|
|PageNumber| |ChangeID |==========|ChangeID |
|DocName | ------------ |ChangeName|
------------ ------------

[/tt]

Notice that DocPageID was removed from the Changes table since the DocChanges table is now used to store that info.

How is this many-to-many?
Well...make a Changes Form with DocPages as a subform....
Each Change will show a nice list of DocPages it is assigned to.

Now make a DocPages Form with Changes as a subform.
For each 1 DocPage you get a list of applicable Changes.

I just figured this out myself...so if you've seen it already wait a bit before replying so I can feel smart that much longer.