Design questions

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
...
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
...
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



.



.



.

stick with this just a little longer. comments inline:

"Bruce" wrote in message
...
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
...
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
...
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



.



.



.

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
...
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
...
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
...
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



.



.



.



.

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
...
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
...
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
...
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
...
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



.



.



.



.

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
...
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
...
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
...
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
...
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



.



.



.



.



.

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
...
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
...
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
...
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
...
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
...
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



.



.



.



.



.