Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Datasheet form subform workaround (97)

    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

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Datasheet form subform workaround (97)

    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.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Datasheet form subform workaround (97)

    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 <img src=/S/blackteeth.gif border=0 alt=blackteeth width=20 height=20> )
    I'll try to post an example later on, and in any case read over your post again this evening.
    Thanks!
    Hasse

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Datasheet form subform workaround (97)

    Hi Hasse,
    You are absolutely correct - you cannot have a subform control on a continuous form. Momentary lapse on my part <img src=/S/stupidme.gif border=0 alt=stupidme width=30 height=30> <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>
    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:<UL><LI>subforms can have many records so you would have to put a set number of controls to display a fixed number of records, and<LI>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.
    Wendell

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Datasheet form subform workaround (97)

    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 for example. Since you are using 97, a technique suggested at http://dbforums.com/arch/217/2002/10/527516 won't work as it requires 2000 or later. If you look under the "L" category on http://www.rogersaccesslibrary.com 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.
    Wendell

  6. #6
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Better synchronise - Re: Datasheet form subform...

    (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 <img src=/S/bow.gif border=0 alt=bow width=15 height=15> 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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •