Main form and loading a few subforms

I'm running a db for a Facilities report.
I have 8 kind of facilities.
I've created 1 table that includes the fields that are identical in those
facilities – "T_Facility" (such as PK, facility name, facility location,
facility manufacturer, etc..), and separated the rest of the fields into 8
different tables, each table relate to a certain facility.
In the table "T_Facility" I have a field the identify the type of the
facility (e.g. Facility1, Facility2, Facility3…) by a Facility Group Number.
I've created a form that has 8 (!!) subforms, and on the load event of the
main form the relevant form is shown (subform_facility1.visible=true) and the
other are visible=false.

I need an advise how to make it better. This method load all the 8 forms
even though they are not all shown.

I tried to change the source object of a sub form, in an on open event,
according to the facility group in the main form, but it's not working.
I've create an event on open, that uses
"select case FacilityNum
Case 1
Me.subForm.Sourceobject = Facility1
Me. subForm.LinkChildFields = nFacilityIx
Me. subForm.LinkMasterFields = nFacilityIx
Case 2
Sourceobject = Facility2
Me. subForm.LinkChildFields = nFacilityIx
Me. subForm.LinkMasterFields = nFacilityIx
Case 3
… etc.
Case else
End select

The Facility1, Facility2, are the facility Group / Type (8 Facilities as I
mentioned)
The nFacilityIx is the unique identifier of each facility (endless items).
The field nFacilityIx are both in the main and in the sub-forms, so it can
be used for all the subforms as child and master field.

The code that I've write is not working properly, and I will be happy to
receive any help on that.
Thank

--
Alu_GK

On Sun, 26 Oct 2008 08:47:00 -0700, Alu_GK
wrote:

I'm running a db for a Facilities report.
I have 8 kind of facilities.
I've created 1 table that includes the fields that are identical in those
facilities – "T_Facility" (such as PK, facility name, facility location,
facility manufacturer, etc..), and separated the rest of the fields into 8
different tables, each table relate to a certain facility.

Ummm... sorry. That's not a correct design. What will you do when they open
two more facilities? Two new tables, all new queries, all new code!?

You do not store data (the identity of a facility) in a table name. Instead
you store the facility's identity *as data in a field*. Rather than
multiplying the number of tables, you add new *RECORDS* to your table.

In the table "T_Facility" I have a field the identify the type of the
facility (e.g. Facility1, Facility2, Facility3…) by a Facility Group Number.
I've created a form that has 8 (!!) subforms, and on the load event of the
main form the relevant form is shown (subform_facility1.visible=true) and the
other are visible=false.

I need an advise how to make it better. This method load all the 8 forms
even though they are not all shown.

One form, one subform - filtered by the identity of the facility.

I tried to change the source object of a sub form, in an on open event,
according to the facility group in the main form, but it's not working.

A Subform will actually be populated BEFORE the mainform opens, so the
mainform's Open event is too late.

I've create an event on open, that uses
"select case FacilityNum
Case 1
Me.subForm.Sourceobject = Facility1
Me. subForm.LinkChildFields = nFacilityIx
Me. subForm.LinkMasterFields = nFacilityIx
Case 2
Sourceobject = Facility2
Me. subForm.LinkChildFields = nFacilityIx
Me. subForm.LinkMasterFields = nFacilityIx
Case 3
… etc.
Case else
End select

The Facility1, Facility2, are the facility Group / Type (8 Facilities as I
mentioned)
The nFacilityIx is the unique identifier of each facility (endless items).
The field nFacilityIx are both in the main and in the sub-forms, so it can
be used for all the subforms as child and master field.

The code that I've write is not working properly, and I will be happy to
receive any help on that.
Thank

Stop, step back, and rethink your table design is my best advice. Any time you
have eight identical or basically-identical tables you're on the wrong track!
--

John W. Vinson [MVP]

Hello -
Thanks for your reply.. I will try and explain the table design.
I will give an example of the facility so you can understand why I design it
the way I did.
The facility we are talking about are for example – Elavators (Type1),
Lifting Machines (Like cranes) (Type2), Steam Boilers (Type3), etc.
Each Type includes identical information fields, such as [description],
[manufacturer], [Location] in the customer area, [FacilityType], etc. this
information is in one table – T_Facilities. For each record I also keep the
"Facility Type" field.
For Each Facility Type, there are fields that are unique for this facility.
For an example –
Type1-Elevator (table name "T_1Elevator")– [Passenger Number allowed],
[Weight allowed to carry], [passenger or luggage elevator], etc.
Type2 - Lifting Machine (table name "T_2Lifting") – [Radius Load allowed],
Serial Number, Safe load, etc.
Type3 – Steam Boiler (table name "T_3SteaBoiler") – Pressure allowed,
Activation Pressure, Boiler Licenses, etc.
The Field that connect the T_Facility with the T_xFacilityType is the
Primary key of the Facility itself, and it connected with one-to-one
relationship.

This is the reason why I keep separated tables for the different facilities
and one that includes the basic data.
I can tell you that if a new facility will be needed – its correct I will
need to define a new set of table, queries, forms and so on, and it is taking
into consideration.

The [Type] field in the table T_Facility is enables me to define what kind
of subform I need to show in order to fill in the unique values for each
facility.
I addition, I open another form in order to fill more unique info for the
facility, therefore I don't see any other option rather that define one major
table and a uniqe info tables for the different facility types. I can tell
you that there is no parallel information in the facilities tables, and they
all includes 7 or more fields that are unique for this specific facility.

This is the explanation… I hope I made clear the purpose of the table
design. And I hope I convince you that this is the best way… Please do tell
me if you think of a better way to do this (after you know all the above).

Given this information – can you think of a better way to show information
on a sub form, instead of show/hide the sub forms according to the facility
type.

Thank you, I hope I didn't tire you with all this information.

Alu.

--
Alu_GK


"John W. Vinson" wrote:

On Sun, 26 Oct 2008 08:47:00 -0700, Alu_GK
wrote:

I'm running a db for a Facilities report.
I have 8 kind of facilities.
I've created 1 table that includes the fields that are identical in those
facilities – "T_Facility" (such as PK, facility name, facility location,
facility manufacturer, etc..), and separated the rest of the fields into 8
different tables, each table relate to a certain facility.

Ummm... sorry. That's not a correct design. What will you do when they open
two more facilities? Two new tables, all new queries, all new code!?

You do not store data (the identity of a facility) in a table name. Instead
you store the facility's identity *as data in a field*. Rather than
multiplying the number of tables, you add new *RECORDS* to your table.

In the table "T_Facility" I have a field the identify the type of the
facility (e.g. Facility1, Facility2, Facility3…) by a Facility Group Number.
I've created a form that has 8 (!!) subforms, and on the load event of the
main form the relevant form is shown (subform_facility1.visible=true) and the
other are visible=false.

I need an advise how to make it better. This method load all the 8 forms
even though they are not all shown.

One form, one subform - filtered by the identity of the facility.

I tried to change the source object of a sub form, in an on open event,
according to the facility group in the main form, but it's not working.

A Subform will actually be populated BEFORE the mainform opens, so the
mainform's Open event is too late.

I've create an event on open, that uses
"select case FacilityNum
Case 1
Me.subForm.Sourceobject = Facility1
Me. subForm.LinkChildFields = nFacilityIx
Me. subForm.LinkMasterFields = nFacilityIx
Case 2
Sourceobject = Facility2
Me. subForm.LinkChildFields = nFacilityIx
Me. subForm.LinkMasterFields = nFacilityIx
Case 3
… etc.
Case else
End select

The Facility1, Facility2, are the facility Group / Type (8 Facilities as I
mentioned)
The nFacilityIx is the unique identifier of each facility (endless items).
The field nFacilityIx are both in the main and in the sub-forms, so it can
be used for all the subforms as child and master field.

The code that I've write is not working properly, and I will be happy to
receive any help on that.
Thank

Stop, step back, and rethink your table design is my best advice. Any time you
have eight identical or basically-identical tables you're on the wrong track!
--

John W. Vinson [MVP]

You are missing the point, each item (facility) you describe should be in
one table and types, manufacture... may require other tables but by doing it
the way you are now you might just a well make a spreadsheet and one sheet
per facility as you will not be able to leverage the power of Access. For
instance, you only need one record for a manufacture with phone, address
etc. This record can be related to all facilities this manufacture has any
relation to. Elevator computer IBM, Steam Boiler control computer IBM,
Lifting Machine controller, IBM. Why put it in three records when one will
do. We have over 1000 facilities on a military base and all are in one
table. Elevators, heating sytems, fire alarms, emergency lighting are all
assesories and in many situations are identical manufactures for fire alarms
and emergency lighting. Why type it twice. Might want to search Google
about relational database design and do a little reading. Your working way
to hard.

"Alu_GK" wrote in message
...
Hello -
Thanks for your reply.. I will try and explain the table design.
I will give an example of the facility so you can understand why I design
it
the way I did.
The facility we are talking about are for example - Elavators (Type1),
Lifting Machines (Like cranes) (Type2), Steam Boilers (Type3), etc.
Each Type includes identical information fields, such as [description],
[manufacturer], [Location] in the customer area, [FacilityType], etc. this
information is in one table - T_Facilities. For each record I also keep
the
"Facility Type" field.
For Each Facility Type, there are fields that are unique for this
facility.
For an example -
Type1-Elevator (table name "T_1Elevator")- [Passenger Number allowed],
[Weight allowed to carry], [passenger or luggage elevator], etc.
Type2 - Lifting Machine (table name "T_2Lifting") - [Radius Load allowed],
Serial Number, Safe load, etc.
Type3 - Steam Boiler (table name "T_3SteaBoiler") - Pressure allowed,
Activation Pressure, Boiler Licenses, etc.
The Field that connect the T_Facility with the T_xFacilityType is the
Primary key of the Facility itself, and it connected with one-to-one
relationship.

This is the reason why I keep separated tables for the different
facilities
and one that includes the basic data.
I can tell you that if a new facility will be needed - its correct I will
need to define a new set of table, queries, forms and so on, and it is
taking
into consideration.

The [Type] field in the table T_Facility is enables me to define what kind
of subform I need to show in order to fill in the unique values for each
facility.
I addition, I open another form in order to fill more unique info for the
facility, therefore I don't see any other option rather that define one
major
table and a uniqe info tables for the different facility types. I can tell
you that there is no parallel information in the facilities tables, and
they
all includes 7 or more fields that are unique for this specific facility.

This is the explanation. I hope I made clear the purpose of the table
design. And I hope I convince you that this is the best way. Please do
tell
me if you think of a better way to do this (after you know all the above).

Given this information - can you think of a better way to show information
on a sub form, instead of show/hide the sub forms according to the
facility
type.

Thank you, I hope I didn't tire you with all this information.

Alu.

--
Alu_GK


"John W. Vinson" wrote:

On Sun, 26 Oct 2008 08:47:00 -0700, Alu_GK

wrote:

I'm running a db for a Facilities report.
I have 8 kind of facilities.
I've created 1 table that includes the fields that are identical in
those
facilities - "T_Facility" (such as PK, facility name, facility location,
facility manufacturer, etc..), and separated the rest of the fields into
8
different tables, each table relate to a certain facility.

Ummm... sorry. That's not a correct design. What will you do when they
open
two more facilities? Two new tables, all new queries, all new code!?

You do not store data (the identity of a facility) in a table name.
Instead
you store the facility's identity *as data in a field*. Rather than
multiplying the number of tables, you add new *RECORDS* to your table.

In the table "T_Facility" I have a field the identify the type of the
facility (e.g. Facility1, Facility2, Facility3.) by a Facility Group
Number.
I've created a form that has 8 (!!) subforms, and on the load event of
the
main form the relevant form is shown (subform_facility1.visible=true)
and the
other are visible=false.

I need an advise how to make it better. This method load all the 8 forms
even though they are not all shown.

One form, one subform - filtered by the identity of the facility.

I tried to change the source object of a sub form, in an on open event,
according to the facility group in the main form, but it's not working.

A Subform will actually be populated BEFORE the mainform opens, so the
mainform's Open event is too late.

I've create an event on open, that uses
"select case FacilityNum
Case 1
Me.subForm.Sourceobject = Facility1
Me. subForm.LinkChildFields = nFacilityIx
Me. subForm.LinkMasterFields = nFacilityIx
Case 2
Sourceobject = Facility2
Me. subForm.LinkChildFields = nFacilityIx
Me. subForm.LinkMasterFields = nFacilityIx
Case 3
. etc.
Case else
End select

The Facility1, Facility2, are the facility Group / Type (8 Facilities as
I
mentioned)
The nFacilityIx is the unique identifier of each facility (endless
items).
The field nFacilityIx are both in the main and in the sub-forms, so it
can
be used for all the subforms as child and master field.

The code that I've write is not working properly, and I will be happy to
receive any help on that.
Thank

Stop, step back, and rethink your table design is my best advice. Any
time you
have eight identical or basically-identical tables you're on the wrong
track!
--

John W. Vinson [MVP]

well, i have a database that stores test data for material certifications.
different cert types require different *sets of tests*. i made a table that
lists all the cert types, called tblCertTypes. then i made a table that
lists all the available tests, called tblTests, with a Yes/No field that
designates whether the test value is numeric or text, because some test
values will need to be treated as Number data type, some as Text data type.
then i made a linking table, called tblCertTypeTests that lists all the
specific tests required for each cert type.

then i made a table to hold finished test records, called tblLabTests, which
stores the cert type, the test date, and the name of the lab technician who
performed the tests. and a child table called tblLabTestDetails to hold the
individual test values for the *set of tests* for the test record stored in
tblLabTests. tblLabTestDetails has a primary key field, a foreign key field
linking it to tblLabTests, another foreign key field linking it to tblTests,
and a text field to store the test value.

consider a similar setup for your situation, where tblCertTypes corresponds
to tblFacilityTypes, and tblTests corresponds to a table that lists all the
information that will be needed for all the facilitiy types
(tblRequirements), as "passenger number allowed", "weight allowed to carry",
"radius load allowed", "serial number", "activiation pressure", "boiler
license", etc. and tblCertTypeTests corresponds to a table
(tblFacilityTypeRequirements) that lists all the specific information
required for each specific facility type. now you have the setup to make a
child table (T_FacilitiesRequirements), related back to your T_Facilities
table, in which you can store all the information for each facility record
in T_Facilities.

expanding facilities is made easy. no new tables, queries, forms, etc. just
add a new record to tblFacilityTypes, add any new records needed to
tblRequirements, add new records in tblFacilityTypeRequirements to identify
the specific information required for the new facility type. then add your
new facility to T_Facilities, and add its' details to
T_FacilitiesRequirements.

the form level is much easier also: one main form, bound to T_Facilities,
with one subform, bound to T_FacilitiesRequirements.

hth


"Alu_GK" wrote in message
...
Hello -
Thanks for your reply.. I will try and explain the table design.
I will give an example of the facility so you can understand why I design
it
the way I did.
The facility we are talking about are for example - Elavators (Type1),
Lifting Machines (Like cranes) (Type2), Steam Boilers (Type3), etc.
Each Type includes identical information fields, such as [description],
[manufacturer], [Location] in the customer area, [FacilityType], etc. this
information is in one table - T_Facilities. For each record I also keep
the
"Facility Type" field.
For Each Facility Type, there are fields that are unique for this
facility.
For an example -
Type1-Elevator (table name "T_1Elevator")- [Passenger Number allowed],
[Weight allowed to carry], [passenger or luggage elevator], etc.
Type2 - Lifting Machine (table name "T_2Lifting") - [Radius Load allowed],
Serial Number, Safe load, etc.
Type3 - Steam Boiler (table name "T_3SteaBoiler") - Pressure allowed,
Activation Pressure, Boiler Licenses, etc.
The Field that connect the T_Facility with the T_xFacilityType is the
Primary key of the Facility itself, and it connected with one-to-one
relationship.

This is the reason why I keep separated tables for the different
facilities
and one that includes the basic data.
I can tell you that if a new facility will be needed - its correct I will
need to define a new set of table, queries, forms and so on, and it is
taking
into consideration.

The [Type] field in the table T_Facility is enables me to define what kind
of subform I need to show in order to fill in the unique values for each
facility.
I addition, I open another form in order to fill more unique info for the
facility, therefore I don't see any other option rather that define one
major
table and a uniqe info tables for the different facility types. I can tell
you that there is no parallel information in the facilities tables, and
they
all includes 7 or more fields that are unique for this specific facility.

This is the explanation. I hope I made clear the purpose of the table
design. And I hope I convince you that this is the best way. Please do
tell
me if you think of a better way to do this (after you know all the above).

Given this information - can you think of a better way to show information
on a sub form, instead of show/hide the sub forms according to the
facility
type.

Thank you, I hope I didn't tire you with all this information.

Alu.

--
Alu_GK


"John W. Vinson" wrote:

On Sun, 26 Oct 2008 08:47:00 -0700, Alu_GK

wrote:

I'm running a db for a Facilities report.
I have 8 kind of facilities.
I've created 1 table that includes the fields that are identical in
those
facilities - "T_Facility" (such as PK, facility name, facility
location,
facility manufacturer, etc..), and separated the rest of the fields
into 8
different tables, each table relate to a certain facility.

Ummm... sorry. That's not a correct design. What will you do when they
open
two more facilities? Two new tables, all new queries, all new code!?

You do not store data (the identity of a facility) in a table name.
Instead
you store the facility's identity *as data in a field*. Rather than
multiplying the number of tables, you add new *RECORDS* to your table.

In the table "T_Facility" I have a field the identify the type of the
facility (e.g. Facility1, Facility2, Facility3.) by a Facility Group
Number.
I've created a form that has 8 (!!) subforms, and on the load event of
the
main form the relevant form is shown (subform_facility1.visible=true)
and the
other are visible=false.

I need an advise how to make it better. This method load all the 8
forms
even though they are not all shown.

One form, one subform - filtered by the identity of the facility.

I tried to change the source object of a sub form, in an on open event,
according to the facility group in the main form, but it's not working.

A Subform will actually be populated BEFORE the mainform opens, so the
mainform's Open event is too late.

I've create an event on open, that uses
"select case FacilityNum
Case 1
Me.subForm.Sourceobject = Facility1
Me. subForm.LinkChildFields = nFacilityIx
Me. subForm.LinkMasterFields = nFacilityIx
Case 2
Sourceobject = Facility2
Me. subForm.LinkChildFields = nFacilityIx
Me. subForm.LinkMasterFields = nFacilityIx
Case 3
. etc.
Case else
End select

The Facility1, Facility2, are the facility Group / Type (8 Facilities
as I
mentioned)
The nFacilityIx is the unique identifier of each facility (endless
items).
The field nFacilityIx are both in the main and in the sub-forms, so it
can
be used for all the subforms as child and master field.

The code that I've write is not working properly, and I will be happy
to
receive any help on that.
Thank

Stop, step back, and rethink your table design is my best advice. Any
time you
have eight identical or basically-identical tables you're on the wrong
track!
--

John W. Vinson [MVP]

Thank you.
also to you Pete D.
tina - I will try this approach. I need to see how it fits to the variety of
fields of each facility. Up front it seems to be logically correct direction.
Thank you for your detailed explanation, which helped a lot to understand
this issue.
--
Alu_GK


"tina" wrote:

well, i have a database that stores test data for material certifications.
different cert types require different *sets of tests*. i made a table that
lists all the cert types, called tblCertTypes. then i made a table that
lists all the available tests, called tblTests, with a Yes/No field that
designates whether the test value is numeric or text, because some test
values will need to be treated as Number data type, some as Text data type.
then i made a linking table, called tblCertTypeTests that lists all the
specific tests required for each cert type.

then i made a table to hold finished test records, called tblLabTests, which
stores the cert type, the test date, and the name of the lab technician who
performed the tests. and a child table called tblLabTestDetails to hold the
individual test values for the *set of tests* for the test record stored in
tblLabTests. tblLabTestDetails has a primary key field, a foreign key field
linking it to tblLabTests, another foreign key field linking it to tblTests,
and a text field to store the test value.

consider a similar setup for your situation, where tblCertTypes corresponds
to tblFacilityTypes, and tblTests corresponds to a table that lists all the
information that will be needed for all the facilitiy types
(tblRequirements), as "passenger number allowed", "weight allowed to carry",
"radius load allowed", "serial number", "activiation pressure", "boiler
license", etc. and tblCertTypeTests corresponds to a table
(tblFacilityTypeRequirements) that lists all the specific information
required for each specific facility type. now you have the setup to make a
child table (T_FacilitiesRequirements), related back to your T_Facilities
table, in which you can store all the information for each facility record
in T_Facilities.

expanding facilities is made easy. no new tables, queries, forms, etc. just
add a new record to tblFacilityTypes, add any new records needed to
tblRequirements, add new records in tblFacilityTypeRequirements to identify
the specific information required for the new facility type. then add your
new facility to T_Facilities, and add its' details to
T_FacilitiesRequirements.

the form level is much easier also: one main form, bound to T_Facilities,
with one subform, bound to T_FacilitiesRequirements.

hth


"Alu_GK" wrote in message
...
Hello -
Thanks for your reply.. I will try and explain the table design.
I will give an example of the facility so you can understand why I design
it
the way I did.
The facility we are talking about are for example - Elavators (Type1),
Lifting Machines (Like cranes) (Type2), Steam Boilers (Type3), etc.
Each Type includes identical information fields, such as [description],
[manufacturer], [Location] in the customer area, [FacilityType], etc. this
information is in one table - T_Facilities. For each record I also keep
the
"Facility Type" field.
For Each Facility Type, there are fields that are unique for this
facility.
For an example -
Type1-Elevator (table name "T_1Elevator")- [Passenger Number allowed],
[Weight allowed to carry], [passenger or luggage elevator], etc.
Type2 - Lifting Machine (table name "T_2Lifting") - [Radius Load allowed],
Serial Number, Safe load, etc.
Type3 - Steam Boiler (table name "T_3SteaBoiler") - Pressure allowed,
Activation Pressure, Boiler Licenses, etc.
The Field that connect the T_Facility with the T_xFacilityType is the
Primary key of the Facility itself, and it connected with one-to-one
relationship.

This is the reason why I keep separated tables for the different
facilities
and one that includes the basic data.
I can tell you that if a new facility will be needed - its correct I will
need to define a new set of table, queries, forms and so on, and it is
taking
into consideration.

The [Type] field in the table T_Facility is enables me to define what kind
of subform I need to show in order to fill in the unique values for each
facility.
I addition, I open another form in order to fill more unique info for the
facility, therefore I don't see any other option rather that define one
major
table and a uniqe info tables for the different facility types. I can tell
you that there is no parallel information in the facilities tables, and
they
all includes 7 or more fields that are unique for this specific facility.

This is the explanation. I hope I made clear the purpose of the table
design. And I hope I convince you that this is the best way. Please do
tell
me if you think of a better way to do this (after you know all the above).

Given this information - can you think of a better way to show information
on a sub form, instead of show/hide the sub forms according to the
facility
type.

Thank you, I hope I didn't tire you with all this information.

Alu.

--
Alu_GK


"John W. Vinson" wrote:

On Sun, 26 Oct 2008 08:47:00 -0700, Alu_GK

wrote:

I'm running a db for a Facilities report.
I have 8 kind of facilities.
I've created 1 table that includes the fields that are identical in
those
facilities - "T_Facility" (such as PK, facility name, facility
location,
facility manufacturer, etc..), and separated the rest of the fields
into 8
different tables, each table relate to a certain facility.

Ummm... sorry. That's not a correct design. What will you do when they
open
two more facilities? Two new tables, all new queries, all new code!?

You do not store data (the identity of a facility) in a table name.
Instead
you store the facility's identity *as data in a field*. Rather than
multiplying the number of tables, you add new *RECORDS* to your table.

In the table "T_Facility" I have a field the identify the type of the
facility (e.g. Facility1, Facility2, Facility3.) by a Facility Group
Number.
I've created a form that has 8 (!!) subforms, and on the load event of
the
main form the relevant form is shown (subform_facility1.visible=true)
and the
other are visible=false.

I need an advise how to make it better. This method load all the 8
forms
even though they are not all shown.

One form, one subform - filtered by the identity of the facility.

I tried to change the source object of a sub form, in an on open event,
according to the facility group in the main form, but it's not working.

A Subform will actually be populated BEFORE the mainform opens, so the
mainform's Open event is too late.

I've create an event on open, that uses
"select case FacilityNum
Case 1
Me.subForm.Sourceobject = Facility1
Me. subForm.LinkChildFields = nFacilityIx
Me. subForm.LinkMasterFields = nFacilityIx
Case 2
Sourceobject = Facility2
Me. subForm.LinkChildFields = nFacilityIx
Me. subForm.LinkMasterFields = nFacilityIx
Case 3
. etc.
Case else
End select

The Facility1, Facility2, are the facility Group / Type (8 Facilities
as I
mentioned)
The nFacilityIx is the unique identifier of each facility (endless
items).
The field nFacilityIx are both in the main and in the sub-forms, so it
can
be used for all the subforms as child and master field.

The code that I've write is not working properly, and I will be happy
to
receive any help on that.
Thank

Stop, step back, and rethink your table design is my best advice. Any
time you
have eight identical or basically-identical tables you're on the wrong
track!
--

John W. Vinson [MVP]

The Manufacturer field is in the main T_Facility Table, that are includes all
facilities, for exactlly the reason you mentioned.
because that information relevant for all facilties. the different os only
in fields that the info structure is deffier - for example - the "number of
passenger" is relevant only for elevators, and not for all the facilities.
I think what tina suggested can work, but i need to get into it deeper in
order to coclude that this is the way.
Thank you for your help.
--
Alu_GK


"Pete D." wrote:

You are missing the point, each item (facility) you describe should be in
one table and types, manufacture... may require other tables but by doing it
the way you are now you might just a well make a spreadsheet and one sheet
per facility as you will not be able to leverage the power of Access. For
instance, you only need one record for a manufacture with phone, address
etc. This record can be related to all facilities this manufacture has any
relation to. Elevator computer IBM, Steam Boiler control computer IBM,
Lifting Machine controller, IBM. Why put it in three records when one will
do. We have over 1000 facilities on a military base and all are in one
table. Elevators, heating sytems, fire alarms, emergency lighting are all
assesories and in many situations are identical manufactures for fire alarms
and emergency lighting. Why type it twice. Might want to search Google
about relational database design and do a little reading. Your working way
to hard.

"Alu_GK" wrote in message
...
Hello -
Thanks for your reply.. I will try and explain the table design.
I will give an example of the facility so you can understand why I design
it
the way I did.
The facility we are talking about are for example - Elavators (Type1),
Lifting Machines (Like cranes) (Type2), Steam Boilers (Type3), etc.
Each Type includes identical information fields, such as [description],
[manufacturer], [Location] in the customer area, [FacilityType], etc. this
information is in one table - T_Facilities. For each record I also keep
the
"Facility Type" field.
For Each Facility Type, there are fields that are unique for this
facility.
For an example -
Type1-Elevator (table name "T_1Elevator")- [Passenger Number allowed],
[Weight allowed to carry], [passenger or luggage elevator], etc.
Type2 - Lifting Machine (table name "T_2Lifting") - [Radius Load allowed],
Serial Number, Safe load, etc.
Type3 - Steam Boiler (table name "T_3SteaBoiler") - Pressure allowed,
Activation Pressure, Boiler Licenses, etc.
The Field that connect the T_Facility with the T_xFacilityType is the
Primary key of the Facility itself, and it connected with one-to-one
relationship.

This is the reason why I keep separated tables for the different
facilities
and one that includes the basic data.
I can tell you that if a new facility will be needed - its correct I will
need to define a new set of table, queries, forms and so on, and it is
taking
into consideration.

The [Type] field in the table T_Facility is enables me to define what kind
of subform I need to show in order to fill in the unique values for each
facility.
I addition, I open another form in order to fill more unique info for the
facility, therefore I don't see any other option rather that define one
major
table and a uniqe info tables for the different facility types. I can tell
you that there is no parallel information in the facilities tables, and
they
all includes 7 or more fields that are unique for this specific facility.

This is the explanation. I hope I made clear the purpose of the table
design. And I hope I convince you that this is the best way. Please do
tell
me if you think of a better way to do this (after you know all the above).

Given this information - can you think of a better way to show information
on a sub form, instead of show/hide the sub forms according to the
facility
type.

Thank you, I hope I didn't tire you with all this information.

Alu.

--
Alu_GK


"John W. Vinson" wrote:

On Sun, 26 Oct 2008 08:47:00 -0700, Alu_GK

wrote:

I'm running a db for a Facilities report.
I have 8 kind of facilities.
I've created 1 table that includes the fields that are identical in
those
facilities - "T_Facility" (such as PK, facility name, facility location,
facility manufacturer, etc..), and separated the rest of the fields into
8
different tables, each table relate to a certain facility.

Ummm... sorry. That's not a correct design. What will you do when they
open
two more facilities? Two new tables, all new queries, all new code!?

You do not store data (the identity of a facility) in a table name.
Instead
you store the facility's identity *as data in a field*. Rather than
multiplying the number of tables, you add new *RECORDS* to your table.

In the table "T_Facility" I have a field the identify the type of the
facility (e.g. Facility1, Facility2, Facility3.) by a Facility Group
Number.
I've created a form that has 8 (!!) subforms, and on the load event of
the
main form the relevant form is shown (subform_facility1.visible=true)
and the
other are visible=false.

I need an advise how to make it better. This method load all the 8 forms
even though they are not all shown.

One form, one subform - filtered by the identity of the facility.

I tried to change the source object of a sub form, in an on open event,
according to the facility group in the main form, but it's not working.

A Subform will actually be populated BEFORE the mainform opens, so the
mainform's Open event is too late.

I've create an event on open, that uses
"select case FacilityNum
Case 1
Me.subForm.Sourceobject = Facility1
Me. subForm.LinkChildFields = nFacilityIx
Me. subForm.LinkMasterFields = nFacilityIx
Case 2
Sourceobject = Facility2
Me. subForm.LinkChildFields = nFacilityIx
Me. subForm.LinkMasterFields = nFacilityIx
Case 3
. etc.
Case else
End select

The Facility1, Facility2, are the facility Group / Type (8 Facilities as
I
mentioned)
The nFacilityIx is the unique identifier of each facility (endless
items).
The field nFacilityIx are both in the main and in the sub-forms, so it
can
be used for all the subforms as child and master field.

The code that I've write is not working properly, and I will be happy to
receive any help on that.
Thank

Stop, step back, and rethink your table design is my best advice. Any
time you
have eight identical or basically-identical tables you're on the wrong
track!
--

John W. Vinson [MVP]

you're very welcome, and good luck with it!


"Alu_GK" wrote in message
...
Thank you.
also to you Pete D.
tina - I will try this approach. I need to see how it fits to the variety
of
fields of each facility. Up front it seems to be logically correct
direction.
Thank you for your detailed explanation, which helped a lot to understand
this issue.
--
Alu_GK


"tina" wrote:

well, i have a database that stores test data for material
certifications.
different cert types require different *sets of tests*. i made a table
that
lists all the cert types, called tblCertTypes. then i made a table that
lists all the available tests, called tblTests, with a Yes/No field that
designates whether the test value is numeric or text, because some test
values will need to be treated as Number data type, some as Text data
type.
then i made a linking table, called tblCertTypeTests that lists all the
specific tests required for each cert type.

then i made a table to hold finished test records, called tblLabTests,
which
stores the cert type, the test date, and the name of the lab technician
who
performed the tests. and a child table called tblLabTestDetails to hold
the
individual test values for the *set of tests* for the test record stored
in
tblLabTests. tblLabTestDetails has a primary key field, a foreign key
field
linking it to tblLabTests, another foreign key field linking it to
tblTests,
and a text field to store the test value.

consider a similar setup for your situation, where tblCertTypes
corresponds
to tblFacilityTypes, and tblTests corresponds to a table that lists all
the
information that will be needed for all the facilitiy types
(tblRequirements), as "passenger number allowed", "weight allowed to
carry",
"radius load allowed", "serial number", "activiation pressure", "boiler
license", etc. and tblCertTypeTests corresponds to a table
(tblFacilityTypeRequirements) that lists all the specific information
required for each specific facility type. now you have the setup to make
a
child table (T_FacilitiesRequirements), related back to your
T_Facilities
table, in which you can store all the information for each facility
record
in T_Facilities.

expanding facilities is made easy. no new tables, queries, forms, etc.
just
add a new record to tblFacilityTypes, add any new records needed to
tblRequirements, add new records in tblFacilityTypeRequirements to
identify
the specific information required for the new facility type. then add
your
new facility to T_Facilities, and add its' details to
T_FacilitiesRequirements.

the form level is much easier also: one main form, bound to
T_Facilities,
with one subform, bound to T_FacilitiesRequirements.

hth


"Alu_GK" wrote in message
...
Hello -
Thanks for your reply.. I will try and explain the table design.
I will give an example of the facility so you can understand why I
design
it
the way I did.
The facility we are talking about are for example - Elavators (Type1),
Lifting Machines (Like cranes) (Type2), Steam Boilers (Type3), etc.
Each Type includes identical information fields, such as
[description],
[manufacturer], [Location] in the customer area, [FacilityType], etc.
this
information is in one table - T_Facilities. For each record I also
keep
the
"Facility Type" field.
For Each Facility Type, there are fields that are unique for this
facility.
For an example -
Type1-Elevator (table name "T_1Elevator")- [Passenger Number
allowed],
[Weight allowed to carry], [passenger or luggage elevator], etc.
Type2 - Lifting Machine (table name "T_2Lifting") - [Radius Load
allowed],
Serial Number, Safe load, etc.
Type3 - Steam Boiler (table name "T_3SteaBoiler") - Pressure allowed,
Activation Pressure, Boiler Licenses, etc.
The Field that connect the T_Facility with the T_xFacilityType is the
Primary key of the Facility itself, and it connected with one-to-one
relationship.

This is the reason why I keep separated tables for the different
facilities
and one that includes the basic data.
I can tell you that if a new facility will be needed - its correct I
will
need to define a new set of table, queries, forms and so on, and it is
taking
into consideration.

The [Type] field in the table T_Facility is enables me to define what
kind
of subform I need to show in order to fill in the unique values for
each
facility.
I addition, I open another form in order to fill more unique info for
the
facility, therefore I don't see any other option rather that define
one
major
table and a uniqe info tables for the different facility types. I can
tell
you that there is no parallel information in the facilities tables,
and
they
all includes 7 or more fields that are unique for this specific
facility.

This is the explanation. I hope I made clear the purpose of the table
design. And I hope I convince you that this is the best way. Please do
tell
me if you think of a better way to do this (after you know all the
above).

Given this information - can you think of a better way to show
information
on a sub form, instead of show/hide the sub forms according to the
facility
type.

Thank you, I hope I didn't tire you with all this information.

Alu.

--
Alu_GK


"John W. Vinson" wrote:

On Sun, 26 Oct 2008 08:47:00 -0700, Alu_GK

wrote:

I'm running a db for a Facilities report.
I have 8 kind of facilities.
I've created 1 table that includes the fields that are identical in
those
facilities - "T_Facility" (such as PK, facility name, facility
location,
facility manufacturer, etc..), and separated the rest of the fields
into 8
different tables, each table relate to a certain facility.

Ummm... sorry. That's not a correct design. What will you do when
they
open
two more facilities? Two new tables, all new queries, all new code!?

You do not store data (the identity of a facility) in a table name.
Instead
you store the facility's identity *as data in a field*. Rather than
multiplying the number of tables, you add new *RECORDS* to your
table.

In the table "T_Facility" I have a field the identify the type of
the
facility (e.g. Facility1, Facility2, Facility3.) by a Facility
Group
Number.
I've created a form that has 8 (!!) subforms, and on the load event
of
the
main form the relevant form is shown
(subform_facility1.visible=true)
and the
other are visible=false.

I need an advise how to make it better. This method load all the 8
forms
even though they are not all shown.

One form, one subform - filtered by the identity of the facility.

I tried to change the source object of a sub form, in an on open
event,
according to the facility group in the main form, but it's not
working.

A Subform will actually be populated BEFORE the mainform opens, so
the
mainform's Open event is too late.

I've create an event on open, that uses
"select case FacilityNum
Case 1
Me.subForm.Sourceobject = Facility1
Me. subForm.LinkChildFields = nFacilityIx
Me. subForm.LinkMasterFields = nFacilityIx
Case 2
Sourceobject = Facility2
Me. subForm.LinkChildFields = nFacilityIx
Me. subForm.LinkMasterFields = nFacilityIx
Case 3
. etc.
Case else
End select

The Facility1, Facility2, are the facility Group / Type (8
Facilities
as I
mentioned)
The nFacilityIx is the unique identifier of each facility (endless
items).
The field nFacilityIx are both in the main and in the sub-forms, so
it
can
be used for all the subforms as child and master field.

The code that I've write is not working properly, and I will be
happy
to
receive any help on that.
Thank

Stop, step back, and rethink your table design is my best advice.
Any
time you
have eight identical or basically-identical tables you're on the
wrong
track!
--

John W. Vinson [MVP]

Hello -
I was trying to apply the method that tina mention, it didn't work, I have
to many difrentiatio between the Facilities and each one of them is an entity
by itself. Any way - The Problem of loding to many subforms and show only the
relevant one, has solved with the following:
I define 1 subform with an unbound name to it, and according to the Facility
Group I define its SourceObject to the correct sub-form.
When i tried it before the problem was that the name of the form was bound
to one of the subforms, and it wasn't stand alone.
That it.
Thank again for all your help.
--
Alu_GK


"tina" wrote:

you're very welcome, and good luck with it!


"Alu_GK" wrote in message
...
Thank you.
also to you Pete D.
tina - I will try this approach. I need to see how it fits to the variety
of
fields of each facility. Up front it seems to be logically correct
direction.
Thank you for your detailed explanation, which helped a lot to understand
this issue.
--
Alu_GK


"tina" wrote:

well, i have a database that stores test data for material
certifications.
different cert types require different *sets of tests*. i made a table
that
lists all the cert types, called tblCertTypes. then i made a table that
lists all the available tests, called tblTests, with a Yes/No field that
designates whether the test value is numeric or text, because some test
values will need to be treated as Number data type, some as Text data
type.
then i made a linking table, called tblCertTypeTests that lists all the
specific tests required for each cert type.

then i made a table to hold finished test records, called tblLabTests,
which
stores the cert type, the test date, and the name of the lab technician
who
performed the tests. and a child table called tblLabTestDetails to hold
the
individual test values for the *set of tests* for the test record stored
in
tblLabTests. tblLabTestDetails has a primary key field, a foreign key
field
linking it to tblLabTests, another foreign key field linking it to
tblTests,
and a text field to store the test value.

consider a similar setup for your situation, where tblCertTypes
corresponds
to tblFacilityTypes, and tblTests corresponds to a table that lists all
the
information that will be needed for all the facilitiy types
(tblRequirements), as "passenger number allowed", "weight allowed to
carry",
"radius load allowed", "serial number", "activiation pressure", "boiler
license", etc. and tblCertTypeTests corresponds to a table
(tblFacilityTypeRequirements) that lists all the specific information
required for each specific facility type. now you have the setup to make
a
child table (T_FacilitiesRequirements), related back to your
T_Facilities
table, in which you can store all the information for each facility
record
in T_Facilities.

expanding facilities is made easy. no new tables, queries, forms, etc.
just
add a new record to tblFacilityTypes, add any new records needed to
tblRequirements, add new records in tblFacilityTypeRequirements to
identify
the specific information required for the new facility type. then add
your
new facility to T_Facilities, and add its' details to
T_FacilitiesRequirements.

the form level is much easier also: one main form, bound to
T_Facilities,
with one subform, bound to T_FacilitiesRequirements.

hth


"Alu_GK" wrote in message
...
Hello -
Thanks for your reply.. I will try and explain the table design.
I will give an example of the facility so you can understand why I
design
it
the way I did.
The facility we are talking about are for example - Elavators (Type1),
Lifting Machines (Like cranes) (Type2), Steam Boilers (Type3), etc.
Each Type includes identical information fields, such as
[description],
[manufacturer], [Location] in the customer area, [FacilityType], etc.
this
information is in one table - T_Facilities. For each record I also
keep
the
"Facility Type" field.
For Each Facility Type, there are fields that are unique for this
facility.
For an example -
Type1-Elevator (table name "T_1Elevator")- [Passenger Number
allowed],
[Weight allowed to carry], [passenger or luggage elevator], etc.
Type2 - Lifting Machine (table name "T_2Lifting") - [Radius Load
allowed],
Serial Number, Safe load, etc.
Type3 - Steam Boiler (table name "T_3SteaBoiler") - Pressure allowed,
Activation Pressure, Boiler Licenses, etc.
The Field that connect the T_Facility with the T_xFacilityType is the
Primary key of the Facility itself, and it connected with one-to-one
relationship.

This is the reason why I keep separated tables for the different
facilities
and one that includes the basic data.
I can tell you that if a new facility will be needed - its correct I
will
need to define a new set of table, queries, forms and so on, and it is
taking
into consideration.

The [Type] field in the table T_Facility is enables me to define what
kind
of subform I need to show in order to fill in the unique values for
each
facility.
I addition, I open another form in order to fill more unique info for
the
facility, therefore I don't see any other option rather that define
one
major
table and a uniqe info tables for the different facility types. I can
tell
you that there is no parallel information in the facilities tables,
and
they
all includes 7 or more fields that are unique for this specific
facility.

This is the explanation. I hope I made clear the purpose of the table
design. And I hope I convince you that this is the best way. Please do
tell
me if you think of a better way to do this (after you know all the
above).

Given this information - can you think of a better way to show
information
on a sub form, instead of show/hide the sub forms according to the
facility
type.

Thank you, I hope I didn't tire you with all this information.

Alu.

--
Alu_GK


"John W. Vinson" wrote:

On Sun, 26 Oct 2008 08:47:00 -0700, Alu_GK

wrote:

I'm running a db for a Facilities report.
I have 8 kind of facilities.
I've created 1 table that includes the fields that are identical in
those
facilities - "T_Facility" (such as PK, facility name, facility
location,
facility manufacturer, etc..), and separated the rest of the fields
into 8
different tables, each table relate to a certain facility.

Ummm... sorry. That's not a correct design. What will you do when
they
open
two more facilities? Two new tables, all new queries, all new code!?

You do not store data (the identity of a facility) in a table name.
Instead
you store the facility's identity *as data in a field*. Rather than
multiplying the number of tables, you add new *RECORDS* to your
table.

In the table "T_Facility" I have a field the identify the type of
the
facility (e.g. Facility1, Facility2, Facility3.) by a Facility
Group
Number.
I've created a form that has 8 (!!) subforms, and on the load event
of
the
main form the relevant form is shown
(subform_facility1.visible=true)
and the
other are visible=false.

I need an advise how to make it better. This method load all the 8
forms
even though they are not all shown.

One form, one subform - filtered by the identity of the facility.

I tried to change the source object of a sub form, in an on open
event,
according to the facility group in the main form, but it's not
working.

A Subform will actually be populated BEFORE the mainform opens, so
the
mainform's Open event is too late.

I've create an event on open, that uses
"select case FacilityNum
Case 1
Me.subForm.Sourceobject = Facility1
Me. subForm.LinkChildFields = nFacilityIx
Me. subForm.LinkMasterFields = nFacilityIx
Case 2
Sourceobject = Facility2
Me. subForm.LinkChildFields = nFacilityIx
Me. subForm.LinkMasterFields = nFacilityIx
Case 3
. etc.
Case else
End select

The Facility1, Facility2, are the facility Group / Type (8
Facilities
as I
mentioned)
The nFacilityIx is the unique identifier of each facility (endless
items).
The field nFacilityIx are both in the main and in the sub-forms, so
it
can
be used for all the subforms as child and master field.

The code that I've write is not working properly, and I will be
happy
to
receive any help on that.
Thank

Stop, step back, and rethink your table design is my best advice.
Any
time you
have eight identical or basically-identical tables you're on the
wrong
track!
--

John W. Vinson [MVP]