Design questions

Discussion in 'Access Table Design' started by Bruce, Jun 11, 2004.

  1. Bruce

    Bruce Guest

    I have successfully completed several smaller databases
    that work quite well. Now I am undertaking the largest
    project so far, and I have some design questions before I
    have completed the first table.
    My company performs a variety of manufacturing processes
    on a variety of Components. The process is detailed in a
    Process Plan, each of which is numbered in the format 04-
    20 (for the twentieth plan developed in 2004).
    Each Process Plan can be for one or more Components. The
    same process on two different Components could be two
    separate Process Plans, or it could be the same Process
    Plan. Also, one component could be processed according to
    several different Process Plans.
    Also, each Process Plan references one or more
    Specifications, but probably no more than 5.
    Here is what I have so far, ignoring for a moment the
    Specifications:

    tblProcessPlan
    PlanID (PK)
    Component ID (FK)
    PlanNumber (could probably be the PK, but I'm not sure)
    Revision
    Date
    Process
    Archive (Y/N, for queries)

    tblComponent
    ComponentID (PK)
    PlanID (FK)
    ComponentNumber (it is possible that 2 manufacturers
    will use the same number, so no PK here)
    ComponentName

    There are other fields, but this is enough for the
    question.
    When somebody looks up a Component (by selecting from a
    list), they should be able to see what Process Plans are
    associated with it. From there they will need to see the
    details of the selected Process Plan, including a listing
    of all Components that may be processed according to that
    plan.
    Each Process Plan can be for many Components, and each
    Component can be associated with many Process Plans, but I
    don't think this makes it a many-to-many relationship. I
    can't imagine a field in a junction table that would not
    be in one of the other two tables. Instead, I see
    tblProcessPlan as the record source for frmProcessPlan,
    and tblComponents as the record source for a subform
    fsubComponents.
    If I am still on track here, the Specifications are the
    next step. I think would have a Specifications table,
    with its PK a FK in tblProcessPlan. Each Process Plan can
    reference many Specifications, and each Specification can
    be associated with many Process Plans. As with
    Components, looking up a Specification would produce a
    listing of associated Process Plans. This is necessary
    because a revised Specification could mean revising the
    associated Process Plans.
    Am I heading in the right general direction with this?
    Any comments or suggestions?
     
    Bruce, Jun 11, 2004
    #1
    1. Advertisements

  2. Bruce

    tina Guest

    comments inline:

    "Bruce" <> wrote in message
    news:1b3f001c44fc8$3f7a9c30$...
    > I have successfully completed several smaller databases
    > that work quite well. Now I am undertaking the largest
    > project so far, and I have some design questions before I
    > have completed the first table.
    > My company performs a variety of manufacturing processes
    > on a variety of Components. The process is detailed in a
    > Process Plan, each of which is numbered in the format 04-
    > 20 (for the twentieth plan developed in 2004).
    > Each Process Plan can be for one or more Components. The
    > same process on two different Components could be two
    > separate Process Plans, or it could be the same Process
    > Plan. Also, one component could be processed according to
    > several different Process Plans.
    > Also, each Process Plan references one or more
    > Specifications, but probably no more than 5.
    > Here is what I have so far, ignoring for a moment the
    > Specifications:
    >
    > tblProcessPlan
    > PlanID (PK)
    > Component ID (FK)
    > PlanNumber (could probably be the PK, but I'm not sure)
    > Revision
    > Date
    > Process
    > Archive (Y/N, for queries)
    >

    so this allows you to link each specific process plan to a single specifc
    component. but how do you plan to link the same process plan to another
    specific component? in this two-table setup, you'd have to enter a duplicate
    process plan record for each component that plan was associated with. that
    solution violates table normalization rules.


    > tblComponent
    > ComponentID (PK)
    > PlanID (FK)
    > ComponentNumber (it is possible that 2 manufacturers
    > will use the same number, so no PK here)
    > ComponentName
    >

    again this allows you to link each specific component to a single specifc
    process plan. and again, you'd have to enter a duplicate component record
    for each process plan that component was associated with.


    > There are other fields, but this is enough for the
    > question.
    > When somebody looks up a Component (by selecting from a
    > list), they should be able to see what Process Plans are
    > associated with it. From there they will need to see the
    > details of the selected Process Plan, including a listing
    > of all Components that may be processed according to that
    > plan.
    > Each Process Plan can be for many Components, and each
    > Component can be associated with many Process Plans, but I
    > don't think this makes it a many-to-many relationship.


    it *is* a many-to-many relationship. to avoid the necessity for duplicate
    records in both tblProcessPlans and tblComponents, you need to remove the
    foreigns from each of those tables and put them in a third table, instead,
    as

    tblProcessPlanComponents
    PlanID (FK) (combo PK)
    ComponentID (FK) (combo PK)

    > I
    > can't imagine a field in a junction table that would not
    > be in one of the other two tables.


    it doesn't matter if there are no additional fields required in the linking
    table. it's purpose is to link the other two tables together while avoiding
    record duplication.


    > Instead, I see
    > tblProcessPlan as the record source for frmProcessPlan,
    > and tblComponents as the record source for a subform
    > fsubComponents.


    whoa. you jumped right over the issue of table relationships and straight
    into forms. forget about forms, until you have modeled normalized tables
    that correctly store and link all your data appropriately. you can't use a
    form to fix a table design problem, and you'll go crazy trying.


    > If I am still on track here, the Specifications are the
    > next step. I think would have a Specifications table,
    > with its PK a FK in tblProcessPlan. Each Process Plan can
    > reference many Specifications, and each Specification can
    > be associated with many Process Plans.


    again, a many-to-many relationship with the same issues noted above. and the
    same solution: use a linking table between process plans and
    specifications.

    > As with
    > Components, looking up a Specification would produce a
    > listing of associated Process Plans. This is necessary
    > because a revised Specification could mean revising the
    > associated Process Plans.
    > Am I heading in the right general direction with this?
    > Any comments or suggestions?


    hth
     
    tina, Jun 11, 2004
    #2
    1. Advertisements

  3. Bruce

    Bruce Guest

    Thanks for the speedy reply. My comments are also inline.
    >-----Original Message-----
    >comments inline:
    >
    >"Bruce" <> wrote in

    message
    >news:1b3f001c44fc8$3f7a9c30$...
    >> I have successfully completed several smaller databases
    >> that work quite well. Now I am undertaking the largest
    >> project so far, and I have some design questions before

    I
    >> have completed the first table.
    >> My company performs a variety of manufacturing processes
    >> on a variety of Components. The process is detailed in

    a
    >> Process Plan, each of which is numbered in the format

    04-
    >> 20 (for the twentieth plan developed in 2004).
    >> Each Process Plan can be for one or more Components.

    The
    >> same process on two different Components could be two
    >> separate Process Plans, or it could be the same Process
    >> Plan. Also, one component could be processed according

    to
    >> several different Process Plans.
    >> Also, each Process Plan references one or more
    >> Specifications, but probably no more than 5.
    >> Here is what I have so far, ignoring for a moment the
    >> Specifications:
    >>
    >> tblProcessPlan
    >> PlanID (PK)
    >> Component ID (FK)
    >> PlanNumber (could probably be the PK, but I'm not

    sure)
    >> Revision
    >> Date
    >> Process
    >> Archive (Y/N, for queries)
    >>

    >so this allows you to link each specific process plan to

    a single specifc
    >component. but how do you plan to link the same process

    plan to another
    >specific component? in this two-table setup, you'd have

    to enter a duplicate
    >process plan record for each component that plan was

    associated with. that
    >solution violates table normalization rules.


    Actually, I am aware of normalization rules, but I am
    relatively inexperienced in DB design. I am aware that
    duplicate data entry is generally to be avoided, and as I
    continued to experiment with the design I saw that the two
    table setup would be a problem in that regard.
    >
    >
    >> tblComponent
    >> ComponentID (PK)
    >> PlanID (FK)
    >> ComponentNumber (it is possible that 2 manufacturers
    >> will use the same number, so no PK here)
    >> ComponentName
    >>

    >again this allows you to link each specific component to

    a single specifc
    >process plan. and again, you'd have to enter a duplicate

    component record
    >for each process plan that component was associated with.
    >

    Not good.

    >> There are other fields, but this is enough for the
    >> question.
    >> When somebody looks up a Component (by selecting from a
    >> list), they should be able to see what Process Plans are
    >> associated with it. From there they will need to see

    the
    >> details of the selected Process Plan, including a

    listing
    >> of all Components that may be processed according to

    that
    >> plan.
    >> Each Process Plan can be for many Components, and each
    >> Component can be associated with many Process Plans,

    but I
    >> don't think this makes it a many-to-many relationship.

    >
    >it *is* a many-to-many relationship. to avoid the

    necessity for duplicate
    >records in both tblProcessPlans and tblComponents, you

    need to remove the
    >foreigns from each of those tables and put them in a

    third table, instead,
    >as
    >
    >tblProcessPlanComponents
    > PlanID (FK) (combo PK)
    > ComponentID (FK) (combo PK)
    >

    Again, I see your point.

    >> I
    >> can't imagine a field in a junction table that would not
    >> be in one of the other two tables.

    >
    >it doesn't matter if there are no additional fields

    required in the linking
    >table. it's purpose is to link the other two tables

    together while avoiding
    >record duplication.
    >

    I have designed one database that used a junction table
    that had a unique field. I fell into a trap of thinking
    the unique field justified use of the junction table, when
    all along it is the many-to-many that makes a junction
    table necessary. I appreciate your making the point,
    because I was a bit stuck there.

    >> Instead, I see
    >> tblProcessPlan as the record source for frmProcessPlan,
    >> and tblComponents as the record source for a subform
    >> fsubComponents.

    >
    >whoa. you jumped right over the issue of table

    relationships and straight
    >into forms. forget about forms, until you have modeled

    normalized tables
    >that correctly store and link all your data

    appropriately. you can't use a
    >form to fix a table design problem, and you'll go crazy

    trying.
    >

    I am not trying to solve a table problem with a form. I
    didn't know there was a table problem. Now I have built
    the tables and their relationships. I have imported a
    list of Component numbers and descriptions into
    tblComponents, and am now trying to associate a Process
    Plan with certain Component numbers. I have created a
    form based on tblProcessPlan. After entering the plan
    number, date, etc. I want to associate certain Component
    numbers with that Process Plan. Maybe it's just because
    it is Friday afternoon, but I can't sort out how to do
    that. If I am not getting ahead of myself I want to begin
    experimenting with data entry.
    >
    >> If I am still on track here, the Specifications are the
    >> next step. I think would have a Specifications table,
    >> with its PK a FK in tblProcessPlan. Each Process Plan

    can
    >> reference many Specifications, and each Specification

    can
    >> be associated with many Process Plans.

    >
    >again, a many-to-many relationship with the same issues

    noted above. and the
    >same solution: use a linking table between process plans

    and
    >specifications.
    >
    >> As with
    >> Components, looking up a Specification would produce a
    >> listing of associated Process Plans. This is necessary
    >> because a revised Specification could mean revising the
    >> associated Process Plans.
    >> Am I heading in the right general direction with this?
    >> Any comments or suggestions?

    >
    >hth
    >
     
    Bruce, Jun 11, 2004
    #3
  4. Bruce

    rpw Guest

    If I may 'butt-in" here and say that it's not clear that you've restructured your table design yet. If so, then I'd suggest that you post your new table design for tina to review.
    --
    rpw


    "Bruce" wrote:

    > Thanks for the speedy reply. My comments are also inline.
    > >-----Original Message-----
    > >comments inline:
    > >
    > >"Bruce" <> wrote in

    > message
    > >news:1b3f001c44fc8$3f7a9c30$...
    > >> I have successfully completed several smaller databases
    > >> that work quite well. Now I am undertaking the largest
    > >> project so far, and I have some design questions before

    > I
    > >> have completed the first table.
    > >> My company performs a variety of manufacturing processes
    > >> on a variety of Components. The process is detailed in

    > a
    > >> Process Plan, each of which is numbered in the format

    > 04-
    > >> 20 (for the twentieth plan developed in 2004).
    > >> Each Process Plan can be for one or more Components.

    > The
    > >> same process on two different Components could be two
    > >> separate Process Plans, or it could be the same Process
    > >> Plan. Also, one component could be processed according

    > to
    > >> several different Process Plans.
    > >> Also, each Process Plan references one or more
    > >> Specifications, but probably no more than 5.
    > >> Here is what I have so far, ignoring for a moment the
    > >> Specifications:
    > >>
    > >> tblProcessPlan
    > >> PlanID (PK)
    > >> Component ID (FK)
    > >> PlanNumber (could probably be the PK, but I'm not

    > sure)
    > >> Revision
    > >> Date
    > >> Process
    > >> Archive (Y/N, for queries)
    > >>

    > >so this allows you to link each specific process plan to

    > a single specifc
    > >component. but how do you plan to link the same process

    > plan to another
    > >specific component? in this two-table setup, you'd have

    > to enter a duplicate
    > >process plan record for each component that plan was

    > associated with. that
    > >solution violates table normalization rules.

    >
    > Actually, I am aware of normalization rules, but I am
    > relatively inexperienced in DB design. I am aware that
    > duplicate data entry is generally to be avoided, and as I
    > continued to experiment with the design I saw that the two
    > table setup would be a problem in that regard.
    > >
    > >
    > >> tblComponent
    > >> ComponentID (PK)
    > >> PlanID (FK)
    > >> ComponentNumber (it is possible that 2 manufacturers
    > >> will use the same number, so no PK here)
    > >> ComponentName
    > >>

    > >again this allows you to link each specific component to

    > a single specifc
    > >process plan. and again, you'd have to enter a duplicate

    > component record
    > >for each process plan that component was associated with.
    > >

    > Not good.
    >
    > >> There are other fields, but this is enough for the
    > >> question.
    > >> When somebody looks up a Component (by selecting from a
    > >> list), they should be able to see what Process Plans are
    > >> associated with it. From there they will need to see

    > the
    > >> details of the selected Process Plan, including a

    > listing
    > >> of all Components that may be processed according to

    > that
    > >> plan.
    > >> Each Process Plan can be for many Components, and each
    > >> Component can be associated with many Process Plans,

    > but I
    > >> don't think this makes it a many-to-many relationship.

    > >
    > >it *is* a many-to-many relationship. to avoid the

    > necessity for duplicate
    > >records in both tblProcessPlans and tblComponents, you

    > need to remove the
    > >foreigns from each of those tables and put them in a

    > third table, instead,
    > >as
    > >
    > >tblProcessPlanComponents
    > > PlanID (FK) (combo PK)
    > > ComponentID (FK) (combo PK)
    > >

    > Again, I see your point.
    >
    > >> I
    > >> can't imagine a field in a junction table that would not
    > >> be in one of the other two tables.

    > >
    > >it doesn't matter if there are no additional fields

    > required in the linking
    > >table. it's purpose is to link the other two tables

    > together while avoiding
    > >record duplication.
    > >

    > I have designed one database that used a junction table
    > that had a unique field. I fell into a trap of thinking
    > the unique field justified use of the junction table, when
    > all along it is the many-to-many that makes a junction
    > table necessary. I appreciate your making the point,
    > because I was a bit stuck there.
    >
    > >> Instead, I see
    > >> tblProcessPlan as the record source for frmProcessPlan,
    > >> and tblComponents as the record source for a subform
    > >> fsubComponents.

    > >
    > >whoa. you jumped right over the issue of table

    > relationships and straight
    > >into forms. forget about forms, until you have modeled

    > normalized tables
    > >that correctly store and link all your data

    > appropriately. you can't use a
    > >form to fix a table design problem, and you'll go crazy

    > trying.
    > >

    > I am not trying to solve a table problem with a form. I
    > didn't know there was a table problem. Now I have built
    > the tables and their relationships. I have imported a
    > list of Component numbers and descriptions into
    > tblComponents, and am now trying to associate a Process
    > Plan with certain Component numbers. I have created a
    > form based on tblProcessPlan. After entering the plan
    > number, date, etc. I want to associate certain Component
    > numbers with that Process Plan. Maybe it's just because
    > it is Friday afternoon, but I can't sort out how to do
    > that. If I am not getting ahead of myself I want to begin
    > experimenting with data entry.
    > >
    > >> If I am still on track here, the Specifications are the
    > >> next step. I think would have a Specifications table,
    > >> with its PK a FK in tblProcessPlan. Each Process Plan

    > can
    > >> reference many Specifications, and each Specification

    > can
    > >> be associated with many Process Plans.

    > >
    > >again, a many-to-many relationship with the same issues

    > noted above. and the
    > >same solution: use a linking table between process plans

    > and
    > >specifications.
    > >
    > >> As with
    > >> Components, looking up a Specification would produce a
    > >> listing of associated Process Plans. This is necessary
    > >> because a revised Specification could mean revising the
    > >> associated Process Plans.
    > >> Am I heading in the right general direction with this?
    > >> Any comments or suggestions?

    > >
    > >hth
    > >

    >
    >
     
    rpw, Jun 12, 2004
    #4
  5. Bruce

    tina Guest

    > I am not trying to solve a table problem with a form. I
    > didn't know there was a table problem. Now I have built
    > the tables and their relationships. I have imported a
    > list of Component numbers and descriptions into
    > tblComponents, and am now trying to associate a Process
    > Plan with certain Component numbers. I have created a
    > form based on tblProcessPlan. After entering the plan
    > number, date, etc. I want to associate certain Component
    > numbers with that Process Plan. Maybe it's just because
    > it is Friday afternoon, but I can't sort out how to do
    > that. If I am not getting ahead of myself I want to begin
    > experimenting with data entry.


    from the remarks in your post (including above excerpt), i'm not clear on
    whether you did implement the linking table for tblProcessPlans and
    tblComponents. and the linking table for tblProcessPlans and
    tblSpecifications.

    if you did set up both linking tables, then i'd suggest the following:

    you made a form based on tblProcessPlan. to associate specific components
    with a specific plan, add a subform to that main form, based on the linking
    table. the Master/Child links in the subform control's properties will be
    the key field from tblProcessPlan. it will automatically be added to each
    record you enter in the subform. so you only need to enter the key value
    from tblComponents in each record in the subform. suggest you make that
    control a combo box, with its' RowSource set to tblComponents.

    hth
     
    tina, Jun 12, 2004
    #5
  6. Bruce

    tina Guest

    thx rwp - i thought it might be just *me* that it wasn't clear to. <g>


    "rpw" <> wrote in message
    news:...
    > If I may 'butt-in" here and say that it's not clear that you've

    restructured your table design yet. If so, then I'd suggest that you post
    your new table design for tina to review.
    > --
    > rpw
    >
    >
    > "Bruce" wrote:
    >
    > > Thanks for the speedy reply. My comments are also inline.
    > > >-----Original Message-----
    > > >comments inline:
    > > >
    > > >"Bruce" <> wrote in

    > > message
    > > >news:1b3f001c44fc8$3f7a9c30$...
    > > >> I have successfully completed several smaller databases
    > > >> that work quite well. Now I am undertaking the largest
    > > >> project so far, and I have some design questions before

    > > I
    > > >> have completed the first table.
    > > >> My company performs a variety of manufacturing processes
    > > >> on a variety of Components. The process is detailed in

    > > a
    > > >> Process Plan, each of which is numbered in the format

    > > 04-
    > > >> 20 (for the twentieth plan developed in 2004).
    > > >> Each Process Plan can be for one or more Components.

    > > The
    > > >> same process on two different Components could be two
    > > >> separate Process Plans, or it could be the same Process
    > > >> Plan. Also, one component could be processed according

    > > to
    > > >> several different Process Plans.
    > > >> Also, each Process Plan references one or more
    > > >> Specifications, but probably no more than 5.
    > > >> Here is what I have so far, ignoring for a moment the
    > > >> Specifications:
    > > >>
    > > >> tblProcessPlan
    > > >> PlanID (PK)
    > > >> Component ID (FK)
    > > >> PlanNumber (could probably be the PK, but I'm not

    > > sure)
    > > >> Revision
    > > >> Date
    > > >> Process
    > > >> Archive (Y/N, for queries)
    > > >>
    > > >so this allows you to link each specific process plan to

    > > a single specifc
    > > >component. but how do you plan to link the same process

    > > plan to another
    > > >specific component? in this two-table setup, you'd have

    > > to enter a duplicate
    > > >process plan record for each component that plan was

    > > associated with. that
    > > >solution violates table normalization rules.

    > >
    > > Actually, I am aware of normalization rules, but I am
    > > relatively inexperienced in DB design. I am aware that
    > > duplicate data entry is generally to be avoided, and as I
    > > continued to experiment with the design I saw that the two
    > > table setup would be a problem in that regard.
    > > >
    > > >
    > > >> tblComponent
    > > >> ComponentID (PK)
    > > >> PlanID (FK)
    > > >> ComponentNumber (it is possible that 2 manufacturers
    > > >> will use the same number, so no PK here)
    > > >> ComponentName
    > > >>
    > > >again this allows you to link each specific component to

    > > a single specifc
    > > >process plan. and again, you'd have to enter a duplicate

    > > component record
    > > >for each process plan that component was associated with.
    > > >

    > > Not good.
    > >
    > > >> There are other fields, but this is enough for the
    > > >> question.
    > > >> When somebody looks up a Component (by selecting from a
    > > >> list), they should be able to see what Process Plans are
    > > >> associated with it. From there they will need to see

    > > the
    > > >> details of the selected Process Plan, including a

    > > listing
    > > >> of all Components that may be processed according to

    > > that
    > > >> plan.
    > > >> Each Process Plan can be for many Components, and each
    > > >> Component can be associated with many Process Plans,

    > > but I
    > > >> don't think this makes it a many-to-many relationship.
    > > >
    > > >it *is* a many-to-many relationship. to avoid the

    > > necessity for duplicate
    > > >records in both tblProcessPlans and tblComponents, you

    > > need to remove the
    > > >foreigns from each of those tables and put them in a

    > > third table, instead,
    > > >as
    > > >
    > > >tblProcessPlanComponents
    > > > PlanID (FK) (combo PK)
    > > > ComponentID (FK) (combo PK)
    > > >

    > > Again, I see your point.
    > >
    > > >> I
    > > >> can't imagine a field in a junction table that would not
    > > >> be in one of the other two tables.
    > > >
    > > >it doesn't matter if there are no additional fields

    > > required in the linking
    > > >table. it's purpose is to link the other two tables

    > > together while avoiding
    > > >record duplication.
    > > >

    > > I have designed one database that used a junction table
    > > that had a unique field. I fell into a trap of thinking
    > > the unique field justified use of the junction table, when
    > > all along it is the many-to-many that makes a junction
    > > table necessary. I appreciate your making the point,
    > > because I was a bit stuck there.
    > >
    > > >> Instead, I see
    > > >> tblProcessPlan as the record source for frmProcessPlan,
    > > >> and tblComponents as the record source for a subform
    > > >> fsubComponents.
    > > >
    > > >whoa. you jumped right over the issue of table

    > > relationships and straight
    > > >into forms. forget about forms, until you have modeled

    > > normalized tables
    > > >that correctly store and link all your data

    > > appropriately. you can't use a
    > > >form to fix a table design problem, and you'll go crazy

    > > trying.
    > > >

    > > I am not trying to solve a table problem with a form. I
    > > didn't know there was a table problem. Now I have built
    > > the tables and their relationships. I have imported a
    > > list of Component numbers and descriptions into
    > > tblComponents, and am now trying to associate a Process
    > > Plan with certain Component numbers. I have created a
    > > form based on tblProcessPlan. After entering the plan
    > > number, date, etc. I want to associate certain Component
    > > numbers with that Process Plan. Maybe it's just because
    > > it is Friday afternoon, but I can't sort out how to do
    > > that. If I am not getting ahead of myself I want to begin
    > > experimenting with data entry.
    > > >
    > > >> If I am still on track here, the Specifications are the
    > > >> next step. I think would have a Specifications table,
    > > >> with its PK a FK in tblProcessPlan. Each Process Plan

    > > can
    > > >> reference many Specifications, and each Specification

    > > can
    > > >> be associated with many Process Plans.
    > > >
    > > >again, a many-to-many relationship with the same issues

    > > noted above. and the
    > > >same solution: use a linking table between process plans

    > > and
    > > >specifications.
    > > >
    > > >> As with
    > > >> Components, looking up a Specification would produce a
    > > >> listing of associated Process Plans. This is necessary
    > > >> because a revised Specification could mean revising the
    > > >> associated Process Plans.
    > > >> Am I heading in the right general direction with this?
    > > >> Any comments or suggestions?
    > > >
    > > >hth
    > > >

    > >
    > >
     
    tina, Jun 12, 2004
    #6
  7. Bruce

    Bruce Guest

    You are right, I did not specify the table structure.
    Forget about specifications for now, by the way. I would
    like to limit this until I have a better handle on
    managing one junction table. The junction table's only
    unique field is its PK. The other fields are linked to
    tblProcessPlan and tblComponent:

    tblProcessPlan
    PlanID (PK)
    PlanNumber (our internal assigned number)
    Revision
    Process
    Date
    Archived (Y/N)

    tblComponent
    ComponentID (PK)
    ComponentNumber
    ComponentName

    tblPartListing
    ListingID (PK)
    PlanID (FK)
    ComponentID (FK)

    First, suppose I have a new Process Plan. I am not trying
    to get ahead of myself, nor to fix a table problem with a
    form. I understand that a properly normalized table
    structure is at the heart of a successful database.
    Having said that, at some point I like to use autoform to
    make forms to test things, because I find it easier to
    envision things that way than directly in a table. I made
    frmProcessPlan from tblProcessPlan. After entering the
    Plan number, date, etc. I would like to enter the
    component number or numbers. I expect this will occur in
    a subform based on tblPartListing (?). I am just not able
    to get my brain around how that will happen. (I have
    already populated tblComponents with a listing (imported
    from a spreadsheet) of part numbers and descriptions.)
    The parent/child links of the subform control are as you
    suggest, and a combo box on the subform has tblComponent
    as its row source. Its bound column is the PK from
    tblComponent, and its visible column is Column 2 (the
    Component number). However, I cannot add a component
    number that is not already in tblComponent, and I cannot
    add a Component description at all. Is there enough here
    for you to tell what I am doing wrong? I know that I am
    just not getting something basic.

    >-----Original Message-----
    >> I am not trying to solve a table problem with a form. I
    >> didn't know there was a table problem. Now I have built
    >> the tables and their relationships. I have imported a
    >> list of Component numbers and descriptions into
    >> tblComponents, and am now trying to associate a Process
    >> Plan with certain Component numbers. I have created a
    >> form based on tblProcessPlan. After entering the plan
    >> number, date, etc. I want to associate certain Component
    >> numbers with that Process Plan. Maybe it's just because
    >> it is Friday afternoon, but I can't sort out how to do
    >> that. If I am not getting ahead of myself I want to

    begin
    >> experimenting with data entry.

    >
    >from the remarks in your post (including above excerpt),

    i'm not clear on
    >whether you did implement the linking table for

    tblProcessPlans and
    >tblComponents. and the linking table for tblProcessPlans

    and
    >tblSpecifications.
    >
    >if you did set up both linking tables, then i'd suggest

    the following:
    >
    >you made a form based on tblProcessPlan. to associate

    specific components
    >with a specific plan, add a subform to that main form,

    based on the linking
    >table. the Master/Child links in the subform control's

    properties will be
    >the key field from tblProcessPlan. it will automatically

    be added to each
    >record you enter in the subform. so you only need to

    enter the key value
    >from tblComponents in each record in the subform. suggest

    you make that
    >control a combo box, with its' RowSource set to

    tblComponents.
    >
    >hth
    >
    >
    >
    >.
    >
     
    Bruce, Jun 14, 2004
    #7
  8. Bruce

    tina Guest

    no, you got it right. :)
    the form/subform you built is focused on adding/updating ProcessPlan
    records, including listing components that "belong to" that plan.
    you can also (or instead) build a form/subform with the opposite approach:
    mainform based on components, subform based on PartListing, foreign key from
    tblComponents automatically entered in subform, foreign key from
    tblProcessPlans entered via a combo box that is based on tblProcessPlans.
    in the above form, the focus is on add/updating Component records, including
    listing process plans that "belong to" that component.
    in either form/subform setup, my first question is: do you want the user to
    be able to add an entry to the combo box list? you have to decide whether
    the user will have enough info to make a complete and valid entry in the
    combo box's underlying table.
    if the answer to the question is Yes, it's fairly easy to set up an process
    to allow entries to be added to the underlying table (and populate the combo
    box droplist) on-the-fly.
    if you want to do that, and need help setting it up, post back and i'll
    provide a code sample.

    hth


    "Bruce" <> wrote in message
    news:1c7cf01c4524d$04f48670$...
    > You are right, I did not specify the table structure.
    > Forget about specifications for now, by the way. I would
    > like to limit this until I have a better handle on
    > managing one junction table. The junction table's only
    > unique field is its PK. The other fields are linked to
    > tblProcessPlan and tblComponent:
    >
    > tblProcessPlan
    > PlanID (PK)
    > PlanNumber (our internal assigned number)
    > Revision
    > Process
    > Date
    > Archived (Y/N)
    >
    > tblComponent
    > ComponentID (PK)
    > ComponentNumber
    > ComponentName
    >
    > tblPartListing
    > ListingID (PK)
    > PlanID (FK)
    > ComponentID (FK)
    >
    > First, suppose I have a new Process Plan. I am not trying
    > to get ahead of myself, nor to fix a table problem with a
    > form. I understand that a properly normalized table
    > structure is at the heart of a successful database.
    > Having said that, at some point I like to use autoform to
    > make forms to test things, because I find it easier to
    > envision things that way than directly in a table. I made
    > frmProcessPlan from tblProcessPlan. After entering the
    > Plan number, date, etc. I would like to enter the
    > component number or numbers. I expect this will occur in
    > a subform based on tblPartListing (?). I am just not able
    > to get my brain around how that will happen. (I have
    > already populated tblComponents with a listing (imported
    > from a spreadsheet) of part numbers and descriptions.)
    > The parent/child links of the subform control are as you
    > suggest, and a combo box on the subform has tblComponent
    > as its row source. Its bound column is the PK from
    > tblComponent, and its visible column is Column 2 (the
    > Component number). However, I cannot add a component
    > number that is not already in tblComponent, and I cannot
    > add a Component description at all. Is there enough here
    > for you to tell what I am doing wrong? I know that I am
    > just not getting something basic.
    >
    > >-----Original Message-----
    > >> I am not trying to solve a table problem with a form. I
    > >> didn't know there was a table problem. Now I have built
    > >> the tables and their relationships. I have imported a
    > >> list of Component numbers and descriptions into
    > >> tblComponents, and am now trying to associate a Process
    > >> Plan with certain Component numbers. I have created a
    > >> form based on tblProcessPlan. After entering the plan
    > >> number, date, etc. I want to associate certain Component
    > >> numbers with that Process Plan. Maybe it's just because
    > >> it is Friday afternoon, but I can't sort out how to do
    > >> that. If I am not getting ahead of myself I want to

    > begin
    > >> experimenting with data entry.

    > >
    > >from the remarks in your post (including above excerpt),

    > i'm not clear on
    > >whether you did implement the linking table for

    > tblProcessPlans and
    > >tblComponents. and the linking table for tblProcessPlans

    > and
    > >tblSpecifications.
    > >
    > >if you did set up both linking tables, then i'd suggest

    > the following:
    > >
    > >you made a form based on tblProcessPlan. to associate

    > specific components
    > >with a specific plan, add a subform to that main form,

    > based on the linking
    > >table. the Master/Child links in the subform control's

    > properties will be
    > >the key field from tblProcessPlan. it will automatically

    > be added to each
    > >record you enter in the subform. so you only need to

    > enter the key value
    > >from tblComponents in each record in the subform. suggest

    > you make that
    > >control a combo box, with its' RowSource set to

    > tblComponents.
    > >
    > >hth
    > >
    > >
    > >
    > >.
    > >
     
    tina, Jun 14, 2004
    #8
  9. Bruce

    Bruce Guest

    The way this needs to work is for the Process Plan to show
    all associated Components. A new Process Plan will start
    with a description of the plan, then will list
    Components. Most process plans are associated with
    multiple Components, but relatively few components are
    associated with multiple Process Plans (except in the case
    of revisions to existing Process Plans). It needs to be
    Process Plan first, then components.
    In answer to your question about the user adding to the
    combo box list, it is not only allowed, it is the whole
    point. I need to be able to add Component numbers and
    descriptions to tblComponents on the fly. I will enter
    the Process Plan information, then either select
    components from a combo box or type them into the box.
    For Process Plan 12345, I have Components 123, bolt; 124,
    nut; and 125, washer. Below the Process Plan description
    I need to see:
    123 Bolt
    124 Nut
    125 Washer
    If I select Component number 123 from a combo box, "Bolt"
    needs to show up next to it. If Component number 124 is
    not in the list, I will add it right there on the subform,
    and will add "Nut" next to it. The next time I need to
    add 124 (to another Process Plan), it will appear on the
    combo box list.
    What I have managed so far is a mainform based on
    tblProcessPlan, and a subform based on qryPartListing,
    which combines tblComponent and tblPartListing (the
    junction table). The subform has a combo box (cboList)
    based on a select query, based in turn on tblComponent
    (the query is to allow sorting later). ComponentID is the
    first column, Component number is the second, and
    Component description is the third. The combo box has
    three columns; only the second is visible (widths of 1 and
    3 are zero). The text box for Component description has
    as its Control Source the third column of the combo box: =
    [cboList].Column(2). This is fine as long as I am always
    selecting an exisiting Component, but it does not work on
    the fly.
    I don't know if the query as the source for the subform is
    the correct approach. I can't see another way to link to
    tblComponents.
    I really appreciate the time you have put into helping
    with this.
    >-----Original Message-----
    >no, you got it right. :)
    >the form/subform you built is focused on adding/updating

    ProcessPlan
    >records, including listing components that "belong to"

    that plan.
    >you can also (or instead) build a form/subform with the

    opposite approach:
    >mainform based on components, subform based on

    PartListing, foreign key from
    >tblComponents automatically entered in subform, foreign

    key from
    >tblProcessPlans entered via a combo box that is based on

    tblProcessPlans.
    >in the above form, the focus is on add/updating Component

    records, including
    >listing process plans that "belong to" that component.
    >in either form/subform setup, my first question is: do

    you want the user to
    >be able to add an entry to the combo box list? you have

    to decide whether
    >the user will have enough info to make a complete and

    valid entry in the
    >combo box's underlying table.
    >if the answer to the question is Yes, it's fairly easy to

    set up an process
    >to allow entries to be added to the underlying table (and

    populate the combo
    >box droplist) on-the-fly.
    >if you want to do that, and need help setting it up, post

    back and i'll
    >provide a code sample.
    >
    >hth
    >
    >
    >"Bruce" <> wrote in

    message
    >news:1c7cf01c4524d$04f48670$...
    >> You are right, I did not specify the table structure.
    >> Forget about specifications for now, by the way. I

    would
    >> like to limit this until I have a better handle on
    >> managing one junction table. The junction table's only
    >> unique field is its PK. The other fields are linked to
    >> tblProcessPlan and tblComponent:
    >>
    >> tblProcessPlan
    >> PlanID (PK)
    >> PlanNumber (our internal assigned number)
    >> Revision
    >> Process
    >> Date
    >> Archived (Y/N)
    >>
    >> tblComponent
    >> ComponentID (PK)
    >> ComponentNumber
    >> ComponentName
    >>
    >> tblPartListing
    >> ListingID (PK)
    >> PlanID (FK)
    >> ComponentID (FK)
    >>
    >> First, suppose I have a new Process Plan. I am not

    trying
    >> to get ahead of myself, nor to fix a table problem with

    a
    >> form. I understand that a properly normalized table
    >> structure is at the heart of a successful database.
    >> Having said that, at some point I like to use autoform

    to
    >> make forms to test things, because I find it easier to
    >> envision things that way than directly in a table. I

    made
    >> frmProcessPlan from tblProcessPlan. After entering the
    >> Plan number, date, etc. I would like to enter the
    >> component number or numbers. I expect this will occur

    in
    >> a subform based on tblPartListing (?). I am just not

    able
    >> to get my brain around how that will happen. (I have
    >> already populated tblComponents with a listing (imported
    >> from a spreadsheet) of part numbers and descriptions.)
    >> The parent/child links of the subform control are as you
    >> suggest, and a combo box on the subform has tblComponent
    >> as its row source. Its bound column is the PK from
    >> tblComponent, and its visible column is Column 2 (the
    >> Component number). However, I cannot add a component
    >> number that is not already in tblComponent, and I cannot
    >> add a Component description at all. Is there enough

    here
    >> for you to tell what I am doing wrong? I know that I am
    >> just not getting something basic.
    >>
    >> >-----Original Message-----
    >> >> I am not trying to solve a table problem with a

    form. I
    >> >> didn't know there was a table problem. Now I have

    built
    >> >> the tables and their relationships. I have imported

    a
    >> >> list of Component numbers and descriptions into
    >> >> tblComponents, and am now trying to associate a

    Process
    >> >> Plan with certain Component numbers. I have created

    a
    >> >> form based on tblProcessPlan. After entering the

    plan
    >> >> number, date, etc. I want to associate certain

    Component
    >> >> numbers with that Process Plan. Maybe it's just

    because
    >> >> it is Friday afternoon, but I can't sort out how to

    do
    >> >> that. If I am not getting ahead of myself I want to

    >> begin
    >> >> experimenting with data entry.
    >> >
    >> >from the remarks in your post (including above

    excerpt),
    >> i'm not clear on
    >> >whether you did implement the linking table for

    >> tblProcessPlans and
    >> >tblComponents. and the linking table for

    tblProcessPlans
    >> and
    >> >tblSpecifications.
    >> >
    >> >if you did set up both linking tables, then i'd suggest

    >> the following:
    >> >
    >> >you made a form based on tblProcessPlan. to associate

    >> specific components
    >> >with a specific plan, add a subform to that main form,

    >> based on the linking
    >> >table. the Master/Child links in the subform control's

    >> properties will be
    >> >the key field from tblProcessPlan. it will

    automatically
    >> be added to each
    >> >record you enter in the subform. so you only need to

    >> enter the key value
    >> >from tblComponents in each record in the subform.

    suggest
    >> you make that
    >> >control a combo box, with its' RowSource set to

    >> tblComponents.
    >> >
    >> >hth
    >> >
    >> >
    >> >
    >> >.
    >> >

    >
    >
    >.
    >
     
    Bruce, Jun 15, 2004
    #9
  10. Bruce

    tina Guest

    i'd say skip the query. instead, base the subform directly on
    tblPartListing. base the combo box for Components solely on tblComponents.
    since you're including all three fields of tblComponents in the query's
    columns, those values are available to you to show in the subform record as
    you choose - which i think you already know.
    from the above setup, you should be able to add new components to the combo
    box "on the fly". if you still have trouble with it after making those
    changes, post the code from your combo box's NotInList event.

    if you need to do a sort on the subform, based on the part numbers....hmmm,
    you might try sorting on a calculated control whose control source is set to
    =ComboBoxName.Column(1)
    i've never tried to do a form sort on a calculated control - don't know if
    it'll work or not.

    hth


    "Bruce" <> wrote in message
    news:1ce5701c45311$66d45f40$...
    > The way this needs to work is for the Process Plan to show
    > all associated Components. A new Process Plan will start
    > with a description of the plan, then will list
    > Components. Most process plans are associated with
    > multiple Components, but relatively few components are
    > associated with multiple Process Plans (except in the case
    > of revisions to existing Process Plans). It needs to be
    > Process Plan first, then components.
    > In answer to your question about the user adding to the
    > combo box list, it is not only allowed, it is the whole
    > point. I need to be able to add Component numbers and
    > descriptions to tblComponents on the fly. I will enter
    > the Process Plan information, then either select
    > components from a combo box or type them into the box.
    > For Process Plan 12345, I have Components 123, bolt; 124,
    > nut; and 125, washer. Below the Process Plan description
    > I need to see:
    > 123 Bolt
    > 124 Nut
    > 125 Washer
    > If I select Component number 123 from a combo box, "Bolt"
    > needs to show up next to it. If Component number 124 is
    > not in the list, I will add it right there on the subform,
    > and will add "Nut" next to it. The next time I need to
    > add 124 (to another Process Plan), it will appear on the
    > combo box list.
    > What I have managed so far is a mainform based on
    > tblProcessPlan, and a subform based on qryPartListing,
    > which combines tblComponent and tblPartListing (the
    > junction table). The subform has a combo box (cboList)
    > based on a select query, based in turn on tblComponent
    > (the query is to allow sorting later). ComponentID is the
    > first column, Component number is the second, and
    > Component description is the third. The combo box has
    > three columns; only the second is visible (widths of 1 and
    > 3 are zero). The text box for Component description has
    > as its Control Source the third column of the combo box: =
    > [cboList].Column(2). This is fine as long as I am always
    > selecting an exisiting Component, but it does not work on
    > the fly.
    > I don't know if the query as the source for the subform is
    > the correct approach. I can't see another way to link to
    > tblComponents.
    > I really appreciate the time you have put into helping
    > with this.
    > >-----Original Message-----
    > >no, you got it right. :)
    > >the form/subform you built is focused on adding/updating

    > ProcessPlan
    > >records, including listing components that "belong to"

    > that plan.
    > >you can also (or instead) build a form/subform with the

    > opposite approach:
    > >mainform based on components, subform based on

    > PartListing, foreign key from
    > >tblComponents automatically entered in subform, foreign

    > key from
    > >tblProcessPlans entered via a combo box that is based on

    > tblProcessPlans.
    > >in the above form, the focus is on add/updating Component

    > records, including
    > >listing process plans that "belong to" that component.
    > >in either form/subform setup, my first question is: do

    > you want the user to
    > >be able to add an entry to the combo box list? you have

    > to decide whether
    > >the user will have enough info to make a complete and

    > valid entry in the
    > >combo box's underlying table.
    > >if the answer to the question is Yes, it's fairly easy to

    > set up an process
    > >to allow entries to be added to the underlying table (and

    > populate the combo
    > >box droplist) on-the-fly.
    > >if you want to do that, and need help setting it up, post

    > back and i'll
    > >provide a code sample.
    > >
    > >hth
    > >
    > >
    > >"Bruce" <> wrote in

    > message
    > >news:1c7cf01c4524d$04f48670$...
    > >> You are right, I did not specify the table structure.
    > >> Forget about specifications for now, by the way. I

    > would
    > >> like to limit this until I have a better handle on
    > >> managing one junction table. The junction table's only
    > >> unique field is its PK. The other fields are linked to
    > >> tblProcessPlan and tblComponent:
    > >>
    > >> tblProcessPlan
    > >> PlanID (PK)
    > >> PlanNumber (our internal assigned number)
    > >> Revision
    > >> Process
    > >> Date
    > >> Archived (Y/N)
    > >>
    > >> tblComponent
    > >> ComponentID (PK)
    > >> ComponentNumber
    > >> ComponentName
    > >>
    > >> tblPartListing
    > >> ListingID (PK)
    > >> PlanID (FK)
    > >> ComponentID (FK)
    > >>
    > >> First, suppose I have a new Process Plan. I am not

    > trying
    > >> to get ahead of myself, nor to fix a table problem with

    > a
    > >> form. I understand that a properly normalized table
    > >> structure is at the heart of a successful database.
    > >> Having said that, at some point I like to use autoform

    > to
    > >> make forms to test things, because I find it easier to
    > >> envision things that way than directly in a table. I

    > made
    > >> frmProcessPlan from tblProcessPlan. After entering the
    > >> Plan number, date, etc. I would like to enter the
    > >> component number or numbers. I expect this will occur

    > in
    > >> a subform based on tblPartListing (?). I am just not

    > able
    > >> to get my brain around how that will happen. (I have
    > >> already populated tblComponents with a listing (imported
    > >> from a spreadsheet) of part numbers and descriptions.)
    > >> The parent/child links of the subform control are as you
    > >> suggest, and a combo box on the subform has tblComponent
    > >> as its row source. Its bound column is the PK from
    > >> tblComponent, and its visible column is Column 2 (the
    > >> Component number). However, I cannot add a component
    > >> number that is not already in tblComponent, and I cannot
    > >> add a Component description at all. Is there enough

    > here
    > >> for you to tell what I am doing wrong? I know that I am
    > >> just not getting something basic.
    > >>
    > >> >-----Original Message-----
    > >> >> I am not trying to solve a table problem with a

    > form. I
    > >> >> didn't know there was a table problem. Now I have

    > built
    > >> >> the tables and their relationships. I have imported

    > a
    > >> >> list of Component numbers and descriptions into
    > >> >> tblComponents, and am now trying to associate a

    > Process
    > >> >> Plan with certain Component numbers. I have created

    > a
    > >> >> form based on tblProcessPlan. After entering the

    > plan
    > >> >> number, date, etc. I want to associate certain

    > Component
    > >> >> numbers with that Process Plan. Maybe it's just

    > because
    > >> >> it is Friday afternoon, but I can't sort out how to

    > do
    > >> >> that. If I am not getting ahead of myself I want to
    > >> begin
    > >> >> experimenting with data entry.
    > >> >
    > >> >from the remarks in your post (including above

    > excerpt),
    > >> i'm not clear on
    > >> >whether you did implement the linking table for
    > >> tblProcessPlans and
    > >> >tblComponents. and the linking table for

    > tblProcessPlans
    > >> and
    > >> >tblSpecifications.
    > >> >
    > >> >if you did set up both linking tables, then i'd suggest
    > >> the following:
    > >> >
    > >> >you made a form based on tblProcessPlan. to associate
    > >> specific components
    > >> >with a specific plan, add a subform to that main form,
    > >> based on the linking
    > >> >table. the Master/Child links in the subform control's
    > >> properties will be
    > >> >the key field from tblProcessPlan. it will

    > automatically
    > >> be added to each
    > >> >record you enter in the subform. so you only need to
    > >> enter the key value
    > >> >from tblComponents in each record in the subform.

    > suggest
    > >> you make that
    > >> >control a combo box, with its' RowSource set to
    > >> tblComponents.
    > >> >
    > >> >hth
    > >> >
    > >> >
    > >> >
    > >> >.
    > >> >

    > >
    > >
    > >.
    > >
     
    tina, Jun 15, 2004
    #10
  11. Bruce

    Bruce Guest

    If the subform is based directly on (has as its record
    source) tblPartListing (the junction table) which contains
    only its own PK plus the FKs from tblComponents and
    tblProcessPlan, then ComponentNumber is not available as
    the combo box's control source. Therefore, it seems that
    the control source for a combo box based on tblComponent
    can only be an expression:
    =[tblComponent]![ComponentNumber] Column 1 is the PK, 2
    is ComponentNumber, 3 is ComponentDescription; I tried
    both 1 and 2 as the bound column. However, any attempt
    to select from the list or to add a part number results in
    the message (on the status bar) that the control can't be
    edited because it is bound to an expression. Therefore,
    the Not In List event cannot run.
    By the way, the sorting I need to do is in the combo box's
    row source, which is easy enough in a SQL expression. I
    can sort the Component numbers that are associated with a
    Process Plan in a report as needed. I was not clear about
    that.
    Thanks again for taking the time to reply, but it is clear
    that I am not explaining something properly. I will need
    to do more research.
    >-----Original Message-----
    >i'd say skip the query. instead, base the subform

    directly on
    >tblPartListing. base the combo box for Components solely

    on tblComponents.
    >since you're including all three fields of tblComponents

    in the query's
    >columns, those values are available to you to show in the

    subform record as
    >you choose - which i think you already know.
    >from the above setup, you should be able to add new

    components to the combo
    >box "on the fly". if you still have trouble with it after

    making those
    >changes, post the code from your combo box's NotInList

    event.
    >
    >if you need to do a sort on the subform, based on the

    part numbers....hmmm,
    >you might try sorting on a calculated control whose

    control source is set to
    >=ComboBoxName.Column(1)
    >i've never tried to do a form sort on a calculated

    control - don't know if
    >it'll work or not.
    >
    >hth
    >
    >
    >"Bruce" <> wrote in

    message
    >news:1ce5701c45311$66d45f40$...
    >> The way this needs to work is for the Process Plan to

    show
    >> all associated Components. A new Process Plan will

    start
    >> with a description of the plan, then will list
    >> Components. Most process plans are associated with
    >> multiple Components, but relatively few components are
    >> associated with multiple Process Plans (except in the

    case
    >> of revisions to existing Process Plans). It needs to be
    >> Process Plan first, then components.
    >> In answer to your question about the user adding to the
    >> combo box list, it is not only allowed, it is the whole
    >> point. I need to be able to add Component numbers and
    >> descriptions to tblComponents on the fly. I will enter
    >> the Process Plan information, then either select
    >> components from a combo box or type them into the box.
    >> For Process Plan 12345, I have Components 123, bolt;

    124,
    >> nut; and 125, washer. Below the Process Plan

    description
    >> I need to see:
    >> 123 Bolt
    >> 124 Nut
    >> 125 Washer
    >> If I select Component number 123 from a combo

    box, "Bolt"
    >> needs to show up next to it. If Component number 124 is
    >> not in the list, I will add it right there on the

    subform,
    >> and will add "Nut" next to it. The next time I need to
    >> add 124 (to another Process Plan), it will appear on the
    >> combo box list.
    >> What I have managed so far is a mainform based on
    >> tblProcessPlan, and a subform based on qryPartListing,
    >> which combines tblComponent and tblPartListing (the
    >> junction table). The subform has a combo box (cboList)
    >> based on a select query, based in turn on tblComponent
    >> (the query is to allow sorting later). ComponentID is

    the
    >> first column, Component number is the second, and
    >> Component description is the third. The combo box has
    >> three columns; only the second is visible (widths of 1

    and
    >> 3 are zero). The text box for Component description has
    >> as its Control Source the third column of the combo

    box: =
    >> [cboList].Column(2). This is fine as long as I am

    always
    >> selecting an exisiting Component, but it does not work

    on
    >> the fly.
    >> I don't know if the query as the source for the subform

    is
    >> the correct approach. I can't see another way to link

    to
    >> tblComponents.
    >> I really appreciate the time you have put into helping
    >> with this.
    >> >-----Original Message-----
    >> >no, you got it right. :)
    >> >the form/subform you built is focused on

    adding/updating
    >> ProcessPlan
    >> >records, including listing components that "belong to"

    >> that plan.
    >> >you can also (or instead) build a form/subform with the

    >> opposite approach:
    >> >mainform based on components, subform based on

    >> PartListing, foreign key from
    >> >tblComponents automatically entered in subform, foreign

    >> key from
    >> >tblProcessPlans entered via a combo box that is based

    on
    >> tblProcessPlans.
    >> >in the above form, the focus is on add/updating

    Component
    >> records, including
    >> >listing process plans that "belong to" that component.
    >> >in either form/subform setup, my first question is: do

    >> you want the user to
    >> >be able to add an entry to the combo box list? you have

    >> to decide whether
    >> >the user will have enough info to make a complete and

    >> valid entry in the
    >> >combo box's underlying table.
    >> >if the answer to the question is Yes, it's fairly easy

    to
    >> set up an process
    >> >to allow entries to be added to the underlying table

    (and
    >> populate the combo
    >> >box droplist) on-the-fly.
    >> >if you want to do that, and need help setting it up,

    post
    >> back and i'll
    >> >provide a code sample.
    >> >
    >> >hth
    >> >
    >> >
    >> >"Bruce" <> wrote in

    >> message
    >> >news:1c7cf01c4524d$04f48670$...
    >> >> You are right, I did not specify the table structure.
    >> >> Forget about specifications for now, by the way. I

    >> would
    >> >> like to limit this until I have a better handle on
    >> >> managing one junction table. The junction table's

    only
    >> >> unique field is its PK. The other fields are linked

    to
    >> >> tblProcessPlan and tblComponent:
    >> >>
    >> >> tblProcessPlan
    >> >> PlanID (PK)
    >> >> PlanNumber (our internal assigned number)
    >> >> Revision
    >> >> Process
    >> >> Date
    >> >> Archived (Y/N)
    >> >>
    >> >> tblComponent
    >> >> ComponentID (PK)
    >> >> ComponentNumber
    >> >> ComponentName
    >> >>
    >> >> tblPartListing
    >> >> ListingID (PK)
    >> >> PlanID (FK)
    >> >> ComponentID (FK)
    >> >>
    >> >> First, suppose I have a new Process Plan. I am not

    >> trying
    >> >> to get ahead of myself, nor to fix a table problem

    with
    >> a
    >> >> form. I understand that a properly normalized table
    >> >> structure is at the heart of a successful database.
    >> >> Having said that, at some point I like to use

    autoform
    >> to
    >> >> make forms to test things, because I find it easier

    to
    >> >> envision things that way than directly in a table. I

    >> made
    >> >> frmProcessPlan from tblProcessPlan. After entering

    the
    >> >> Plan number, date, etc. I would like to enter the
    >> >> component number or numbers. I expect this will

    occur
    >> in
    >> >> a subform based on tblPartListing (?). I am just not

    >> able
    >> >> to get my brain around how that will happen. (I have
    >> >> already populated tblComponents with a listing

    (imported
    >> >> from a spreadsheet) of part numbers and

    descriptions.)
    >> >> The parent/child links of the subform control are as

    you
    >> >> suggest, and a combo box on the subform has

    tblComponent
    >> >> as its row source. Its bound column is the PK from
    >> >> tblComponent, and its visible column is Column 2 (the
    >> >> Component number). However, I cannot add a component
    >> >> number that is not already in tblComponent, and I

    cannot
    >> >> add a Component description at all. Is there enough

    >> here
    >> >> for you to tell what I am doing wrong? I know that

    I am
    >> >> just not getting something basic.
    >> >>
    >> >> >-----Original Message-----
    >> >> >> I am not trying to solve a table problem with a

    >> form. I
    >> >> >> didn't know there was a table problem. Now I have

    >> built
    >> >> >> the tables and their relationships. I have

    imported
    >> a
    >> >> >> list of Component numbers and descriptions into
    >> >> >> tblComponents, and am now trying to associate a

    >> Process
    >> >> >> Plan with certain Component numbers. I have

    created
    >> a
    >> >> >> form based on tblProcessPlan. After entering the

    >> plan
    >> >> >> number, date, etc. I want to associate certain

    >> Component
    >> >> >> numbers with that Process Plan. Maybe it's just

    >> because
    >> >> >> it is Friday afternoon, but I can't sort out how

    to
    >> do
    >> >> >> that. If I am not getting ahead of myself I want

    to
    >> >> begin
    >> >> >> experimenting with data entry.
    >> >> >
    >> >> >from the remarks in your post (including above

    >> excerpt),
    >> >> i'm not clear on
    >> >> >whether you did implement the linking table for
    >> >> tblProcessPlans and
    >> >> >tblComponents. and the linking table for

    >> tblProcessPlans
    >> >> and
    >> >> >tblSpecifications.
    >> >> >
    >> >> >if you did set up both linking tables, then i'd

    suggest
    >> >> the following:
    >> >> >
    >> >> >you made a form based on tblProcessPlan. to

    associate
    >> >> specific components
    >> >> >with a specific plan, add a subform to that main

    form,
    >> >> based on the linking
    >> >> >table. the Master/Child links in the subform

    control's
    >> >> properties will be
    >> >> >the key field from tblProcessPlan. it will

    >> automatically
    >> >> be added to each
    >> >> >record you enter in the subform. so you only need to
    >> >> enter the key value
    >> >> >from tblComponents in each record in the subform.

    >> suggest
    >> >> you make that
    >> >> >control a combo box, with its' RowSource set to
    >> >> tblComponents.
    >> >> >
    >> >> >hth
    >> >> >
    >> >> >
    >> >> >
    >> >> >.
    >> >> >
    >> >
    >> >
    >> >.
    >> >

    >
    >
    >.
    >
     
    Bruce, Jun 16, 2004
    #11
  12. Bruce

    tina Guest

    stick with this just a little longer. comments inline:

    "Bruce" <> wrote in message
    news:1d3d801c4539f$6c43e1b0$...
    > If the subform is based directly on (has as its record
    > source) tblPartListing (the junction table) which contains
    > only its own PK plus the FKs from tblComponents and
    > tblProcessPlan, then ComponentNumber is not available as
    > the combo box's control source.


    correct. and it's *not supposed to be*. the foreign key field in
    tblPartListing is ComponentID, NOT ComponentNumber. the value you need to
    save in tblPartListing is ComponentID, NOT ComponentNumber. your combo box
    needs to be bound to (have it's ControlSource set to) ComponentID, NOT
    ComponentNumber.


    Therefore, it seems that
    > the control source for a combo box based on tblComponent
    > can only be an expression:
    > =[tblComponent]![ComponentNumber]


    see above.


    Column 1 is the PK, 2
    > is ComponentNumber, 3 is ComponentDescription; I tried
    > both 1 and 2 as the bound column.


    set the combo box BoundColumn to 1. the primary key (ComponentID) is the
    value you need to save.


    However, any attempt
    > to select from the list or to add a part number results in
    > the message (on the status bar) that the control can't be
    > edited because it is bound to an expression. Therefore,
    > the Not In List event cannot run.


    once you set up the combo box the way i said, the user will *see* the
    ComponentNumber in the combo box (because you have the first column's width
    set to zero, which is correct). when he/she enters a component number that
    does not exist in tblComponents, the NotInList event *will* run.


    > By the way, the sorting I need to do is in the combo box's
    > row source, which is easy enough in a SQL expression. I
    > can sort the Component numbers that are associated with a
    > Process Plan in a report as needed. I was not clear about
    > that.


    good. that makes it even easier - no complications involving sorting the
    subform records.


    > Thanks again for taking the time to reply, but it is clear
    > that I am not explaining something properly. I will need
    > to do more research.


    hopefully, you'll come back to the thread and read this reply. you've
    explained your setup and what you're doing very well. it seems that i'm the
    one who has not been explaining something properly, because i haven't been
    able to help you understand how i'm instructing you to set up the
    form/subform/combobox solution - which is a standard solution, by the way,
    not some crazy thing i made up. <g>

    hth


    > >-----Original Message-----
    > >i'd say skip the query. instead, base the subform

    > directly on
    > >tblPartListing. base the combo box for Components solely

    > on tblComponents.
    > >since you're including all three fields of tblComponents

    > in the query's
    > >columns, those values are available to you to show in the

    > subform record as
    > >you choose - which i think you already know.
    > >from the above setup, you should be able to add new

    > components to the combo
    > >box "on the fly". if you still have trouble with it after

    > making those
    > >changes, post the code from your combo box's NotInList

    > event.
    > >
    > >if you need to do a sort on the subform, based on the

    > part numbers....hmmm,
    > >you might try sorting on a calculated control whose

    > control source is set to
    > >=ComboBoxName.Column(1)
    > >i've never tried to do a form sort on a calculated

    > control - don't know if
    > >it'll work or not.
    > >
    > >hth
    > >
    > >
    > >"Bruce" <> wrote in

    > message
    > >news:1ce5701c45311$66d45f40$...
    > >> The way this needs to work is for the Process Plan to

    > show
    > >> all associated Components. A new Process Plan will

    > start
    > >> with a description of the plan, then will list
    > >> Components. Most process plans are associated with
    > >> multiple Components, but relatively few components are
    > >> associated with multiple Process Plans (except in the

    > case
    > >> of revisions to existing Process Plans). It needs to be
    > >> Process Plan first, then components.
    > >> In answer to your question about the user adding to the
    > >> combo box list, it is not only allowed, it is the whole
    > >> point. I need to be able to add Component numbers and
    > >> descriptions to tblComponents on the fly. I will enter
    > >> the Process Plan information, then either select
    > >> components from a combo box or type them into the box.
    > >> For Process Plan 12345, I have Components 123, bolt;

    > 124,
    > >> nut; and 125, washer. Below the Process Plan

    > description
    > >> I need to see:
    > >> 123 Bolt
    > >> 124 Nut
    > >> 125 Washer
    > >> If I select Component number 123 from a combo

    > box, "Bolt"
    > >> needs to show up next to it. If Component number 124 is
    > >> not in the list, I will add it right there on the

    > subform,
    > >> and will add "Nut" next to it. The next time I need to
    > >> add 124 (to another Process Plan), it will appear on the
    > >> combo box list.
    > >> What I have managed so far is a mainform based on
    > >> tblProcessPlan, and a subform based on qryPartListing,
    > >> which combines tblComponent and tblPartListing (the
    > >> junction table). The subform has a combo box (cboList)
    > >> based on a select query, based in turn on tblComponent
    > >> (the query is to allow sorting later). ComponentID is

    > the
    > >> first column, Component number is the second, and
    > >> Component description is the third. The combo box has
    > >> three columns; only the second is visible (widths of 1

    > and
    > >> 3 are zero). The text box for Component description has
    > >> as its Control Source the third column of the combo

    > box: =
    > >> [cboList].Column(2). This is fine as long as I am

    > always
    > >> selecting an exisiting Component, but it does not work

    > on
    > >> the fly.
    > >> I don't know if the query as the source for the subform

    > is
    > >> the correct approach. I can't see another way to link

    > to
    > >> tblComponents.
    > >> I really appreciate the time you have put into helping
    > >> with this.
    > >> >-----Original Message-----
    > >> >no, you got it right. :)
    > >> >the form/subform you built is focused on

    > adding/updating
    > >> ProcessPlan
    > >> >records, including listing components that "belong to"
    > >> that plan.
    > >> >you can also (or instead) build a form/subform with the
    > >> opposite approach:
    > >> >mainform based on components, subform based on
    > >> PartListing, foreign key from
    > >> >tblComponents automatically entered in subform, foreign
    > >> key from
    > >> >tblProcessPlans entered via a combo box that is based

    > on
    > >> tblProcessPlans.
    > >> >in the above form, the focus is on add/updating

    > Component
    > >> records, including
    > >> >listing process plans that "belong to" that component.
    > >> >in either form/subform setup, my first question is: do
    > >> you want the user to
    > >> >be able to add an entry to the combo box list? you have
    > >> to decide whether
    > >> >the user will have enough info to make a complete and
    > >> valid entry in the
    > >> >combo box's underlying table.
    > >> >if the answer to the question is Yes, it's fairly easy

    > to
    > >> set up an process
    > >> >to allow entries to be added to the underlying table

    > (and
    > >> populate the combo
    > >> >box droplist) on-the-fly.
    > >> >if you want to do that, and need help setting it up,

    > post
    > >> back and i'll
    > >> >provide a code sample.
    > >> >
    > >> >hth
    > >> >
    > >> >
    > >> >"Bruce" <> wrote in
    > >> message
    > >> >news:1c7cf01c4524d$04f48670$...
    > >> >> You are right, I did not specify the table structure.
    > >> >> Forget about specifications for now, by the way. I
    > >> would
    > >> >> like to limit this until I have a better handle on
    > >> >> managing one junction table. The junction table's

    > only
    > >> >> unique field is its PK. The other fields are linked

    > to
    > >> >> tblProcessPlan and tblComponent:
    > >> >>
    > >> >> tblProcessPlan
    > >> >> PlanID (PK)
    > >> >> PlanNumber (our internal assigned number)
    > >> >> Revision
    > >> >> Process
    > >> >> Date
    > >> >> Archived (Y/N)
    > >> >>
    > >> >> tblComponent
    > >> >> ComponentID (PK)
    > >> >> ComponentNumber
    > >> >> ComponentName
    > >> >>
    > >> >> tblPartListing
    > >> >> ListingID (PK)
    > >> >> PlanID (FK)
    > >> >> ComponentID (FK)
    > >> >>
    > >> >> First, suppose I have a new Process Plan. I am not
    > >> trying
    > >> >> to get ahead of myself, nor to fix a table problem

    > with
    > >> a
    > >> >> form. I understand that a properly normalized table
    > >> >> structure is at the heart of a successful database.
    > >> >> Having said that, at some point I like to use

    > autoform
    > >> to
    > >> >> make forms to test things, because I find it easier

    > to
    > >> >> envision things that way than directly in a table. I
    > >> made
    > >> >> frmProcessPlan from tblProcessPlan. After entering

    > the
    > >> >> Plan number, date, etc. I would like to enter the
    > >> >> component number or numbers. I expect this will

    > occur
    > >> in
    > >> >> a subform based on tblPartListing (?). I am just not
    > >> able
    > >> >> to get my brain around how that will happen. (I have
    > >> >> already populated tblComponents with a listing

    > (imported
    > >> >> from a spreadsheet) of part numbers and

    > descriptions.)
    > >> >> The parent/child links of the subform control are as

    > you
    > >> >> suggest, and a combo box on the subform has

    > tblComponent
    > >> >> as its row source. Its bound column is the PK from
    > >> >> tblComponent, and its visible column is Column 2 (the
    > >> >> Component number). However, I cannot add a component
    > >> >> number that is not already in tblComponent, and I

    > cannot
    > >> >> add a Component description at all. Is there enough
    > >> here
    > >> >> for you to tell what I am doing wrong? I know that

    > I am
    > >> >> just not getting something basic.
    > >> >>
    > >> >> >-----Original Message-----
    > >> >> >> I am not trying to solve a table problem with a
    > >> form. I
    > >> >> >> didn't know there was a table problem. Now I have
    > >> built
    > >> >> >> the tables and their relationships. I have

    > imported
    > >> a
    > >> >> >> list of Component numbers and descriptions into
    > >> >> >> tblComponents, and am now trying to associate a
    > >> Process
    > >> >> >> Plan with certain Component numbers. I have

    > created
    > >> a
    > >> >> >> form based on tblProcessPlan. After entering the
    > >> plan
    > >> >> >> number, date, etc. I want to associate certain
    > >> Component
    > >> >> >> numbers with that Process Plan. Maybe it's just
    > >> because
    > >> >> >> it is Friday afternoon, but I can't sort out how

    > to
    > >> do
    > >> >> >> that. If I am not getting ahead of myself I want

    > to
    > >> >> begin
    > >> >> >> experimenting with data entry.
    > >> >> >
    > >> >> >from the remarks in your post (including above
    > >> excerpt),
    > >> >> i'm not clear on
    > >> >> >whether you did implement the linking table for
    > >> >> tblProcessPlans and
    > >> >> >tblComponents. and the linking table for
    > >> tblProcessPlans
    > >> >> and
    > >> >> >tblSpecifications.
    > >> >> >
    > >> >> >if you did set up both linking tables, then i'd

    > suggest
    > >> >> the following:
    > >> >> >
    > >> >> >you made a form based on tblProcessPlan. to

    > associate
    > >> >> specific components
    > >> >> >with a specific plan, add a subform to that main

    > form,
    > >> >> based on the linking
    > >> >> >table. the Master/Child links in the subform

    > control's
    > >> >> properties will be
    > >> >> >the key field from tblProcessPlan. it will
    > >> automatically
    > >> >> be added to each
    > >> >> >record you enter in the subform. so you only need to
    > >> >> enter the key value
    > >> >> >from tblComponents in each record in the subform.
    > >> suggest
    > >> >> you make that
    > >> >> >control a combo box, with its' RowSource set to
    > >> >> tblComponents.
    > >> >> >
    > >> >> >hth
    > >> >> >
    > >> >> >
    > >> >> >
    > >> >> >.
    > >> >> >
    > >> >
    > >> >
    > >> >.
    > >> >

    > >
    > >
    > >.
    > >
     
    tina, Jun 16, 2004
    #12
  13. Bruce

    Bruce Guest

    Thanks again for all of your patience in the midst of my
    frustration. I was indeed making the mistake of trying to
    set the control source of the combo box to the visible
    value rather than to the PK. As you explained, I need to
    set the combo box control source to the PK from the
    junction table, and the row source to the table (by way of
    a SQL statement). Component description is drawn from
    combo box.Column(2) as I mentioned earlier. I have the
    Not In List event set to hop over to a form for editing
    the list of part numbers.
    I have lots more to do on this database. I hope you won't
    duck the next time you see my name come up in the
    newsgroup. Your help has been invaluable.
    >-----Original Message-----
    >stick with this just a little longer. comments inline:
    >
    >"Bruce" <> wrote in

    message
    >news:1d3d801c4539f$6c43e1b0$...
    >> If the subform is based directly on (has as its record
    >> source) tblPartListing (the junction table) which

    contains
    >> only its own PK plus the FKs from tblComponents and
    >> tblProcessPlan, then ComponentNumber is not available as
    >> the combo box's control source.

    >
    >correct. and it's *not supposed to be*. the foreign key

    field in
    >tblPartListing is ComponentID, NOT ComponentNumber. the

    value you need to
    >save in tblPartListing is ComponentID, NOT

    ComponentNumber. your combo box
    >needs to be bound to (have it's ControlSource set to)

    ComponentID, NOT
    >ComponentNumber.
    >
    >
    > Therefore, it seems that
    >> the control source for a combo box based on tblComponent
    >> can only be an expression:
    >> =[tblComponent]![ComponentNumber]

    >
    >see above.
    >
    >
    > Column 1 is the PK, 2
    >> is ComponentNumber, 3 is ComponentDescription; I tried
    >> both 1 and 2 as the bound column.

    >
    >set the combo box BoundColumn to 1. the primary key

    (ComponentID) is the
    >value you need to save.
    >
    >
    > However, any attempt
    >> to select from the list or to add a part number results

    in
    >> the message (on the status bar) that the control can't

    be
    >> edited because it is bound to an expression. Therefore,
    >> the Not In List event cannot run.

    >
    >once you set up the combo box the way i said, the user

    will *see* the
    >ComponentNumber in the combo box (because you have the

    first column's width
    >set to zero, which is correct). when he/she enters a

    component number that
    >does not exist in tblComponents, the NotInList event

    *will* run.
    >
    >
    >> By the way, the sorting I need to do is in the combo

    box's
    >> row source, which is easy enough in a SQL expression. I
    >> can sort the Component numbers that are associated with

    a
    >> Process Plan in a report as needed. I was not clear

    about
    >> that.

    >
    >good. that makes it even easier - no complications

    involving sorting the
    >subform records.
    >
    >
    >> Thanks again for taking the time to reply, but it is

    clear
    >> that I am not explaining something properly. I will

    need
    >> to do more research.

    >
    >hopefully, you'll come back to the thread and read this

    reply. you've
    >explained your setup and what you're doing very well. it

    seems that i'm the
    >one who has not been explaining something properly,

    because i haven't been
    >able to help you understand how i'm instructing you to

    set up the
    >form/subform/combobox solution - which is a standard

    solution, by the way,
    >not some crazy thing i made up. <g>
    >
    >hth
    >
    >
    >> >-----Original Message-----
    >> >i'd say skip the query. instead, base the subform

    >> directly on
    >> >tblPartListing. base the combo box for Components

    solely
    >> on tblComponents.
    >> >since you're including all three fields of

    tblComponents
    >> in the query's
    >> >columns, those values are available to you to show in

    the
    >> subform record as
    >> >you choose - which i think you already know.
    >> >from the above setup, you should be able to add new

    >> components to the combo
    >> >box "on the fly". if you still have trouble with it

    after
    >> making those
    >> >changes, post the code from your combo box's NotInList

    >> event.
    >> >
    >> >if you need to do a sort on the subform, based on the

    >> part numbers....hmmm,
    >> >you might try sorting on a calculated control whose

    >> control source is set to
    >> >=ComboBoxName.Column(1)
    >> >i've never tried to do a form sort on a calculated

    >> control - don't know if
    >> >it'll work or not.
    >> >
    >> >hth
    >> >
    >> >
    >> >"Bruce" <> wrote in

    >> message
    >> >news:1ce5701c45311$66d45f40$...
    >> >> The way this needs to work is for the Process Plan to

    >> show
    >> >> all associated Components. A new Process Plan will

    >> start
    >> >> with a description of the plan, then will list
    >> >> Components. Most process plans are associated with
    >> >> multiple Components, but relatively few components

    are
    >> >> associated with multiple Process Plans (except in the

    >> case
    >> >> of revisions to existing Process Plans). It needs

    to be
    >> >> Process Plan first, then components.
    >> >> In answer to your question about the user adding to

    the
    >> >> combo box list, it is not only allowed, it is the

    whole
    >> >> point. I need to be able to add Component numbers

    and
    >> >> descriptions to tblComponents on the fly. I will

    enter
    >> >> the Process Plan information, then either select
    >> >> components from a combo box or type them into the

    box.
    >> >> For Process Plan 12345, I have Components 123, bolt;

    >> 124,
    >> >> nut; and 125, washer. Below the Process Plan

    >> description
    >> >> I need to see:
    >> >> 123 Bolt
    >> >> 124 Nut
    >> >> 125 Washer
    >> >> If I select Component number 123 from a combo

    >> box, "Bolt"
    >> >> needs to show up next to it. If Component number

    124 is
    >> >> not in the list, I will add it right there on the

    >> subform,
    >> >> and will add "Nut" next to it. The next time I need

    to
    >> >> add 124 (to another Process Plan), it will appear on

    the
    >> >> combo box list.
    >> >> What I have managed so far is a mainform based on
    >> >> tblProcessPlan, and a subform based on

    qryPartListing,
    >> >> which combines tblComponent and tblPartListing (the
    >> >> junction table). The subform has a combo box

    (cboList)
    >> >> based on a select query, based in turn on

    tblComponent
    >> >> (the query is to allow sorting later). ComponentID

    is
    >> the
    >> >> first column, Component number is the second, and
    >> >> Component description is the third. The combo box

    has
    >> >> three columns; only the second is visible (widths of

    1
    >> and
    >> >> 3 are zero). The text box for Component description

    has
    >> >> as its Control Source the third column of the combo

    >> box: =
    >> >> [cboList].Column(2). This is fine as long as I am

    >> always
    >> >> selecting an exisiting Component, but it does not

    work
    >> on
    >> >> the fly.
    >> >> I don't know if the query as the source for the

    subform
    >> is
    >> >> the correct approach. I can't see another way to

    link
    >> to
    >> >> tblComponents.
    >> >> I really appreciate the time you have put into

    helping
    >> >> with this.
    >> >> >-----Original Message-----
    >> >> >no, you got it right. :)
    >> >> >the form/subform you built is focused on

    >> adding/updating
    >> >> ProcessPlan
    >> >> >records, including listing components that "belong

    to"
    >> >> that plan.
    >> >> >you can also (or instead) build a form/subform with

    the
    >> >> opposite approach:
    >> >> >mainform based on components, subform based on
    >> >> PartListing, foreign key from
    >> >> >tblComponents automatically entered in subform,

    foreign
    >> >> key from
    >> >> >tblProcessPlans entered via a combo box that is

    based
    >> on
    >> >> tblProcessPlans.
    >> >> >in the above form, the focus is on add/updating

    >> Component
    >> >> records, including
    >> >> >listing process plans that "belong to" that

    component.
    >> >> >in either form/subform setup, my first question

    is: do
    >> >> you want the user to
    >> >> >be able to add an entry to the combo box list? you

    have
    >> >> to decide whether
    >> >> >the user will have enough info to make a complete

    and
    >> >> valid entry in the
    >> >> >combo box's underlying table.
    >> >> >if the answer to the question is Yes, it's fairly

    easy
    >> to
    >> >> set up an process
    >> >> >to allow entries to be added to the underlying table

    >> (and
    >> >> populate the combo
    >> >> >box droplist) on-the-fly.
    >> >> >if you want to do that, and need help setting it up,

    >> post
    >> >> back and i'll
    >> >> >provide a code sample.
    >> >> >
    >> >> >hth
    >> >> >
    >> >> >
    >> >> >"Bruce" <> wrote

    in
    >> >> message
    >> >> >news:1c7cf01c4524d$04f48670$...
    >> >> >> You are right, I did not specify the table

    structure.
    >> >> >> Forget about specifications for now, by the way.

    I
    >> >> would
    >> >> >> like to limit this until I have a better handle on
    >> >> >> managing one junction table. The junction table's

    >> only
    >> >> >> unique field is its PK. The other fields are

    linked
    >> to
    >> >> >> tblProcessPlan and tblComponent:
    >> >> >>
    >> >> >> tblProcessPlan
    >> >> >> PlanID (PK)
    >> >> >> PlanNumber (our internal assigned number)
    >> >> >> Revision
    >> >> >> Process
    >> >> >> Date
    >> >> >> Archived (Y/N)
    >> >> >>
    >> >> >> tblComponent
    >> >> >> ComponentID (PK)
    >> >> >> ComponentNumber
    >> >> >> ComponentName
    >> >> >>
    >> >> >> tblPartListing
    >> >> >> ListingID (PK)
    >> >> >> PlanID (FK)
    >> >> >> ComponentID (FK)
    >> >> >>
    >> >> >> First, suppose I have a new Process Plan. I am

    not
    >> >> trying
    >> >> >> to get ahead of myself, nor to fix a table problem

    >> with
    >> >> a
    >> >> >> form. I understand that a properly normalized

    table
    >> >> >> structure is at the heart of a successful

    database.
    >> >> >> Having said that, at some point I like to use

    >> autoform
    >> >> to
    >> >> >> make forms to test things, because I find it

    easier
    >> to
    >> >> >> envision things that way than directly in a

    table. I
    >> >> made
    >> >> >> frmProcessPlan from tblProcessPlan. After

    entering
    >> the
    >> >> >> Plan number, date, etc. I would like to enter the
    >> >> >> component number or numbers. I expect this will

    >> occur
    >> >> in
    >> >> >> a subform based on tblPartListing (?). I am just

    not
    >> >> able
    >> >> >> to get my brain around how that will happen. (I

    have
    >> >> >> already populated tblComponents with a listing

    >> (imported
    >> >> >> from a spreadsheet) of part numbers and

    >> descriptions.)
    >> >> >> The parent/child links of the subform control are

    as
    >> you
    >> >> >> suggest, and a combo box on the subform has

    >> tblComponent
    >> >> >> as its row source. Its bound column is the PK

    from
    >> >> >> tblComponent, and its visible column is Column 2

    (the
    >> >> >> Component number). However, I cannot add a

    component
    >> >> >> number that is not already in tblComponent, and I

    >> cannot
    >> >> >> add a Component description at all. Is there

    enough
    >> >> here
    >> >> >> for you to tell what I am doing wrong? I know

    that
    >> I am
    >> >> >> just not getting something basic.
    >> >> >>
    >> >> >> >-----Original Message-----
    >> >> >> >> I am not trying to solve a table problem with a
    >> >> form. I
    >> >> >> >> didn't know there was a table problem. Now I

    have
    >> >> built
    >> >> >> >> the tables and their relationships. I have

    >> imported
    >> >> a
    >> >> >> >> list of Component numbers and descriptions into
    >> >> >> >> tblComponents, and am now trying to associate a
    >> >> Process
    >> >> >> >> Plan with certain Component numbers. I have

    >> created
    >> >> a
    >> >> >> >> form based on tblProcessPlan. After entering

    the
    >> >> plan
    >> >> >> >> number, date, etc. I want to associate certain
    >> >> Component
    >> >> >> >> numbers with that Process Plan. Maybe it's

    just
    >> >> because
    >> >> >> >> it is Friday afternoon, but I can't sort out

    how
    >> to
    >> >> do
    >> >> >> >> that. If I am not getting ahead of myself I

    want
    >> to
    >> >> >> begin
    >> >> >> >> experimenting with data entry.
    >> >> >> >
    >> >> >> >from the remarks in your post (including above
    >> >> excerpt),
    >> >> >> i'm not clear on
    >> >> >> >whether you did implement the linking table for
    >> >> >> tblProcessPlans and
    >> >> >> >tblComponents. and the linking table for
    >> >> tblProcessPlans
    >> >> >> and
    >> >> >> >tblSpecifications.
    >> >> >> >
    >> >> >> >if you did set up both linking tables, then i'd

    >> suggest
    >> >> >> the following:
    >> >> >> >
    >> >> >> >you made a form based on tblProcessPlan. to

    >> associate
    >> >> >> specific components
    >> >> >> >with a specific plan, add a subform to that main

    >> form,
    >> >> >> based on the linking
    >> >> >> >table. the Master/Child links in the subform

    >> control's
    >> >> >> properties will be
    >> >> >> >the key field from tblProcessPlan. it will
    >> >> automatically
    >> >> >> be added to each
    >> >> >> >record you enter in the subform. so you only

    need to
    >> >> >> enter the key value
    >> >> >> >from tblComponents in each record in the subform.
    >> >> suggest
    >> >> >> you make that
    >> >> >> >control a combo box, with its' RowSource set to
    >> >> >> tblComponents.
    >> >> >> >
    >> >> >> >hth
    >> >> >> >
    >> >> >> >
    >> >> >> >
    >> >> >> >.
    >> >> >> >
    >> >> >
    >> >> >
    >> >> >.
    >> >> >
    >> >
    >> >
    >> >.
    >> >

    >
    >
    >.
    >
     
    Bruce, Jun 16, 2004
    #13
  14. Bruce

    tina Guest

    no problem, and you're very welcome. the tough ones just make me that much
    happier when it finally works! :)

    one minor note: from previous descriptions, your combo box is 3 columns
    altogether (though not all showing in the droplist, of course). the first
    column being the primary key ComponentID, and the second column being
    ComponentNumber. i noticed you referring to the second column as
    ComboBox.Column(2).
    just an fyi - columns in a combo box are zero-based. so if you want to refer
    to column one, in code or in an expression, it would be
    ComboBoxName.Column(0). to refer to column two, it's ComboBoxName.Column(1).
    etc, etc.


    "Bruce" <> wrote in message
    news:1d64701c453d9$d2f3acd0$...
    > Thanks again for all of your patience in the midst of my
    > frustration. I was indeed making the mistake of trying to
    > set the control source of the combo box to the visible
    > value rather than to the PK. As you explained, I need to
    > set the combo box control source to the PK from the
    > junction table, and the row source to the table (by way of
    > a SQL statement). Component description is drawn from
    > combo box.Column(2) as I mentioned earlier. I have the
    > Not In List event set to hop over to a form for editing
    > the list of part numbers.
    > I have lots more to do on this database. I hope you won't
    > duck the next time you see my name come up in the
    > newsgroup. Your help has been invaluable.
    > >-----Original Message-----
    > >stick with this just a little longer. comments inline:
    > >
    > >"Bruce" <> wrote in

    > message
    > >news:1d3d801c4539f$6c43e1b0$...
    > >> If the subform is based directly on (has as its record
    > >> source) tblPartListing (the junction table) which

    > contains
    > >> only its own PK plus the FKs from tblComponents and
    > >> tblProcessPlan, then ComponentNumber is not available as
    > >> the combo box's control source.

    > >
    > >correct. and it's *not supposed to be*. the foreign key

    > field in
    > >tblPartListing is ComponentID, NOT ComponentNumber. the

    > value you need to
    > >save in tblPartListing is ComponentID, NOT

    > ComponentNumber. your combo box
    > >needs to be bound to (have it's ControlSource set to)

    > ComponentID, NOT
    > >ComponentNumber.
    > >
    > >
    > > Therefore, it seems that
    > >> the control source for a combo box based on tblComponent
    > >> can only be an expression:
    > >> =[tblComponent]![ComponentNumber]

    > >
    > >see above.
    > >
    > >
    > > Column 1 is the PK, 2
    > >> is ComponentNumber, 3 is ComponentDescription; I tried
    > >> both 1 and 2 as the bound column.

    > >
    > >set the combo box BoundColumn to 1. the primary key

    > (ComponentID) is the
    > >value you need to save.
    > >
    > >
    > > However, any attempt
    > >> to select from the list or to add a part number results

    > in
    > >> the message (on the status bar) that the control can't

    > be
    > >> edited because it is bound to an expression. Therefore,
    > >> the Not In List event cannot run.

    > >
    > >once you set up the combo box the way i said, the user

    > will *see* the
    > >ComponentNumber in the combo box (because you have the

    > first column's width
    > >set to zero, which is correct). when he/she enters a

    > component number that
    > >does not exist in tblComponents, the NotInList event

    > *will* run.
    > >
    > >
    > >> By the way, the sorting I need to do is in the combo

    > box's
    > >> row source, which is easy enough in a SQL expression. I
    > >> can sort the Component numbers that are associated with

    > a
    > >> Process Plan in a report as needed. I was not clear

    > about
    > >> that.

    > >
    > >good. that makes it even easier - no complications

    > involving sorting the
    > >subform records.
    > >
    > >
    > >> Thanks again for taking the time to reply, but it is

    > clear
    > >> that I am not explaining something properly. I will

    > need
    > >> to do more research.

    > >
    > >hopefully, you'll come back to the thread and read this

    > reply. you've
    > >explained your setup and what you're doing very well. it

    > seems that i'm the
    > >one who has not been explaining something properly,

    > because i haven't been
    > >able to help you understand how i'm instructing you to

    > set up the
    > >form/subform/combobox solution - which is a standard

    > solution, by the way,
    > >not some crazy thing i made up. <g>
    > >
    > >hth
    > >
    > >
    > >> >-----Original Message-----
    > >> >i'd say skip the query. instead, base the subform
    > >> directly on
    > >> >tblPartListing. base the combo box for Components

    > solely
    > >> on tblComponents.
    > >> >since you're including all three fields of

    > tblComponents
    > >> in the query's
    > >> >columns, those values are available to you to show in

    > the
    > >> subform record as
    > >> >you choose - which i think you already know.
    > >> >from the above setup, you should be able to add new
    > >> components to the combo
    > >> >box "on the fly". if you still have trouble with it

    > after
    > >> making those
    > >> >changes, post the code from your combo box's NotInList
    > >> event.
    > >> >
    > >> >if you need to do a sort on the subform, based on the
    > >> part numbers....hmmm,
    > >> >you might try sorting on a calculated control whose
    > >> control source is set to
    > >> >=ComboBoxName.Column(1)
    > >> >i've never tried to do a form sort on a calculated
    > >> control - don't know if
    > >> >it'll work or not.
    > >> >
    > >> >hth
    > >> >
    > >> >
    > >> >"Bruce" <> wrote in
    > >> message
    > >> >news:1ce5701c45311$66d45f40$...
    > >> >> The way this needs to work is for the Process Plan to
    > >> show
    > >> >> all associated Components. A new Process Plan will
    > >> start
    > >> >> with a description of the plan, then will list
    > >> >> Components. Most process plans are associated with
    > >> >> multiple Components, but relatively few components

    > are
    > >> >> associated with multiple Process Plans (except in the
    > >> case
    > >> >> of revisions to existing Process Plans). It needs

    > to be
    > >> >> Process Plan first, then components.
    > >> >> In answer to your question about the user adding to

    > the
    > >> >> combo box list, it is not only allowed, it is the

    > whole
    > >> >> point. I need to be able to add Component numbers

    > and
    > >> >> descriptions to tblComponents on the fly. I will

    > enter
    > >> >> the Process Plan information, then either select
    > >> >> components from a combo box or type them into the

    > box.
    > >> >> For Process Plan 12345, I have Components 123, bolt;
    > >> 124,
    > >> >> nut; and 125, washer. Below the Process Plan
    > >> description
    > >> >> I need to see:
    > >> >> 123 Bolt
    > >> >> 124 Nut
    > >> >> 125 Washer
    > >> >> If I select Component number 123 from a combo
    > >> box, "Bolt"
    > >> >> needs to show up next to it. If Component number

    > 124 is
    > >> >> not in the list, I will add it right there on the
    > >> subform,
    > >> >> and will add "Nut" next to it. The next time I need

    > to
    > >> >> add 124 (to another Process Plan), it will appear on

    > the
    > >> >> combo box list.
    > >> >> What I have managed so far is a mainform based on
    > >> >> tblProcessPlan, and a subform based on

    > qryPartListing,
    > >> >> which combines tblComponent and tblPartListing (the
    > >> >> junction table). The subform has a combo box

    > (cboList)
    > >> >> based on a select query, based in turn on

    > tblComponent
    > >> >> (the query is to allow sorting later). ComponentID

    > is
    > >> the
    > >> >> first column, Component number is the second, and
    > >> >> Component description is the third. The combo box

    > has
    > >> >> three columns; only the second is visible (widths of

    > 1
    > >> and
    > >> >> 3 are zero). The text box for Component description

    > has
    > >> >> as its Control Source the third column of the combo
    > >> box: =
    > >> >> [cboList].Column(2). This is fine as long as I am
    > >> always
    > >> >> selecting an exisiting Component, but it does not

    > work
    > >> on
    > >> >> the fly.
    > >> >> I don't know if the query as the source for the

    > subform
    > >> is
    > >> >> the correct approach. I can't see another way to

    > link
    > >> to
    > >> >> tblComponents.
    > >> >> I really appreciate the time you have put into

    > helping
    > >> >> with this.
    > >> >> >-----Original Message-----
    > >> >> >no, you got it right. :)
    > >> >> >the form/subform you built is focused on
    > >> adding/updating
    > >> >> ProcessPlan
    > >> >> >records, including listing components that "belong

    > to"
    > >> >> that plan.
    > >> >> >you can also (or instead) build a form/subform with

    > the
    > >> >> opposite approach:
    > >> >> >mainform based on components, subform based on
    > >> >> PartListing, foreign key from
    > >> >> >tblComponents automatically entered in subform,

    > foreign
    > >> >> key from
    > >> >> >tblProcessPlans entered via a combo box that is

    > based
    > >> on
    > >> >> tblProcessPlans.
    > >> >> >in the above form, the focus is on add/updating
    > >> Component
    > >> >> records, including
    > >> >> >listing process plans that "belong to" that

    > component.
    > >> >> >in either form/subform setup, my first question

    > is: do
    > >> >> you want the user to
    > >> >> >be able to add an entry to the combo box list? you

    > have
    > >> >> to decide whether
    > >> >> >the user will have enough info to make a complete

    > and
    > >> >> valid entry in the
    > >> >> >combo box's underlying table.
    > >> >> >if the answer to the question is Yes, it's fairly

    > easy
    > >> to
    > >> >> set up an process
    > >> >> >to allow entries to be added to the underlying table
    > >> (and
    > >> >> populate the combo
    > >> >> >box droplist) on-the-fly.
    > >> >> >if you want to do that, and need help setting it up,
    > >> post
    > >> >> back and i'll
    > >> >> >provide a code sample.
    > >> >> >
    > >> >> >hth
    > >> >> >
    > >> >> >
    > >> >> >"Bruce" <> wrote

    > in
    > >> >> message
    > >> >> >news:1c7cf01c4524d$04f48670$...
    > >> >> >> You are right, I did not specify the table

    > structure.
    > >> >> >> Forget about specifications for now, by the way.

    > I
    > >> >> would
    > >> >> >> like to limit this until I have a better handle on
    > >> >> >> managing one junction table. The junction table's
    > >> only
    > >> >> >> unique field is its PK. The other fields are

    > linked
    > >> to
    > >> >> >> tblProcessPlan and tblComponent:
    > >> >> >>
    > >> >> >> tblProcessPlan
    > >> >> >> PlanID (PK)
    > >> >> >> PlanNumber (our internal assigned number)
    > >> >> >> Revision
    > >> >> >> Process
    > >> >> >> Date
    > >> >> >> Archived (Y/N)
    > >> >> >>
    > >> >> >> tblComponent
    > >> >> >> ComponentID (PK)
    > >> >> >> ComponentNumber
    > >> >> >> ComponentName
    > >> >> >>
    > >> >> >> tblPartListing
    > >> >> >> ListingID (PK)
    > >> >> >> PlanID (FK)
    > >> >> >> ComponentID (FK)
    > >> >> >>
    > >> >> >> First, suppose I have a new Process Plan. I am

    > not
    > >> >> trying
    > >> >> >> to get ahead of myself, nor to fix a table problem
    > >> with
    > >> >> a
    > >> >> >> form. I understand that a properly normalized

    > table
    > >> >> >> structure is at the heart of a successful

    > database.
    > >> >> >> Having said that, at some point I like to use
    > >> autoform
    > >> >> to
    > >> >> >> make forms to test things, because I find it

    > easier
    > >> to
    > >> >> >> envision things that way than directly in a

    > table. I
    > >> >> made
    > >> >> >> frmProcessPlan from tblProcessPlan. After

    > entering
    > >> the
    > >> >> >> Plan number, date, etc. I would like to enter the
    > >> >> >> component number or numbers. I expect this will
    > >> occur
    > >> >> in
    > >> >> >> a subform based on tblPartListing (?). I am just

    > not
    > >> >> able
    > >> >> >> to get my brain around how that will happen. (I

    > have
    > >> >> >> already populated tblComponents with a listing
    > >> (imported
    > >> >> >> from a spreadsheet) of part numbers and
    > >> descriptions.)
    > >> >> >> The parent/child links of the subform control are

    > as
    > >> you
    > >> >> >> suggest, and a combo box on the subform has
    > >> tblComponent
    > >> >> >> as its row source. Its bound column is the PK

    > from
    > >> >> >> tblComponent, and its visible column is Column 2

    > (the
    > >> >> >> Component number). However, I cannot add a

    > component
    > >> >> >> number that is not already in tblComponent, and I
    > >> cannot
    > >> >> >> add a Component description at all. Is there

    > enough
    > >> >> here
    > >> >> >> for you to tell what I am doing wrong? I know

    > that
    > >> I am
    > >> >> >> just not getting something basic.
    > >> >> >>
    > >> >> >> >-----Original Message-----
    > >> >> >> >> I am not trying to solve a table problem with a
    > >> >> form. I
    > >> >> >> >> didn't know there was a table problem. Now I

    > have
    > >> >> built
    > >> >> >> >> the tables and their relationships. I have
    > >> imported
    > >> >> a
    > >> >> >> >> list of Component numbers and descriptions into
    > >> >> >> >> tblComponents, and am now trying to associate a
    > >> >> Process
    > >> >> >> >> Plan with certain Component numbers. I have
    > >> created
    > >> >> a
    > >> >> >> >> form based on tblProcessPlan. After entering

    > the
    > >> >> plan
    > >> >> >> >> number, date, etc. I want to associate certain
    > >> >> Component
    > >> >> >> >> numbers with that Process Plan. Maybe it's

    > just
    > >> >> because
    > >> >> >> >> it is Friday afternoon, but I can't sort out

    > how
    > >> to
    > >> >> do
    > >> >> >> >> that. If I am not getting ahead of myself I

    > want
    > >> to
    > >> >> >> begin
    > >> >> >> >> experimenting with data entry.
    > >> >> >> >
    > >> >> >> >from the remarks in your post (including above
    > >> >> excerpt),
    > >> >> >> i'm not clear on
    > >> >> >> >whether you did implement the linking table for
    > >> >> >> tblProcessPlans and
    > >> >> >> >tblComponents. and the linking table for
    > >> >> tblProcessPlans
    > >> >> >> and
    > >> >> >> >tblSpecifications.
    > >> >> >> >
    > >> >> >> >if you did set up both linking tables, then i'd
    > >> suggest
    > >> >> >> the following:
    > >> >> >> >
    > >> >> >> >you made a form based on tblProcessPlan. to
    > >> associate
    > >> >> >> specific components
    > >> >> >> >with a specific plan, add a subform to that main
    > >> form,
    > >> >> >> based on the linking
    > >> >> >> >table. the Master/Child links in the subform
    > >> control's
    > >> >> >> properties will be
    > >> >> >> >the key field from tblProcessPlan. it will
    > >> >> automatically
    > >> >> >> be added to each
    > >> >> >> >record you enter in the subform. so you only

    > need to
    > >> >> >> enter the key value
    > >> >> >> >from tblComponents in each record in the subform.
    > >> >> suggest
    > >> >> >> you make that
    > >> >> >> >control a combo box, with its' RowSource set to
    > >> >> >> tblComponents.
    > >> >> >> >
    > >> >> >> >hth
    > >> >> >> >
    > >> >> >> >
    > >> >> >> >
    > >> >> >> >.
    > >> >> >> >
    > >> >> >
    > >> >> >
    > >> >> >.
    > >> >> >
    > >> >
    > >> >
    > >> >.
    > >> >

    > >
    > >
    > >.
    > >
     
    tina, Jun 17, 2004
    #14
  15. Bruce

    Bruce Guest

    LIteral column 1 , or Column(0) in AccessSpeak is the PK;
    literal column 2, or Column(1) is the Component Number;
    and literal column 3, or Column(2) is the description. My
    Combo box is three columns: Literal column 1, or Column
    (0) is the bound column, and column widths are 0";1";0".
    When I select the component number, the Description text
    box swipes the invisible literal third column, or Column
    (2). I had encountered that curiosity on an earlier
    project. It does make it difficult to talk about the
    columns. You certainly gave my posting a very thorough
    reading to pick up on that. By the way, I have begun to
    enter test data into the DB, and all is working as hoped.
    >-----Original Message-----
    >no problem, and you're very welcome. the tough ones just

    make me that much
    >happier when it finally works! :)
    >
    >one minor note: from previous descriptions, your combo

    box is 3 columns
    >altogether (though not all showing in the droplist, of

    course). the first
    >column being the primary key ComponentID, and the second

    column being
    >ComponentNumber. i noticed you referring to the second

    column as
    >ComboBox.Column(2).
    >just an fyi - columns in a combo box are zero-based. so

    if you want to refer
    >to column one, in code or in an expression, it would be
    >ComboBoxName.Column(0). to refer to column two, it's

    ComboBoxName.Column(1).
    >etc, etc.
    >
    >
    >"Bruce" <> wrote in

    message
    >news:1d64701c453d9$d2f3acd0$...
    >> Thanks again for all of your patience in the midst of my
    >> frustration. I was indeed making the mistake of trying

    to
    >> set the control source of the combo box to the visible
    >> value rather than to the PK. As you explained, I need

    to
    >> set the combo box control source to the PK from the
    >> junction table, and the row source to the table (by way

    of
    >> a SQL statement). Component description is drawn from
    >> combo box.Column(2) as I mentioned earlier. I have the
    >> Not In List event set to hop over to a form for editing
    >> the list of part numbers.
    >> I have lots more to do on this database. I hope you

    won't
    >> duck the next time you see my name come up in the
    >> newsgroup. Your help has been invaluable.
    >> >-----Original Message-----
    >> >stick with this just a little longer. comments inline:
    >> >
    >> >"Bruce" <> wrote in

    >> message
    >> >news:1d3d801c4539f$6c43e1b0$...
    >> >> If the subform is based directly on (has as its

    record
    >> >> source) tblPartListing (the junction table) which

    >> contains
    >> >> only its own PK plus the FKs from tblComponents and
    >> >> tblProcessPlan, then ComponentNumber is not

    available as
    >> >> the combo box's control source.
    >> >
    >> >correct. and it's *not supposed to be*. the foreign key

    >> field in
    >> >tblPartListing is ComponentID, NOT ComponentNumber. the

    >> value you need to
    >> >save in tblPartListing is ComponentID, NOT

    >> ComponentNumber. your combo box
    >> >needs to be bound to (have it's ControlSource set to)

    >> ComponentID, NOT
    >> >ComponentNumber.
    >> >
    >> >
    >> > Therefore, it seems that
    >> >> the control source for a combo box based on

    tblComponent
    >> >> can only be an expression:
    >> >> =[tblComponent]![ComponentNumber]
    >> >
    >> >see above.
    >> >
    >> >
    >> > Column 1 is the PK, 2
    >> >> is ComponentNumber, 3 is ComponentDescription; I

    tried
    >> >> both 1 and 2 as the bound column.
    >> >
    >> >set the combo box BoundColumn to 1. the primary key

    >> (ComponentID) is the
    >> >value you need to save.
    >> >
    >> >
    >> > However, any attempt
    >> >> to select from the list or to add a part number

    results
    >> in
    >> >> the message (on the status bar) that the control

    can't
    >> be
    >> >> edited because it is bound to an expression.

    Therefore,
    >> >> the Not In List event cannot run.
    >> >
    >> >once you set up the combo box the way i said, the user

    >> will *see* the
    >> >ComponentNumber in the combo box (because you have the

    >> first column's width
    >> >set to zero, which is correct). when he/she enters a

    >> component number that
    >> >does not exist in tblComponents, the NotInList event

    >> *will* run.
    >> >
    >> >
    >> >> By the way, the sorting I need to do is in the combo

    >> box's
    >> >> row source, which is easy enough in a SQL

    expression. I
    >> >> can sort the Component numbers that are associated

    with
    >> a
    >> >> Process Plan in a report as needed. I was not clear

    >> about
    >> >> that.
    >> >
    >> >good. that makes it even easier - no complications

    >> involving sorting the
    >> >subform records.
    >> >
    >> >
    >> >> Thanks again for taking the time to reply, but it is

    >> clear
    >> >> that I am not explaining something properly. I will

    >> need
    >> >> to do more research.
    >> >
    >> >hopefully, you'll come back to the thread and read this

    >> reply. you've
    >> >explained your setup and what you're doing very well.

    it
    >> seems that i'm the
    >> >one who has not been explaining something properly,

    >> because i haven't been
    >> >able to help you understand how i'm instructing you to

    >> set up the
    >> >form/subform/combobox solution - which is a standard

    >> solution, by the way,
    >> >not some crazy thing i made up. <g>
    >> >
    >> >hth
    >> >
    >> >
    >> >> >-----Original Message-----
    >> >> >i'd say skip the query. instead, base the subform
    >> >> directly on
    >> >> >tblPartListing. base the combo box for Components

    >> solely
    >> >> on tblComponents.
    >> >> >since you're including all three fields of

    >> tblComponents
    >> >> in the query's
    >> >> >columns, those values are available to you to show

    in
    >> the
    >> >> subform record as
    >> >> >you choose - which i think you already know.
    >> >> >from the above setup, you should be able to add new
    >> >> components to the combo
    >> >> >box "on the fly". if you still have trouble with it

    >> after
    >> >> making those
    >> >> >changes, post the code from your combo box's

    NotInList
    >> >> event.
    >> >> >
    >> >> >if you need to do a sort on the subform, based on

    the
    >> >> part numbers....hmmm,
    >> >> >you might try sorting on a calculated control whose
    >> >> control source is set to
    >> >> >=ComboBoxName.Column(1)
    >> >> >i've never tried to do a form sort on a calculated
    >> >> control - don't know if
    >> >> >it'll work or not.
    >> >> >
    >> >> >hth
    >> >> >
    >> >> >
    >> >> >"Bruce" <> wrote

    in
    >> >> message
    >> >> >news:1ce5701c45311$66d45f40$...
    >> >> >> The way this needs to work is for the Process

    Plan to
    >> >> show
    >> >> >> all associated Components. A new Process Plan

    will
    >> >> start
    >> >> >> with a description of the plan, then will list
    >> >> >> Components. Most process plans are associated

    with
    >> >> >> multiple Components, but relatively few components

    >> are
    >> >> >> associated with multiple Process Plans (except in

    the
    >> >> case
    >> >> >> of revisions to existing Process Plans). It needs

    >> to be
    >> >> >> Process Plan first, then components.
    >> >> >> In answer to your question about the user adding

    to
    >> the
    >> >> >> combo box list, it is not only allowed, it is the

    >> whole
    >> >> >> point. I need to be able to add Component numbers

    >> and
    >> >> >> descriptions to tblComponents on the fly. I will

    >> enter
    >> >> >> the Process Plan information, then either select
    >> >> >> components from a combo box or type them into the

    >> box.
    >> >> >> For Process Plan 12345, I have Components 123,

    bolt;
    >> >> 124,
    >> >> >> nut; and 125, washer. Below the Process Plan
    >> >> description
    >> >> >> I need to see:
    >> >> >> 123 Bolt
    >> >> >> 124 Nut
    >> >> >> 125 Washer
    >> >> >> If I select Component number 123 from a combo
    >> >> box, "Bolt"
    >> >> >> needs to show up next to it. If Component number

    >> 124 is
    >> >> >> not in the list, I will add it right there on the
    >> >> subform,
    >> >> >> and will add "Nut" next to it. The next time I

    need
    >> to
    >> >> >> add 124 (to another Process Plan), it will appear

    on
    >> the
    >> >> >> combo box list.
    >> >> >> What I have managed so far is a mainform based on
    >> >> >> tblProcessPlan, and a subform based on

    >> qryPartListing,
    >> >> >> which combines tblComponent and tblPartListing

    (the
    >> >> >> junction table). The subform has a combo box

    >> (cboList)
    >> >> >> based on a select query, based in turn on

    >> tblComponent
    >> >> >> (the query is to allow sorting later).

    ComponentID
    >> is
    >> >> the
    >> >> >> first column, Component number is the second, and
    >> >> >> Component description is the third. The combo box

    >> has
    >> >> >> three columns; only the second is visible (widths

    of
    >> 1
    >> >> and
    >> >> >> 3 are zero). The text box for Component

    description
    >> has
    >> >> >> as its Control Source the third column of the

    combo
    >> >> box: =
    >> >> >> [cboList].Column(2). This is fine as long as I am
    >> >> always
    >> >> >> selecting an exisiting Component, but it does not

    >> work
    >> >> on
    >> >> >> the fly.
    >> >> >> I don't know if the query as the source for the

    >> subform
    >> >> is
    >> >> >> the correct approach. I can't see another way to

    >> link
    >> >> to
    >> >> >> tblComponents.
    >> >> >> I really appreciate the time you have put into

    >> helping
    >> >> >> with this.
    >> >> >> >-----Original Message-----
    >> >> >> >no, you got it right. :)
    >> >> >> >the form/subform you built is focused on
    >> >> adding/updating
    >> >> >> ProcessPlan
    >> >> >> >records, including listing components

    that "belong
    >> to"
    >> >> >> that plan.
    >> >> >> >you can also (or instead) build a form/subform

    with
    >> the
    >> >> >> opposite approach:
    >> >> >> >mainform based on components, subform based on
    >> >> >> PartListing, foreign key from
    >> >> >> >tblComponents automatically entered in subform,

    >> foreign
    >> >> >> key from
    >> >> >> >tblProcessPlans entered via a combo box that is

    >> based
    >> >> on
    >> >> >> tblProcessPlans.
    >> >> >> >in the above form, the focus is on add/updating
    >> >> Component
    >> >> >> records, including
    >> >> >> >listing process plans that "belong to" that

    >> component.
    >> >> >> >in either form/subform setup, my first question

    >> is: do
    >> >> >> you want the user to
    >> >> >> >be able to add an entry to the combo box list?

    you
    >> have
    >> >> >> to decide whether
    >> >> >> >the user will have enough info to make a complete

    >> and
    >> >> >> valid entry in the
    >> >> >> >combo box's underlying table.
    >> >> >> >if the answer to the question is Yes, it's fairly

    >> easy
    >> >> to
    >> >> >> set up an process
    >> >> >> >to allow entries to be added to the underlying

    table
    >> >> (and
    >> >> >> populate the combo
    >> >> >> >box droplist) on-the-fly.
    >> >> >> >if you want to do that, and need help setting it

    up,
    >> >> post
    >> >> >> back and i'll
    >> >> >> >provide a code sample.
    >> >> >> >
    >> >> >> >hth
    >> >> >> >
    >> >> >> >
    >> >> >> >"Bruce" <>

    wrote
    >> in
    >> >> >> message
    >> >> >> >news:1c7cf01c4524d$04f48670$...
    >> >> >> >> You are right, I did not specify the table

    >> structure.
    >> >> >> >> Forget about specifications for now, by the

    way.
    >> I
    >> >> >> would
    >> >> >> >> like to limit this until I have a better

    handle on
    >> >> >> >> managing one junction table. The junction

    table's
    >> >> only
    >> >> >> >> unique field is its PK. The other fields are

    >> linked
    >> >> to
    >> >> >> >> tblProcessPlan and tblComponent:
    >> >> >> >>
    >> >> >> >> tblProcessPlan
    >> >> >> >> PlanID (PK)
    >> >> >> >> PlanNumber (our internal assigned number)
    >> >> >> >> Revision
    >> >> >> >> Process
    >> >> >> >> Date
    >> >> >> >> Archived (Y/N)
    >> >> >> >>
    >> >> >> >> tblComponent
    >> >> >> >> ComponentID (PK)
    >> >> >> >> ComponentNumber
    >> >> >> >> ComponentName
    >> >> >> >>
    >> >> >> >> tblPartListing
    >> >> >> >> ListingID (PK)
    >> >> >> >> PlanID (FK)
    >> >> >> >> ComponentID (FK)
    >> >> >> >>
    >> >> >> >> First, suppose I have a new Process Plan. I am

    >> not
    >> >> >> trying
    >> >> >> >> to get ahead of myself, nor to fix a table

    problem
    >> >> with
    >> >> >> a
    >> >> >> >> form. I understand that a properly normalized

    >> table
    >> >> >> >> structure is at the heart of a successful

    >> database.
    >> >> >> >> Having said that, at some point I like to use
    >> >> autoform
    >> >> >> to
    >> >> >> >> make forms to test things, because I find it

    >> easier
    >> >> to
    >> >> >> >> envision things that way than directly in a

    >> table. I
    >> >> >> made
    >> >> >> >> frmProcessPlan from tblProcessPlan. After

    >> entering
    >> >> the
    >> >> >> >> Plan number, date, etc. I would like to enter

    the
    >> >> >> >> component number or numbers. I expect this

    will
    >> >> occur
    >> >> >> in
    >> >> >> >> a subform based on tblPartListing (?). I am

    just
    >> not
    >> >> >> able
    >> >> >> >> to get my brain around how that will happen.

    (I
    >> have
    >> >> >> >> already populated tblComponents with a listing
    >> >> (imported
    >> >> >> >> from a spreadsheet) of part numbers and
    >> >> descriptions.)
    >> >> >> >> The parent/child links of the subform control

    are
    >> as
    >> >> you
    >> >> >> >> suggest, and a combo box on the subform has
    >> >> tblComponent
    >> >> >> >> as its row source. Its bound column is the PK

    >> from
    >> >> >> >> tblComponent, and its visible column is Column

    2
    >> (the
    >> >> >> >> Component number). However, I cannot add a

    >> component
    >> >> >> >> number that is not already in tblComponent,

    and I
    >> >> cannot
    >> >> >> >> add a Component description at all. Is there

    >> enough
    >> >> >> here
    >> >> >> >> for you to tell what I am doing wrong? I know

    >> that
    >> >> I am
    >> >> >> >> just not getting something basic.
    >> >> >> >>
    >> >> >> >> >-----Original Message-----
    >> >> >> >> >> I am not trying to solve a table problem

    with a
    >> >> >> form. I
    >> >> >> >> >> didn't know there was a table problem. Now

    I
    >> have
    >> >> >> built
    >> >> >> >> >> the tables and their relationships. I have
    >> >> imported
    >> >> >> a
    >> >> >> >> >> list of Component numbers and descriptions

    into
    >> >> >> >> >> tblComponents, and am now trying to

    associate a
    >> >> >> Process
    >> >> >> >> >> Plan with certain Component numbers. I have
    >> >> created
    >> >> >> a
    >> >> >> >> >> form based on tblProcessPlan. After

    entering
    >> the
    >> >> >> plan
    >> >> >> >> >> number, date, etc. I want to associate

    certain
    >> >> >> Component
    >> >> >> >> >> numbers with that Process Plan. Maybe it's

    >> just
    >> >> >> because
    >> >> >> >> >> it is Friday afternoon, but I can't sort out

    >> how
    >> >> to
    >> >> >> do
    >> >> >> >> >> that. If I am not getting ahead of myself I

    >> want
    >> >> to
    >> >> >> >> begin
    >> >> >> >> >> experimenting with data entry.
    >> >> >> >> >
    >> >> >> >> >from the remarks in your post (including above
    >> >> >> excerpt),
    >> >> >> >> i'm not clear on
    >> >> >> >> >whether you did implement the linking table

    for
    >> >> >> >> tblProcessPlans and
    >> >> >> >> >tblComponents. and the linking table for
    >> >> >> tblProcessPlans
    >> >> >> >> and
    >> >> >> >> >tblSpecifications.
    >> >> >> >> >
    >> >> >> >> >if you did set up both linking tables, then

    i'd
    >> >> suggest
    >> >> >> >> the following:
    >> >> >> >> >
    >> >> >> >> >you made a form based on tblProcessPlan. to
    >> >> associate
    >> >> >> >> specific components
    >> >> >> >> >with a specific plan, add a subform to that

    main
    >> >> form,
    >> >> >> >> based on the linking
    >> >> >> >> >table. the Master/Child links in the subform
    >> >> control's
    >> >> >> >> properties will be
    >> >> >> >> >the key field from tblProcessPlan. it will
    >> >> >> automatically
    >> >> >> >> be added to each
    >> >> >> >> >record you enter in the subform. so you only

    >> need to
    >> >> >> >> enter the key value
    >> >> >> >> >from tblComponents in each record in the

    subform.
    >> >> >> suggest
    >> >> >> >> you make that
    >> >> >> >> >control a combo box, with its' RowSource set

    to
    >> >> >> >> tblComponents.
    >> >> >> >> >
    >> >> >> >> >hth
    >> >> >> >> >
    >> >> >> >> >
    >> >> >> >> >
    >> >> >> >> >.
    >> >> >> >> >
    >> >> >> >
    >> >> >> >
    >> >> >> >.
    >> >> >> >
    >> >> >
    >> >> >
    >> >> >.
    >> >> >
    >> >
    >> >
    >> >.
    >> >

    >
    >
    >.
    >
     
    Bruce, Jun 17, 2004
    #15
  16. Bruce

    tina Guest

    oh good, glad it's working for you. :)
    re the combo box columns: i overkill sometimes, telling people what they
    already know - but better safe than sorry! <g>


    "Bruce" <> wrote in message
    news:1db9501c454a6$08fe62d0$...
    > LIteral column 1 , or Column(0) in AccessSpeak is the PK;
    > literal column 2, or Column(1) is the Component Number;
    > and literal column 3, or Column(2) is the description. My
    > Combo box is three columns: Literal column 1, or Column
    > (0) is the bound column, and column widths are 0";1";0".
    > When I select the component number, the Description text
    > box swipes the invisible literal third column, or Column
    > (2). I had encountered that curiosity on an earlier
    > project. It does make it difficult to talk about the
    > columns. You certainly gave my posting a very thorough
    > reading to pick up on that. By the way, I have begun to
    > enter test data into the DB, and all is working as hoped.
    > >-----Original Message-----
    > >no problem, and you're very welcome. the tough ones just

    > make me that much
    > >happier when it finally works! :)
    > >
    > >one minor note: from previous descriptions, your combo

    > box is 3 columns
    > >altogether (though not all showing in the droplist, of

    > course). the first
    > >column being the primary key ComponentID, and the second

    > column being
    > >ComponentNumber. i noticed you referring to the second

    > column as
    > >ComboBox.Column(2).
    > >just an fyi - columns in a combo box are zero-based. so

    > if you want to refer
    > >to column one, in code or in an expression, it would be
    > >ComboBoxName.Column(0). to refer to column two, it's

    > ComboBoxName.Column(1).
    > >etc, etc.
    > >
    > >
    > >"Bruce" <> wrote in

    > message
    > >news:1d64701c453d9$d2f3acd0$...
    > >> Thanks again for all of your patience in the midst of my
    > >> frustration. I was indeed making the mistake of trying

    > to
    > >> set the control source of the combo box to the visible
    > >> value rather than to the PK. As you explained, I need

    > to
    > >> set the combo box control source to the PK from the
    > >> junction table, and the row source to the table (by way

    > of
    > >> a SQL statement). Component description is drawn from
    > >> combo box.Column(2) as I mentioned earlier. I have the
    > >> Not In List event set to hop over to a form for editing
    > >> the list of part numbers.
    > >> I have lots more to do on this database. I hope you

    > won't
    > >> duck the next time you see my name come up in the
    > >> newsgroup. Your help has been invaluable.
    > >> >-----Original Message-----
    > >> >stick with this just a little longer. comments inline:
    > >> >
    > >> >"Bruce" <> wrote in
    > >> message
    > >> >news:1d3d801c4539f$6c43e1b0$...
    > >> >> If the subform is based directly on (has as its

    > record
    > >> >> source) tblPartListing (the junction table) which
    > >> contains
    > >> >> only its own PK plus the FKs from tblComponents and
    > >> >> tblProcessPlan, then ComponentNumber is not

    > available as
    > >> >> the combo box's control source.
    > >> >
    > >> >correct. and it's *not supposed to be*. the foreign key
    > >> field in
    > >> >tblPartListing is ComponentID, NOT ComponentNumber. the
    > >> value you need to
    > >> >save in tblPartListing is ComponentID, NOT
    > >> ComponentNumber. your combo box
    > >> >needs to be bound to (have it's ControlSource set to)
    > >> ComponentID, NOT
    > >> >ComponentNumber.
    > >> >
    > >> >
    > >> > Therefore, it seems that
    > >> >> the control source for a combo box based on

    > tblComponent
    > >> >> can only be an expression:
    > >> >> =[tblComponent]![ComponentNumber]
    > >> >
    > >> >see above.
    > >> >
    > >> >
    > >> > Column 1 is the PK, 2
    > >> >> is ComponentNumber, 3 is ComponentDescription; I

    > tried
    > >> >> both 1 and 2 as the bound column.
    > >> >
    > >> >set the combo box BoundColumn to 1. the primary key
    > >> (ComponentID) is the
    > >> >value you need to save.
    > >> >
    > >> >
    > >> > However, any attempt
    > >> >> to select from the list or to add a part number

    > results
    > >> in
    > >> >> the message (on the status bar) that the control

    > can't
    > >> be
    > >> >> edited because it is bound to an expression.

    > Therefore,
    > >> >> the Not In List event cannot run.
    > >> >
    > >> >once you set up the combo box the way i said, the user
    > >> will *see* the
    > >> >ComponentNumber in the combo box (because you have the
    > >> first column's width
    > >> >set to zero, which is correct). when he/she enters a
    > >> component number that
    > >> >does not exist in tblComponents, the NotInList event
    > >> *will* run.
    > >> >
    > >> >
    > >> >> By the way, the sorting I need to do is in the combo
    > >> box's
    > >> >> row source, which is easy enough in a SQL

    > expression. I
    > >> >> can sort the Component numbers that are associated

    > with
    > >> a
    > >> >> Process Plan in a report as needed. I was not clear
    > >> about
    > >> >> that.
    > >> >
    > >> >good. that makes it even easier - no complications
    > >> involving sorting the
    > >> >subform records.
    > >> >
    > >> >
    > >> >> Thanks again for taking the time to reply, but it is
    > >> clear
    > >> >> that I am not explaining something properly. I will
    > >> need
    > >> >> to do more research.
    > >> >
    > >> >hopefully, you'll come back to the thread and read this
    > >> reply. you've
    > >> >explained your setup and what you're doing very well.

    > it
    > >> seems that i'm the
    > >> >one who has not been explaining something properly,
    > >> because i haven't been
    > >> >able to help you understand how i'm instructing you to
    > >> set up the
    > >> >form/subform/combobox solution - which is a standard
    > >> solution, by the way,
    > >> >not some crazy thing i made up. <g>
    > >> >
    > >> >hth
    > >> >
    > >> >
    > >> >> >-----Original Message-----
    > >> >> >i'd say skip the query. instead, base the subform
    > >> >> directly on
    > >> >> >tblPartListing. base the combo box for Components
    > >> solely
    > >> >> on tblComponents.
    > >> >> >since you're including all three fields of
    > >> tblComponents
    > >> >> in the query's
    > >> >> >columns, those values are available to you to show

    > in
    > >> the
    > >> >> subform record as
    > >> >> >you choose - which i think you already know.
    > >> >> >from the above setup, you should be able to add new
    > >> >> components to the combo
    > >> >> >box "on the fly". if you still have trouble with it
    > >> after
    > >> >> making those
    > >> >> >changes, post the code from your combo box's

    > NotInList
    > >> >> event.
    > >> >> >
    > >> >> >if you need to do a sort on the subform, based on

    > the
    > >> >> part numbers....hmmm,
    > >> >> >you might try sorting on a calculated control whose
    > >> >> control source is set to
    > >> >> >=ComboBoxName.Column(1)
    > >> >> >i've never tried to do a form sort on a calculated
    > >> >> control - don't know if
    > >> >> >it'll work or not.
    > >> >> >
    > >> >> >hth
    > >> >> >
    > >> >> >
    > >> >> >"Bruce" <> wrote

    > in
    > >> >> message
    > >> >> >news:1ce5701c45311$66d45f40$...
    > >> >> >> The way this needs to work is for the Process

    > Plan to
    > >> >> show
    > >> >> >> all associated Components. A new Process Plan

    > will
    > >> >> start
    > >> >> >> with a description of the plan, then will list
    > >> >> >> Components. Most process plans are associated

    > with
    > >> >> >> multiple Components, but relatively few components
    > >> are
    > >> >> >> associated with multiple Process Plans (except in

    > the
    > >> >> case
    > >> >> >> of revisions to existing Process Plans). It needs
    > >> to be
    > >> >> >> Process Plan first, then components.
    > >> >> >> In answer to your question about the user adding

    > to
    > >> the
    > >> >> >> combo box list, it is not only allowed, it is the
    > >> whole
    > >> >> >> point. I need to be able to add Component numbers
    > >> and
    > >> >> >> descriptions to tblComponents on the fly. I will
    > >> enter
    > >> >> >> the Process Plan information, then either select
    > >> >> >> components from a combo box or type them into the
    > >> box.
    > >> >> >> For Process Plan 12345, I have Components 123,

    > bolt;
    > >> >> 124,
    > >> >> >> nut; and 125, washer. Below the Process Plan
    > >> >> description
    > >> >> >> I need to see:
    > >> >> >> 123 Bolt
    > >> >> >> 124 Nut
    > >> >> >> 125 Washer
    > >> >> >> If I select Component number 123 from a combo
    > >> >> box, "Bolt"
    > >> >> >> needs to show up next to it. If Component number
    > >> 124 is
    > >> >> >> not in the list, I will add it right there on the
    > >> >> subform,
    > >> >> >> and will add "Nut" next to it. The next time I

    > need
    > >> to
    > >> >> >> add 124 (to another Process Plan), it will appear

    > on
    > >> the
    > >> >> >> combo box list.
    > >> >> >> What I have managed so far is a mainform based on
    > >> >> >> tblProcessPlan, and a subform based on
    > >> qryPartListing,
    > >> >> >> which combines tblComponent and tblPartListing

    > (the
    > >> >> >> junction table). The subform has a combo box
    > >> (cboList)
    > >> >> >> based on a select query, based in turn on
    > >> tblComponent
    > >> >> >> (the query is to allow sorting later).

    > ComponentID
    > >> is
    > >> >> the
    > >> >> >> first column, Component number is the second, and
    > >> >> >> Component description is the third. The combo box
    > >> has
    > >> >> >> three columns; only the second is visible (widths

    > of
    > >> 1
    > >> >> and
    > >> >> >> 3 are zero). The text box for Component

    > description
    > >> has
    > >> >> >> as its Control Source the third column of the

    > combo
    > >> >> box: =
    > >> >> >> [cboList].Column(2). This is fine as long as I am
    > >> >> always
    > >> >> >> selecting an exisiting Component, but it does not
    > >> work
    > >> >> on
    > >> >> >> the fly.
    > >> >> >> I don't know if the query as the source for the
    > >> subform
    > >> >> is
    > >> >> >> the correct approach. I can't see another way to
    > >> link
    > >> >> to
    > >> >> >> tblComponents.
    > >> >> >> I really appreciate the time you have put into
    > >> helping
    > >> >> >> with this.
    > >> >> >> >-----Original Message-----
    > >> >> >> >no, you got it right. :)
    > >> >> >> >the form/subform you built is focused on
    > >> >> adding/updating
    > >> >> >> ProcessPlan
    > >> >> >> >records, including listing components

    > that "belong
    > >> to"
    > >> >> >> that plan.
    > >> >> >> >you can also (or instead) build a form/subform

    > with
    > >> the
    > >> >> >> opposite approach:
    > >> >> >> >mainform based on components, subform based on
    > >> >> >> PartListing, foreign key from
    > >> >> >> >tblComponents automatically entered in subform,
    > >> foreign
    > >> >> >> key from
    > >> >> >> >tblProcessPlans entered via a combo box that is
    > >> based
    > >> >> on
    > >> >> >> tblProcessPlans.
    > >> >> >> >in the above form, the focus is on add/updating
    > >> >> Component
    > >> >> >> records, including
    > >> >> >> >listing process plans that "belong to" that
    > >> component.
    > >> >> >> >in either form/subform setup, my first question
    > >> is: do
    > >> >> >> you want the user to
    > >> >> >> >be able to add an entry to the combo box list?

    > you
    > >> have
    > >> >> >> to decide whether
    > >> >> >> >the user will have enough info to make a complete
    > >> and
    > >> >> >> valid entry in the
    > >> >> >> >combo box's underlying table.
    > >> >> >> >if the answer to the question is Yes, it's fairly
    > >> easy
    > >> >> to
    > >> >> >> set up an process
    > >> >> >> >to allow entries to be added to the underlying

    > table
    > >> >> (and
    > >> >> >> populate the combo
    > >> >> >> >box droplist) on-the-fly.
    > >> >> >> >if you want to do that, and need help setting it

    > up,
    > >> >> post
    > >> >> >> back and i'll
    > >> >> >> >provide a code sample.
    > >> >> >> >
    > >> >> >> >hth
    > >> >> >> >
    > >> >> >> >
    > >> >> >> >"Bruce" <>

    > wrote
    > >> in
    > >> >> >> message
    > >> >> >> >news:1c7cf01c4524d$04f48670$...
    > >> >> >> >> You are right, I did not specify the table
    > >> structure.
    > >> >> >> >> Forget about specifications for now, by the

    > way.
    > >> I
    > >> >> >> would
    > >> >> >> >> like to limit this until I have a better

    > handle on
    > >> >> >> >> managing one junction table. The junction

    > table's
    > >> >> only
    > >> >> >> >> unique field is its PK. The other fields are
    > >> linked
    > >> >> to
    > >> >> >> >> tblProcessPlan and tblComponent:
    > >> >> >> >>
    > >> >> >> >> tblProcessPlan
    > >> >> >> >> PlanID (PK)
    > >> >> >> >> PlanNumber (our internal assigned number)
    > >> >> >> >> Revision
    > >> >> >> >> Process
    > >> >> >> >> Date
    > >> >> >> >> Archived (Y/N)
    > >> >> >> >>
    > >> >> >> >> tblComponent
    > >> >> >> >> ComponentID (PK)
    > >> >> >> >> ComponentNumber
    > >> >> >> >> ComponentName
    > >> >> >> >>
    > >> >> >> >> tblPartListing
    > >> >> >> >> ListingID (PK)
    > >> >> >> >> PlanID (FK)
    > >> >> >> >> ComponentID (FK)
    > >> >> >> >>
    > >> >> >> >> First, suppose I have a new Process Plan. I am
    > >> not
    > >> >> >> trying
    > >> >> >> >> to get ahead of myself, nor to fix a table

    > problem
    > >> >> with
    > >> >> >> a
    > >> >> >> >> form. I understand that a properly normalized
    > >> table
    > >> >> >> >> structure is at the heart of a successful
    > >> database.
    > >> >> >> >> Having said that, at some point I like to use
    > >> >> autoform
    > >> >> >> to
    > >> >> >> >> make forms to test things, because I find it
    > >> easier
    > >> >> to
    > >> >> >> >> envision things that way than directly in a
    > >> table. I
    > >> >> >> made
    > >> >> >> >> frmProcessPlan from tblProcessPlan. After
    > >> entering
    > >> >> the
    > >> >> >> >> Plan number, date, etc. I would like to enter

    > the
    > >> >> >> >> component number or numbers. I expect this

    > will
    > >> >> occur
    > >> >> >> in
    > >> >> >> >> a subform based on tblPartListing (?). I am

    > just
    > >> not
    > >> >> >> able
    > >> >> >> >> to get my brain around how that will happen.

    > (I
    > >> have
    > >> >> >> >> already populated tblComponents with a listing
    > >> >> (imported
    > >> >> >> >> from a spreadsheet) of part numbers and
    > >> >> descriptions.)
    > >> >> >> >> The parent/child links of the subform control

    > are
    > >> as
    > >> >> you
    > >> >> >> >> suggest, and a combo box on the subform has
    > >> >> tblComponent
    > >> >> >> >> as its row source. Its bound column is the PK
    > >> from
    > >> >> >> >> tblComponent, and its visible column is Column

    > 2
    > >> (the
    > >> >> >> >> Component number). However, I cannot add a
    > >> component
    > >> >> >> >> number that is not already in tblComponent,

    > and I
    > >> >> cannot
    > >> >> >> >> add a Component description at all. Is there
    > >> enough
    > >> >> >> here
    > >> >> >> >> for you to tell what I am doing wrong? I know
    > >> that
    > >> >> I am
    > >> >> >> >> just not getting something basic.
    > >> >> >> >>
    > >> >> >> >> >-----Original Message-----
    > >> >> >> >> >> I am not trying to solve a table problem

    > with a
    > >> >> >> form. I
    > >> >> >> >> >> didn't know there was a table problem. Now

    > I
    > >> have
    > >> >> >> built
    > >> >> >> >> >> the tables and their relationships. I have
    > >> >> imported
    > >> >> >> a
    > >> >> >> >> >> list of Component numbers and descriptions

    > into
    > >> >> >> >> >> tblComponents, and am now trying to

    > associate a
    > >> >> >> Process
    > >> >> >> >> >> Plan with certain Component numbers. I have
    > >> >> created
    > >> >> >> a
    > >> >> >> >> >> form based on tblProcessPlan. After

    > entering
    > >> the
    > >> >> >> plan
    > >> >> >> >> >> number, date, etc. I want to associate

    > certain
    > >> >> >> Component
    > >> >> >> >> >> numbers with that Process Plan. Maybe it's
    > >> just
    > >> >> >> because
    > >> >> >> >> >> it is Friday afternoon, but I can't sort out
    > >> how
    > >> >> to
    > >> >> >> do
    > >> >> >> >> >> that. If I am not getting ahead of myself I
    > >> want
    > >> >> to
    > >> >> >> >> begin
    > >> >> >> >> >> experimenting with data entry.
    > >> >> >> >> >
    > >> >> >> >> >from the remarks in your post (including above
    > >> >> >> excerpt),
    > >> >> >> >> i'm not clear on
    > >> >> >> >> >whether you did implement the linking table

    > for
    > >> >> >> >> tblProcessPlans and
    > >> >> >> >> >tblComponents. and the linking table for
    > >> >> >> tblProcessPlans
    > >> >> >> >> and
    > >> >> >> >> >tblSpecifications.
    > >> >> >> >> >
    > >> >> >> >> >if you did set up both linking tables, then

    > i'd
    > >> >> suggest
    > >> >> >> >> the following:
    > >> >> >> >> >
    > >> >> >> >> >you made a form based on tblProcessPlan. to
    > >> >> associate
    > >> >> >> >> specific components
    > >> >> >> >> >with a specific plan, add a subform to that

    > main
    > >> >> form,
    > >> >> >> >> based on the linking
    > >> >> >> >> >table. the Master/Child links in the subform
    > >> >> control's
    > >> >> >> >> properties will be
    > >> >> >> >> >the key field from tblProcessPlan. it will
    > >> >> >> automatically
    > >> >> >> >> be added to each
    > >> >> >> >> >record you enter in the subform. so you only
    > >> need to
    > >> >> >> >> enter the key value
    > >> >> >> >> >from tblComponents in each record in the

    > subform.
    > >> >> >> suggest
    > >> >> >> >> you make that
    > >> >> >> >> >control a combo box, with its' RowSource set

    > to
    > >> >> >> >> tblComponents.
    > >> >> >> >> >
    > >> >> >> >> >hth
    > >> >> >> >> >
    > >> >> >> >> >
    > >> >> >> >> >
    > >> >> >> >> >.
    > >> >> >> >> >
    > >> >> >> >
    > >> >> >> >
    > >> >> >> >.
    > >> >> >> >
    > >> >> >
    > >> >> >
    > >> >> >.
    > >> >> >
    > >> >
    > >> >
    > >> >.
    > >> >

    > >
    > >
    > >.
    > >
     
    tina, Jun 19, 2004
    #16
  17. Bruce

    Guest

    i have issue in pdf design...
    pls help me..
    thank you
     
    , Apr 12, 2013
    #17
  18. On Fri, 12 Apr 2013 04:16:14 -0700 (PDT), wrote:

    >i have issue in pdf design...
    >pls help me..
    >thank you


    Are you designing pdf files in a Microsoft Access database application? That's
    the subject of this (defunct) newsgroup.

    I strongly suspect you need to a) choose a more appropriate forum, perhaps one
    having to do with designing pdf files; and b) post a longer and clearer
    question. This one sounds like "Doctor, I don't feel good, what should I take"
    - asked in a bicycle repair shop!
    --

    John W. Vinson [MVP]
    Microsoft's replacements for these newsgroups:
    http://social.msdn.microsoft.com/Forums/en-US/accessdev/
    http://social.answers.microsoft.com/Forums/en-US/addbuz/
    and see also http://www.utteraccess.com
     
    John W. Vinson, Apr 12, 2013
    #18
    1. Advertisements

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

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. JPearson
    Replies:
    3
    Views:
    163
    John Nurick
    May 24, 2004
  2. Darren
    Replies:
    9
    Views:
    210
    John Vinson
    Nov 16, 2004
  3. Replies:
    2
    Views:
    304
    tlyczko
    Jan 18, 2005
  4. Amit
    Replies:
    1
    Views:
    93
    Duane Hookom
    Jan 27, 2005
  5. Damon Heron

    Table design questions

    Damon Heron, Mar 11, 2005, in forum: Access Table Design
    Replies:
    5
    Views:
    148
    Chris2
    Mar 11, 2005
  6. stgpatrick
    Replies:
    1
    Views:
    214
    stgpatrick
    May 12, 2005
  7. Charles W. Stricklin

    Questions on database design and normalization

    Charles W. Stricklin, Aug 30, 2005, in forum: Access Table Design
    Replies:
    2
    Views:
    211
    Charles W. Stricklin
    Sep 1, 2005
  8. tjr

    DB Design and Relationship Questions

    tjr, Nov 12, 2005, in forum: Access Table Design
    Replies:
    7
    Views:
    126
Loading...