none
Creating Relationships and using forms in MA RRS feed

  • Question

  • Hello all 

    I need some help with creating a database in access.

    I HAVE TWO TABLES 

    Identity(IDEN): Fields: Consumer ID[PRIMARY key], First Name, Last Name, Residence, Parish, Country

    Consumer Contact(CC):  Phone ID, Consumer ID[FOREIGN key], Phone Number, TYPE

    However I am not sure what I did wrong but the Consumer ID in the CC table is different from the one in IDEN e.g. the Consumer ID may be 8 in the IDEN table but it is 9 in the CC table so whenever I add a new record I get "You cannot add or change a record because a related record is required in the table." In addition to that I created a form to enter the data but  I am not seeing an automatic subform being creating though I  have created a relationship between the IDEN and CC table.

    I think I made a mistake when creating the CC table because I forgot to put in the Consumer ID as one of the fields and copied and pasted it later I am not  sure but this is really confusing so any assistance is well appreciated. 


    iV

    Sunday, December 13, 2015 4:38 AM

Answers

  • Hi iivii,

    >> the Consumer ID may be 8 in the IDEN table but it is 9 in the CC table so whenever I add a new record I get "You cannot add or change a record because a related record is required in the table."

    This was caused by that you checked Enforce Referential Integrity in Relationships in the picture below. If you want to Enforce Referential Integrity, value for Consumer ID in CC should be list in Consumer ID of IDEN. In other words, if 9 is not in Consumer ID in IDEN, you will get this error, and you should enter 8 which was in. If you do not need Enforce Referential Integrity, you could uncheck it, and you will not get error when you enter 9.

    >> In addition to that I created a form to enter the data but  I am not seeing an automatic subform being creating though I  have created a relationship between the IDEN and CC table.

    How did you create a Form and relationship? Did you create a form based on IDEN or CC? If you are based on CC, you would not get subform. You need to create based on IDEN, and you will get the form like below:

    For defining relationships in Access, you could refer the link below:

    # How to define relationships between tables in an Access database
    https://support.microsoft.com/en-us/kb/304466

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, December 14, 2015 2:47 AM
  • What Edward does not mention is that it is a PRIMARY TENANT of *ANY* RELATIONAL DATABASE to have enforced relationships. Do not follow his advise to not enforce referential integrity unless you REALLY REALLY know what you are doing.

    As a point of reference: I have been doing this for more than 20 years, and the case to not enforce happens maybe once every 5 years or so.


    -Tom. Microsoft Access MVP

    Monday, December 14, 2015 2:56 AM
  • ................so whenever I add a new record I get "You cannot add or change a record because a related record is required in the table."

    That suggests that you are already enforcing referential integrity, in which case if you have one or more rows in CC with a Consumer ID value of 9, there must be a row in IDEN with a Consumer ID value of 9.  Whether it is the right consumer is another matter of course.

    As regards an 'automatic' subform, if you select the IDEN table in the navigation pane and then select Form in the Create ribbon you should  get a form with a subform, but it will be a very crude representation of the data, and not something I'd recommend.  Create a form in single form view, based on an ordered query on the IDEN table, then create a form in continuous form view based on an ordered query on the CC table, and embed it in the first form as a subform, linking the subform to the parent form on Consumer ID.



    Ken Sheridan, Stafford, England


    Tuesday, December 15, 2015 6:24 PM

All replies

  • Hi iivii,

    >> the Consumer ID may be 8 in the IDEN table but it is 9 in the CC table so whenever I add a new record I get "You cannot add or change a record because a related record is required in the table."

    This was caused by that you checked Enforce Referential Integrity in Relationships in the picture below. If you want to Enforce Referential Integrity, value for Consumer ID in CC should be list in Consumer ID of IDEN. In other words, if 9 is not in Consumer ID in IDEN, you will get this error, and you should enter 8 which was in. If you do not need Enforce Referential Integrity, you could uncheck it, and you will not get error when you enter 9.

    >> In addition to that I created a form to enter the data but  I am not seeing an automatic subform being creating though I  have created a relationship between the IDEN and CC table.

    How did you create a Form and relationship? Did you create a form based on IDEN or CC? If you are based on CC, you would not get subform. You need to create based on IDEN, and you will get the form like below:

    For defining relationships in Access, you could refer the link below:

    # How to define relationships between tables in an Access database
    https://support.microsoft.com/en-us/kb/304466

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, December 14, 2015 2:47 AM
  • What Edward does not mention is that it is a PRIMARY TENANT of *ANY* RELATIONAL DATABASE to have enforced relationships. Do not follow his advise to not enforce referential integrity unless you REALLY REALLY know what you are doing.

    As a point of reference: I have been doing this for more than 20 years, and the case to not enforce happens maybe once every 5 years or so.


    -Tom. Microsoft Access MVP

    Monday, December 14, 2015 2:56 AM
  • OK. What do you suggest Tom? I am interested because I would want referential integrity to be enforced.

    iV

    Tuesday, December 15, 2015 3:08 AM
  • I'm not sure I understand the question. I suggest you enforce RI.

    -Tom. Microsoft Access MVP

    Tuesday, December 15, 2015 1:40 PM
  • ................so whenever I add a new record I get "You cannot add or change a record because a related record is required in the table."

    That suggests that you are already enforcing referential integrity, in which case if you have one or more rows in CC with a Consumer ID value of 9, there must be a row in IDEN with a Consumer ID value of 9.  Whether it is the right consumer is another matter of course.

    As regards an 'automatic' subform, if you select the IDEN table in the navigation pane and then select Form in the Create ribbon you should  get a form with a subform, but it will be a very crude representation of the data, and not something I'd recommend.  Create a form in single form view, based on an ordered query on the IDEN table, then create a form in continuous form view based on an ordered query on the CC table, and embed it in the first form as a subform, linking the subform to the parent form on Consumer ID.



    Ken Sheridan, Stafford, England


    Tuesday, December 15, 2015 6:24 PM