Changing unbound combo boxes 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

 

[Marshall Barton]

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.

Please read my reply in our other thread. With the
additional information here, I think the combo box's
RowSource should be:

SELECT [Product ID], Product Name
FROM Products
WHERE Category ID = Forms!mainform.Category
ORDER BY [Product Name]

The ColumnCount property would be 2
The BoundColumn whould be 1
The ColumnWidths property would be 0;1

subform3's LinkMasterFields would then be set to the product
combo box.
-----------------------------
It all depends on what you are trying to accomplish with
these forms, but I am beginning to think that there's
something fundamentally odd about about what you are trying
to do with these subforms. It doesn't appear to make sense
for subform2 to be bound to the products table and at the
same time the products combo box to bound to the Product ID
field. It seems like you only need one form (bound to the
Components table) with an unbound(?) category combo box and
the Products combo box bound to the Product ID field in
Components???

 

[Karl]

Hi Marshall,

Thanks for your all your sugguestions. I'll give them a try at work
tommorrow and let you know what happens. As to your point on the fundamentals
of my subform. I see your point, I had originally tried it this way but was
not able to figure out how to bind the products combo box to the product id
field in components.

I am new to forms and most have what I've learned is through trial and error
and from these internet postings (which I've found very helpful) (i'm also
taking a course on Access next week which will hopefully give me a better
understanding of all this works, but I was hoping to get this database up and
running before my course).

As for the reason I set it up this way with the subforms is I thought each
table would need its own subform. Ie. Categories, Products, and components as
the only way I could figure out how to link them is through master and child
fields. Originally this worked fine if combo boxes weren't used. However I
wanted to add the combo boxes so a category could be chosen and then a
product could be chosen based on the category. However like i said when the
combo boxes (Categories and Products) were on the same from I couldn't figure
out how to get components to link up to the product. The purpose of this
database is to create an inventory or products that will be sorted by
category. In the end I planned on adding more subforms for maintenence info,
and purchase info for each components based on a component id. Hopefully this
theory makes some sense to you. Do you think I should re-attempt to do it
your way instead?


I can not tell you how appreciative I am of all your help, as a new user it
definately makes access a little less intimadating being able to get expert
help so easily.

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.

Please read my reply in our other thread. With the
additional information here, I think the combo box's
RowSource should be:

SELECT [Product ID], Product Name
FROM Products
WHERE Category ID = Forms!mainform.Category
ORDER BY [Product Name]

The ColumnCount property would be 2
The BoundColumn whould be 1
The ColumnWidths property would be 0;1

subform3's LinkMasterFields would then be set to the product
combo box.
-----------------------------
It all depends on what you are trying to accomplish with
these forms, but I am beginning to think that there's
something fundamentally odd about about what you are trying
to do with these subforms. It doesn't appear to make sense
for subform2 to be bound to the products table and at the
same time the products combo box to bound to the Product ID
field. It seems like you only need one form (bound to the
Components table) with an unbound(?) category combo box and
the Products combo box bound to the Product ID field in
Components???

 

[Karl]

Hi,
I tried setting them up the way you mentioned and I was able to get it to
work. Thanks a lot for your help.

Karl

Hi Marshall,

Thanks for your all your sugguestions. I'll give them a try at work
tommorrow and let you know what happens. As to your point on the fundamentals
of my subform. I see your point, I had originally tried it this way but was
not able to figure out how to bind the products combo box to the product id
field in components.

I am new to forms and most have what I've learned is through trial and error
and from these internet postings (which I've found very helpful) (i'm also
taking a course on Access next week which will hopefully give me a better
understanding of all this works, but I was hoping to get this database up and
running before my course).

As for the reason I set it up this way with the subforms is I thought each
table would need its own subform. Ie. Categories, Products, and components as
the only way I could figure out how to link them is through master and child
fields. Originally this worked fine if combo boxes weren't used. However I
wanted to add the combo boxes so a category could be chosen and then a
product could be chosen based on the category. However like i said when the
combo boxes (Categories and Products) were on the same from I couldn't figure
out how to get components to link up to the product. The purpose of this
database is to create an inventory or products that will be sorted by
category. In the end I planned on adding more subforms for maintenence info,
and purchase info for each components based on a component id. Hopefully this
theory makes some sense to you. Do you think I should re-attempt to do it
your way instead?


I can not tell you how appreciative I am of all your help, as a new user it
definately makes access a little less intimadating being able to get expert
help so easily.

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.

Please read my reply in our other thread. With the
additional information here, I think the combo box's
RowSource should be:

SELECT [Product ID], Product Name
FROM Products
WHERE Category ID = Forms!mainform.Category
ORDER BY [Product Name]

The ColumnCount property would be 2
The BoundColumn whould be 1
The ColumnWidths property would be 0;1

subform3's LinkMasterFields would then be set to the product
combo box.
-----------------------------
It all depends on what you are trying to accomplish with
these forms, but I am beginning to think that there's
something fundamentally odd about about what you are trying
to do with these subforms. It doesn't appear to make sense
for subform2 to be bound to the products table and at the
same time the products combo box to bound to the Product ID
field. It seems like you only need one form (bound to the
Components table) with an unbound(?) category combo box and
the Products combo box bound to the Product ID field in
Components???

 

[Marshall Barton]

That's good news Karl. I was composing a message with more
details about what I thought you might need, but you figured
it out before I finished. Way to go.
--
Marsh
MVP [MS Access]

I tried setting them up the way you mentioned and I was able to get it to
work. Thanks a lot for your help.

Thanks for your all your sugguestions. I'll give them a try at work
tommorrow and let you know what happens. As to your point on the fundamentals
of my subform. I see your point, I had originally tried it this way but was
not able to figure out how to bind the products combo box to the product id
field in components.

I am new to forms and most have what I've learned is through trial and error
and from these internet postings (which I've found very helpful) (i'm also
taking a course on Access next week which will hopefully give me a better
understanding of all this works, but I was hoping to get this database up and
running before my course).

As for the reason I set it up this way with the subforms is I thought each
table would need its own subform. Ie. Categories, Products, and components as
the only way I could figure out how to link them is through master and child
fields. Originally this worked fine if combo boxes weren't used. However I
wanted to add the combo boxes so a category could be chosen and then a
product could be chosen based on the category. However like i said when the
combo boxes (Categories and Products) were on the same from I couldn't figure
out how to get components to link up to the product. The purpose of this
database is to create an inventory or products that will be sorted by
category. In the end I planned on adding more subforms for maintenence info,
and purchase info for each components based on a component id. Hopefully this
theory makes some sense to you. Do you think I should re-attempt to do it
your way instead?


I can not tell you how appreciative I am of all your help, as a new user it
definately makes access a little less intimadating being able to get expert
help so easily.

Karl wrote:

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.


Please read my reply in our other thread. With the
additional information here, I think the combo box's
RowSource should be:

SELECT [Product ID], Product Name
FROM Products
WHERE Category ID = Forms!mainform.Category
ORDER BY [Product Name]

The ColumnCount property would be 2
The BoundColumn whould be 1
The ColumnWidths property would be 0;1

subform3's LinkMasterFields would then be set to the product
combo box.
-----------------------------
It all depends on what you are trying to accomplish with
these forms, but I am beginning to think that there's
something fundamentally odd about about what you are trying
to do with these subforms. It doesn't appear to make sense
for subform2 to be bound to the products table and at the
same time the products combo box to bound to the Product ID
field. It seems like you only need one form (bound to the
Components table) with an unbound(?) category combo box and
the Products combo box bound to the Product ID field in
Components???