Enforced One-to-one relationship in subform

[kroland]

I have a situation where I wish to merge the data from two tables onto a single form. The relationship between the two tables is one-to-one for reasons that for the moment I will skip. I am using an Access database project (.adp) to access data on an MSDE server.

The problem is that subforms assume a one-to-many relationship. I create a subform on a main form to link the two tables together. If I am in the subform, enter some data, and press return a new record is generated in the table represented by the subform, which violates the one-to-one relationship. I can stop the natural creation of a new record by disabling the "Allow Insert" property. As a result, I can only edit the entries. Great... until you try to insert a new "main" record. The moment I go to the end of the set of records I get a blank main record, but the subform disappears.

Is there a way to get both the single record limitation in the subform AND the ability to add new "main" records without the subform vanishing?

I am getting the feeling that it requires VBA code to make this work... if it is possible at all.

Kevin
Global Communications Solutions

 

[JJOHNS]

Do you have to do this with a subform? It seems to me that you could create a query with both tables, selecting the information you want from each, and then create a form from the query.

 

[kroland]

Unfortunately I already tried this. I used a "view" instead of a "query", since it is an Access "project". Unless I made a mistake, however, the form I created based on this view was not much better. I can create new records, but I can only modify the fields that belong to the "Unique Table". Since I can only pick one table for this property, the data from the other table is read-only. So not only can I NOT edit the existing records, the new record's fields are blank and stuck that way.

Other ideas?

Kevin