Mainform and Subform based on the same Table?

[Bauer99]

I have a Main form based on Table "Site" the main form only has a couple of fields and an Locate combobox. I have sub foms on pages underneath. One of the sub forms is based on Table "Site" too.

When I enter Header data on the Main form everything works OK. But when I enter data on the Subform based on the Table "Site" I get an Error telling me that I would create duplicate vaules in the index, primary key or Relationship.

Some things to note: when I enter data on the other subforms based on different tables everything works fine. My relationships are fine. I also checked the Indexes on all my field names and only one (the primary key [SITE_ID]) doesn't allow duplicates.
and a wierd thing: When I look at the Master/Child Links Both fields have SITE_ID;SITE_ID;SITE;ID;SITE;ID repeating along the field?????

Can you have a Subform based on the same table as the Main table? Or is something else at play??? thanx

 

[willir]

Bauer99

I suspect when you enter the subform, Access is trying to add a new record - "duplicate vaules in the index, primary key"

As for a solution, I guess my first question is why you are using a subform and main form that uses the same table? Typically, a subform is used reference a linked table. Does it make sense to use two forms? (For example, make the form visible or invisible based on security or events.)

If so, then you have to set the control reference to referenced the record in the main form. For example...

=[forms]![MainForName]![fldName]

Richard

 

[arebelli]

Probably the problem is that you put the primary key fields in the main form so you have to fill first these fields and only then you can write on subform keys.