Subform wants to connect to a deleted table

[Bobp3114]

I have a form with three subforms. all is well except for recent entries in subform frmContactDetailsSubformRF, linked by StationID that says..."you cannot add or change a record because a related record is required in table "tblStationData2".
"tblStationData2" is a table that has been renamed before replacing it with "StationData"
I have rebuilt the subform...no change
There are three main forms similar to this one but covering different sections of business....all are effected!
There has been considerable changes in the database to cover recent changes in freight etc.

 

[arnelgp]

maybe the foreign key of this table has been "orphaned" due to your changes.
if you will need major changes, make a Backup first so if any thing goes wrong, like this one,
you can revert to the backup.

 

[Bobp3114]

What do you men "orphaned/ How to fix?

 

[arnelgp]

rebuild your relationship object.

 

[Bobp3114]

I am sure you are correct as if I type the info into the back end directly the error message appears as soon as I type in the "StationID". I have rebuilt the relationship, via Database Tools. Still no luck Any further help much appreciated....I am obviously not a professional developer

 

[Gasman]

Are you using table lookups?
You could just rename the table back?
All my tables are prefixed with tbl ?

 

[LarryE]

I have a form with three subforms. all is well except for recent entries in subform frmContactDetailsSubformRF, linked by StationID that says..."you cannot add or change a record because a related record is required in table "tblStationData2".
"tblStationData2" is a table that has been renamed before replacing it with "StationData"
I have rebuilt the subform...no change
There are three main forms similar to this one but covering different sections of business....all are effected!
There has been considerable changes in the database to cover recent changes in freight etc.
View attachment 116354

1. Open each sub-form in design view
2. Open the Record Source of the sub-form and make sure you don't get any reference errors and make sure you can enter new data
3. Select the control in each sub-form that has a Control Source that references the tblStationData2 table Primary Key or the new StationData Primary Key. Make sure the Default Value is NOT referencing any blStationData2 field.

It is clear that this is a Foreign Key reference problem. You need to open each sub-form and find any Foreign Key reference and make sure it is correct. Since the original table was renamed, this is going to make the process a real pain but needs to be done. In fact, ANY reference to the old table name needs to be found and corrected in queries, forms and reports.

 

[Pat Hartman]

The problem you are running into is a result of leaving the "Name Autocorrect" feature on all the time. At one point, you renamed the original table to table2 and made changes to the original table thinking that would just automatically replace the renamed version. Not what happened though. MS was "protecting" you and Name Autocorrect made a note of the name change and the next time you opened an object that referred to tablename it corrected the reference to tablename2 because it thought that was what you wanted.

This is why experts recommend that this particular "feature" be turned off since most people have no clue how it works. Then you can turn it on if you need it.

Use the dependencies feature of Access to locate all the objects you need to change. Change each table2 reference back to table. Then turn Name Autocorrect off or do some studying so you understand how it actually works. I have posted a link to the old paper. I'll try to find it and repost. If some one needs it and I don't post it by tomorrow, just jog me to dig it up

 

[isladogs]

@Pat Hartman
You say this regularly but in my opinion your statement needs qualifying.
Some experts recommend switching off Name Autocorrect but many don't.
Exactly the same 'disconnect' can be obtained by leaving Name Autocorrect off all the time.
There are also risks in enabling & disabling NAC as you suggest.

Whilst you are searching out your old paper, I'll post a link to my articles so the OP gets both points of view:

Use Name AutoCorrect Successfully

This article explains how the Name AutoCorrect feature works in Access, its limitations and how to use it successfully in your applications

www.isladogs.co.uk

 

[Pat Hartman]

None of the experts recognized the issue. It would of course be better if people understood how Name Autocorrect works but they don't. The PPT is from a talk I gave at a user group. It pulls some stuff out of the original MS document

 

[isladogs]

Yes I know. I’ve read it previously.

 

[Bobp3114]

Have read mentioned articles...tried to find any reference to "tblStationData2"...none found...rebuilt forms etc...no change
Need help at my self taught level.

 

[Pat Hartman]

tried to find any reference to "tblStationData2"...none found

That's because tblStationData2 is YOUR object name and the error message is telling you that it cannot be found. Obviously because it has been deleted. Therefore, you need to change all references to tblStationData2 to whatever the new name of the object is.

 

[Bobp3114]

I cannot find any such references. If it helps, the problems only exists when I enter a new customer and then try to add a contact. There is no problem in adding a contact to older customers

 

[Gasman]

So how are you adding new contacts? or new customers.
Is the error on customers or contacts?

 

[Bobp3114]

New contacts are added via a subform of the main form of customers. Customers are added via a separate form. If I look at the Contacts table the new contacts are listed but the joining StationID is missing. If I try adding it in datasheet view I get the error message "you cannot add or change a record because a related record is required in tblStationData2.....tblStationData2 has been deleted!

 

[Gasman]

So show your relationships.

 

[Pat Hartman]

It is not always easy to find the source of an error like this. You have to check various places such as the sort property. Search the code, search the query. If you want to post the database, someone might be able to find it for you.

PS be sure to obfuscate any sensitive data before you post a database.