Form design difficulties

Am having difficulty with form design. After many attempts at trying To setup
a form design to allow data entry for a set(record) of data, I have failed
to achieve any workable results. I have done a lot of reading and reviewed
several tutorials without seeing the detailed choice descriptions allowing me
to succeed. The following are the tables in my database:

tblLiturgies
LitID (primary key, Number, field size Byte)
LitName (Text)

tblCycles
CycleID (pk, Number, field size Byte)
CycleName (Text)

tblLiturgyParts
PartID (pk, Number, field size Byte)
PartCode (Text; entries in this field would be "Prl", "InProc", "Gath", etc)
PartName (Text; entries in this field would be "Prelude", "Instrumental
Procession", "Gathering", etc)

tblHymns
HymnID (pk, Number, field size Byte)
HymnName (TEXT)
HymnNumber (TEXT)

tblLiturgyEvents
LitEventID (pk, Autonumber)
LitID (foreign key from tblLiturgies)
CycleID (fk from tblCycles)
EventDate (the particular date that this particular liturgy was delivered)

tblEventDetails
DetailID (pk, Autonumber)
LitEventID (fk from tblLiturgyEvents)
PartID (fk from tblLiturgyParts)
HymnID (fk from tblHymns)

the relationships a
tblLiturgies.LitID 1:n tblLiturgyEvents.LitID
tblCycles.CycleID 1:n tblLiturgyEvents.CycleID
tblLiturgyEvents.LitEventID 1:n tblEventDetails.LitEventID
tblLiturgyParts.PartID 1:n tblEventDetails.PartID
tblHymns.HymnID 1:n tblEventDetails.HymnID


My Main data tables are tblLiturgyEvents and tblEventDetails

Form design process:
• Selected “CREATE FORM BY USING WIZARD.�
• The first question is “WHICH FIELDS DO I WANT ON FORM?�
• I selected each table and the required fields as suggested.
o tblLitugies: LitName
o tblCycles: CycleName
o tblLitParts: PartCode, PartName
o tbl Hymns: HymnName, HymnNumber
• Next question is “HOW DO I WANT TO VIEW MY FORM?�
o Choices are FORM WITH SUBFORM(S) or LINKED FORM
• Selected Form with Subform(s)
• Next question is “WHAT LAYOUT WOULD YOU LIKE FOR EACH SUBFORM?�
o Used the default DATASHEET
• Selected the EXPEDITION style
• Selected titles for each form:
o FORM – Liturgies
o SUBFORM – Cycles Subform
o SUBFORM – Hymns Subform

Results: LitName field with drop down choice
Cycles subform with CycleName field and drop down choice
Hymns subform with PartCode, PartName, HymnName, HymnNumber fields and
No drop down choices.

• Upon attempting to input data I found the following:
• LitName field accepted data and allowed drop down choice
o Using the TAB key I found the cursor moved to the next record in that
field NOT THE NEXT FIELD on the form??? Do I need to specify
an ON
CLICK {event procedure} to the next field?
o CycleName field accepted data and allowed drop down choice with
same TAB key results.
• Upon choosing the PartCode field with the mouse I attempted to enter
data and got FIELD CANNOT BE UPDATED error message??? After selecting
the
OK button in the error message box the box went away and the cursor was
located after the character entered in the PartCode field and I was able
to enter
the data in the all the fields in the subform.

I have navigation controls in all three forms.
• I expected only the Main form controls hoping to enter data into each
field and moving to the next record.
• I am confused by my need to enter 11 unique parts with corresponding
Hymns and Hymn numbers into each record but there is only the ability
to enter
one part/hymn/hymn number per record.

I am sure there is a way but I do not understand enough to make it happen. I
have spent the last three weeks researching using the HELP feature of ACCESS
2003, recommended tutorials (DataPig Technologies were excellent) (DATABASE
DESIGN TIPS by Michael J. Hernandez) (CONRAD SYSTEMS DESIGN’s Database Design
101) , STEP BY STEP ACCESS 2003 by Online Training Solutions, Inc., HOW TO DO
EVERYTHING WITH ACCESS 2003 by Virginia Anderson. I have a sense of “Brain
dead� with form design but wish to go forward. Any suggestions on how to
proceed are most welcome. THANX

Designing databases in Access is not a trivial matter and you need to either
put in the effort to learn it or hire someone who can do it for you. To
expect a forum such as this to help you build a complex database by asking
questions - well it will take you a long time and be very laborious. Asking
specific questions is fine but no-one can tell you how to build a complete
database.
Normally when confronted by a complex problem, its best to break it up into
manageable parts, so I would proceed by building one small piece at a time.

-Dorian

"linronamy" wrote:

Am having difficulty with form design. After many attempts at trying To setup
a form design to allow data entry for a set(record) of data, I have failed
to achieve any workable results. I have done a lot of reading and reviewed
several tutorials without seeing the detailed choice descriptions allowing me
to succeed. The following are the tables in my database:

tblLiturgies
LitID (primary key, Number, field size Byte)
LitName (Text)

tblCycles
CycleID (pk, Number, field size Byte)
CycleName (Text)

tblLiturgyParts
PartID (pk, Number, field size Byte)
PartCode (Text; entries in this field would be "Prl", "InProc", "Gath", etc)
PartName (Text; entries in this field would be "Prelude", "Instrumental
Procession", "Gathering", etc)

tblHymns
HymnID (pk, Number, field size Byte)
HymnName (TEXT)
HymnNumber (TEXT)

tblLiturgyEvents
LitEventID (pk, Autonumber)
LitID (foreign key from tblLiturgies)
CycleID (fk from tblCycles)
EventDate (the particular date that this particular liturgy was delivered)

tblEventDetails
DetailID (pk, Autonumber)
LitEventID (fk from tblLiturgyEvents)
PartID (fk from tblLiturgyParts)
HymnID (fk from tblHymns)

the relationships a
tblLiturgies.LitID 1:n tblLiturgyEvents.LitID
tblCycles.CycleID 1:n tblLiturgyEvents.CycleID
tblLiturgyEvents.LitEventID 1:n tblEventDetails.LitEventID
tblLiturgyParts.PartID 1:n tblEventDetails.PartID
tblHymns.HymnID 1:n tblEventDetails.HymnID


My Main data tables are tblLiturgyEvents and tblEventDetails

Form design process:
• Selected “CREATE FORM BY USING WIZARD.�
• The first question is “WHICH FIELDS DO I WANT ON FORM?�
• I selected each table and the required fields as suggested.
o tblLitugies: LitName
o tblCycles: CycleName
o tblLitParts: PartCode, PartName
o tbl Hymns: HymnName, HymnNumber
• Next question is “HOW DO I WANT TO VIEW MY FORM?�
o Choices are FORM WITH SUBFORM(S) or LINKED FORM
• Selected Form with Subform(s)
• Next question is “WHAT LAYOUT WOULD YOU LIKE FOR EACH SUBFORM?�
o Used the default DATASHEET
• Selected the EXPEDITION style
• Selected titles for each form:
o FORM – Liturgies
o SUBFORM – Cycles Subform
o SUBFORM – Hymns Subform

Results: LitName field with drop down choice
Cycles subform with CycleName field and drop down choice
Hymns subform with PartCode, PartName, HymnName, HymnNumber fields and
No drop down choices.

• Upon attempting to input data I found the following:
• LitName field accepted data and allowed drop down choice
o Using the TAB key I found the cursor moved to the next record in that
field NOT THE NEXT FIELD on the form??? Do I need to specify
an ON
CLICK {event procedure} to the next field?
o CycleName field accepted data and allowed drop down choice with
same TAB key results.
• Upon choosing the PartCode field with the mouse I attempted to enter
data and got FIELD CANNOT BE UPDATED error message??? After selecting
the
OK button in the error message box the box went away and the cursor was
located after the character entered in the PartCode field and I was able
to enter
the data in the all the fields in the subform.

I have navigation controls in all three forms.
• I expected only the Main form controls hoping to enter data into each
field and moving to the next record.
• I am confused by my need to enter 11 unique parts with corresponding
Hymns and Hymn numbers into each record but there is only the ability
to enter
one part/hymn/hymn number per record.

I am sure there is a way but I do not understand enough to make it happen. I
have spent the last three weeks researching using the HELP feature of ACCESS
2003, recommended tutorials (DataPig Technologies were excellent) (DATABASE
DESIGN TIPS by Michael J. Hernandez) (CONRAD SYSTEMS DESIGN’s Database Design
101) , STEP BY STEP ACCESS 2003 by Online Training Solutions, Inc., HOW TO DO
EVERYTHING WITH ACCESS 2003 by Virginia Anderson. I have a sense of “Brain
dead� with form design but wish to go forward. Any suggestions on how to
proceed are most welcome. THANX

I cannot understand what you are trying to do, i would base 1 form on one
table and use subforms to display the other information, you are using one
form to fill in parts of records for different tables

why dont you explain what you are hoping to do in english, then someone may
be able to help

"linronamy" wrote:

Am having difficulty with form design. After many attempts at trying To setup
a form design to allow data entry for a set(record) of data, I have failed
to achieve any workable results. I have done a lot of reading and reviewed
several tutorials without seeing the detailed choice descriptions allowing me
to succeed. The following are the tables in my database:

tblLiturgies
LitID (primary key, Number, field size Byte)
LitName (Text)

tblCycles
CycleID (pk, Number, field size Byte)
CycleName (Text)

tblLiturgyParts
PartID (pk, Number, field size Byte)
PartCode (Text; entries in this field would be "Prl", "InProc", "Gath", etc)
PartName (Text; entries in this field would be "Prelude", "Instrumental
Procession", "Gathering", etc)

tblHymns
HymnID (pk, Number, field size Byte)
HymnName (TEXT)
HymnNumber (TEXT)

tblLiturgyEvents
LitEventID (pk, Autonumber)
LitID (foreign key from tblLiturgies)
CycleID (fk from tblCycles)
EventDate (the particular date that this particular liturgy was delivered)

tblEventDetails
DetailID (pk, Autonumber)
LitEventID (fk from tblLiturgyEvents)
PartID (fk from tblLiturgyParts)
HymnID (fk from tblHymns)

the relationships a
tblLiturgies.LitID 1:n tblLiturgyEvents.LitID
tblCycles.CycleID 1:n tblLiturgyEvents.CycleID
tblLiturgyEvents.LitEventID 1:n tblEventDetails.LitEventID
tblLiturgyParts.PartID 1:n tblEventDetails.PartID
tblHymns.HymnID 1:n tblEventDetails.HymnID


My Main data tables are tblLiturgyEvents and tblEventDetails

Form design process:
• Selected “CREATE FORM BY USING WIZARD.�
• The first question is “WHICH FIELDS DO I WANT ON FORM?�
• I selected each table and the required fields as suggested.
o tblLitugies: LitName
o tblCycles: CycleName
o tblLitParts: PartCode, PartName
o tbl Hymns: HymnName, HymnNumber
• Next question is “HOW DO I WANT TO VIEW MY FORM?�
o Choices are FORM WITH SUBFORM(S) or LINKED FORM
• Selected Form with Subform(s)
• Next question is “WHAT LAYOUT WOULD YOU LIKE FOR EACH SUBFORM?�
o Used the default DATASHEET
• Selected the EXPEDITION style
• Selected titles for each form:
o FORM – Liturgies
o SUBFORM – Cycles Subform
o SUBFORM – Hymns Subform

Results: LitName field with drop down choice
Cycles subform with CycleName field and drop down choice
Hymns subform with PartCode, PartName, HymnName, HymnNumber fields and
No drop down choices.

• Upon attempting to input data I found the following:
• LitName field accepted data and allowed drop down choice
o Using the TAB key I found the cursor moved to the next record in that
field NOT THE NEXT FIELD on the form??? Do I need to specify
an ON
CLICK {event procedure} to the next field?
o CycleName field accepted data and allowed drop down choice with
same TAB key results.
• Upon choosing the PartCode field with the mouse I attempted to enter
data and got FIELD CANNOT BE UPDATED error message??? After selecting
the
OK button in the error message box the box went away and the cursor was
located after the character entered in the PartCode field and I was able
to enter
the data in the all the fields in the subform.

I have navigation controls in all three forms.
• I expected only the Main form controls hoping to enter data into each
field and moving to the next record.
• I am confused by my need to enter 11 unique parts with corresponding
Hymns and Hymn numbers into each record but there is only the ability
to enter
one part/hymn/hymn number per record.

I am sure there is a way but I do not understand enough to make it happen. I
have spent the last three weeks researching using the HELP feature of ACCESS
2003, recommended tutorials (DataPig Technologies were excellent) (DATABASE
DESIGN TIPS by Michael J. Hernandez) (CONRAD SYSTEMS DESIGN’s Database Design
101) , STEP BY STEP ACCESS 2003 by Online Training Solutions, Inc., HOW TO DO
EVERYTHING WITH ACCESS 2003 by Virginia Anderson. I have a sense of “Brain
dead� with form design but wish to go forward. Any suggestions on how to
proceed are most welcome. THANX

I have spent a lot of time trying to learn enough to move forward in the
design process. Thru help on this forum, and a lot of effort (last 3 months)
I have completed the tables design phase (which I was told was MOST
important) and am now trying to understand the form design process. I can't
afford to hire someone. I don't expect this forum to build my database; I
hoped that a few tips heading me in the right direction to my specific
questions was reasonable. My questions were related to my experiences while
using the “CREATE FORM BY USING WIZARD� choice. I did not understand the
results and spent the last 3 weeks trying to understand those results before
my 3/8 posting. I thought this forum choice was for NEW USERS. If this forum
is only for simple Q/A that are easily found thru Access help (F1), I am
mistaken to use it. These are my current questions relating to creating a
form using the wizard:

1. Why couldn't the PartCode FIELD BE UPDATED?

2. Why 3 sets of Navigation controls for 1 data record set? Aren't the 3
forms linked together representing a complete record? I don't understand the
Form/Subforms relationship with respect to entering a complete record set,
one record at a time.

3. How do I configure the form to allow entry of data for 11 unique parts
of a specific liturgy with only 1 PartsName field? Do I use the 3 forms
(Main/Subform/subform) 11 times to complete entry of one record?

If these are unreasonable questions for this forum, pleae just state it as
such.

THANX


"mscertified" wrote:

Designing databases in Access is not a trivial matter and you need to either
put in the effort to learn it or hire someone who can do it for you. To
expect a forum such as this to help you build a complex database by asking
questions - well it will take you a long time and be very laborious. Asking
specific questions is fine but no-one can tell you how to build a complete
database.
Normally when confronted by a complex problem, its best to break it up into
manageable parts, so I would proceed by building one small piece at a time.

-Dorian

"linronamy" wrote:

Am having difficulty with form design. After many attempts at trying To setup
a form design to allow data entry for a set(record) of data, I have failed
to achieve any workable results. I have done a lot of reading and reviewed
several tutorials without seeing the detailed choice descriptions allowing me
to succeed. The following are the tables in my database:

tblLiturgies
LitID (primary key, Number, field size Byte)
LitName (Text)

tblCycles
CycleID (pk, Number, field size Byte)
CycleName (Text)

tblLiturgyParts
PartID (pk, Number, field size Byte)
PartCode (Text; entries in this field would be "Prl", "InProc", "Gath", etc)
PartName (Text; entries in this field would be "Prelude", "Instrumental
Procession", "Gathering", etc)

tblHymns
HymnID (pk, Number, field size Byte)
HymnName (TEXT)
HymnNumber (TEXT)

tblLiturgyEvents
LitEventID (pk, Autonumber)
LitID (foreign key from tblLiturgies)
CycleID (fk from tblCycles)
EventDate (the particular date that this particular liturgy was delivered)

tblEventDetails
DetailID (pk, Autonumber)
LitEventID (fk from tblLiturgyEvents)
PartID (fk from tblLiturgyParts)
HymnID (fk from tblHymns)

the relationships a
tblLiturgies.LitID 1:n tblLiturgyEvents.LitID
tblCycles.CycleID 1:n tblLiturgyEvents.CycleID
tblLiturgyEvents.LitEventID 1:n tblEventDetails.LitEventID
tblLiturgyParts.PartID 1:n tblEventDetails.PartID
tblHymns.HymnID 1:n tblEventDetails.HymnID


My Main data tables are tblLiturgyEvents and tblEventDetails

Form design process:
• Selected “CREATE FORM BY USING WIZARD.�
• The first question is “WHICH FIELDS DO I WANT ON FORM?�
• I selected each table and the required fields as suggested.
o tblLitugies: LitName
o tblCycles: CycleName
o tblLitParts: PartCode, PartName
o tbl Hymns: HymnName, HymnNumber
• Next question is “HOW DO I WANT TO VIEW MY FORM?�
o Choices are FORM WITH SUBFORM(S) or LINKED FORM
• Selected Form with Subform(s)
• Next question is “WHAT LAYOUT WOULD YOU LIKE FOR EACH SUBFORM?�
o Used the default DATASHEET
• Selected the EXPEDITION style
• Selected titles for each form:
o FORM – Liturgies
o SUBFORM – Cycles Subform
o SUBFORM – Hymns Subform

Results: LitName field with drop down choice
Cycles subform with CycleName field and drop down choice
Hymns subform with PartCode, PartName, HymnName, HymnNumber fields and
No drop down choices.

• Upon attempting to input data I found the following:
• LitName field accepted data and allowed drop down choice
o Using the TAB key I found the cursor moved to the next record in that
field NOT THE NEXT FIELD on the form??? Do I need to specify
an ON
CLICK {event procedure} to the next field?
o CycleName field accepted data and allowed drop down choice with
same TAB key results.
• Upon choosing the PartCode field with the mouse I attempted to enter
data and got FIELD CANNOT BE UPDATED error message??? After selecting
the
OK button in the error message box the box went away and the cursor was
located after the character entered in the PartCode field and I was able
to enter
the data in the all the fields in the subform.

I have navigation controls in all three forms.
• I expected only the Main form controls hoping to enter data into each
field and moving to the next record.
• I am confused by my need to enter 11 unique parts with corresponding
Hymns and Hymn numbers into each record but there is only the ability
to enter
one part/hymn/hymn number per record.

I am sure there is a way but I do not understand enough to make it happen. I
have spent the last three weeks researching using the HELP feature of ACCESS
2003, recommended tutorials (DataPig Technologies were excellent) (DATABASE
DESIGN TIPS by Michael J. Hernandez) (CONRAD SYSTEMS DESIGN’s Database Design
101) , STEP BY STEP ACCESS 2003 by Online Training Solutions, Inc., HOW TO DO
EVERYTHING WITH ACCESS 2003 by Virginia Anderson. I have a sense of “Brain
dead� with form design but wish to go forward. Any suggestions on how to
proceed are most welcome. THANX

I have to agree with somebody else who posted that I can't quite figure out
what you are trying to do, and that it may be best to explain your objective
in non-database terms. Also in concurrence with that post, a table
generally has a single record source. Forms based on related tables are
typically used as subforms.
Many people here are not familiar with the terminology; therefore the
relationships may be puzzling. For instance, according to your
relationships each Liturgy may have many events, and each Cycle may have
many events, but there is no connection between Liturgy and Cycle. That
each is related to LiturgyEvents does not establish a connection between
them.
In response to your questions:
1) It's very difficult to say just why PartCode can't be updated. I think
it needs to be in a subform of a form that is based on EventDetails, but I
can't be sure.
2) Each form and subform has its own navigation controls. Think of it this
way. If all you had was (I'm using my own terms here) a LiturgyEvent that
consists of a number of EventDetails, you would have tblLiturgyEvent and
tblEventDetails. LiturgyID is the PK of tblLiturgyEvent, and a field by the
same name is the FK of tblEventDetails. You establish a one-to-many between
those two LiturgyID fields, then use the form wizard. Choose view for the
subform. Now for each LiturgyEvent you can see all of the associated
details. I will have to admit I have not used the form wizard for quite a
while, but I think this would work.
3) See the answer to #2. There needs to be a one-to-many relationship
between Liturgy and Parts. Each Liturgy may have many Parts.

This is indeed a Getting Started forum, but your project is quite complex.
Table design is indeed the foundation of an Access database, but I do not
see how your table design can give you the desired results. A record in
tblLiturgy is a separate record from one in tblCycle. Putting fields from
both tables on one form does not relate the records to each other. I'm not
sure what it would do, but it wouldn't do that.

"linronamy" wrote in message
...
I have spent a lot of time trying to learn enough to move forward in the
design process. Thru help on this forum, and a lot of effort (last 3
months)
I have completed the tables design phase (which I was told was MOST
important) and am now trying to understand the form design process. I
can't
afford to hire someone. I don't expect this forum to build my database; I
hoped that a few tips heading me in the right direction to my specific
questions was reasonable. My questions were related to my experiences
while
using the "CREATE FORM BY USING WIZARD" choice. I did not understand the
results and spent the last 3 weeks trying to understand those results
before
my 3/8 posting. I thought this forum choice was for NEW USERS. If this
forum
is only for simple Q/A that are easily found thru Access help (F1), I am
mistaken to use it. These are my current questions relating to creating a
form using the wizard:

1. Why couldn't the PartCode FIELD BE UPDATED?

2. Why 3 sets of Navigation controls for 1 data record set? Aren't the 3
forms linked together representing a complete record? I don't understand
the
Form/Subforms relationship with respect to entering a complete record set,
one record at a time.

3. How do I configure the form to allow entry of data for 11 unique parts
of a specific liturgy with only 1 PartsName field? Do I use the 3 forms
(Main/Subform/subform) 11 times to complete entry of one record?

If these are unreasonable questions for this forum, pleae just state it as
such.

THANX


"mscertified" wrote:

Designing databases in Access is not a trivial matter and you need to
either
put in the effort to learn it or hire someone who can do it for you. To
expect a forum such as this to help you build a complex database by
asking
questions - well it will take you a long time and be very laborious.
Asking
specific questions is fine but no-one can tell you how to build a
complete
database.
Normally when confronted by a complex problem, its best to break it up
into
manageable parts, so I would proceed by building one small piece at a
time.

-Dorian

"linronamy" wrote:

Am having difficulty with form design. After many attempts at trying To
setup
a form design to allow data entry for a set(record) of data, I have
failed
to achieve any workable results. I have done a lot of reading and
reviewed
several tutorials without seeing the detailed choice descriptions
allowing me
to succeed. The following are the tables in my database:

tblLiturgies
LitID (primary key, Number, field size Byte)
LitName (Text)

tblCycles
CycleID (pk, Number, field size Byte)
CycleName (Text)

tblLiturgyParts
PartID (pk, Number, field size Byte)
PartCode (Text; entries in this field would be "Prl", "InProc", "Gath",
etc)
PartName (Text; entries in this field would be "Prelude", "Instrumental
Procession", "Gathering", etc)

tblHymns
HymnID (pk, Number, field size Byte)
HymnName (TEXT)
HymnNumber (TEXT)

tblLiturgyEvents
LitEventID (pk, Autonumber)
LitID (foreign key from tblLiturgies)
CycleID (fk from tblCycles)
EventDate (the particular date that this particular liturgy was
delivered)

tblEventDetails
DetailID (pk, Autonumber)
LitEventID (fk from tblLiturgyEvents)
PartID (fk from tblLiturgyParts)
HymnID (fk from tblHymns)

the relationships a
tblLiturgies.LitID 1:n tblLiturgyEvents.LitID
tblCycles.CycleID 1:n tblLiturgyEvents.CycleID
tblLiturgyEvents.LitEventID 1:n tblEventDetails.LitEventID
tblLiturgyParts.PartID 1:n tblEventDetails.PartID
tblHymns.HymnID 1:n tblEventDetails.HymnID


My Main data tables are tblLiturgyEvents and tblEventDetails

Form design process:
. Selected "CREATE FORM BY USING WIZARD."
. The first question is "WHICH FIELDS DO I WANT ON FORM?"
. I selected each table and the required fields as suggested.
o tblLitugies: LitName
o tblCycles: CycleName
o tblLitParts: PartCode, PartName
o tbl Hymns: HymnName, HymnNumber
. Next question is "HOW DO I WANT TO VIEW MY FORM?"
o Choices are FORM WITH SUBFORM(S) or LINKED FORM
. Selected Form with Subform(s)
. Next question is "WHAT LAYOUT WOULD YOU LIKE FOR EACH SUBFORM?"
o Used the default DATASHEET
. Selected the EXPEDITION style
. Selected titles for each form:
o FORM - Liturgies
o SUBFORM - Cycles Subform
o SUBFORM - Hymns Subform

Results: LitName field with drop down choice
Cycles subform with CycleName field and drop down choice
Hymns subform with PartCode, PartName, HymnName, HymnNumber fields and
No drop down choices.

. Upon attempting to input data I found the following:
. LitName field accepted data and allowed drop down choice
o Using the TAB key I found the cursor moved to the next record in
that
field NOT THE NEXT FIELD on the form??? Do I need to
specify
an ON
CLICK {event procedure} to the next field?
o CycleName field accepted data and allowed drop down choice with
same TAB key results.
. Upon choosing the PartCode field with the mouse I attempted to enter
data and got FIELD CANNOT BE UPDATED error message??? After
selecting
the
OK button in the error message box the box went away and the cursor
was
located after the character entered in the PartCode field and I was
able
to enter
the data in the all the fields in the subform.

I have navigation controls in all three forms.
. I expected only the Main form controls hoping to enter data into each
field and moving to the next record.
. I am confused by my need to enter 11 unique parts with corresponding
Hymns and Hymn numbers into each record but there is only the
ability
to enter
one part/hymn/hymn number per record.

I am sure there is a way but I do not understand enough to make it
happen. I
have spent the last three weeks researching using the HELP feature of
ACCESS
2003, recommended tutorials (DataPig Technologies were excellent)
(DATABASE
DESIGN TIPS by Michael J. Hernandez) (CONRAD SYSTEMS DESIGN's Database
Design
101) , STEP BY STEP ACCESS 2003 by Online Training Solutions, Inc., HOW
TO DO
EVERYTHING WITH ACCESS 2003 by Virginia Anderson. I have a sense of
"Brain
dead" with form design but wish to go forward. Any suggestions on how
to
proceed are most welcome. THANX

I'm not an expert in Access and I can't address your entire situation but
maybe I can point you in a helping direction. This link has several Table
design layouts for many different applications.
http://www.databaseanswers.org/data_models/index.htm
Also when using the Forms Wizard, the order you add the items to appear on
the form is the order in which you will tab through the items (even if you
move them around). You need to reassign their order if you want them to tab
through in a different order.

A record consists of data defining the music played during a sunday liturgy.

There are 52 liturgies per year.

There are three cycles of liturgies, meaning each liturgy will repeat every
3 years. (example, Liturgy 100 cycle A will be followed by Liturgy 100 Cycle
B one year later with different hymns and Liturgy 100 Cycle C will follow one
year later with yet different hymns than the previous two years.)

The liturgy has 11 parts, where a hymn is played.

A single record looks like this:
Liturgy Name
Cycle Name
Part1 Name, Hymn Name, Hymn Number
Part2 Name, Hymn Name, Hymn Number
Part3 Name, Hymn Name, Hymn Number
Part4 Name, Hymn Name, Hymn Number
and so on thru 11 parts.

I have six years of records, each on a single sheet.

GOAL:
1. Transfer from paper to a database.
2. Prepare new Liturgy Records for future Liturgies using past history to
choose from. (Planning new liturgies requires selecting hymns that
appropriately reflect the readings for each part of the liturgy. Difficult
when the responsible person with the experience moves on, requiring educating
the next responsible person without experience.) The database will make the
appropriate choices easily accessible and printable for future liturgies.

I hope this clarifies a little.

"Phil" wrote:

I cannot understand what you are trying to do, i would base 1 form on one
table and use subforms to display the other information, you are using one
form to fill in parts of records for different tables

why dont you explain what you are hoping to do in english, then someone may
be able to help

"linronamy" wrote:

Am having difficulty with form design. After many attempts at trying To setup
a form design to allow data entry for a set(record) of data, I have failed
to achieve any workable results. I have done a lot of reading and reviewed
several tutorials without seeing the detailed choice descriptions allowing me
to succeed. The following are the tables in my database:

tblLiturgies
LitID (primary key, Number, field size Byte)
LitName (Text)

tblCycles
CycleID (pk, Number, field size Byte)
CycleName (Text)

tblLiturgyParts
PartID (pk, Number, field size Byte)
PartCode (Text; entries in this field would be "Prl", "InProc", "Gath", etc)
PartName (Text; entries in this field would be "Prelude", "Instrumental
Procession", "Gathering", etc)

tblHymns
HymnID (pk, Number, field size Byte)
HymnName (TEXT)
HymnNumber (TEXT)

tblLiturgyEvents
LitEventID (pk, Autonumber)
LitID (foreign key from tblLiturgies)
CycleID (fk from tblCycles)
EventDate (the particular date that this particular liturgy was delivered)

tblEventDetails
DetailID (pk, Autonumber)
LitEventID (fk from tblLiturgyEvents)
PartID (fk from tblLiturgyParts)
HymnID (fk from tblHymns)

the relationships a
tblLiturgies.LitID 1:n tblLiturgyEvents.LitID
tblCycles.CycleID 1:n tblLiturgyEvents.CycleID
tblLiturgyEvents.LitEventID 1:n tblEventDetails.LitEventID
tblLiturgyParts.PartID 1:n tblEventDetails.PartID
tblHymns.HymnID 1:n tblEventDetails.HymnID


My Main data tables are tblLiturgyEvents and tblEventDetails

Form design process:
• Selected “CREATE FORM BY USING WIZARD.�
• The first question is “WHICH FIELDS DO I WANT ON FORM?�
• I selected each table and the required fields as suggested.
o tblLitugies: LitName
o tblCycles: CycleName
o tblLitParts: PartCode, PartName
o tbl Hymns: HymnName, HymnNumber
• Next question is “HOW DO I WANT TO VIEW MY FORM?�
o Choices are FORM WITH SUBFORM(S) or LINKED FORM
• Selected Form with Subform(s)
• Next question is “WHAT LAYOUT WOULD YOU LIKE FOR EACH SUBFORM?�
o Used the default DATASHEET
• Selected the EXPEDITION style
• Selected titles for each form:
o FORM – Liturgies
o SUBFORM – Cycles Subform
o SUBFORM – Hymns Subform

Results: LitName field with drop down choice
Cycles subform with CycleName field and drop down choice
Hymns subform with PartCode, PartName, HymnName, HymnNumber fields and
No drop down choices.

• Upon attempting to input data I found the following:
• LitName field accepted data and allowed drop down choice
o Using the TAB key I found the cursor moved to the next record in that
field NOT THE NEXT FIELD on the form??? Do I need to specify
an ON
CLICK {event procedure} to the next field?
o CycleName field accepted data and allowed drop down choice with
same TAB key results.
• Upon choosing the PartCode field with the mouse I attempted to enter
data and got FIELD CANNOT BE UPDATED error message??? After selecting
the
OK button in the error message box the box went away and the cursor was
located after the character entered in the PartCode field and I was able
to enter
the data in the all the fields in the subform.

I have navigation controls in all three forms.
• I expected only the Main form controls hoping to enter data into each
field and moving to the next record.
• I am confused by my need to enter 11 unique parts with corresponding
Hymns and Hymn numbers into each record but there is only the ability
to enter
one part/hymn/hymn number per record.

I am sure there is a way but I do not understand enough to make it happen. I
have spent the last three weeks researching using the HELP feature of ACCESS
2003, recommended tutorials (DataPig Technologies were excellent) (DATABASE
DESIGN TIPS by Michael J. Hernandez) (CONRAD SYSTEMS DESIGN’s Database Design
101) , STEP BY STEP ACCESS 2003 by Online Training Solutions, Inc., HOW TO DO
EVERYTHING WITH ACCESS 2003 by Virginia Anderson. I have a sense of “Brain
dead� with form design but wish to go forward. Any suggestions on how to
proceed are most welcome. THANX

A record consists of data defining the music played during a sunday liturgy.

There are 52 liturgies per year.

There are three cycles of liturgies, meaning each liturgy will repeat every
3 years. (example, Liturgy 100 cycle A will be followed by Liturgy 100 Cycle
B one year later with different hymns and Liturgy 100 Cycle C will follow one
year later with yet different hymns than the previous two years.)

The liturgy has 11 parts, where a hymn is played.

A single record looks like this:
Liturgy Name
Cycle Name
Part1 Name, Hymn Name, Hymn Number
Part2 Name, Hymn Name, Hymn Number
Part3 Name, Hymn Name, Hymn Number
Part4 Name, Hymn Name, Hymn Number
and so on thru 11 parts.

I have six years of records, each on a single sheet.

GOAL:
1. Transfer from paper to a database.
2. Prepare new Liturgy Records for future Liturgies using past history to
choose from. (Planning new liturgies requires selecting hymns that
appropriately reflect the readings for each part of the liturgy. Difficult
when the responsible person with the experience moves on, requiring educating
the next responsible person without experience.) The database will make the
appropriate choices easily accessible and printable for future liturgies.

About 2 months ago I had the tblLitugies configured with ID, liturgy, and
cycle fields and was told I needed to separate them for future query
flexibility. I probably have gone thru 20 or so table configurations with
corresponding forms without success.
I have this gut feeling that my inability to sense the table relationships
and how they affect each other is where my confusion begins.

In the beginning, I started with one table having one field for each
parameter thinking that if the form to be designed looked just like my
existing paper record layout, I could easily enter the data but viewing the
history to choose from for future liturgies didn't pan out. I was advised
against it.

"Phil" wrote:

I cannot understand what you are trying to do, i would base 1 form on one
table and use subforms to display the other information, you are using one
form to fill in parts of records for different tables

why dont you explain what you are hoping to do in english, then someone may
be able to help

"linronamy" wrote:

Am having difficulty with form design. After many attempts at trying To setup
a form design to allow data entry for a set(record) of data, I have failed
to achieve any workable results. I have done a lot of reading and reviewed
several tutorials without seeing the detailed choice descriptions allowing me
to succeed. The following are the tables in my database:

tblLiturgies
LitID (primary key, Number, field size Byte)
LitName (Text)

tblCycles
CycleID (pk, Number, field size Byte)
CycleName (Text)

tblLiturgyParts
PartID (pk, Number, field size Byte)
PartCode (Text; entries in this field would be "Prl", "InProc", "Gath", etc)
PartName (Text; entries in this field would be "Prelude", "Instrumental
Procession", "Gathering", etc)

tblHymns
HymnID (pk, Number, field size Byte)
HymnName (TEXT)
HymnNumber (TEXT)

tblLiturgyEvents
LitEventID (pk, Autonumber)
LitID (foreign key from tblLiturgies)
CycleID (fk from tblCycles)
EventDate (the particular date that this particular liturgy was delivered)

tblEventDetails
DetailID (pk, Autonumber)
LitEventID (fk from tblLiturgyEvents)
PartID (fk from tblLiturgyParts)
HymnID (fk from tblHymns)

the relationships a
tblLiturgies.LitID 1:n tblLiturgyEvents.LitID
tblCycles.CycleID 1:n tblLiturgyEvents.CycleID
tblLiturgyEvents.LitEventID 1:n tblEventDetails.LitEventID
tblLiturgyParts.PartID 1:n tblEventDetails.PartID
tblHymns.HymnID 1:n tblEventDetails.HymnID


My Main data tables are tblLiturgyEvents and tblEventDetails

Form design process:
• Selected “CREATE FORM BY USING WIZARD.�
• The first question is “WHICH FIELDS DO I WANT ON FORM?�
• I selected each table and the required fields as suggested.
o tblLitugies: LitName
o tblCycles: CycleName
o tblLitParts: PartCode, PartName
o tbl Hymns: HymnName, HymnNumber
• Next question is “HOW DO I WANT TO VIEW MY FORM?�
o Choices are FORM WITH SUBFORM(S) or LINKED FORM
• Selected Form with Subform(s)
• Next question is “WHAT LAYOUT WOULD YOU LIKE FOR EACH SUBFORM?�
o Used the default DATASHEET
• Selected the EXPEDITION style
• Selected titles for each form:
o FORM – Liturgies
o SUBFORM – Cycles Subform
o SUBFORM – Hymns Subform

Results: LitName field with drop down choice
Cycles subform with CycleName field and drop down choice
Hymns subform with PartCode, PartName, HymnName, HymnNumber fields and
No drop down choices.

• Upon attempting to input data I found the following:
• LitName field accepted data and allowed drop down choice
o Using the TAB key I found the cursor moved to the next record in that
field NOT THE NEXT FIELD on the form??? Do I need to specify
an ON
CLICK {event procedure} to the next field?
o CycleName field accepted data and allowed drop down choice with
same TAB key results.
• Upon choosing the PartCode field with the mouse I attempted to enter
data and got FIELD CANNOT BE UPDATED error message??? After selecting
the
OK button in the error message box the box went away and the cursor was
located after the character entered in the PartCode field and I was able
to enter
the data in the all the fields in the subform.

I have navigation controls in all three forms.
• I expected only the Main form controls hoping to enter data into each
field and moving to the next record.
• I am confused by my need to enter 11 unique parts with corresponding
Hymns and Hymn numbers into each record but there is only the ability
to enter
one part/hymn/hymn number per record.

I am sure there is a way but I do not understand enough to make it happen. I
have spent the last three weeks researching using the HELP feature of ACCESS
2003, recommended tutorials (DataPig Technologies were excellent) (DATABASE
DESIGN TIPS by Michael J. Hernandez) (CONRAD SYSTEMS DESIGN’s Database Design
101) , STEP BY STEP ACCESS 2003 by Online Training Solutions, Inc., HOW TO DO
EVERYTHING WITH ACCESS 2003 by Virginia Anderson. I have a sense of “Brain
dead� with form design but wish to go forward. Any suggestions on how to
proceed are most welcome. THANX

A record consists of data defining the music played during a sunday liturgy.

There are 52 liturgies per year.

There are three cycles of liturgies, meaning each liturgy will repeat every
3 years. (example, Liturgy 100 cycle A will be followed by Liturgy 100 Cycle
B one year later with different hymns and Liturgy 100 Cycle C will follow one
year later with yet different hymns than the previous two years.)

The liturgy has 11 parts, where a hymn is played.

A single record looks like this:
Liturgy Name
Cycle Name
Part1 Name, Hymn Name, Hymn Number
Part2 Name, Hymn Name, Hymn Number
Part3 Name, Hymn Name, Hymn Number
Part4 Name, Hymn Name, Hymn Number
and so on thru 11 parts.

I have six years of records, each on a single sheet.

GOAL:
1. Transfer from paper to a database.
2. Prepare new Liturgy Records for future Liturgies using past history to
choose from. (Planning new liturgies requires selecting hymns that
appropriately reflect the readings for each part of the liturgy. Difficult
when the responsible person with the experience moves on, requiring educating
the next responsible person without experience.) The database will make the
appropriate choices easily accessible and printable for future liturgies.

I have a lot of questions about how my current table/relationship setups
based on your response. I am still trying to digest them.

Thanks for responding.

"BruceM" wrote:

I have to agree with somebody else who posted that I can't quite figure out
what you are trying to do, and that it may be best to explain your objective
in non-database terms. Also in concurrence with that post, a table
generally has a single record source. Forms based on related tables are
typically used as subforms.
Many people here are not familiar with the terminology; therefore the
relationships may be puzzling. For instance, according to your
relationships each Liturgy may have many events, and each Cycle may have
many events, but there is no connection between Liturgy and Cycle. That
each is related to LiturgyEvents does not establish a connection between
them.
In response to your questions:
1) It's very difficult to say just why PartCode can't be updated. I think
it needs to be in a subform of a form that is based on EventDetails, but I
can't be sure.
2) Each form and subform has its own navigation controls. Think of it this
way. If all you had was (I'm using my own terms here) a LiturgyEvent that
consists of a number of EventDetails, you would have tblLiturgyEvent and
tblEventDetails. LiturgyID is the PK of tblLiturgyEvent, and a field by the
same name is the FK of tblEventDetails. You establish a one-to-many between
those two LiturgyID fields, then use the form wizard. Choose view for the
subform. Now for each LiturgyEvent you can see all of the associated
details. I will have to admit I have not used the form wizard for quite a
while, but I think this would work.
3) See the answer to #2. There needs to be a one-to-many relationship
between Liturgy and Parts. Each Liturgy may have many Parts.

This is indeed a Getting Started forum, but your project is quite complex.
Table design is indeed the foundation of an Access database, but I do not
see how your table design can give you the desired results. A record in
tblLiturgy is a separate record from one in tblCycle. Putting fields from
both tables on one form does not relate the records to each other. I'm not
sure what it would do, but it wouldn't do that.

"linronamy" wrote in message
...
I have spent a lot of time trying to learn enough to move forward in the
design process. Thru help on this forum, and a lot of effort (last 3
months)
I have completed the tables design phase (which I was told was MOST
important) and am now trying to understand the form design process. I
can't
afford to hire someone. I don't expect this forum to build my database; I
hoped that a few tips heading me in the right direction to my specific
questions was reasonable. My questions were related to my experiences
while
using the "CREATE FORM BY USING WIZARD" choice. I did not understand the
results and spent the last 3 weeks trying to understand those results
before
my 3/8 posting. I thought this forum choice was for NEW USERS. If this
forum
is only for simple Q/A that are easily found thru Access help (F1), I am
mistaken to use it. These are my current questions relating to creating a
form using the wizard:

1. Why couldn't the PartCode FIELD BE UPDATED?

2. Why 3 sets of Navigation controls for 1 data record set? Aren't the 3
forms linked together representing a complete record? I don't understand
the
Form/Subforms relationship with respect to entering a complete record set,
one record at a time.

3. How do I configure the form to allow entry of data for 11 unique parts
of a specific liturgy with only 1 PartsName field? Do I use the 3 forms
(Main/Subform/subform) 11 times to complete entry of one record?

If these are unreasonable questions for this forum, pleae just state it as
such.

THANX


"mscertified" wrote:

Designing databases in Access is not a trivial matter and you need to
either
put in the effort to learn it or hire someone who can do it for you. To
expect a forum such as this to help you build a complex database by
asking
questions - well it will take you a long time and be very laborious.
Asking
specific questions is fine but no-one can tell you how to build a
complete
database.
Normally when confronted by a complex problem, its best to break it up
into
manageable parts, so I would proceed by building one small piece at a
time.

-Dorian

"linronamy" wrote:

Am having difficulty with form design. After many attempts at trying To
setup
a form design to allow data entry for a set(record) of data, I have
failed
to achieve any workable results. I have done a lot of reading and
reviewed
several tutorials without seeing the detailed choice descriptions
allowing me
to succeed. The following are the tables in my database:

tblLiturgies
LitID (primary key, Number, field size Byte)
LitName (Text)

tblCycles
CycleID (pk, Number, field size Byte)
CycleName (Text)

tblLiturgyParts
PartID (pk, Number, field size Byte)
PartCode (Text; entries in this field would be "Prl", "InProc", "Gath",
etc)
PartName (Text; entries in this field would be "Prelude", "Instrumental
Procession", "Gathering", etc)

tblHymns
HymnID (pk, Number, field size Byte)
HymnName (TEXT)
HymnNumber (TEXT)

tblLiturgyEvents
LitEventID (pk, Autonumber)
LitID (foreign key from tblLiturgies)
CycleID (fk from tblCycles)
EventDate (the particular date that this particular liturgy was
delivered)

tblEventDetails
DetailID (pk, Autonumber)
LitEventID (fk from tblLiturgyEvents)
PartID (fk from tblLiturgyParts)
HymnID (fk from tblHymns)

the relationships a
tblLiturgies.LitID 1:n tblLiturgyEvents.LitID
tblCycles.CycleID 1:n tblLiturgyEvents.CycleID
tblLiturgyEvents.LitEventID 1:n tblEventDetails.LitEventID
tblLiturgyParts.PartID 1:n tblEventDetails.PartID
tblHymns.HymnID 1:n tblEventDetails.HymnID


My Main data tables are tblLiturgyEvents and tblEventDetails

Form design process:
. Selected "CREATE FORM BY USING WIZARD."
. The first question is "WHICH FIELDS DO I WANT ON FORM?"
. I selected each table and the required fields as suggested.
o tblLitugies: LitName
o tblCycles: CycleName
o tblLitParts: PartCode, PartName
o tbl Hymns: HymnName, HymnNumber
. Next question is "HOW DO I WANT TO VIEW MY FORM?"
o Choices are FORM WITH SUBFORM(S) or LINKED FORM
. Selected Form with Subform(s)
. Next question is "WHAT LAYOUT WOULD YOU LIKE FOR EACH SUBFORM?"
o Used the default DATASHEET
. Selected the EXPEDITION style
. Selected titles for each form:
o FORM - Liturgies
o SUBFORM - Cycles Subform
o SUBFORM - Hymns Subform

Results: LitName field with drop down choice
Cycles subform with CycleName field and drop down choice
Hymns subform with PartCode, PartName, HymnName, HymnNumber fields and
No drop down choices.

. Upon attempting to input data I found the following:
. LitName field accepted data and allowed drop down choice
o Using the TAB key I found the cursor moved to the next record in
that
field NOT THE NEXT FIELD on the form??? Do I need to
specify
an ON
CLICK {event procedure} to the next field?
o CycleName field accepted data and allowed drop down choice with
same TAB key results.
. Upon choosing the PartCode field with the mouse I attempted to enter
data and got FIELD CANNOT BE UPDATED error message??? After
selecting
the
OK button in the error message box the box went away and the cursor
was
located after the character entered in the PartCode field and I was
able
to enter
the data in the all the fields in the subform.

I have navigation controls in all three forms.
. I expected only the Main form controls hoping to enter data into each
field and moving to the next record.
. I am confused by my need to enter 11 unique parts with corresponding
Hymns and Hymn numbers into each record but there is only the
ability
to enter
one part/hymn/hymn number per record.

I am sure there is a way but I do not understand enough to make it
happen. I
have spent the last three weeks researching using the HELP feature of
ACCESS
2003, recommended tutorials (DataPig Technologies were excellent)
(DATABASE
DESIGN TIPS by Michael J. Hernandez) (CONRAD SYSTEMS DESIGN's Database
Design
101) , STEP BY STEP ACCESS 2003 by Online Training Solutions, Inc., HOW
TO DO
EVERYTHING WITH ACCESS 2003 by Virginia Anderson. I have a sense of
"Brain
dead" with form design but wish to go forward. Any suggestions on how
to
proceed are most welcome. THANX

Wow, that's a lot of hymns. Anyhow, now I begin to understand just what you
are trying to do. To clarify a few details, this is how I understand it
(correct me if I am in error):
Liturgy 100 is the same liturgy no matter the Cycle. In other words,
Liturgy 100 is a group of readings, lessons, etc. that go together. It does
not vary from year to year. I understand that the hymns change, but let's
leave them out for now.
Now for a few questions. Does Cycle A occur this calendar year, Cycle B
next year, and Cycle C the year after that, or are Cycle A, B, and C
scattered amongst each other? Of what does a liturgy part consist other
than the hymn? For what will a future database user search: Liturgy,
readings, hymns, liturgy events, or what?
Liturgy Events are the records that will be created as you go, as I
understand it. For each liturgy event you will select from a list of
existing liturgies. Am I correct so far? If I am, you can still create a
new liturgy, but you would do that separately from creating a liturgy event,
just as you need to add a person to the mailing list before you could
generate a mailing label for that person.
Here's something I don't quite get. Is Liturgy 100, Cycle A the same very
time (every three years, that is), or can the event details change three
years later?
In general, I see it something like this. You will create a liturgy event,
and select a liturgy. Since each liturgy may be associated with several
events, but each event contains just one liturgy, there is a one-to-many
relationship between liturgy events and liturgies (one liturgy, many
events). tblLiturgyEvents therefore needs LitID as a foreign key. You can
make a form based on tblLiturgyEvents, and select a liturgy from a combo box
that is based on tblLiturgy. The combo box is bound to LitID.
Are invariable details associated with that particular liturgy? That is,
are they the same every time the liturgy is used, or are details associated
with the event rather than the liturgy, or both? A simplified example may
help my understanding of this.
One more question for now. Do the 11 parts vary from event to event, or
does Liturgy 100 Cycle A always contain the exact same parts?

"linronamy" wrote in message
...
A record consists of data defining the music played during a sunday
liturgy.

There are 52 liturgies per year.

There are three cycles of liturgies, meaning each liturgy will repeat
every
3 years. (example, Liturgy 100 cycle A will be followed by Liturgy 100
Cycle
B one year later with different hymns and Liturgy 100 Cycle C will follow
one
year later with yet different hymns than the previous two years.)

The liturgy has 11 parts, where a hymn is played.

A single record looks like this:
Liturgy Name
Cycle Name
Part1 Name, Hymn Name, Hymn Number
Part2 Name, Hymn Name, Hymn Number
Part3 Name, Hymn Name, Hymn Number
Part4 Name, Hymn Name, Hymn Number
and so on thru 11 parts.

I have six years of records, each on a single sheet.

GOAL:
1. Transfer from paper to a database.
2. Prepare new Liturgy Records for future Liturgies using past history to
choose from. (Planning new liturgies requires selecting hymns that
appropriately reflect the readings for each part of the liturgy.
Difficult
when the responsible person with the experience moves on, requiring
educating
the next responsible person without experience.) The database will make
the
appropriate choices easily accessible and printable for future liturgies.

I have a lot of questions about how my current table/relationship setups
based on your response. I am still trying to digest them.

Thanks for responding.

"BruceM" wrote:

I have to agree with somebody else who posted that I can't quite figure
out
what you are trying to do, and that it may be best to explain your
objective
in non-database terms. Also in concurrence with that post, a table
generally has a single record source. Forms based on related tables are
typically used as subforms.
Many people here are not familiar with the terminology; therefore the
relationships may be puzzling. For instance, according to your
relationships each Liturgy may have many events, and each Cycle may have
many events, but there is no connection between Liturgy and Cycle. That
each is related to LiturgyEvents does not establish a connection between
them.
In response to your questions:
1) It's very difficult to say just why PartCode can't be updated. I
think
it needs to be in a subform of a form that is based on EventDetails, but
I
can't be sure.
2) Each form and subform has its own navigation controls. Think of it
this
way. If all you had was (I'm using my own terms here) a LiturgyEvent
that
consists of a number of EventDetails, you would have tblLiturgyEvent and
tblEventDetails. LiturgyID is the PK of tblLiturgyEvent, and a field by
the
same name is the FK of tblEventDetails. You establish a one-to-many
between
those two LiturgyID fields, then use the form wizard. Choose view for
the
subform. Now for each LiturgyEvent you can see all of the associated
details. I will have to admit I have not used the form wizard for quite
a
while, but I think this would work.
3) See the answer to #2. There needs to be a one-to-many relationship
between Liturgy and Parts. Each Liturgy may have many Parts.

This is indeed a Getting Started forum, but your project is quite
complex.
Table design is indeed the foundation of an Access database, but I do not
see how your table design can give you the desired results. A record in
tblLiturgy is a separate record from one in tblCycle. Putting fields
from
both tables on one form does not relate the records to each other. I'm
not
sure what it would do, but it wouldn't do that.

"linronamy" wrote in message
...
I have spent a lot of time trying to learn enough to move forward in the
design process. Thru help on this forum, and a lot of effort (last 3
months)
I have completed the tables design phase (which I was told was MOST
important) and am now trying to understand the form design process. I
can't
afford to hire someone. I don't expect this forum to build my database;
I
hoped that a few tips heading me in the right direction to my specific
questions was reasonable. My questions were related to my experiences
while
using the "CREATE FORM BY USING WIZARD" choice. I did not understand
the
results and spent the last 3 weeks trying to understand those results
before
my 3/8 posting. I thought this forum choice was for NEW USERS. If this
forum
is only for simple Q/A that are easily found thru Access help (F1), I
am
mistaken to use it. These are my current questions relating to creating
a
form using the wizard:

1. Why couldn't the PartCode FIELD BE UPDATED?

2. Why 3 sets of Navigation controls for 1 data record set? Aren't the
3
forms linked together representing a complete record? I don't
understand
the
Form/Subforms relationship with respect to entering a complete record
set,
one record at a time.

3. How do I configure the form to allow entry of data for 11 unique
parts
of a specific liturgy with only 1 PartsName field? Do I use the 3 forms
(Main/Subform/subform) 11 times to complete entry of one record?

If these are unreasonable questions for this forum, pleae just state it
as
such.

THANX


"mscertified" wrote:

Designing databases in Access is not a trivial matter and you need to
either
put in the effort to learn it or hire someone who can do it for you.
To
expect a forum such as this to help you build a complex database by
asking
questions - well it will take you a long time and be very laborious.
Asking
specific questions is fine but no-one can tell you how to build a
complete
database.
Normally when confronted by a complex problem, its best to break it up
into
manageable parts, so I would proceed by building one small piece at a
time.

-Dorian

"linronamy" wrote:

Am having difficulty with form design. After many attempts at trying
To
setup
a form design to allow data entry for a set(record) of data, I have
failed
to achieve any workable results. I have done a lot of reading and
reviewed
several tutorials without seeing the detailed choice descriptions
allowing me
to succeed. The following are the tables in my database:

tblLiturgies
LitID (primary key, Number, field size Byte)
LitName (Text)

tblCycles
CycleID (pk, Number, field size Byte)
CycleName (Text)

tblLiturgyParts
PartID (pk, Number, field size Byte)
PartCode (Text; entries in this field would be "Prl", "InProc",
"Gath",
etc)
PartName (Text; entries in this field would be "Prelude",
"Instrumental
Procession", "Gathering", etc)

tblHymns
HymnID (pk, Number, field size Byte)
HymnName (TEXT)
HymnNumber (TEXT)

tblLiturgyEvents
LitEventID (pk, Autonumber)
LitID (foreign key from tblLiturgies)
CycleID (fk from tblCycles)
EventDate (the particular date that this particular liturgy was
delivered)

tblEventDetails
DetailID (pk, Autonumber)
LitEventID (fk from tblLiturgyEvents)
PartID (fk from tblLiturgyParts)
HymnID (fk from tblHymns)

the relationships a
tblLiturgies.LitID 1:n tblLiturgyEvents.LitID
tblCycles.CycleID 1:n tblLiturgyEvents.CycleID
tblLiturgyEvents.LitEventID 1:n tblEventDetails.LitEventID
tblLiturgyParts.PartID 1:n tblEventDetails.PartID
tblHymns.HymnID 1:n tblEventDetails.HymnID


My Main data tables are tblLiturgyEvents and tblEventDetails

Form design process:
. Selected "CREATE FORM BY USING WIZARD."
. The first question is "WHICH FIELDS DO I WANT ON FORM?"
. I selected each table and the required fields as suggested.
o tblLitugies: LitName
o tblCycles: CycleName
o tblLitParts: PartCode, PartName
o tbl Hymns: HymnName, HymnNumber
. Next question is "HOW DO I WANT TO VIEW MY FORM?"
o Choices are FORM WITH SUBFORM(S) or LINKED FORM
. Selected Form with Subform(s)
. Next question is "WHAT LAYOUT WOULD YOU LIKE FOR EACH SUBFORM?"
o Used the default DATASHEET
. Selected the EXPEDITION style
. Selected titles for each form:
o FORM - Liturgies
o SUBFORM - Cycles Subform
o SUBFORM - Hymns Subform

Results: LitName field with drop down choice
Cycles subform with CycleName field and drop down choice
Hymns subform with PartCode, PartName, HymnName, HymnNumber fields
and
No drop down choices.

. Upon attempting to input data I found the following:
. LitName field accepted data and allowed drop down choice
o Using the TAB key I found the cursor moved to the next record
in
that
field NOT THE NEXT FIELD on the form??? Do I need to
specify
an ON
CLICK {event procedure} to the next field?
o CycleName field accepted data and allowed drop down choice with
same TAB key results.
. Upon choosing the PartCode field with the mouse I attempted to
enter
data and got FIELD CANNOT BE UPDATED error message??? After
selecting
the
OK button in the error message box the box went away and the
cursor
was
located after the character entered in the PartCode field and I
was
able
to enter
the data in the all the fields in the subform.

I have navigation controls in all three forms.
. I expected only the Main form controls hoping to enter data into
each
field and moving to the next record.
. I am confused by my need to enter 11 unique parts with
corresponding
Hymns and Hymn numbers into each record but there is only the
ability
to enter
one part/hymn/hymn number per record.

I am sure there is a way but I do not understand enough to make it
happen. I
have spent the last three weeks researching using the HELP feature
of
ACCESS
2003, recommended tutorials (DataPig Technologies were excellent)
(DATABASE
DESIGN TIPS by Michael J. Hernandez) (CONRAD SYSTEMS DESIGN's
Database
Design
101) , STEP BY STEP ACCESS 2003 by Online Training Solutions, Inc.,
HOW
TO DO
EVERYTHING WITH ACCESS 2003 by Virginia Anderson. I have a sense of
"Brain
dead" with form design but wish to go forward. Any suggestions on
how
to
proceed are most welcome. THANX