Datasheet form subform workaround (97)

Topic

New Reply

Dear members of the board,
suppose I would create a form to ‘fake’ a setup with a datasheet form and datasheet subforms, with a (rough) setup like a form including both:
– one datasheet showing the ‘main’ data
– some embedded datasheet form(s) or subform(s) of which the data source depends on the active record in the first datasheet.
Without heading into technical details (I assume it must be possible using some VBA), I wonder: are there any particular problems I’ll probably face? And… does anyone have seen already examples of this approach?
Hasse

Reply | Quote

Replies

Actually, if I understand what you are trying to do, you shouldn’t need any VBA to do what you are asking. The Main form would be a continuous form that looks like a datasheet for the first row of the form, and then a subform control that refers to a subform that is displayed in datasheet form. If you have multiple subforms, they would be stacked one beneath the other, or possibly side by side, though that could get ugly. Does that sound like what you are attempting to achieve? That would look something like the subdatasheets that you can use in 2000 and up, but cause nasty performance problems.

Reply | Quote

Hey, Wendell,
I’m just not quiet sure how to understand ‘a datasheet for the first row of the form’ but that’s probably because of my own wobby writing…
Just to be sure, I’ll rewrite my question: in a continuous or datasheet form, Access doesn’t allow subforms.
As a workaround I would like to assemble in one form (1) a continuous form (or an ’empty’ one with an embedded datasheet form) (2) in the same form (e.g. in footer continuous form) another embedded form (subform not allowed!) showing all records related to the active record in the first form. Now I was wondering: how I get both controls linked? (Layout to avoid as much uglyness as possible is the next step )
I’ll try to post an example later on, and in any case read over your post again this evening.
Thanks!
Hasse

Reply | Quote

Hi Hasse,
You are absolutely correct – you cannot have a subform control on a continuous form. Momentary lapse on my part
So I’m afraid you will need to resort to VBA, and even then it will get ugly – there are two real issues if you must have a continuous form:

• subforms can have many records so you would have to put a set number of controls to display a fixed number of records, and
• controls on a subform cannot be addressed on a record-by-record basis.[/list]I’ll do a bit of research and see if anybody has a workaround, but at this point it sounds like lots more work than I would want to tackle.

Reply | Quote

Hi Hasse,
You are absolutely correct – you cannot have a subform control on a continuous form. Momentary lapse on my part
So I’m afraid you will need to resort to VBA, and even then it will get ugly – there are two real issues if you must have a continuous form:

• subforms can have many records so you would have to put a set number of controls to display a fixed number of records, and
• controls on a subform cannot be addressed on a record-by-record basis.[/list]I’ll do a bit of research and see if anybody has a workaround, but at this point it sounds like lots more work than I would want to tackle.

Reply | Quote

After some digging, the general advice seems to be to find another way to display the data if possible – see http://p2p.wrox.com/topic.asp?TOPIC_ID=3730%5B/url%5D for example. Since you are using 97, a technique suggested at http://dbforums.com/arch/217/2002/10/527516%5B/url%5D won’t work as it requires 2000 or later. If you look under the “L” category on http://www.rogersaccesslibrary.com[/url%5D there is a database which uses a ListBox to simulate a subform – that might be a possibility if your subform is quite simple. That is about the best I can do – perhaps someone else has another idea.

Reply | Quote

After some digging, the general advice seems to be to find another way to display the data if possible – see http://p2p.wrox.com/topic.asp?TOPIC_ID=3730%5B/url%5D for example. Since you are using 97, a technique suggested at http://dbforums.com/arch/217/2002/10/527516%5B/url%5D won’t work as it requires 2000 or later. If you look under the “L” category on http://www.rogersaccesslibrary.com[/url%5D there is a database which uses a ListBox to simulate a subform – that might be a possibility if your subform is quite simple. That is about the best I can do – perhaps someone else has another idea.

Reply | Quote

Hey, Wendell,
I’m just not quiet sure how to understand ‘a datasheet for the first row of the form’ but that’s probably because of my own wobby writing…
Just to be sure, I’ll rewrite my question: in a continuous or datasheet form, Access doesn’t allow subforms.
As a workaround I would like to assemble in one form (1) a continuous form (or an ’empty’ one with an embedded datasheet form) (2) in the same form (e.g. in footer continuous form) another embedded form (subform not allowed!) showing all records related to the active record in the first form. Now I was wondering: how I get both controls linked? (Layout to avoid as much uglyness as possible is the next step )
I’ll try to post an example later on, and in any case read over your post again this evening.
Thanks!
Hasse

Reply | Quote

(one year later… another time, another database… I realise my approach was wrong… at least for my case)
It is far better and simpler to use two synchronized datasheet subforms side by side (the second showing only records related to the current record in the first subform) than trying to incorporate one inside another…
So, Wendell, thanks for your (first) answer as this is what you actually suggested… I think …

Method: the actual ‘parent’ subform’s ID is stored in a hidden text box on the parent form to which you can refer at the ‘child’ subforms’ LinkChildFields property. This is among others discussed in:
– Access 2002 Desktop Dev Handbook p.502 “Using synchronised forms”: ‘feeding’ the hidden text box value through code from inside the ‘parent’ subform (preferred);
– Luna Systems Access Tips: uses the hidden text box’ record source property to link it directly to the ‘parent’ subform ID control. In my case, this was impossible to work with as my form refreshed constantly at a high frequency (probably because I referred to the parent subform’s ID field (… !ID) and not to the ID field’s control (… .IDControl)).

One constraint: future must tell if this technique isn’t too difficult for the users… as e.g. the list box alternative is far more dummy proof. Bob Bedell has a good point on this in the discussion you referred to in your previous post.

Reply | Quote

Actually, if I understand what you are trying to do, you shouldn’t need any VBA to do what you are asking. The Main form would be a continuous form that looks like a datasheet for the first row of the form, and then a subform control that refers to a subform that is displayed in datasheet form. If you have multiple subforms, they would be stacked one beneath the other, or possibly side by side, though that could get ugly. Does that sound like what you are attempting to achieve? That would look something like the subdatasheets that you can use in 2000 and up, but cause nasty performance problems.

Reply | Quote