Subform/mainform linked via 3 fields

[steinkebunch]

I have a subform linked to a main form. The subform is for data entry. 3 fields in the main form (which make up the parent tables primary key) determine what records are linked in the subform.

I used the Subform field linker to select the 3 fields in the main form and the related fields in the subform. When finished, the "Link Child Fields" and Link Master Fields" properties of the subform are similar to the following:

Link Child Fields . . . LinkChild1;LinkChild2;LinkChild3
Link Master Fields . . . LinkMaster1;LinkMaster2;LinkMaster3

The form/subform seemed to work OK. I performed data entry for 25 or so new records in the subform. However, as I got to looking at the table the subform is based on, I'm finding that data is not always writing to the correct records. Some of the nonprimary data entry fields get written to the wrong record.

So I went back to see if the Subform field linker was still correct, and when I bring up the wizard, it gives me the message "Parts of the link with missing pair fields will be ignored." I click OK and it shows me what fields are linked. All three "links" are still correct, and the fields exist, etc.

If I delete any one of the linking fields, so that the main form and subform are only linked via two fields, I don't get the linker error. However, I need all 3 fields to link, so the related table updates correctly.

Any ideas? Thanks.

 

[Aivars]

Compare the Child and Master fields types, maybe you try to link fields which have different field types?

Aivars

 

[steinkebunch]

The paired child and master field types are identical, same length, format, etc. Two pairs are text fields, and one pair is a date field.

 

[Aivars]

Enigma...

Oh, but maybe table(s) of linked subform's query is(are) related as "one to many" to master frm? And therefore you change absolutely different record as you want?

Aivars

 

[steinkebunch]

Aivars: I think you have led me to my problem. I looked at the relationship between the underlying tables. The only relationship that is set is one between LinkMaster1 and LinkChild1. The relationship type is "indeterminate". No other fields have relationships between the two tables. The database will not allow me to relate two tables by multiple fields.

All records in the master table are deleted and repopulated from a mainframe download each week, so I can't go to an autoindex field. Maybe I'll have to modify the import link specification of the master table to generate a single field that is a combonation of the 3 fields.

Any thoughts? Thanks.

 

[Aivars]

I think it doesn't matter what relationship connects tables. It doesn't affect links between main and sub forms. I was made forms with subforms which was linked by using form controls (e.g. textboxes). I can counsel you nothing...

About indeterminate relations. You created incorrect primary keys in your tables. At least one of fields which related must have primary key.

Good luck!
Aivars