Nested forms and DB Design

[Dom Torrez]

I've been given an Access front end to a postgres DB. The initial developer
had told us that Access, in order to function properly with nested forms
requires compound PK's. We'd like to normalize our Db since this compound
PK's is causing many issues with our other software. I've never heard of
compound PK's being necessary for Access, but then I've never created such a
complicated Access DB. Anyone have any suggestions or comments about this?
currently some of my forms go about 5 layers deep in nesting. what about
the VBA coding, are compound keys required?
Thanks,
Dom Torrez

 

[Tim Ferguson]

I've been given an Access front end to a postgres DB. The initial
developer had told us that Access, in order to function properly with
nested forms requires compound PK's.

This does not make a lot of sense. It may be that the designer has opted
to use compound keys. If the database design is set up that way, then you
presumably don't have a lot of choice unless you are talking about re-
designing the prostgres system.

We'd like to normalize our Db

.... but there is nothing un-normal about having compound natural keys --=
probably rather the reverse in fact.

since this compound PK's is causing many issues with our other
software.

Like what?

Anyone have any suggestions or comments about this? currently some of
my forms go about 5 layers deep in nesting.

I would be very suspicious about the process mapping. Have you talked to
your users? How on earth do they manage with five levels of subforms? I
have to confess that I regard subform controls as the work of the devil
and simply avoid them with all my life, so I am not exactly a neutral
observer -- but this sounds like a dog's dinner by anyone's standards.

There is no reason you have to use compound natural keys in Access --
that is what the autonumber was invented to replace. But you are not
using Jet; you are using Postgres, and your db design will be constrained
by that rather than by Access.

Hope that helps


Tim F

 

[tina]

I

have to confess that I regard subform controls as the work of the devil
and simply avoid them with all my life, so I am not exactly a neutral
observer -- but this sounds like a dog's dinner by anyone's standards.

LOL
....while i, on the other hand, love subforms and use them all the time,
sometimes nested as child then "grandchild" (okay, i did make up that term
<g>)...but i completely agree with you, Tim, that the idea of subforms
nested 5 deep is somewhat horrifying - to build as well as to work in!

 

[Pat Hartman\(MVP\)]

I've never met anyone who didn't think subforms were awesome. I'd be
interested in why you don't like them.

 

[Tim Ferguson]

I've never met anyone who didn't think subforms were awesome. I'd be
interested in why you don't like them.

It's mainly because they flout windows usability standards, particularly
the tab order. You can fall into them easily enough by tabbing from the
previous control, but then it takes a ctrl-tab to get out. When I have
tried to explain this to users they just look blank. In addition, the
coding seems to do all kinds of stuff that I can't control and may or may
not be what I want, like creating new records filling in invisible
fields.

It's probably my personality type(!) but I prefer to be in control of
user processes, and to give them things they can start using even if they
only used Notepad before. If that means I have to take a bit longer
programming double-click events in listboxes, so be it.

I do use subreports, though, if that helps!

All the best


Tim F

 

[Pat Hartman\(MVP\)]

Hi Tim,
Subforms work exactly the same way that main forms work and you control them
in exactly the same way. This usually means putting your final edit code
into the BeforeUpdate event (which you can cancel if necessary). You cannot
control them from the main form because the main form doesn't "see" the
subform events. That's why your code always goes in the subform. As to the
tab issue, subforms generally show multiple records and so the tab goes from
one record to the next and there isn't any "end" to the subform hence there
is no way to tab out of it. The subform has no way of knowing if you want
to tab into the next subform record or back to the main form, so you have to
tell it. Surely that is an understandable explanation for your users.

Hope this makes you rethink your usage of subforms. They are an awesome
feature and provide a great deal of functionality with no code.