Dynamically changing sub-forms in code.

[Dave]

Hi all,

I hope someone can hep me here - I've been looking at this for days now, and I'm out of ideas.

I'll firstly explain how my db is structured...

Tbl: stockStation (a computer worstation)
PK: ss_StationId

Tbl: stockAsset (assets on a workstation)
PK: sa_AssetId
FK: sa_fk_ss_StationId

Tbl: stockFaults (faults for an asset)
sf_fk_sa_AssetId (links to sa_AssetId)
Tbl: stockFixtures (fixtures for an asset ie. patch info.)
fx_fk_sa_AssetId (links to sa_AssetId)
Tbl: stockNotes (notes related to an asset)
nt_fk_sa_AssetId (links to sa_AssetId)

All the tables are linked ok with integrity.

I have setup 3 forms...

Each main form links to stockStation, a subform control which links (via a form) to subformAsset and a further subform control which links (via a form) to either: faults, notes or fixtures.

I hope I'm making sense, thanks for bearing with me. each of these forma works well and each set of subforms works well too.

My problem came when I though the next logical step would be to put 3 buttons on the form and dynamically change each lower subform - on-the-fly so to speak. I copiedeach LinkMaster and LinkChild from
the original forms and inserted it onto the 'on click' event of the corresponding buttons. But each time I click on a button - I get a paremeter box which asks me for one of the keys - not the key
where I'm going... sometimes the key of the subform I'm leaving - sometimes the one I'm going to - it's bizzare.

If I just pick one to keep this message short, I'll explain what happens.

2nd subform control...

Name: frmSubformFaults
SourceObject: subformFaults
LinkChildFields: sf_fk_sa_AssetId
LinkMasterFields: text41 (yes, I know it a bad name - it's a text field on the main form which links to the first subform (the asset) sa_AssetId.

This loads up fine and displays the asset faults ok. So I now click a button to replace this 2nd subform with the 'notes' subform - whilst staying on the same asset...

The notes subform is set up like this...

Name: frmSubformNotes
SourceObject: subformNotes
LinkChildFields: nt_fk_sa_AssetId
LinkMasterFields: text41

I've got the following 'on click' code behind the notes button...

strSubformName = "subformNotes"
Me.frmSubformFaults.SourceObject = strSubformName
Me.frmSubformFaults.LinkChildFields = "[nt_fk_sa_AssetId]"

I then get a parameter box asking for sf_sk_sa_AssetId

I simply enter nothing, click ok and it works - it brings up the notes ok. Each button brings up the goods - but each ne asks me for a different key.

I've put messages in to try and find out the line whic bring up the parameter box and it's this one...

Me.frmSubformFaults.LinkChildFields = "[nt_fk_sa_AssetId]"

Does anyone have any idea why it's asking for a key for the subform I'm leaving behind?

And why if I don't put anything in the patameter box - it works fine. The box is bugging me and I obviously can't use it like this.

If I can offer more help I will do.

Many thanks for looking.

Dave

 

[Ken Snell \(MVP\)]

May be a timing issue. Try clearing out the LinkChildFields property before
you switch the SourceObject of the subform control:

Me.frmSubformFaults.LinkChildFields = ""
strSubformName = "subformNotes"
Me.frmSubformFaults.SourceObject = strSubformName
Me.frmSubformFaults.LinkChildFields = "[nt_fk_sa_AssetId]"

--

Ken Snell
<MS ACCESS MVP>

Hi all,

I hope someone can hep me here - I've been looking at this for days now,
and I'm out of ideas.

I'll firstly explain how my db is structured...

Tbl: stockStation (a computer worstation)
PK: ss_StationId

Tbl: stockAsset (assets on a workstation)
PK: sa_AssetId
FK: sa_fk_ss_StationId

Tbl: stockFaults (faults for an asset)
sf_fk_sa_AssetId (links to sa_AssetId)
Tbl: stockFixtures (fixtures for an asset ie. patch info.)
fx_fk_sa_AssetId (links to sa_AssetId)
Tbl: stockNotes (notes related to an asset)
nt_fk_sa_AssetId (links to sa_AssetId)

All the tables are linked ok with integrity.

I have setup 3 forms...

Each main form links to stockStation, a subform control which links (via a
form) to subformAsset and a further subform control which links (via a
form) to either: faults, notes or fixtures.

I hope I'm making sense, thanks for bearing with me. each of these forma
works well and each set of subforms works well too.

My problem came when I though the next logical step would be to put 3
buttons on the form and dynamically change each lower subform - on-the-fly
so to speak. I copiedeach LinkMaster and LinkChild from
the original forms and inserted it onto the 'on click' event of the
corresponding buttons. But each time I click on a button - I get a
paremeter box which asks me for one of the keys - not the key
where I'm going... sometimes the key of the subform I'm leaving -
sometimes the one I'm going to - it's bizzare.

If I just pick one to keep this message short, I'll explain what happens.

2nd subform control...

Name: frmSubformFaults
SourceObject: subformFaults
LinkChildFields: sf_fk_sa_AssetId
LinkMasterFields: text41 (yes, I know it a bad name - it's a text field
on the main form which links to the first subform (the asset) sa_AssetId.

This loads up fine and displays the asset faults ok. So I now click a
button to replace this 2nd subform with the 'notes' subform - whilst
staying on the same asset...

The notes subform is set up like this...

Name: frmSubformNotes
SourceObject: subformNotes
LinkChildFields: nt_fk_sa_AssetId
LinkMasterFields: text41

I've got the following 'on click' code behind the notes button...

strSubformName = "subformNotes"
Me.frmSubformFaults.SourceObject = strSubformName
Me.frmSubformFaults.LinkChildFields = "[nt_fk_sa_AssetId]"

I then get a parameter box asking for sf_sk_sa_AssetId

I simply enter nothing, click ok and it works - it brings up the notes ok.
Each button brings up the goods - but each ne asks me for a different key.

I've put messages in to try and find out the line whic bring up the
parameter box and it's this one...

Me.frmSubformFaults.LinkChildFields = "[nt_fk_sa_AssetId]"

Does anyone have any idea why it's asking for a key for the subform I'm
leaving behind?

And why if I don't put anything in the patameter box - it works fine. The
box is bugging me and I obviously can't use it like this.

If I can offer more help I will do.

Many thanks for looking.

Dave

 

[Dave]

Hi Ken,

Thanks for taking the time to read and reply - very kind of you.

I managed to get it to work earlier today after reading some replies in a forum - not on what the problem was, but someone recounting how they set dynamic subforms up. Someone did actually mention
clearing them out before resetting them too.

I renamed all the fk's I am using so they are all the same name - that way I didn't need to change LinkChildFields only sourceobject - and it worked. The parameter box doesn't appear any more.

I'm still not sure why it didn't work before and I suspect you may have a valid point. Whilst I was testing this morning I noticed that if I reset the sourceobject 'after' the lcf, the parameter
prompt changed - asking me on all forms for the key of the subform I was moving to. Rather than spurious keys if the order of change was reversed.

This behavior is spooky and a little bit flaky in my book.

It's a pity there's no definitive guides to how this actually works.

For now though I can move on, but I'd rather all my fk's were prefixed by the table prefix than all be the same name. So if I get definitive answers - I'll probably return and change it.

Many thanks for the help,
Dave