managing multiple related subforms and many to many relationships

[banderson]

Hello I'm new to developing databases in Access and using Access2003. I have a dataset with a few many to many relationships that I have created linking tables for to make multiple 1:M relationships.
My problem is trying to figure out a set of forms and subforms for this data in which the users can enter new data in a straightforward way. In my dataset there are: Buildings, Contacts, addresses for buildings and contacts, and different types of contacts. The contact types are super, manager, and volunteer.
Here are the relationships:
- A building has more than one contact and a contact can be associated with more than one building. (M:M)
- contact can be more than one type (1:M)
- an address can be associated with more than one contact (1:M).
- a building is associated with one address. (1:1)

Here is how I set up my tables:

tblBldg
BldgID PK
BldgName
(other unique bldg attributes)
AddrID FK

tblContacts
ContactID PK
ContactNmLast
ContactNmFirst
(other unique contact attributes)

tblContactTypes
TypeID PK
TypeNm

tbl Addresses
AddrID PK
StreetAddr
City
State
ZipCode

tblContactTypeU Q
ContactTypeID PK
ContactID
TypeID

tblContactTypeA ddr
ContactTypeID
AddrID

tblBldgContactT ype
BldgID
ContactTypeID

Specifically, I tried to set up a form starting with Buildings that had subforms in which you can view the building address and the multiple contacts associated with a building and their attributes (including their addresses - a nested subform under Contacts). When I add a new building name in the main form and then try to add an address for that building in the adress subform (to be entered in the address table) it will not let me b/c there is not already an ID for the Address that I want to add. Also, when I try to add a contact and its related data in the contact subform, the linking tables do not update (the tbl BldgContactType table or a record is not added to the tblContactTypeU Q table). Same for the table linking ContactTypeID and AddrID.
Please advise!
thanks in advance.
Bridget

[FishVal]

Hi FishVal.
Here is the OnClick code for the btnAddAddr in the main form:

Private Sub btnAddAddr_Click()
On Error GoTo Err_btnAddAddr_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frmAddr1"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    DoCmd.GoToRecord , , acNewRec
Exit_btnAddAddr_Click:
    Exit Sub
 
Err_btnAddAddr_Click:
    MsgBox Err.Description
    Resume Exit_btnAddAddr_Click
 
End Sub
 

This opens frmAddr1 in which I can add a new address.

Then there is a "Done" button in the frmAddr1
The code here for this button is:

Private Sub btnDone_Click()
On Error GoTo Err_btnDone_Click
 
    DoCmd.Close
 
Exit_btnDone_Click:
    Exit Sub
 
Err_btnDone_Click:
    MsgBox Err.Description
    Resume Exit_btnDone_Click
 
End Sub
 

What happens after I click "Done" is that the frmAddr1 closes and you are back looking at the main form. However, the new address is not showing in the control and when i click on the control to select it, the form jumps back to the first record.

Bridget

Hi, Bridget.

Plz post where combobox [Addr1] is located. Include the names of all forms in form/subform nesting.
And BTW did you try the code for NotInList event I've posted in msg#18 ?

[banderson]

Hello FishVal,
Sorry for long-delayed response. I finally bought a VBA for beginners book and this is all starting to make more sense. And, yes, the code you gave me for the notinlist event worked! Thank you for having taken the time to respond to my questions!
Bridget

Hi, Bridget.

Plz post where combobox [Addr1] is located. Include the names of all forms in form/subform nesting.
And BTW did you try the code for NotInList event I've posted in msg#18 ?

[FishVal]

Hello FishVal,
Sorry for long-delayed response. I finally bought a VBA for beginners book and this is all starting to make more sense. And, yes, the code you gave me for the notinlist event worked! Thank you for having taken the time to respond to my questions!
Bridget

You are welcome.

Regards,

Fish