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

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

fred615

Thread Starter
Joined
Jul 21, 2007
Messages
98
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?
 

Attachments

fred615

Thread Starter
Joined
Jul 21, 2007
Messages
98
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?
I added one more file.....
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,895
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.
 

fred615

Thread Starter
Joined
Jul 21, 2007
Messages
98
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?
 

Attachments

fred615

Thread Starter
Joined
Jul 21, 2007
Messages
98
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.
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,895
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.
 

fred615

Thread Starter
Joined
Jul 21, 2007
Messages
98
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 ?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
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.
 

fred615

Thread Starter
Joined
Jul 21, 2007
Messages
98
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)..
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,895
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"?
 

fred615

Thread Starter
Joined
Jul 21, 2007
Messages
98
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..
 

Attachments

OBP

Joined
Mar 8, 2005
Messages
19,895
Does the loojup Combo have 2 columns, one for the Key Field and one for the description?
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Members online

Top