Subform Not showing new records created

[apetho17]

I just updated our database to Office 2010, Access 2007. In my main form, I have a subform linked to a table, linking ID to ID. But when I create new records in the subform table, the new records do not show up in the form view. If I close the Main form and open the table that it's linked too, I can see the records created but they aren't using the same ID as what record they were created in.



I'm not a programmer - and I only have experience with this database when it comes to Access. We have had this database for 12 years and it has worked perfect in Office 2000/XP, but now that we have finally upgraded, we can't use this linked subform. In those 12 years, I have been able to fumble my way through updates and changes to this database. But I can't figure this one out.

My main table is "Orders Main" and my subform is linking my "Tech Table". My tech table is showing notes from client phone calls. I have deleted the subform and tried to recreate it. I created a relationship between the Main table and the tech table linking ID to ID.

What am I doing wrong!??? Help!

[June7]

Can you provide db, or an extract of the relevant objects, for analysis. Make copy, remove confidential data, just need some dummy records for testing.

[apetho17]

i can't attach it - it's too large, even with it stripped out of almost all data and compacted/repaired and zipped.

[apetho17]

you can download it here: client.windowstickers.biz username: access pw: help2012 - there is a copy of Office Pro 2010 Access File and Office 2000/XP Access File

[June7]

Sorry, can't figure out the url to that site. I don't know why I should need username/pw to download file. Usually just need url link to the file. That's how box.com works.

[apetho17]

i'm not using box.com. the url is linked to our server. no http://www... just type in client.windowstickers.biz ... that's why the username/pw is required to log in and grab the file. Sorry this is my only method of transferring this file to you.

[apetho17]

http://dl.dropbox.com/u/63768696/Orders%20sample.accdb
http://dl.dropbox.com/u/63768696/Orders%20sample.mdb

Here - i forgot i have dropbox on my computer...i never use it..

[apetho17]

I think if I just change the ID field in the tech table to a "number" instead of "auto-number" that that might fix my problem. What do you think? I'm sure you're laughing at the database setup! It's probably a programmer's nightmare or joke for that matter. The person who created it (not me)...was not very experienced as you can probably tell.

[apetho17]

The above worked for me. I changed the ID field to a Number instead of Auto-Number and this fixed my issue. Maybe not the best solution - but it works and we haven't had any issues since. Thank you for looking.

[June7]

That couldn't have been like that for 12 years! This is something new? So instead of ID as the unique record ID for Tech Table, it is the foreign key for ID from Order Main? Tech Table then doesn't have a unique ID which might be fine if never have need for this pk/fk link to another table. I notice Orders Table is same. Means these tables are lowest in the chain. Nothing needs a key from those tables.

I do notice some tables don't have primary key defined, such as Order Main.

Also, something I had not encountered before: Orders Table ID field is autonumber but there are duplicates in this field. Order Main and Orders Table have a link on their autonumber ID fields in the Relationships builder. I have never seen this before. I tested adding new record in Order Main and then a related record in Orders Table. I did it directly on tables. The new record in Order Main was ID 15934 but in Orders Table it shows 15936 was generated. They appeared to remain linked until I closed and reopened tables. The link was lost. I think there is a critical flaw here. Again, this could not have been in place for 12 years. Or else you have been working with very bad data. As with the Tech Table, Orders Table ID field should be number, not autonumber.

[apetho17]

Everything was linked perfectly until we upgraded to Office Pro 2010. That's when the problems started and when the tables were not showing the right data as expected. However, since changing the ID in Tech Table to a Number instead of Auto Number, the records are following the right customer.

Interesting, we haven't had an issue with the orders not following. Perhaps it is because in the properties, it's not indexed, so it allows duplicates? Not sure but it works..so I am not complaining.

Thank you for your input! I appreciate you taking the time to look and help out!

[June7]

Well, my test of the Order Main and Orders Table linking failed. You should check on that some more.