Help?! Subform's child link field not synchronizing after new record added

[lucy]

Hi,

I've searched this problem and read post after post and spent hours
trying to *pinpoint* what exactly the problem is to no avail.

The situation:

4 Tabs with Master form/table on first tab and 3 child subforms on
other tabs, all linked through primary/foreign keys (autonumbers)

Everything works great for 2 subforms. Broken on tab with a nested
subform.

Every time I add a new record to the broken subform and update the
master record with the new key I can't navigate back to the tab. The
foreign key (Master link) looks fine, but the primary key (child link
field) is always Null.

How can I get the child link field to be filtered properly to the
Master Link field??? I can't get them to resynchronize.

Is there a way to force it to find the record I'm looking for? I can't
find a reason that it won't link. The child record exists in the
underlying table, the master record is saved, the link is just set to
null.

If I go to a brand new master record all is well again if I navigate
back to the previously broken record. I can click on the tab and
voila, everything is correct.

The joys of it all.

Cheers,
Jennifer

 

[lucy]

Ok, so I think I've got an idea what the problem is.

I had the primary/foreign keys on the forms for debugging purposes and
when I was setting the foreign key in the master form was setting the
key instead of the combo box bound to the foreign key.

So, it leaves me with this question. What if I didn't have linked
field on my form (in this the I have bound cmbo boxes linked to every
foreign key). But what if I didn't? I would then have to set the
value directly in the underlying table? I find this confusing. And
I'm not even sure how I would accomplish that.

So could someone tell me that? How would I link a new record to it's
parent form if the link field wasn't on the parent form?

Thanks....

Jennifer

 

[Guest]

So could someone tell me that? How would I link a new record to it's

parent form if the link field wasn't on the parent form?

Don't do that. Always include a bound control for the link field.

(david)

 

[lucy]

Thanks David,

Ok, so in this case I had 2 link fields on the master form - a bound
combo box and a text box so I could see what number I was on for
debbuging purposes.

On the child form I had the link field on the form strictly for
debugging purposes. Is it okay to refer to the link field even if it's
not on the form? I know the number should not be displayed for the
user. It should all work like magic.

Cheers,
Jennifer

 

[david epsom dot com dot au]

From stuff I've read here (Allen Browne):

On the parent, the link field should be a control to help
Access determine the data type.

On the child, the link field should be a control to avoid
a possible Access 2002/2003 bug.

You may wish to set the visible property false on these controls

Also, changing a value in a table is not the same as
updating a form. If you want to use DAO, you should
use the RecordsetClone of the form, rather than creating
an independent recordset. If you wish to use ADO, you
should use the RecordSet property of the form.

Also, changing a value on the form by using code is not
the same as typing a value into a field. Access will not
run the After Update event, and may not realise that anything
has changed. You may wish to do a form recalc after using
code to change a value on a form. If you are using a subform,
you may wish to reset the subform data source, or reset the
form in the subform control.

(david)

 

[david epsom dot com dot au]

And it would probably be a good idea to make sure both fields
are indexed, if you have not declared a relationship between
the two fields.

(david)