Automatically update the foreign key in a subform

[RobChurch]

I have a main form that will look too busy if I put in a subform, so I added
a button that will launch the subform. The problem I'm having is that when I
add new records to this subform it doesn't automatically update the foreign
key field with the primary key value. The tables are linked properly because
if I make the subform a part of the main form the foreign key is updated
properly. This update mechanism seems to get broken once I make the subform
a standalone form via openform from the button's OnClick event. I've also
enabled referential integrity for updates.

 

[tina]

well, the update mechanism isn't "broken". a subform, by definition, is a
form that is "contained" within a subform control on a main form. when you
open a form by itself, it's simply a form - regardless of what relationships
its' underlying table is involved in - and it has no automatic link to any
other form. if you want a standalone form to behave as though it is linked
to another open standalone form, you'll have to write code to perform the
filtering and foreign key updates that are done automatically between a main
form and a subform.

if you don't want to go to the trouble, you might consider using a tab
control on your main form, to provide additional real estate so you have
room for a subform without crowding.

hth

 

[strive4peace]

Hi Rob,

"so I added a button that will launch the subform"

then, it is not called a subform... if it opens on its own, it is a form

when you open the subform, use ther WhereCondition clause to set the ID
of the foreign key...

for instance...

'~~~~~~~~~~~~~~~~~~~~~~~~
'save changes to form if they have been made
if me.dirty then me.dirty = false

if me.newrecord then
msgbox "You are not on a current record" _
,, "Cannot open form"
exit sub
end if

if isnull(me.IDfield_controlname) then
msgbox "You have not filled out the [FILL-IN-NAME] field" _
,, "Cannot open form"
exit sub
end if

DoCmd.OpenForm pFormName, , , "IDfield=" & me.IDfield_controlname
'~~~~~~~~~~~~~~~~~~~~~~~~

the skipped arguments in OpenForm are View and Filtername

"I've also enabled referential integrity for updates."

This only applies AFTER the records are created...if the parent table
has an Autonumber field, you may as well remove this, it will do nothing

"The tables are linked properly because if I make the subform a part of
the main form the foreign key is updated properly. "

That is because you must be telling the foreign key what to be by
defining MasterLinkFields and LinkChildFields


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

 

[RobChurch]

Hi Tina,

I took your advice and used the tab control. Works great. Thanks.

-Rob

 

[tina]

you're welcome

Hi Tina,

I took your advice and used the tab control. Works great. Thanks.

-Rob