error 3341 or error 3022; Do or don't, I'm damned.

[Banana]

Okay, I'm kind of stumped here.

I have a subform that has a button that sends a user to a "sibling" subform on another tab page, pass some information to ensure they are adding more details to the same records rather than creating two separate record.

First time I programmed it, I got an error 3022 (keys cannot have duplicate values). I checked the query of the sibling subform and saw that the ID is from the one side table. I changed it so many table's foreign key is used. Second try, I got an error 3341 (there isn't a matching key in one side table).

After some thinking, it also occured to me that I had set the query this way to allow addition of new record which wouldn't be possible if I had the query pulling the many side key, not the one side key.

How do I get the subform to accept the ID that is being passed and create a new record using that ID?

 

[Banana]

Forgot to add one more thing; when accessing the sibling subform's query, typing in the ID causes no problem whatever. That is true whether the query uses one or many side's ID field.

Why, oh why, can't it do the same thing in form?!?

 

[Banana]

For now, I'll just "hardtype" the data via tables.

However, I really would like to have a form interface for my users to use.

Does anyone have a suggestion?

TIA.

 

[Bodisathva]

I'll take a stab at it...

Insertion in to the "one" side must be followed by an update of the original recordset prior to attempting to add records to the "many" side of the relationship. It sounds like the update isn't taking place before you try to add the second set of entries. Check your recordsets between forms...subforms and siblings accessing the same data? They'll need to be refreshed if they're already open by the time the update occurs.

 

[Banana]

Now that's a clue. I'll take a look at it and make sure that it's properly refreshed.

Thanks!

 

[Karma]

If the sibling is a subform of a subform-it is intended to store data in a related table not an extension of data in the same table- then you should link the sibling to the child in the same way as you would link the child to the parent. To get the correct syntax for the link -as access will tell you it can't join two subforms - open the forms - not design view - then open a query in design view and select the forms from the magic wand build wizard, you'll find them under forms loaded forms. Copy the paths into the master and child properties of the sibling subform.

If the sibling is an extension of the child i.e all the data relates to the same table then the tabs should be part of the child form and the fields spread across the tabs.

Clear as mud hey

K.