1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Using Access 2016 on a Win10 PC. Have a 2 table db w/problem

Discussion in 'Business Applications' started by fred615, Jul 11, 2018.

Advertisement
  1. fred615

    fred615 Thread Starter

    Joined:
    Jul 21, 2007
    Messages:
    59
    I have previously built 3 or 4 much larger databases with up to 10-12 tables with with look-up tables with no problem. I am now creating a new DB connecting a look-up table to the main table with a 1-to-many join. I used the same join in another DB that works fine but with the new one, the lookup value is not being saved to the main table. After reviewing the tables and the relationship diagram and comparing them to an older working DB, the only difference I can see is that in the older DB, the main table and the look-up table are joined from field TypeCode to TypeCode in a 1-to-many. In the new DB, the Cat Type table is the 1 (field CCode) to the SLIDES table (field CatCode.Value) as the many connection. The SLIDES table has a field of CatCode which is comparable to the CCode field in the look-up table. Even when I edit the relationship to join to the CatCode field, it is not saved and changed from the CatCode.Value link. The CatCode.value field does NOT exist in the SLIDES table so I have no idea where it came from and why it is capturing the standard 1-to-many link. Here is a simple diagram of the two tables and the important fields as shown in the Relationship diagram. There are 5 more fields in the Main table "SLIDES".



    SLIDES Cat Type

    ---------------- -------------------------

    Slides CCode

    CatCode CatDesc

    CatCode.Value



    Yes, the CatCode.Value is indented in the actual diagram and it is connected to Cat Type(CCode). Why and why is it there in the first place ?? I don't know.... What I think needs to be connected is the CatCode field to the CCode field. But that raises the question of whether both fields need to have the exact same fieldname?

    In the relationship window, I've checked Enforce Referential Integrity and in the Join properties window I have selected the type 1 Join, as I had in the previous projects.



    (I try to attach a file with images of an older and the new DB relationship diagrams and the two windows for each if someone can tell me the best format in which to send or attach them.)



    Any thoughts?
     

    Attached Files:

  2. fred615

    fred615 Thread Starter

    Joined:
    Jul 21, 2007
    Messages:
    59
    I added one more file.....
     

    Attached Files:

  3. fred615

    fred615 Thread Starter

    Joined:
    Jul 21, 2007
    Messages:
    59
    I added one more file with the relationship diagram.
     

    Attached Files:

  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,625
    You are trying to link a Key field (CCode) which is probably an Autonumber to a sub key field (CatCode) which is a TEXT field, where it should be an Integer Type Long to match the Autonumber.
    Hence the .value where Access is trying convert the text to a number.
     
  5. fred615

    fred615 Thread Starter

    Joined:
    Jul 21, 2007
    Messages:
    59
    I took a screen shot of what is available for data types and I don't see a "integer type long" listed. Is the Large Number equivalent to it?
     

    Attached Files:

  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,625
    Probably, I would try it.
     
  7. fred615

    fred615 Thread Starter

    Joined:
    Jul 21, 2007
    Messages:
    59
    OK, this is getting confusing.. You can see on SLIDE4 what happens with Large Number. Since this db seems to be in the newer format (accdb) I have no idea why it cares about earlier versions of Access. It doesn't appear to want to let me save this change to that CatCode field.
     

    Attached Files:

  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,625
    Sorry, although I said Integer type long the actual description in the Table in Design View is
    Number - Long Integer.

    Ensure that you delete the Relationship link before making the change.
    I think that is just a warning and probabl would allow it, it maynot however be the correct type.
     
  9. fred615

    fred615 Thread Starter

    Joined:
    Jul 21, 2007
    Messages:
    59
    Another error message after entering Number - Long Integer.

    upload_2018-7-13_16-1-41.png

    Is it difference between Access versions? Like 2007 vs. 2016 ?
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,625
    I do not think it is a difference in versions.
    That message is obviously pointing to Access being messed up over that table, because you are selecting an item on the list.
    Can I suggest that you create a new table in Design mode and add 1 field and set it to Number - Long Integer to see what happens.
    Or add another field to the old table and try and set that.
     
  11. fred615

    fred615 Thread Starter

    Joined:
    Jul 21, 2007
    Messages:
    59
    OB, that is what I was thinking also. went back to the SLIDES table and deleted the CatCode line and then re-entered it as a Long Number (the ONLY category close to Number - Long Integer). I don't understand why I cannot see a Number - Long Integer. I am using Access 2016 in WIN10.. When I create a table and save it, I am asked if I want it to be backwards compatible to Access 2007 and I select NO. Is that possibly the reason for the difference in data types?
    Anyway, after entering some more data with the change from above, at least I am now seeing something being saved in CatCode column in SLIDES. It isn't the text I hoped to see nor is it the proper integer relating to the code I selected. I will also now try to add a new field and see if that makes a difference.
    The attached file shows the results of the above change (last line only)..
     

    Attached Files:

  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,625
    If it was backwards compatible (2007) I could take a look at it for you.
    Can you manually enter the code that you want in the Table version CatCode, or are you using a "Look Up"?
     
  13. fred615

    fred615 Thread Starter

    Joined:
    Jul 21, 2007
    Messages:
    59
    OB,
    I created a new field (leaving the old one in place) and linked it to MySlides and to the Slides Report. It worked like the previous trial did. A "zero" showed up saved in the Slides table at record 13. Strange thing is that record 12, which previously had the zero show up, is now clear.
    I haven't tried manually entering the total code because as soon as I type the first letter or two on my input form, the full Category description shows up but does not get saved in the table.
    File Slide6 is attached..
     

    Attached Files:

  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,625
    Does the loojup Combo have 2 columns, one for the Key Field and one for the description?
     
  15. fred615

    fred615 Thread Starter

    Joined:
    Jul 21, 2007
    Messages:
    59
    No, this is it...
    upload_2018-7-17_14-59-39.png
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...

Short URL to this thread: https://techguy.org/1212742

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice