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]

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

Hi, Bridget.

Your current tables set does not seem to be optimal.

• You say
  "a building is associated with one address. (1:1)".
  What is the reason to make 2 tables for buildings and addresses? 1:1 relationship is worth in a case when FK-side table supposed to have 1 record or not to have record at all and the record is space consuming (e.g. Memo or OLE). In your case I don't see a reason why not to merge these tables.
• You say
  "- A building has more than one contact and a contact can be associated with more than one building. (M:M)
  - an address can be associated with more than one contact (1:M)",
  but these is impossible as soon as building is associated with 1 address only. What do you actually mean here?
• and the last but not the least. You should have an intermediate table to implement M:M relationship between tblBldg and tblContacts

[banderson]

Thank you for your reply!
The problem with the addresses in my dataset is that I'm trying to avoid duplicate entry of addresses. A building has one address, yes. Contacts that live in the building have the same address plus an apartment or suite number. So one address can be associated with a building and one or more contacts. A further complicating issue is that the contacts for a building do not always live in that building, and thus their address is different from the building address. For example, a super linked to a particular building may or may not live in the building. Same for a manager of a building. And, here is the kicker... one contact might be a vounteer in a building AND the manager of that building. This person, therefore, might have two addresses - one when wearing the volunteer hat and a different one when wearing the manager hat (b/c the management office is in a different location.)

To get around this, I was trying to use a single address table whose records could be linked to buildings and contacts of different types. I thought if I made a ContactTypeUQ table linking unique combinations of contacts and contact types, I could then associate an address for each unique combination of contact and contact type. This address could then also be linked to a building by including the addrID in the tblBuilding table. Additionally, I need to link each building with its multiple contacts. As you can see it gets complicated quickly!
Please, if you have suggestions as to a better way to set up the tables I would love advice.

Thanks again in advance.
Bridget

Hi, Bridget.

Your current tables set does not seem to be optimal.

• You say
  "a building is associated with one address. (1:1)".
  What is the reason to make 2 tables for buildings and addresses? 1:1 relationship is worth in a case when FK-side table supposed to have 1 record or not to have record at all and the record is space consuming (e.g. Memo or OLE). In your case I don't see a reason why not to merge these tables.
• You say
  "- A building has more than one contact and a contact can be associated with more than one building. (M:M)
  - an address can be associated with more than one contact (1:M)",
  but these is impossible as soon as building is associated with 1 address only. What do you actually mean here?
• and the last but not the least. You should have an intermediate table to implement M:M relationship between tblBldg and tblContacts

[FishVal]

Thank you for your reply!
The problem with the addresses in my dataset is that I'm trying to avoid duplicate entry of addresses. A building has one address, yes. Contacts that live in the building have the same address plus an apartment or suite number. So one address can be associated with a building and one or more contacts. A further complicating issue is that the contacts for a building do not always live in that building, and thus their address is different from the building address. For example, a super linked to a particular building may or may not live in the building. Same for a manager of a building. And, here is the kicker... one contact might be a vounteer in a building AND the manager of that building. This person, therefore, might have two addresses - one when wearing the volunteer hat and a different one when wearing the manager hat (b/c the management office is in a different location.)

To get around this, I was trying to use a single address table whose records could be linked to buildings and contacts of different types. I thought if I made a ContactTypeUQ table linking unique combinations of contacts and contact types, I could then associate an address for each unique combination of contact and contact type. This address could then also be linked to a building by including the addrID in the tblBuilding table. Additionally, I need to link each building with its multiple contacts. As you can see it gets complicated quickly!
Please, if you have suggestions as to a better way to set up the tables I would love advice.

Thanks again in advance.
Bridget

Well, Bridget. This is quite acceptable. But why buildings and addresses are associated with contact types rather than with contacts?

[banderson]

The addresses / buildings need to be associated with unique combinations of contacts / types (e.g. John Doe is a volunteer of one building - with one address - and also acts as a manager of another building - with another address. So address 1 is for John Doe as a volunteer and address 2 is for John Doe as a manager.) I thought making a table with unique combinations of contacts and types would allow me to do this, however, when I try to create the forms, the link table does not get updated when I enter a new Contact.

Also, when generating a form with tblBuildings as the main table, and address as a subform, I can enter a new building name, but then when I try to enter the address for this building in the subform, I get an error message that I cannot add or edit a record because a record is required in the tblAddress table.

I guess I do not understand how to work with link tables (used to get around M:M relationships) in forms. If you have any reference materials that might help with this, I would be very appreciative.
Bridget

Well, Bridget. This is quite acceptable. But why buildings and addresses are associated with contact types rather than with contacts?

[FishVal]

The addresses / buildings need to be associated with unique combinations of contacts / types (e.g. John Doe is a volunteer of one building - with one address - and also acts as a manager of another building - with another address. So address 1 is for John Doe as a volunteer and address 2 is for John Doe as a manager.) I thought making a table with unique combinations of contacts and types would allow me to do this, however, when I try to create the forms, the link table does not get updated when I enter a new Contact.

Also, when generating a form with tblBuildings as the main table, and address as a subform, I can enter a new building name, but then when I try to enter the address for this building in the subform, I get an error message that I cannot add or edit a record because a record is required in the tblAddress table.

I guess I do not understand how to work with link tables (used to get around M:M relationships) in forms. If you have any reference materials that might help with this, I would be very appreciative.
Bridget

Hi, Bridget.

Here is what I'd like to suggest you.

• concerning main form representing [tblBldg] 1:1 [tbl Addresses]
  make a query where the tables are joined
  

  SELECT tblBldg.*, [tbl Addresses].* FROM tblBldg INNER JOIN [tbl Addresses] ON tblBldg.[AddrID] = [tbl Addresses].[AddrID] 

  then build a form based on this query
• concerning subform representing building contacts, I suggest you to build it based on [tblBldgContactT ype] joined with [tblContactTypeU Q], something like
  

  SELECT tblBldgContactType.BldgID, tblContactTypeUQ.ContactID, tblContactTypeUQ.TypeID FROM tblBldgContactType INNER JOIN tblContactTypeUQ ON tblBldgContactType.ContactTypeID = tblContactTypeUQ.ContactTypeID 

  and link via MasterField = tblBldg.BldgID, ChildField = tblBldgContactT ype.BldgID of course
  surely you will get 2 comboboxes limited to list, the common approach is to add new items to list handling NotInList event (where are many posts on the forum concerning this and I as well will be glad to help you with this rather simple coding)
• and finally to the subform above mentioned oadd a subform based on [tbl Addresses]

BTW, the last question: what type is your PK fields? Autonumber?

Keep online. :)

regards
Fish

[banderson]

Hello Fish,
Thank you for these suggestions. I will try them and post back if I have any problems. I will also look for NotInList Event postings in the forum as well. (Knowing the right the search terms to use is half the battle!)
And, yes, all of my PK's are autonumber. If you have an alternate suggestion, please let me know. Elsewhere I've read about using composite primary keys in associate tables that consist of the foreign keys of the primary tables, but have yet to figure out why this is advantageous over making a new primary key.

Bridget

Hi, Bridget.

Here is what I'd like to suggest you.

• concerning main form representing [tblBldg] 1:1 [tbl Addresses]
  make a query where the tables are joined
  

  SELECT tblBldg.*, [tbl Addresses].* FROM tblBldg INNER JOIN [tbl Addresses] ON tblBldg.[AddrID] = [tbl Addresses].[AddrID] 

  then build a form based on this query
• concerning subform representing building contacts, I suggest you to build it based on [tblBldgContactT ype] joined with [tblContactTypeU Q], something like
  

  SELECT tblBldgContactType.BldgID, tblContactTypeUQ.ContactID, tblContactTypeUQ.TypeID FROM tblBldgContactType INNER JOIN tblContactTypeUQ ON tblBldgContactType.ContactTypeID = tblContactTypeUQ.ContactTypeID 

  and link via MasterField = tblBldg.BldgID, ChildField = tblBldgContactT ype.BldgID of course
  surely you will get 2 comboboxes limited to list, the common approach is to add new items to list handling NotInList event (where are many posts on the forum concerning this and I as well will be glad to help you with this rather simple coding)
• and finally to the subform above mentioned oadd a subform based on [tbl Addresses]

BTW, the last question: what type is your PK fields? Autonumber?

Keep online. :)

regards
Fish

[FishVal]

Hello Fish,
Thank you for these suggestions. I will try them and post back if I have any problems. I will also look for NotInList Event postings in the forum as well. (Knowing the right the search terms to use is half the battle!)
And, yes, all of my PK's are autonumber. If you have an alternate suggestion, please let me know. Elsewhere I've read about using composite primary keys in associate tables that consist of the foreign keys of the primary tables, but have yet to figure out why this is advantageous over making a new primary key.

Bridget

Hi, Bridget.

Everything is ok with Autonumber PK.
Personally I use only Autonumber fields as PKs. :)
And what concerning composite keys, I think the only advantage is to prevent records duplication, but the same could be easily achieved by adding multifield index.

[banderson]

Hi FishVal,
I found some code online to enable the ability to add new records to a combo box (NotInList Event). I am trying it with my address combo box, however it is not quite working. If a new address is entered into the combo box, it asks you if you want to add the new entry to the list. If you click yes, the new entry should, in theory, be added to the list. If you click no, it should prompt you to choose from the list. What happens now, is that if you click yes, it tells you that it was added to the list, but then gives an error and it tells you that it cannot add it to the list. I don't know enough about writing code yet to know where it is going wrong. Below is the code. Any insight is appreciated! Thanks in advance.
Bridget

 Private Sub Addr1_NotInList(NewData As String, Response As Integer)
    On Error GoTo Addr1_NotInList_Err
    Dim intAnswer As Integer
    Dim strSQL As String
    intAnswer = MsgBox("The address " & Chr(34) & NewData & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "Street Addresses")
    If intAnswer = vbYes Then
        strSQL = "INSERT INTO tblAddr1f([Addr1]) " & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        MsgBox "The new address has been added to the list." _
            , vbInformation, "Street Addresses"
        Response = acDataErrAdded
    Else
    MsgBox "Please choose a job title from the list." _
        , vbInformation, "Street Address"
    Response = acDataErrContinue
    End If
Addr1_NotInList_Exit:
    Exit Sub
Addr1_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume Addr1_NotInList_Exit
End Sub 

Hi, Bridget.

Everything is ok with Autonumber PK.
Personally I use only Autonumber fields as PKs. :)
And what concerning composite keys, I think the only advantage is to prevent records duplication, but the same could be easily achieved by adding multifield index.

[banderson]

The error message is:
The text you entered isn’t an item in the list.
Select an item from the list or enter text that matches one of the listed items.

It appears as though it refuses to enter the data into the table after telling you it did enter the new data.

Hi FishVal,
I found some code online to enable the ability to add new records to a combo box (NotInList Event). I am trying it with my address combo box, however it is not quite working. If a new address is entered into the combo box, it asks you if you want to add the new entry to the list. If you click yes, the new entry should, in theory, be added to the list. If you click no, it should prompt you to choose from the list. What happens now, is that if you click yes, it tells you that it was added to the list, but then gives an error and it tells you that it cannot add it to the list. I don't know enough about writing code yet to know where it is going wrong. Below is the code. Any insight is appreciated! Thanks in advance.
Bridget

 Private Sub Addr1_NotInList(NewData As String, Response As Integer)
    On Error GoTo Addr1_NotInList_Err
    Dim intAnswer As Integer
    Dim strSQL As String
    intAnswer = MsgBox("The address " & Chr(34) & NewData & _
        Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?" _
        , vbQuestion + vbYesNo, "Street Addresses")
    If intAnswer = vbYes Then
        strSQL = "INSERT INTO tblAddr1f([Addr1]) " & _
                 "VALUES ('" & NewData & "');"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        MsgBox "The new address has been added to the list." _
            , vbInformation, "Street Addresses"
        Response = acDataErrAdded
    Else
    MsgBox "Please choose a job title from the list." _
        , vbInformation, "Street Address"
    Response = acDataErrContinue
    End If
Addr1_NotInList_Exit:
    Exit Sub
Addr1_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume Addr1_NotInList_Exit
End Sub