Adding new records in a filtered subform

[Guest]

I have a form based on Organisation table with a linked subform based on
Contact table in a one to many relationship. The subform is filtered where
Organisation.OrganisationID = Contact.OrganisationID.

After opening the subform from the parent using a command button the
relevant contacts are displayed - when I attempt to add a new contact through
the filtered subform the record is saved as usual but without the relevant
OrganisationID.

I can see why this happens but would like to know how I can pass forward the
OrganisationID to the new record.

Any help gratefully received.

 

[Marshall Barton]

I have a form based on Organisation table with a linked subform based on
Contact table in a one to many relationship. The subform is filtered where
Organisation.OrganisationID = Contact.OrganisationID.

After opening the subform from the parent using a command button the
relevant contacts are displayed - when I attempt to add a new contact through
the filtered subform the record is saved as usual but without the relevant
OrganisationID.

I can see why this happens but would like to know how I can pass forward the
OrganisationID to the new record.

How are you filtering the subform?

If you use the Link Master/Child properties, then the
linking field is filled in automatically.

 

[Guest]

Thanks Marsh. Your question has pointed me in the right direction. I didn't
have the properties set because I'd (foolishly as it turns out) just had the
subform as a separate form opened from the main form and not a subform at all.

As I already have the form set up and a display/hide toggle button on the
main form to control it is there any easy way to get Access to recognise that
this is in fact a subform and activating the master/child link.

Here's another fine mess I've got me into.

 

[Marshall Barton]

Thanks Marsh. Your question has pointed me in the right direction. I didn't
have the properties set because I'd (foolishly as it turns out) just had the
subform as a separate form opened from the main form and not a subform at all.

As I already have the form set up and a display/hide toggle button on the
main form to control it is there any easy way to get Access to recognise that
this is in fact a subform and activating the master/child link.

Ahhh, its a terminolgy error ;-)

You will have to find a mechanism to get the value from one
form to another. If you Are opening the contacts form (not
subform), then use the OpenForm method's OpenArgs argument
to pass the org id to the contacts form. the contacts
form's load event can then set the DefaultValue property of
the org id text box:

If Not IsNull(Me.OpenArgs) Then
Me.txtOrgID.DefaultValue = Me.OpenArgs
End If

If the contacts form is already open and you are just making
it visible, then you could set the default value before
making it visible:
Forms!contactsform.txtOrgID.DefaultValue = Me.txtOrgNum

 

[Guest]

Divided by a common jargon, as they say.

Many thanks Marsh - your suggestion works as described.

What would be really nice would be a forms layout screen (equivalent to
tables layout) where you could set the relationships directly between forms
and get an overview of the forms navigation - maybe in Access 12 ?

 

[Marshall Barton]

Divided by a common jargon, as they say.

Many thanks Marsh - your suggestion works as described.

What would be really nice would be a forms layout screen (equivalent to
tables layout) where you could set the relationships directly between forms
and get an overview of the forms navigation - maybe in Access 12 ?

Glad you are up and running.

I don't see a forms relationship feature coming any thime
soon. Considering all the flexibility to change the
relationships at both design and run time, I have trouble
envisioning what benefits it would provide.