Form - Subforms Question (please, help)

I

Igorin

Hello,

I've posted this question in the Form Discussion Group but have gotten no
reply yet
(http://www.microsoft.com/office/com...&p=1&tid=83575537-7251-4ee3-8143-6a6e97189bbe)

Maybe someone can help me. I can't get this form to work like I need. This
is the question that I posted (it's a little long, but please read it through
to see if you can figure it out and can help me):

Situation:
I have a Form with 5 Subforms. Of these 5 Subforms, 3 should be subordinate
to the first Subform. The first Subform is linked to the other 3 Subforms by
the field "Work Area" (which is not unique). The 5th Subform is not linked to
the first Subform, instead it is linked directly to the Form by the field
"Report Nr".
The Form is linked to all 5 Subforms by the field "Report Nr" (Primary Key
in the main table).

Problem:
I would like all 5 subforms to be single form and I need: a) that when I
introduce a new Work Area in the main Subform automatically new input boxes
appear in the 3 subforms that are linked to the main Subform; and b) that
when I introduce a new Report Nr every field is new.


Further information, in case it might help...

This form is for inputting information I get from a daily Excel report.

The report for which this DB was created has the following data:

- Report Number (Appears only once, at the beginning of the Excel report)
- Date (Appears only once, at the beginning of the Excel report)
- Supervisor (Appears only once, at the beginning of the Excel report)
- Manager (Appears only once, at the beginning of the Excel report)
- Work Week (Appears only once, at the beginning of the Excel report)
- Work Area (there are 8 work areas, and in one day some - but not all -
appear in the report, and they may appear mor than once. Each time one
appears it has it's own column)
- Work Area Specific Data 1
- Work Area Specific Data 2
- Work Area Specific Data 3
- etc.
-Data for each work area (Each has it's own row and it's in the column for
it's corresponding reported Work Area): start time (i.e., 4:05 p.m.), end
time, activity 1 time (total amount of time invested in this activity),
activity 2 time, activity 3 time, etc.
- Production Data (for each reported work area): Prod data 1, prod data 2,
Prod data 3, etc.
- Statistical Data (for each reported work area): Stat Data 1, Stat Data 2,
Stat Data 3, etc.
- Data that is not work area related: Total fuel consumption, Equipment
used, Total labor, etc.

The Excel file may look something like this:

Report Nr
Date
Supervisor
Manager
Work Week

Work Area 1 | Work Area 2 | Work Area 3 | Work Area 2 | (etc.) |
WA 1 Sp D 1 | WA 2 Sp D 1 | WA 3 Sp D 1 | WA 2 Sp D 1 | (etc.) |
WA 1 Sp D 2 | WA 2 Sp D 2 | WA 3 Sp D 2 | WA 2 Sp D 2 | (etc.) |
WA 1 Sp D 3 | WA 2 Sp D 3 | WA 3 Sp D 3 | WA 2 Sp D 3 | (etc.) |
(etc.) | (etc.) | (etc.) | (etc.) | (etc.) |
.. . . . .
.. . . . .
Start Time | Start Time | Start Time | Start Time | (etc.) |
End Time | End Time | End Time | End Time | (etc.) |
Act 1 Time | Act 1 Time | Act 1 Time | Act 1 Time | (etc.) |
Act 2 Time | Act 2 Time | Act 2 Time | Act 2 Time | (etc.) |
Act 3 Time | Act 3 Time | Act 3 Time | Act 3 Time | (etc.) |
(etc.) | (etc.) | (etc.) | (etc.) | (etc.) |
.. . . . .
.. . . . .
Prod Data 1 | Prod Data 1 | Prod Data 1 | Prod Data 1 | (etc.) |
Prod Data 2 | Prod Data 2 | Prod Data 2 | Prod Data 2 | (etc.) |
Prod Data 3 | Prod Data 3 | Prod Data 3 | Prod Data 3 | (etc.) |
(etc.) | (etc.) | (etc.) | (etc.) | (etc.) |
.. . . . .
.. . . . .
Stat Data 1 | Stat Data 1 | Stat Data 1 | Stat Data 1 | (etc.) |
Stat Data 2 | Stat Data 2 | Stat Data 2 | Stat Data 2 | (etc.) |
Stat Data 3 | Stat Data 3 | Stat Data 3 | Stat Data 3 | (etc.) |
(etc.) | (etc.) | (etc.) | (etc.) | (etc.) |
.. . . . .
.. . . . .
Total fuel consumption
Equipment used
Total labor
(etc.)


I have the following Tables:

Table 1:
- Report Nr (PK)
- Date
- Supervisor
- Manager
- Work Week

Table 2:
- Report Nr
- Work Area
- Work Area Specific Data 1
- Work Area Specific Data 2
- Work Area Specific Data 3
- etc.
- Start Time
- End Time
- Act 1 Time
- Act 2 Time
- Act 3 Time
- etc.
- Prod Data 1
- Prod Data 2
- Prod Data 3
- etc.
- Stat Data 1
- Stat Data 2
- Stat Data 3
- etc.

Table 3:
- Report Nr
- Total fuel consumption
- Equipment used
- Total labor
- etc.


The Form and Subforms are these:

Form:
- Report Nr
- Date
- Supervisor
- Manager
- Work Week

First Subform:
- Report Nr
- Work Area
- Work Area Specific Data 1
- Work Area Specific Data 2
- Work Area Specific Data 3
- etc.

Second Subform:
- Report Nr (invisible)
- Work Area (invisible)
- Start Time
- End Time
- Act 1 Time
- Act 2 Time
- Act 3 Time
- etc.

Third Subform:
- Report Nr (invisible)
- Work Area (invisible)
- Prod Data 1
- Prod Data 2
- Prod Data 3
- etc.

Fourth Subform:
- Report Nr (invisible)
- Work Area (invisible)
- Stat Data 1
- Stat Data 2
- Stat Data 3
- etc.

Fifth Subform:
- Report Nr (invisible)
- Total fuel consumption
- Equipment used
- Total labor
- etc.

....Thanks for the help!!!
 
I

Igorin

I solved it already. Thanks anyway.

--
igor


Igorin said:
Hello,

I've posted this question in the Form Discussion Group but have gotten no
reply yet
(http://www.microsoft.com/office/com...&p=1&tid=83575537-7251-4ee3-8143-6a6e97189bbe)

Maybe someone can help me. I can't get this form to work like I need. This
is the question that I posted (it's a little long, but please read it through
to see if you can figure it out and can help me):

Situation:
I have a Form with 5 Subforms. Of these 5 Subforms, 3 should be subordinate
to the first Subform. The first Subform is linked to the other 3 Subforms by
the field "Work Area" (which is not unique). The 5th Subform is not linked to
the first Subform, instead it is linked directly to the Form by the field
"Report Nr".
The Form is linked to all 5 Subforms by the field "Report Nr" (Primary Key
in the main table).

Problem:
I would like all 5 subforms to be single form and I need: a) that when I
introduce a new Work Area in the main Subform automatically new input boxes
appear in the 3 subforms that are linked to the main Subform; and b) that
when I introduce a new Report Nr every field is new.


Further information, in case it might help...

This form is for inputting information I get from a daily Excel report.

The report for which this DB was created has the following data:

- Report Number (Appears only once, at the beginning of the Excel report)
- Date (Appears only once, at the beginning of the Excel report)
- Supervisor (Appears only once, at the beginning of the Excel report)
- Manager (Appears only once, at the beginning of the Excel report)
- Work Week (Appears only once, at the beginning of the Excel report)
- Work Area (there are 8 work areas, and in one day some - but not all -
appear in the report, and they may appear mor than once. Each time one
appears it has it's own column)
- Work Area Specific Data 1
- Work Area Specific Data 2
- Work Area Specific Data 3
- etc.
-Data for each work area (Each has it's own row and it's in the column for
it's corresponding reported Work Area): start time (i.e., 4:05 p.m.), end
time, activity 1 time (total amount of time invested in this activity),
activity 2 time, activity 3 time, etc.
- Production Data (for each reported work area): Prod data 1, prod data 2,
Prod data 3, etc.
- Statistical Data (for each reported work area): Stat Data 1, Stat Data 2,
Stat Data 3, etc.
- Data that is not work area related: Total fuel consumption, Equipment
used, Total labor, etc.

The Excel file may look something like this:

Report Nr
Date
Supervisor
Manager
Work Week

Work Area 1 | Work Area 2 | Work Area 3 | Work Area 2 | (etc.) |
WA 1 Sp D 1 | WA 2 Sp D 1 | WA 3 Sp D 1 | WA 2 Sp D 1 | (etc.) |
WA 1 Sp D 2 | WA 2 Sp D 2 | WA 3 Sp D 2 | WA 2 Sp D 2 | (etc.) |
WA 1 Sp D 3 | WA 2 Sp D 3 | WA 3 Sp D 3 | WA 2 Sp D 3 | (etc.) |
(etc.) | (etc.) | (etc.) | (etc.) | (etc.) |
. . . . .
. . . . .
Start Time | Start Time | Start Time | Start Time | (etc.) |
End Time | End Time | End Time | End Time | (etc.) |
Act 1 Time | Act 1 Time | Act 1 Time | Act 1 Time | (etc.) |
Act 2 Time | Act 2 Time | Act 2 Time | Act 2 Time | (etc.) |
Act 3 Time | Act 3 Time | Act 3 Time | Act 3 Time | (etc.) |
(etc.) | (etc.) | (etc.) | (etc.) | (etc.) |
. . . . .
. . . . .
Prod Data 1 | Prod Data 1 | Prod Data 1 | Prod Data 1 | (etc.) |
Prod Data 2 | Prod Data 2 | Prod Data 2 | Prod Data 2 | (etc.) |
Prod Data 3 | Prod Data 3 | Prod Data 3 | Prod Data 3 | (etc.) |
(etc.) | (etc.) | (etc.) | (etc.) | (etc.) |
. . . . .
. . . . .
Stat Data 1 | Stat Data 1 | Stat Data 1 | Stat Data 1 | (etc.) |
Stat Data 2 | Stat Data 2 | Stat Data 2 | Stat Data 2 | (etc.) |
Stat Data 3 | Stat Data 3 | Stat Data 3 | Stat Data 3 | (etc.) |
(etc.) | (etc.) | (etc.) | (etc.) | (etc.) |
. . . . .
. . . . .
Total fuel consumption
Equipment used
Total labor
(etc.)


I have the following Tables:

Table 1:
- Report Nr (PK)
- Date
- Supervisor
- Manager
- Work Week

Table 2:
- Report Nr
- Work Area
- Work Area Specific Data 1
- Work Area Specific Data 2
- Work Area Specific Data 3
- etc.
- Start Time
- End Time
- Act 1 Time
- Act 2 Time
- Act 3 Time
- etc.
- Prod Data 1
- Prod Data 2
- Prod Data 3
- etc.
- Stat Data 1
- Stat Data 2
- Stat Data 3
- etc.

Table 3:
- Report Nr
- Total fuel consumption
- Equipment used
- Total labor
- etc.


The Form and Subforms are these:

Form:
- Report Nr
- Date
- Supervisor
- Manager
- Work Week

First Subform:
- Report Nr
- Work Area
- Work Area Specific Data 1
- Work Area Specific Data 2
- Work Area Specific Data 3
- etc.

Second Subform:
- Report Nr (invisible)
- Work Area (invisible)
- Start Time
- End Time
- Act 1 Time
- Act 2 Time
- Act 3 Time
- etc.

Third Subform:
- Report Nr (invisible)
- Work Area (invisible)
- Prod Data 1
- Prod Data 2
- Prod Data 3
- etc.

Fourth Subform:
- Report Nr (invisible)
- Work Area (invisible)
- Stat Data 1
- Stat Data 2
- Stat Data 3
- etc.

Fifth Subform:
- Report Nr (invisible)
- Total fuel consumption
- Equipment used
- Total labor
- etc.

...Thanks for the help!!!
 
W

Wayne-I-M

Hi

Even though you hav solved it. Can I give 1 very simple method.

Create a hiden text box on your main form
OnClick od the first subform - the one the other 3 are linked to use this to
set the value of the hidden text box to the ID of the 1st subform

Forms!MainForm!HiddenTxtBox = Forms!MainForm!SubForm.Form!ID
DoCmd.GoToControl "2ndSubFrm"

Don't link the 3 subforms with the 1st. Link them with the hidden text box,
This will solve most of your problems.

Dead simple and so not likely to mess up

:)
 
I

Igorin

Thank you, Wayne.

I will try it the next time to see how it works.

Thanks for your time.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top