Changing a combo box from unbound to bound!

[Karl]

I have 3 tables that are linked.

Category Table:
Category ID (primary key) -autonumber
Category Name

Products Table:
Product ID (primary key) - autonumber
Category ID
Product Name

Components Table:
Component ID (primary key) - autonumber
Product ID
Equipment Name
Make
Model

I have created a form (Form1) that has an unbound combo box that lists all
the names of categories for all the products I have. I have a subform (Form
2) with a unbound combo box for products. For this combo box on the products
that are associated with the chosen category are shown. I would like to
create a subform (Form 3) in Form 2 that shows the Component information of
each product selected. My original plan was to simply link these by product
id as a master and child field, however as the product and category combo
boxes are unbound I don’t think this is possible. I tried to make the product
combo box a bound field with the products table the record source and product
name the control source (I also tried to make product id the control source
and it caused my combo box to quit working properly). I then added a text box
with the product id as the control source that would allow me to use as a
master field for the link to the child field (product id on form 3). However
the text box with the product id doesn’t update to the correct product id
number for the product name chosen in the products combo box. Note: I left
the category box unbound.

I was wondering why this isn’t working or if there is a better method for
connecting subform 2 to subform 3.

Thanks a lot,
Karl Zimmer

 

[tina]

well, you don't really need three forms. suggest an unbound main form (which
i'll call frmMain), which contains two unbound combo box controls and a
subform control (which i'll call ChildComponents). set the RowSource of the
first combo box (i'll call it cboCategory), as

SELECT CategoryID, CategoryName FROM Category;

set the ColumnCount to 2, the BoundColumn to 1, and the ColumnWidths to 0";
1".

set the RowSource of the second combo box (i'll call it cboProduct), as

SELECT ProductID, ProductName WHERE CategoryID = Forms!frmMain!cboCategory;

set the ColumnCount to 2, the BoundColumn to 1, and the ColumnWidths to 0";
1".

add some code to cboCategory control's AfterUpdate event procedure, as

Me!cboProduct.Requery

create a form bound to the Components table (which i'll call
sfrmComponents). back in frmMain design view, set the SourceObject property
of the ChildComponents subform control to sfrmComponents. set the
LinkChildFields property to ProductID, and set the LinkMasterFields property
to [cboProduct].

now test frmMain in form view. when you choose a category from cboCategory,
the cboProduct droplist should be filtered. then when you choose a product
from cboProduct, the associated components should show in the subform.

hth

 

[Karl]

Hello,
Wow thank you very much that was so easy. You don't know how long I've been
working on that! Thank you very very much. I was wondering if I could
trouble you with another question. Beyond the subform you told be to add I
added another subform (sfrm sub components) to enter sub compent information
about some of the components. I was albe to get this to work no problem, but
i tried to do a query that included category name, product name, component
name it works fine to show this but when i add the sub component to the query
it only shows components that also have subcompents so i was wondering if
there was something i could add to the criteria of the component name that
would allow it show all components (not just ones with subcomponets) I.e.
like a show all command or something.

Thanks again for solving my problem,

Karl

well, you don't really need three forms. suggest an unbound main form (which
i'll call frmMain), which contains two unbound combo box controls and a
subform control (which i'll call ChildComponents). set the RowSource of the
first combo box (i'll call it cboCategory), as

SELECT CategoryID, CategoryName FROM Category;

set the ColumnCount to 2, the BoundColumn to 1, and the ColumnWidths to 0";
1".

set the RowSource of the second combo box (i'll call it cboProduct), as

SELECT ProductID, ProductName WHERE CategoryID = Forms!frmMain!cboCategory;

set the ColumnCount to 2, the BoundColumn to 1, and the ColumnWidths to 0";
1".

add some code to cboCategory control's AfterUpdate event procedure, as

Me!cboProduct.Requery

create a form bound to the Components table (which i'll call
sfrmComponents). back in frmMain design view, set the SourceObject property
of the ChildComponents subform control to sfrmComponents. set the
LinkChildFields property to ProductID, and set the LinkMasterFields property
to [cboProduct].

now test frmMain in form view. when you choose a category from cboCategory,
the cboProduct droplist should be filtered. then when you choose a product
from cboProduct, the associated components should show in the subform.

hth

I have 3 tables that are linked.

Category Table:
Category ID (primary key) -autonumber
Category Name

Products Table:
Product ID (primary key) - autonumber
Category ID
Product Name

Components Table:
Component ID (primary key) - autonumber
Product ID
Equipment Name
Make
Model

I have created a form (Form1) that has an unbound combo box that lists all
the names of categories for all the products I have. I have a subform (Form
2) with a unbound combo box for products. For this combo box on the products
that are associated with the chosen category are shown. I would like to
create a subform (Form 3) in Form 2 that shows the Component information of
each product selected. My original plan was to simply link these by product
id as a master and child field, however as the product and category combo
boxes are unbound I don't think this is possible. I tried to make the product
combo box a bound field with the products table the record source and product
name the control source (I also tried to make product id the control source
and it caused my combo box to quit working properly). I then added a text box
with the product id as the control source that would allow me to use as a
master field for the link to the child field (product id on form 3). However
the text box with the product id doesn't update to the correct product id
number for the product name chosen in the products combo box. Note: I left
the category box unbound.

I was wondering why this isn't working or if there is a better method for
connecting subform 2 to subform 3.

Thanks a lot,
Karl Zimmer

 

[tina]

post the SQL of your query, Karl. to do that, open the query in design view,
then on the menu bar click View | SQL View. highlight the *entire* text,
copy, and paste into a post. also, pls clarify for me: the query you
described is used as the RecordSource of sfrm sub components, correct?

hth

Hello,
Wow thank you very much that was so easy. You don't know how long I've been
working on that! Thank you very very much. I was wondering if I could
trouble you with another question. Beyond the subform you told be to add I
added another subform (sfrm sub components) to enter sub compent information
about some of the components. I was albe to get this to work no problem, but
i tried to do a query that included category name, product name, component
name it works fine to show this but when i add the sub component to the query
it only shows components that also have subcompents so i was wondering if
there was something i could add to the criteria of the component name that
would allow it show all components (not just ones with subcomponets) I.e.
like a show all command or something.

Thanks again for solving my problem,

Karl

well, you don't really need three forms. suggest an unbound main form (which
i'll call frmMain), which contains two unbound combo box controls and a
subform control (which i'll call ChildComponents). set the RowSource of the
first combo box (i'll call it cboCategory), as

SELECT CategoryID, CategoryName FROM Category;

set the ColumnCount to 2, the BoundColumn to 1, and the ColumnWidths to 0";
1".

set the RowSource of the second combo box (i'll call it cboProduct), as

SELECT ProductID, ProductName WHERE CategoryID = Forms!frmMain!cboCategory;

set the ColumnCount to 2, the BoundColumn to 1, and the ColumnWidths to 0";
1".

add some code to cboCategory control's AfterUpdate event procedure, as

Me!cboProduct.Requery

create a form bound to the Components table (which i'll call
sfrmComponents). back in frmMain design view, set the SourceObject property
of the ChildComponents subform control to sfrmComponents. set the
LinkChildFields property to ProductID, and set the LinkMasterFields property
to [cboProduct].

now test frmMain in form view. when you choose a category from cboCategory,
the cboProduct droplist should be filtered. then when you choose a product
from cboProduct, the associated components should show in the subform.

hth

I have 3 tables that are linked.

Category Table:
Category ID (primary key) -autonumber
Category Name

Products Table:
Product ID (primary key) - autonumber
Category ID
Product Name

Components Table:
Component ID (primary key) - autonumber
Product ID
Equipment Name
Make
Model

I have created a form (Form1) that has an unbound combo box that lists all
the names of categories for all the products I have. I have a subform (Form
2) with a unbound combo box for products. For this combo box on the products
that are associated with the chosen category are shown. I would like to
create a subform (Form 3) in Form 2 that shows the Component

information
of

each product selected. My original plan was to simply link these by product
id as a master and child field, however as the product and category combo
boxes are unbound I don't think this is possible. I tried to make the product
combo box a bound field with the products table the record source and product
name the control source (I also tried to make product id the control source
and it caused my combo box to quit working properly). I then added a

text
box

with the product id as the control source that would allow me to use as a
master field for the link to the child field (product id on form 3). However
the text box with the product id doesn't update to the correct product id
number for the product name chosen in the products combo box. Note: I left
the category box unbound.

I was wondering why this isn't working or if there is a better method for
connecting subform 2 to subform 3.

Thanks a lot,
Karl Zimmer

 

[Karl]

Here is the query SQL.

SELECT Categories.Category, [Product Table].ProductName, [Components
table].[Name of Equipment], [Sub Component Table].[Component Name]
FROM ((Categories INNER JOIN [Product Table] ON Categories.CategoryID =
[Product Table].CategoryID) INNER JOIN [Components table] ON [Product
Table].ProductID = [Components table].ProductID) INNER JOIN [Sub Component
Table] ON [Components table].[Equipment ID] = [Sub Component
Table].[Equipment ID];

No, this query is not used for a record source in the subform. I am using
it for a record source for a report. I want to create a report that will list
all my products by category, and then all the corresponding components and
subcompents of the each product (plus some additional info about the
components and subcompents (i.e. quantity, make, model #). However currently
the query only shows the category, product and compents that also have
subcompenents (some of my components don't have sub-components), but I want
it to show all the products that do and do not have sub-components as well as
the sub-components that do exist. I thought their might be a sql statement i
could put in the criteria of the component field that would cause it to show
all the components even the ones without sub-componets (without removing the
sub-component table from my query).

Hope this makes sense.

Karl

post the SQL of your query, Karl. to do that, open the query in design view,
then on the menu bar click View | SQL View. highlight the *entire* text,
copy, and paste into a post. also, pls clarify for me: the query you
described is used as the RecordSource of sfrm sub components, correct?

hth

Hello,
Wow thank you very much that was so easy. You don't know how long I've been
working on that! Thank you very very much. I was wondering if I could
trouble you with another question. Beyond the subform you told be to add I
added another subform (sfrm sub components) to enter sub compent information
about some of the components. I was albe to get this to work no problem, but
i tried to do a query that included category name, product name, component
name it works fine to show this but when i add the sub component to the query
it only shows components that also have subcompents so i was wondering if
there was something i could add to the criteria of the component name that
would allow it show all components (not just ones with subcomponets) I.e.
like a show all command or something.

Thanks again for solving my problem,

Karl

well, you don't really need three forms. suggest an unbound main form (which
i'll call frmMain), which contains two unbound combo box controls and a
subform control (which i'll call ChildComponents). set the RowSource of the
first combo box (i'll call it cboCategory), as

SELECT CategoryID, CategoryName FROM Category;

set the ColumnCount to 2, the BoundColumn to 1, and the ColumnWidths to 0";
1".

set the RowSource of the second combo box (i'll call it cboProduct), as

SELECT ProductID, ProductName WHERE CategoryID = Forms!frmMain!cboCategory;

set the ColumnCount to 2, the BoundColumn to 1, and the ColumnWidths to 0";
1".

add some code to cboCategory control's AfterUpdate event procedure, as

Me!cboProduct.Requery

create a form bound to the Components table (which i'll call
sfrmComponents). back in frmMain design view, set the SourceObject property
of the ChildComponents subform control to sfrmComponents. set the
LinkChildFields property to ProductID, and set the LinkMasterFields property
to [cboProduct].

now test frmMain in form view. when you choose a category from cboCategory,
the cboProduct droplist should be filtered. then when you choose a product
from cboProduct, the associated components should show in the subform.

hth


I have 3 tables that are linked.

Category Table:
Category ID (primary key) -autonumber
Category Name

Products Table:
Product ID (primary key) - autonumber
Category ID
Product Name

Components Table:
Component ID (primary key) - autonumber
Product ID
Equipment Name
Make
Model

I have created a form (Form1) that has an unbound combo box that lists all
the names of categories for all the products I have. I have a subform
(Form
2) with a unbound combo box for products. For this combo box on the
products
that are associated with the chosen category are shown. I would like to
create a subform (Form 3) in Form 2 that shows the Component information
of
each product selected. My original plan was to simply link these by
product
id as a master and child field, however as the product and category combo
boxes are unbound I don't think this is possible. I tried to make the
product
combo box a bound field with the products table the record source and
product
name the control source (I also tried to make product id the control
source
and it caused my combo box to quit working properly). I then added a text
box
with the product id as the control source that would allow me to use as a
master field for the link to the child field (product id on form 3).
However
the text box with the product id doesn't update to the correct product id
number for the product name chosen in the products combo box. Note: I left
the category box unbound.

I was wondering why this isn't working or if there is a better method for
connecting subform 2 to subform 3.

Thanks a lot,
Karl Zimmer

 

[tina]

okay. try changing all your INNER JOINs to LEFT JOINs, as

SELECT Categories.Category, [Product Table].ProductName, [Components
table].[Name of Equipment], [Sub Component Table].[Component Name]
FROM ((Categories LEFT JOIN [Product Table] ON Categories.CategoryID =
[Product Table].CategoryID) LEFT JOIN [Components table] ON [Product
Table].ProductID = [Components table].ProductID) LEFT JOIN [Sub Component
Table] ON [Components table].[Equipment ID] = [Sub Component
Table].[Equipment ID];

hth

Here is the query SQL.

SELECT Categories.Category, [Product Table].ProductName, [Components
table].[Name of Equipment], [Sub Component Table].[Component Name]
FROM ((Categories INNER JOIN [Product Table] ON Categories.CategoryID =
[Product Table].CategoryID) INNER JOIN [Components table] ON [Product
Table].ProductID = [Components table].ProductID) INNER JOIN [Sub Component
Table] ON [Components table].[Equipment ID] = [Sub Component
Table].[Equipment ID];

No, this query is not used for a record source in the subform. I am using
it for a record source for a report. I want to create a report that will list
all my products by category, and then all the corresponding components and
subcompents of the each product (plus some additional info about the
components and subcompents (i.e. quantity, make, model #). However currently
the query only shows the category, product and compents that also have
subcompenents (some of my components don't have sub-components), but I want
it to show all the products that do and do not have sub-components as well as
the sub-components that do exist. I thought their might be a sql statement i
could put in the criteria of the component field that would cause it to show
all the components even the ones without sub-componets (without removing the
sub-component table from my query).

Hope this makes sense.

Karl

post the SQL of your query, Karl. to do that, open the query in design view,
then on the menu bar click View | SQL View. highlight the *entire* text,
copy, and paste into a post. also, pls clarify for me: the query you
described is used as the RecordSource of sfrm sub components, correct?

hth

Hello,
Wow thank you very much that was so easy. You don't know how long I've been
working on that! Thank you very very much. I was wondering if I could
trouble you with another question. Beyond the subform you told be to add I
added another subform (sfrm sub components) to enter sub compent information
about some of the components. I was albe to get this to work no

problem,
but

i tried to do a query that included category name, product name, component
name it works fine to show this but when i add the sub component to

the
query

it only shows components that also have subcompents so i was wondering if
there was something i could add to the criteria of the component name that
would allow it show all components (not just ones with subcomponets) I.e.
like a show all command or something.

Thanks again for solving my problem,

Karl


:

well, you don't really need three forms. suggest an unbound main

form
(which

i'll call frmMain), which contains two unbound combo box controls and a
subform control (which i'll call ChildComponents). set the RowSource

of
the

first combo box (i'll call it cboCategory), as

SELECT CategoryID, CategoryName FROM Category;

set the ColumnCount to 2, the BoundColumn to 1, and the ColumnWidths

to
0";

1".

set the RowSource of the second combo box (i'll call it cboProduct), as

SELECT ProductID, ProductName WHERE CategoryID = Forms!frmMain!cboCategory;

set the ColumnCount to 2, the BoundColumn to 1, and the ColumnWidths

to
0";

1".

add some code to cboCategory control's AfterUpdate event procedure, as

Me!cboProduct.Requery

create a form bound to the Components table (which i'll call
sfrmComponents). back in frmMain design view, set the SourceObject property
of the ChildComponents subform control to sfrmComponents. set the
LinkChildFields property to ProductID, and set the LinkMasterFields property
to [cboProduct].

now test frmMain in form view. when you choose a category from cboCategory,
the cboProduct droplist should be filtered. then when you choose a product
from cboProduct, the associated components should show in the subform.

hth


I have 3 tables that are linked.

Category Table:
Category ID (primary key) -autonumber
Category Name

Products Table:
Product ID (primary key) - autonumber
Category ID
Product Name

Components Table:
Component ID (primary key) - autonumber
Product ID
Equipment Name
Make
Model

I have created a form (Form1) that has an unbound combo box that

lists
all

the names of categories for all the products I have. I have a subform
(Form
2) with a unbound combo box for products. For this combo box on the
products
that are associated with the chosen category are shown. I would

like
to

create a subform (Form 3) in Form 2 that shows the Component information
of
each product selected. My original plan was to simply link these by
product
id as a master and child field, however as the product and

category
combo

boxes are unbound I don't think this is possible. I tried to make the
product
combo box a bound field with the products table the record source and
product
name the control source (I also tried to make product id the control
source
and it caused my combo box to quit working properly). I then added

a
text

box
with the product id as the control source that would allow me to

use
as a

master field for the link to the child field (product id on form 3).
However
the text box with the product id doesn't update to the correct

product
id

number for the product name chosen in the products combo box.

Note: I
left

the category box unbound.

I was wondering why this isn't working or if there is a better

method
for

connecting subform 2 to subform 3.

Thanks a lot,
Karl Zimmer