Many to many relationship

[SQLSister]

I have a table which joins two other tables in a many to many relationship. I want to create a form which displays the data from one of these tables but allows you to update the table which has the link. SO what I have is a table which holds data on proposals, a table which holds data on clients and one which relates clients to specific proposals which only contains the ID fields from the Proposal and Client tables.

I want a form which will show the basic contact info for any clients which have been identified for the open proposal and which will allow me to add more clients to that proposal.

What is the best way to go about this? I can't seem to do both the insert and view the related data at the same time.

 

[RoyVidar]

Hi!

I think the usual way to do this, is to use the form wizard to create a main form with a subform. Main form proposal, subform the junction table (or a query including the clientinfo).

Then use a (wizard crated on the client) combo to enable you to select clients, and bind it to the junction tables client identificator.

This will enable you to add existing clients to the proposal, but not add new clients (for that, you could use the not in list event of the combo to add it, see KenReays faq faq702-4283)

Roy-Vidar

 

[JeremyNYC]

SQLsis,

I dealt with this issue in a database yeasrs ago. I couldn't find it, so I spent a few minutes reconstructing it. I think this is how I did it back then, but I honestly don't remember.

I built the example with tblPerson, tblAddress, and tblPersonAddress, just because I had a testing database with the first two tables already in there. So the task was to build frmPerson with frmPersonAddressSub that always shows all records in tblAddress and indicates whether each address applies to the person shown on frmPerson and give an easy mechanism for the user to toggle that state.

Here's how I did it:
Make frmPerson.

Make the query:
SELECT Max(IIf([personid]=[forms]![frmperson]![txtPersonID],"yes","no")) AS Include, tblAddress.AddressID, tblAddress.StreetAddr
FROM tblAddress LEFT JOIN tblPersonAddress ON tblAddress.AddressID = tblPersonAddress.AddressID
GROUP BY tblAddress.AddressID, tblAddress.StreetAddr;

This works because "yes" is 'bigger' than "no". If you need to change the output of the iif statement, you may have to change the "max" to a "min".

Make a datasheet form that displays the data in this query and save this as frmPersonAddressIDSub. Name the controls with "txt" in front of the control source name (e.g. txtPersonID).

Put the following in a module (making sure to have a reference to the dao library):

Public Function fncUpdateTblPersonAddress()

Dim db As DAO.Database
Dim strSql As String

Set db = CurrentDb
If Forms!frmperson!frmPersonAddressSub.Form!txtInclude = "yes" Then
    'delete the record for this person/address combination
    strSql = "DELETE * FROM tblPersonAddress " _
     & " WHERE PersonID = " & Forms!frmperson!txtPersonID & " AND " _
     & " AddressID = " & Forms!frmperson!frmPersonAddressSub.Form!txtAddressID & ";"
Else
    'create a record for this person/address combination
    strSql = "INSERT INTO tblPersonAddress (PersonID, AddressID) " _
     & " VALUES (" & Forms!frmperson!txtPersonID & ", " _
     & Forms!frmperson!frmPersonAddressSub.Form!txtAddressID & ");"
End If

'execute the sql created above and update the data displayed
Call db.Execute(strSql, dbFailOnError)
Forms!frmperson!frmPersonAddressSub.Requery

db.Close
Set db = Nothing
End Function

(You should definitely include error trapping in your function.)

Then just call that function from the double click event of the form and the field I've called "Include" (on the subform).

You will definitely want to tweak this some, but hopefully it can serve as a decent template.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.