Announcement

Collapse
No announcement yet.

Relationships not working...

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Relationships not working...

    OK, I have an Access 2007 Database, design as below. Cascade update fields is on every relationship, cascade delete records is not. The Join Type of all relationships is type 1: include only rows where both join fields are equal. The primary keys for Supplier, Category and Sales are all autonumber and they link to long integer fields in the "Many" side of the relationship. The "Category" field in the "Supplier" table looks up from the "Categories" table - it's the main product category which is bought from that supplier. As you can see from the diagram, there's no referential loop.

    I have added two supplier records and several category records. When I attempt to add a "Product", I enter the details. Where a detail comes from a table (category, supplier) I use a drop-down-box to enter it, and select an available value. I display the text name of the detail, but store the "Bound Column" as the primary key. When I try to leave the new record, and commit it to the DB, it says "Unable to add a record now because a related entry is required in table <nnnn>", where "nnnn" alternates randomly between supplier and category.

    The "Default Value" for the fields is "Null" so that it doesn't pre-select a value for you and you MUST enter one.

    I've done this a thousand times and I cannot for the life of me think what I might be doing wrongly. I have tried all three "Join Types" and it makes no difference. Anyone any clue at all? Please? It's doing my nut in.
    Attached Files
    Last edited by Stonelaughter; 22nd February 2007, 22:16.


    Tom
    For my own and your protection, I do not provide support by private message under any circumstances. All such messages will be deleted and ignored.

    Anything you say will be misquoted and used against you

  • #2
    Re: Relationships not working...

    I'm going at this from Access 2003 in case any of the terminology doesn't match.

    If you don't enforce integrity on the relationships, what values get put in those fields? Good ones or bogus ones? Trying to see if there is a setup issue w/ your drop downs.

    Speaking of drop downs, these list or combo and are these config'd off the detail table's field name in the Lookup tab? Maybe a pict of that tab for one field would help? Or these in a form you've built?
    Cheers,

    Rick

    ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

    2006-2099 R Valstar. This post is offered "as is" for discussion purposes only with no express or implied warranty of any kind including, but not limited to, correctness or fitness for use. Nothing herein shall be construed as advice. Attempting any activity based on information in this post is done at your own risk.

    Comment


    • #3
      Re: Relationships not working...

      See attachment for details of the drop-down-box.

      If I don't enforce referential integrity, I get sensible values...
      Attached Files


      Tom
      For my own and your protection, I do not provide support by private message under any circumstances. All such messages will be deleted and ignored.

      Anything you say will be misquoted and used against you

      Comment


      • #4
        Re: Relationships not working...

        But if I try to re-enforce referential integrity, having entered the data using the controls and existing data, the bloody thing refuses saying that the data is missing in one table... **GRINDS TEETH**

        Except, look at the data - it all matches beautifully. WTH is wrong with me?!
        Attached Files
        Last edited by Stonelaughter; 23rd February 2007, 00:33.


        Tom
        For my own and your protection, I do not provide support by private message under any circumstances. All such messages will be deleted and ignored.

        Anything you say will be misquoted and used against you

        Comment


        • #5
          Re: Relationships not working...

          Couple things to try:

          1st, does setting the bound column = 1 make a difference? 0 is the ListIndex (in Access 2003) property which I don't believe is what you want.

          2nd, if #1 doesn't fix it, stay w/ #1 and also change your row source to a select stmt vs. a table. Hit the ... when on that property and select the two fields (id, desc).
          Cheers,

          Rick

          ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

          2006-2099 R Valstar. This post is offered "as is" for discussion purposes only with no express or implied warranty of any kind including, but not limited to, correctness or fitness for use. Nothing herein shall be construed as advice. Attempting any activity based on information in this post is done at your own risk.

          Comment


          • #6
            Re: Relationships not working...

            Originally posted by rvalstar View Post
            Couple things to try:

            1st, does setting the bound column = 1 make a difference? 0 is the ListIndex (in Access 2003) property which I don't believe is what you want.

            2nd, if #1 doesn't fix it, stay w/ #1 and also change your row source to a select stmt vs. a table. Hit the ... when on that property and select the two fields (id, desc).
            Fantastic advice thank you. Option 1 cured it. I thought that column zero was the leftmost column. I have no idea what a ListIndex property is. LOL.

            Next question then, seeing as you seem to know something of it:

            The join types talk about "including" rows in three ways as follows:
            • Only include rows where the join fields from both tables are equal.
            • Include ALL rows from "table 1" and only those rows from "table 2" where the join fields are equal.
            • Include ALL rows from "table 2" and only those rows from "table 1" where the join fields are equal


            I understand the terms... but "include" the rows in WHAT?! I simply don't get what the three join types are getting at. So - can you explain WTH it means, because Access Help can't.

            BTW - I thoroughly understand 3rd Normal Form and can see databases in my head when I encounter business situations that would benefit from one. I still don't get these join types.

            Thanks


            Tom
            For my own and your protection, I do not provide support by private message under any circumstances. All such messages will be deleted and ignored.

            Anything you say will be misquoted and used against you

            Comment


            • #7
              Re: Relationships not working...

              I'm glad to hear you're up on your normal forms. I find most DBAs and Database Developers haven't a clue.

              Stick w/ the first option (inner join). Codd and Date will roll over in their graves (Date is still very much alive) if you choose the left or right outer join options.

              For some explanations on the choices:

              Cheers,

              Rick

              ** Remember to give credit where credit is due and leave reputation points sigpic where appropriate **

              2006-2099 R Valstar. This post is offered "as is" for discussion purposes only with no express or implied warranty of any kind including, but not limited to, correctness or fitness for use. Nothing herein shall be construed as advice. Attempting any activity based on information in this post is done at your own risk.

              Comment

              Working...
              X