Form/Subform with One-to-one relationship is read-only?

[knechod]

I am working with Access 2003, with tables linked to an Oracle database. I have a main table, and a child table with a one-to-one relationship with the parent.

Parent:
RecPK: Primary Key

Child:
RecID: Primary Key, Foreign Key to Parent.RecPK

The main form is using a Tab Control, and one of the tabs displays a subform of 0 or 1 Child records.

If the RecPK value does not exist in Child.RecID, then the subform is blank, and I cannot add a record. If the RecPK does exist, then I can display the record, but I cannot change it. I have gone through the properties of the subform, and I have the properties set correctly to allow edits and additions. I have also checked the properties of the subform, adn the child, parent and source are correct.

I can go directly to the tables and add records, so it is not a table permissions problem. As I search for a solution to this, I see comments about making the tables one-to-many, then constrain the child to a single record in code, but I don't have the luxury of changing the structure.

How do I make it so the when I visit the tab, the subform allows a new record if none exists, and allows edits if the record is there?

To add a further wrinkle to the problem, the main form is instantiated multiple times, so that I can open and edit multiple records at once.

I would appreciate any ideas.

Kevin Nechodom
University of Utah

 

[Savil]

Had a similar problem with an SQL server BE linked to an Access FE using a one 2 one relationship, main form and subform where I could find nothing at all that should prevent a record update. I found an article on the internet reporting a similar problem and the fix was to create timestamp columns on the tables (don't know what the Oracle equivalent is). After having carried out the reccomendations it has worked perfectly.

 

[knechod]

Here's an interesting bit of information. I had the properties window up while looking at the subform, and I noticed that AllowAdditions, AllowEdits, and AllowDeletions were all set to False. However, when I look at the subform directly, they are all set True. Somewhere, somehow, something is changing their values. I don't know what it is, but I was able to fix this by adding code on the Tab Change event to force them back to True, and now it works!

Kevin