Arranging several forms (subforms)

[Nicola M]

Hi all! Access 2003.
Due to a mistake in the design phase I did some changing in my DB, but this
isn't the main issue. NOw the DB works properly. My problem regards how to
display a lot of forms on the screen in the easiest and simpliest manner.
First I had a form (PK ID_PEOPLE) with an inner Tab control (10 pages) and
all worked properly.
Now I have again the same form but unfortunately I need to keep on the
screen also another form (I put it as a subform). All the previous forms in
the Tab Control are now linked to this former mask (i.e. they should be now
subforms of the subform) so I have a screen with a complete mess.

Before
┌------------------------------------------------â”
│ Main Form PK = IDPEOPLE │
│__________________________________│
│ TAB CONTROL WITH 10 PAGES │
│ EACH PAGE IS A SUBFORM
│
│ │
│ │
└------------------------------------------------┘
Now
┌------------------------------------------------â”
│ Main Form PK = IDPEOPLE │
│__________________________________│
│ Other Form PK = IDCOURSE │
│__________________________________│
│ PUTTING HERE THE TAB CONTROL │
│THE DIMENSION OF THE FORM GREW UP │
│ TOO MUCH. I NEED TO SHOW │
│ "ON DEMAND" ONLY THE PAGES/FORM │
│ ARE NECESSARY IN THAT MOMENT. I'M│
│ THINKING ABOUT COMMAND BUTTONS │
│ AND LINKED FORMS BUT I DON'T KNOW │
│ HOW TO GET IT. │
└------------------------------------------------┘
Thank you in advance for your time and suggestions.
Sorry for my bad English. I'm here if someone needs more information.

 

[Rod Plastow]

Hi Nicola

<(i.e. they should be now subforms of the subform)>

OK, but why not still use the tab control solution?

You are very brave attempting to draw a picture in these posts; I will not
attempt it. However let me try to describe a possible solution.

Your main parent form is IDPEOPLE.
The first level sub form is IDCOURSE.
This first level sub form as well as the course information contains a tab
control.
Each page of the tab control contains a second level sub form.

Just make sure the necessary linking values are exposed at each form level.

Hope this helps.

 

[Nicola M]

Thanks Rod.

The links are good. I'm not able to layout on the main form all the other
controls. I tried with page break but every time the focus change from a tab
to another tab I get an unpleasant scrolling.
I tried also with togglebutton and linked form (using a wizard) but I can't
to link more than one form: I think it is necessary to change/add somthing
else in the code but I'm not so a "cool coder".
Using normal commandbuttons I open and close every single form properly but
if I have had add a record in the "parent" form, when I open the subform the
FK in this subform are not automatically updated.

Therefore, I'm still in a complete mess.

Nicola M

 

[Rod Plastow]

Hi Nicola,

You seem to have two problems.

1. You don't have enough screen space to show all three levels of forms
simultaneously (without scrolling) and wish to implement some kind of
on-demand overlay for the third-level (bottom level) form. By overlay I mean
this form will obscure some of the parent and/or intermediate level
information.

2. You have problems synchronising the forms when adding new records at the
parent level.

I will not here attempt to answer number 2 but confine myself to number 1.

The actual design and layout of the three form levels has to be up to you.
What I envisage is a rank (array) of toggle buttons - never obscured, you
could place them in the form footer to ensure this - one for each third-level
sub form. When you depress (click) a toggle button the corresponding sub
form becomes visible, similar to a pop-up. Alternatively you could use an
Option Group - but more about this below.

Now I can think of three ways of arranging this:

a) You include one third-level sub form control (defaulted to be hidden) and
reassign the Souce Object to correspond with the active toggle button before
making this control visible.

b) You include a stack, one on top of another, of third-level sub form
controls - one for each third-level form. All are defaulted to be hidden.
You make the appropriate sub form control visible to correspond with the
active toggle button.

c) You include one third-level sub form control (defaulted to be hidden) and
attach your form with the tab control to it. Remove the tabs from the tab
control. Now when a toggle button is activated, switch to the appropriate
page before making the sub form control visible.

I think c) would be easier to maintain and debug.

Now about that array of toggle buttons. You need to code a mechanism
whereby if a second button is clicked, the active button is 'unclicked' and
the appropriate action taken before actioning the new button. This is why I
suggest you could think about an option group instead of toggle buttons
because some of this work is done for you - not much I agree.

----------------------------------

As I wrote this I wondered whether a better solution is to abandon the
third-level sub form concept entirely and display the additional information
on-demand as separate pop-up, modal forms. In this way you can drag the
pop-up form around if it obscures any data on the original form that you want
to view simultaneously. With this solution simple command buttons will
suffice. As the pop-up form is modal you cannot return to the original form
until the pop-up is closed.

The choice is yours,

Rod

 

[Nicola M]

Superthanks Rod!

[CUT]

You seem to have two problems.

1. You don't have enough screen space to show all three levels of forms
simultaneously (without scrolling) and wish to implement some kind of
on-demand overlay for the third-level (bottom level) form. By overlay I mean
this form will obscure some of the parent and/or intermediate level
information.

Yes! This is the focus.

2. You have problems synchronising the forms when adding new records at the
parent level.

So far (500 records in Parents tables) the performance (speed in refreshing
after <next record> is pressed) is acceptable. But in a short time the number
of records will increase a lot.

[CUT]

Just to try I put one tabcontrol with four tabs on the main form. The first
tab, also, include a form with a subform where I placed another tabcontrol.
It works, syncronization works with a minimum delay and so on. I'm not sure
if this way is a good way because this is the first time I work so deep with
Acces (code, modules, user defined functions, lot of controls on form and a
"heavy" logic on the ER diagram behind the scenes.

Now I can think of three ways of arranging this:

a) You include one third-level sub form control (defaulted to be hidden) and
reassign the Souce Object to correspond with the active toggle button before
making this control visible.

As I said, this is the first time... I need to practice with the complex
VBA. I'll try.

b) You include a stack, one on top of another, of third-level sub form
controls - one for each third-level form. All are defaulted to be hidden.
You make the appropriate sub form control visible to correspond with the
active toggle button.
Absolutely.

c) You include one third-level sub form control (defaulted to be hidden) and
attach your form with the tab control to it. Remove the tabs from the tab
control. Now when a toggle button is activated, switch to the appropriate
page before making the sub form control visible.

Actually I don't understand this due to my bad English. What do you mean with
"attach your form with the tab control to it. Remove the tabs from the tab
control" ?

I think c) would be easier to maintain and debug.

Now about that array of toggle buttons. You need to code a mechanism
whereby if a second button is clicked, the active button is 'unclicked' and
the appropriate action taken before actioning the new button. This is why I
suggest you could think about an option group instead of toggle buttons
because some of this work is done for you - not much I agree.

You're right again. In my attempts to solve this I encountered troubles
managing visibility and positions of the different forms at the same time. So
far I know only the toggle buttons code Access do automatically when you
create a form with data from two tables.

[CUT]

As I wrote this I wondered whether a better solution is to abandon the
third-level sub form concept entirely and display the additional information
on-demand as separate pop-up, modal forms.

I discovered the popup function just yesterday... (I'm reading the Help
whenever I have time to do)

In this way you can drag the
pop-up form around if it obscures any data on the original form that you want
to view simultaneously. With this solution simple command buttons will
suffice. As the pop-up form is modal you cannot return to the original form
until the pop-up is closed.

Yes! Creation is simple. Managing visibility, position and syncronization is
just a little bit less easy ;-) . I'll read about it and I'll try.

The choice is yours,

Unfortunately

Rod

Nicola M

 

[Rod Plastow]

Sorry, I'm just as much to blame for using terms loosely. By attach I meant
'link.' As I would strongly recommend the pop-up, modal form solution - I
assume you just want to view the information and not change it - don't spend
too much time on this third-level subform solution.

By the way: a common misunderstanding with those new to Access is to think
that the control that contains the subform and the subform itself are the
same thing. They are not. Unfortunately if you use the subform wizard,
Access suggests the subform name as the name of the control. Thus these two
different things have the same name. No wonder people get confused.

If you need any help with the pop-up form then shout.

Rod

 

[Nicola M]

Rod said:

If you need any help with the pop-up form then shout.

Here your shout! ;-)

Besides Joke, in some third level I have to insert/update data. To avoid
misunderstandings I explain my DB structure: T_People with PK IDPEOPLE;
T_COURSES with PK IDCOURSE and FK IDPEOPLE. I have also other table at this
level with absolutely no problem. Problems starts with those I have called
"third level" forms as the followings are:
T_IDDOC with PK IDDOC and (FKs T_IDPEOPLE.IDPEOPLE + T_COURSES.IDCOURSE);
T_PREVSKILLS with PK IDPREVSKILL and ((FKs T_IDPEOPLE.IDPEOPLE +
T_COURSES.IDCOURSE);
....
Even if I indicated as FK T_PEOPLE.IDPEOPLE I'm using the IDPEOPLE contained
in T_COURSES because this approach it has been simplier to me, considering I
used wizards and then I have modify something in the result.

So, I use this megaforms (T_PEOPLE <north>, TabCtl1 <south> with at page0
<west> T_COURSE fields and a TabCtl2 <east> with the third level subforms)
and obviously I use it to insert and update records.
I'd like to use popup way to reduce the presence of controls on the screen
but at the same time I musta have the capability to insert/update.

Did your observation "I assume you just want to view the information and not
change it - don't spend too much time on this third-level subform solution"
mean that with popup way I could have problems in insert/update?

Nicola M

[CUT]

 

[Rod Plastow]

Hi Nicola,

Sorry, I did reply last Friday but when I posted the content it was reported
that I had lost contact with the server – timed out presumably. There is no
way that I could find of recovering my post! I then waited to see if it was
delayed in the system but unfortunately it seems I have to rewrite it.

I shall confine this response purely to pop-up modal forms and not attempt a
critique of your data design. No I did not mean that you cannot edit data
from pop-up forms; of course you can; they are no different from ordinary
forms, just displayed in a different way. However I would suggest you do not
allow any edits of the linking values.

Design your forms in the normal way but pay attention to the following
Property Sheet entries.

On the Data tab:

Make sure the Record Source is linked to a table or query to enable
automatic updating of your edits;
Set Data Entry to ‘No’ and Allow Edits and Allow Filters to ‘Yes.’ These
are the defaults.
I suggest you set Allow Additions to ‘No.’ Only set it to ‘Yes’ if the
pop-up form is based on separate, discrete table(s) from your main table
(i.e. typically displayed as a Continuous Form).
Similarly I suggest you set Allow Deletions to ‘No.’

On the Other tab:

You may set Pop Up and Modal to ‘Yes’ but you will set these values anyway
when you open the form.

On your main form include a Command Button for each and every pop-up form.
In the On Click event of each Command Button code something similar to the
following procedure. Don’t use the control wizard as this does not allow you
to specify linking criteria.

Private Sub Command1_Click()

Dim strCriteria As String

strCriteria = "IDCOURSE = " & Me.IDCOURSE & " AND IDPEOPLE = " &
Me.IDPEOPLE
DoCmd.OpenForm "Form1", , , strCriteria, , acDialog

End Sub

I can’t be exact because I do not know your requirements but I assume that
you are linking on course and person and that IDCOURSE and IDPEOPLE are
foreign keys in the table or query upon which your pop-up is based. I assume
these values are numeric.

I also assume you have columns in the underlying result set of your main
form named IDCOURSE and IDPEOPLE, hence Me.IDCOURSE and Me.IDPEOPLE. I also
assume these are numeric.

The DoCmd has three arguments of interest: the first is the name of your
pop-up form; the fourth is the linking/filtering clause which in this case is
encoded in a string variable; and the sixth that is an instruction to Access
to display the form as pop-up and modal.

Alter the foregoing according to your requirements and names and you should
be OK.

Rod

 

[Defemex]

Hi Nicola,

Sorry, I did reply last Friday but when I posted the content it was reported
that I had lost contact with the server – timed out presumably.  There is no
way that I could find of recovering my post!  I then waited to see if it was
delayed in the system but unfortunately it seems I have to rewrite it.

I shall confine this response purely to pop-up modal forms and not attempt a
critique of your data design.  No I did not mean that you cannot edit data
from pop-up forms; of course you can; they are no different from ordinary
forms, just displayed in a different way.  However I would suggest you do not
allow any edits of the linking values.

Design your forms in the normal way but pay attention to the following
Property Sheet entries.

On the Data tab:

Make sure the Record Source is linked to a table or query to enable
automatic updating of your edits;
Set Data Entry to ‘No’ and Allow Edits and Allow Filters to ‘Yes.’  These
are the defaults.
I suggest you set Allow Additions to ‘No.’  Only set it to ‘Yes’ if the
pop-up form is based on separate, discrete table(s) from your main table
(i.e. typically displayed as a Continuous Form).
Similarly I suggest you set Allow Deletions to ‘No.’

On the Other tab:

You may set Pop Up and Modal to ‘Yes’ but you will set these values anyway
when you open the form.

On your main form include a Command Button for each and every pop-up form..  
In the On Click event of each Command Button code something similar to the
following procedure.  Don’t use the control wizard as this does not allow you
to specify linking criteria.

Private Sub Command1_Click()

    Dim strCriteria As String

    strCriteria = "IDCOURSE = " & Me.IDCOURSE & " AND IDPEOPLE = " &
Me.IDPEOPLE
    DoCmd.OpenForm "Form1", , , strCriteria, , acDialog

End Sub

I can’t be exact because I do not know your requirements but I assume that
you are linking on course and person and that IDCOURSE and IDPEOPLE are
foreign keys in the table or query upon which your pop-up is based.  I assume
these values are numeric.

I also assume you have columns in the underlying result set of your main
form named IDCOURSE and IDPEOPLE, hence Me.IDCOURSE and Me.IDPEOPLE.  Ialso
assume these are numeric.

The DoCmd has three arguments of interest: the first is the name of your
pop-up form; the fourth is the linking/filtering clause which in this case is
encoded in a string variable; and the sixth that is an instruction to Access
to display the form as pop-up and modal.

Alter the foregoing according to your requirements and names and you should
be OK.

Rod

I would just like to throw my two cents into this one. I have recent
implemented a FullScreen switchboard window which contains one main
Subform that is almost the whole area of the screen. At the bottom of
my switchboard I placed a 1" footer which contains many buttons. Each
button changes the main subform.sourceobject to any form I like. After
I made the switchboard a fullscreen window, all other forms become
fullscreen when opened, so to counter that I made them all popups.
Some of these popups forms insert/update records in underlying tables
that the popup forms are linked to. The reason I am posting here is
because recently I've found that I cannot insert/update underlying
tables from popup forms. For whatever reason they failed to update the
BE, they keep a local record on the ODBC linked table, but when
refreshed or application restart they are gone (along with any insert/
updates after the initial popup insert). Major problem as records are
being lost. Got any ideas? There was mention of popup insert/update in
this post... and I'm hunting for a solution.

Thanks a bunch,
Dan

 

[Rod Plastow]

Dan,

I guarantee your problem has nothing to do with pop-up forms per se; they
are no different from any other form except in the way they are displayed and
the way they behave. My original comment about assuming no updates was
because I had not warned Nicola to protect the linking values; allowing users
to changes linking values can lead to some pretty big messes.

You say you are using ODBC. My ODBC experience is very much read-only. I
suspect however that the failure to permanently update your target database
has something to do with that database itself. Are you sure you don't need
to issue a 'commit' or some such command?

Rod

 

[Nicola M]

Rod Plastow ha scritto:

Hi Nicola,

Sorry, I did reply last Friday but when I posted the content it was reported
that I had lost contact with the server – timed out presumably. There is no
way that I could find of recovering my post! I then waited to see if it was
delayed in the system but unfortunately it seems I have to rewrite it.

I shall confine this response purely to pop-up modal forms and not attempt a
critique of your data design. No I did not mean that you cannot edit data
from pop-up forms; of course you can; they are no different from ordinary
forms, just displayed in a different way. However I would suggest you do not
allow any edits of the linking values.

Design your forms in the normal way but pay attention to the following
Property Sheet entries.

On the Data tab:

Make sure the Record Source is linked to a table or query to enable
automatic updating of your edits;
Set Data Entry to ‘No’ and Allow Edits and Allow Filters to ‘Yes.’ These
are the defaults.
I suggest you set Allow Additions to ‘No.’ Only set it to ‘Yes’ if the
pop-up form is based on separate, discrete table(s) from your main table
(i.e. typically displayed as a Continuous Form).
Similarly I suggest you set Allow Deletions to ‘No.’

On the Other tab:

You may set Pop Up and Modal to ‘Yes’ but you will set these values anyway
when you open the form.

On your main form include a Command Button for each and every pop-up form.
In the On Click event of each Command Button code something similar to the
following procedure. Don’t use the control wizard as this does not allow you
to specify linking criteria.

Private Sub Command1_Click()

Dim strCriteria As String

strCriteria = "IDCOURSE = " & Me.IDCOURSE & " AND IDPEOPLE = " &
Me.IDPEOPLE
DoCmd.OpenForm "Form1", , , strCriteria, , acDialog

End Sub

I can’t be exact because I do not know your requirements but I assume that
you are linking on course and person and that IDCOURSE and IDPEOPLE are
foreign keys in the table or query upon which your pop-up is based. I assume
these values are numeric.

I also assume you have columns in the underlying result set of your main
form named IDCOURSE and IDPEOPLE, hence Me.IDCOURSE and Me.IDPEOPLE. I also
assume these are numeric.

The DoCmd has three arguments of interest: the first is the name of your
pop-up form; the fourth is the linking/filtering clause which in this case is
encoded in a string variable; and the sixth that is an instruction to Access
to display the form as pop-up and modal.

Alter the foregoing according to your requirements and names and you should
be OK.

Rod

Sorry for the long delay.
At the end I decided to use form -> sub form with ctlPage and on a page
other subforms located in further pages of a ctlPage.
Really thanks for your tips.
Nicola M