Combo Box Problem/Question #2

[William Bradley]

I have a production table and its accompanying form. There can be up to ten
ingredients in what is produced.

The ingredients are obtained from the incoming goods table which supplies
the two parameters that I use, "Name" and "CatlogNo".

This production table needs to contain each ingredient by name and catalogue
number. With help from this list I was able to set up a combo box that
allowed me to select a particular ingredient, and when I did, the catalog
number was automatically filled in. Worked fine!

Then I went down to the second ingredient and there I ran in to trouble. As
the field "CatlogNo" was being used in the previous combo box on Ingredient
number one, I could not use it again. Now I'm stuck. Is there a workaround
for this sort of thing?

Any help would be appreciated and please realize I am an absolute newbie
with Access.

Thank you,

Bill.
--
William Bradley
Come visit us at:
http://www.catholicmissionleaflets.org
Free Rosaries available at the above.

[Phil Stanton]

Sounds like a progrem I wrote for recipies.

You need a Production table TblProduction (ProductID and ProductName)
You need an Ingregient table TblIngredient (Ingredient ID ,IngredientName,
IngredientCatlogNo)
You need a Join table to joint a product with as many or as few ingredients
as you like JnProductionlIngredient (ProductID, Ingredient ID and maybe a
quantity) Make the ProductID, Ingredient ID koint primary keys, link the
three tabled and make the Indredients a continuous subform of the Product
form. The Ingredients can be selected from the combo box on the subform

HTH

Phil

"William Bradley" <br******@magma.ca> wrote in message
news:WZ********************@magma.ca...

I have a production table and its accompanying form. There can be up to ten ingredients in what is produced.

The ingredients are obtained from the incoming goods table which supplies
the two parameters that I use, "Name" and "CatlogNo".

This production table needs to contain each ingredient by name and catalogue number. With help from this list I was able to set up a combo box that
allowed me to select a particular ingredient, and when I did, the catalog
number was automatically filled in. Worked fine!

Then I went down to the second ingredient and there I ran in to trouble. As the field "CatlogNo" was being used in the previous combo box on Ingredient number one, I could not use it again. Now I'm stuck. Is there a workaround
for this sort of thing?

Any help would be appreciated and please realize I am an absolute newbie
with Access.

Thank you,

Bill.
--
William Bradley
Come visit us at:
http://www.catholicmissionleaflets.org
Free Rosaries available at the above.

[PC Datasheet]

Bill,

It sounds like you are using a continuous form with a field named CatalogNo that
is not bound to any field in the record source of the form. When you look at the
form in design view, does it sau 'Unbound' inside the field? If this is the
case, you need to make CatlogNo bound.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"William Bradley" <br******@magma.ca> wrote in message
news:WZ********************@magma.ca...

I have a production table and its accompanying form. There can be up to ten
ingredients in what is produced.

The ingredients are obtained from the incoming goods table which supplies
the two parameters that I use, "Name" and "CatlogNo".

This production table needs to contain each ingredient by name and catalogue
number. With help from this list I was able to set up a combo box that
allowed me to select a particular ingredient, and when I did, the catalog
number was automatically filled in. Worked fine!

Then I went down to the second ingredient and there I ran in to trouble. As
the field "CatlogNo" was being used in the previous combo box on Ingredient
number one, I could not use it again. Now I'm stuck. Is there a workaround
for this sort of thing?

Any help would be appreciated and please realize I am an absolute newbie
with Access.

Thank you,

Bill.
--
William Bradley
Come visit us at:
http://www.catholicmissionleaflets.org
Free Rosaries available at the above.

[William Bradley]

PC Datasheet wrote:

Hi,

Following are the instructions you sent to me, and they work just fine.
Basically what I want to is repeat it, using "Ingredent Name" and
"Ingredient Catalog Number". This will then be repeated ten times on a
single form.

When I tried binding the unbound box (IndexNo) in the lower example (which
was unbound by the way), I bound myself into a mess. :-( The "Help"menu
says to bind the Record Source from on of the tables or queries listed and
I must have chosen the wrong one.

So my question is to what do I bind the "Index No" box to?

Thank you,

Bill.

================================================== ==============
First, The order of the fields in the query is very important so be sure you
have that. Product is in the first field and IndexNo is in the second field.

Open your form in design view and select the textbox. Click on the
properties icon at the top of the screen to open properties. Click on the
'Other' tab. The name property is at the top of the list. Type in IndexNo
for the name of the textbox.

Next select the combobox. Be sure you are still on the Other tab. The name
property is at the top of the list. Does it say, 'Combo131'? If not type
that in.

Go to the Data tab. Down a couple of lines is the Bound Column property.
Does it say 2? If not type in a 2.

Go to the Format tab. At the top of the list is the Column Count property.
Does it say 2? If not enter a 2. Two lines down from there is the Column
Width property. Does it say 1.5;0? If not type that in.

Go to the Events tab and click on the AfterUpdate event. At the right of the
white box are three dots, click on them. In the dialog that appears, select
Code Builder then click OK. You are now at the code module behind the form.
You should see the following at the cursor: Private Sub
Combo131_AfterUpdate()

End Sub
Put the following code between these two lines:
Me!IndexNo = Me!Combo131.Column(1)

It sounds like you are using a continuous form with a field named
CatalogNo that is not bound to any field in the record source of the form.
When you look at the form in design view, does it sau 'Unbound' inside the
field? If this is the case, you need to make CatlogNo bound.

William Bradley
Come visit us at:
http://www.catholicmissionleaflets.org
Free Rosaries available at the above.

[PC Datasheet]

Bill,

I'm going to borrow from Phil a little. You need the following tables:

TblProduction (ProductID and ProductName)
You need an Ingregient table TblIngredient (Ingredient ID ,IngredientName,
IngredientCatlogNo)
You need a Join table to joint a product with as many or as few ingredients
as you like JnProductionlIngredient (ProductID, Ingredient ID and maybe a
quantity) Make the ProductID, Ingredient ID koint primary keys, link the
three tabled and make the Indredients a continuous subform of the Product
form. The Ingredients can be selected from the combo box on the subform
TblProduct
ProductId
ProductName
etc

TblInGredient
InGredientID
InGredientName
InGredientCatlogNo

TblProductionProduct
ProductionProductID
ProductID
ProductDate
etc

TblProductionProductInGredients
ProductionProductInGredientsID
ProductionProductID
InGredientID
QuantityOfInGredient ???

Create a relationship between ProductId In TblProduct and ProductID In
TblProductionProduct. Create a relationship between InGredientID In
TblInGredient and InGredientID In TblProductionProductInGredients. Create a
relationship between ProductionProductID In TblProductionProduct and
ProductionProductID In TblProductionProductInGredients.

You then need to build a form/subform system.

The main form will be based on TblProductionProduct and will be a single form.
You can use a combobox to select the productID. Base the combobox on TblProduct.
Set the bound column to 1, column count to 2 and column width to 0;2.

The subform will be based on TblProductionProductInGredients and will be a
continuous form. You can use a combobox to select the IngredientID. Base the
combobox on a query based on TblIngredient.
The query will have the fields:
InGredientID
InGredient:[InGredientCatlogNo] & " " & [InGredientName]

Set the bound column to 1, column count to 2 and column width to 0;2.5.

After you install the subform on the main form, open the main form in design
view, select the subform control, open properties, go to the Data tab and check
the LinkMaster and LinkChild fields. They both should be ProductionProductID. If
they are blank, click on the three dots at the right.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"William Bradley" <br******@magma.ca> wrote in message
news:eO********************@magma.ca...

PC Datasheet wrote:

Hi,

Following are the instructions you sent to me, and they work just fine.
Basically what I want to is repeat it, using "Ingredent Name" and
"Ingredient Catalog Number". This will then be repeated ten times on a
single form.

When I tried binding the unbound box (IndexNo) in the lower example (which
was unbound by the way), I bound myself into a mess. :-( The "Help"menu
says to bind the Record Source from on of the tables or queries listed and
I must have chosen the wrong one.

So my question is to what do I bind the "Index No" box to?

Thank you,

Bill.

================================================== ==============
First, The order of the fields in the query is very important so be sure you
have that. Product is in the first field and IndexNo is in the second field.

Open your form in design view and select the textbox. Click on the
properties icon at the top of the screen to open properties. Click on the
'Other' tab. The name property is at the top of the list. Type in IndexNo
for the name of the textbox.

Next select the combobox. Be sure you are still on the Other tab. The name
property is at the top of the list. Does it say, 'Combo131'? If not type
that in.

Go to the Data tab. Down a couple of lines is the Bound Column property.
Does it say 2? If not type in a 2.

Go to the Format tab. At the top of the list is the Column Count property.
Does it say 2? If not enter a 2. Two lines down from there is the Column
Width property. Does it say 1.5;0? If not type that in.

Go to the Events tab and click on the AfterUpdate event. At the right of the
white box are three dots, click on them. In the dialog that appears, select
Code Builder then click OK. You are now at the code module behind the form.
You should see the following at the cursor: Private Sub
Combo131_AfterUpdate()

End Sub
Put the following code between these two lines:
Me!IndexNo = Me!Combo131.Column(1)

It sounds like you are using a continuous form with a field named
CatalogNo that is not bound to any field in the record source of the form.
When you look at the form in design view, does it sau 'Unbound' inside the
field? If this is the case, you need to make CatlogNo bound.

William Bradley
Come visit us at:
http://www.catholicmissionleaflets.org
Free Rosaries available at the above.

[William Bradley]

Phil Stanton wrote:

Thank you for the following Paul. It is a little more complex than I thought
it would be.

Bill.

Sounds like a progrem I wrote for recipies.

You need a Production table TblProduction (ProductID and ProductName)
You need an Ingregient table TblIngredient (Ingredient ID ,IngredientName,
IngredientCatlogNo)
You need a Join table to joint a product with as many or as few
ingredients as you like JnProductionlIngredient (ProductID, Ingredient ID
and maybe a quantity) Make the ProductID, Ingredient ID koint primary
keys, link the three tabled and make the Indredients a continuous subform
of the Product form. The Ingredients can be selected from the combo box on
the subform

HTH

Phil

"William Bradley" <br******@magma.ca> wrote in message
news:WZ********************@magma.ca...

I have a production table and its accompanying form. There can be up to

ten

ingredients in what is produced.

The ingredients are obtained from the incoming goods table which supplies
the two parameters that I use, "Name" and "CatlogNo".

This production table needs to contain each ingredient by name and

catalogue

number. With help from this list I was able to set up a combo box that
allowed me to select a particular ingredient, and when I did, the catalog
number was automatically filled in. Worked fine!

Then I went down to the second ingredient and there I ran in to trouble.

As

the field "CatlogNo" was being used in the previous combo box on

Ingredient

number one, I could not use it again. Now I'm stuck. Is there a
workaround for this sort of thing?

Any help would be appreciated and please realize I am an absolute newbie
with Access.

Thank you,

Bill.
--
William Bradley
Come visit us at:
http://www.catholicmissionleaflets.org
Free Rosaries available at the above.

--
William Bradley
Come visit us at:
http://www.catholicmissionleaflets.org
Free Rosaries available at the above.

[William Bradley]

PC Datasheet wrote:

Once again, thank you for your detailed response. I had no idea it would be
this complex. In any event I will work at it and I'm sure it will work out.

Bill.

Bill,

I'm going to borrow from Phil a little. You need the following tables:

TblProduction (ProductID and ProductName)
You need an Ingregient table TblIngredient (Ingredient ID ,IngredientName,
IngredientCatlogNo)
You need a Join table to joint a product with as many or as few
ingredients as you like JnProductionlIngredient (ProductID, Ingredient ID
and maybe a quantity) Make the ProductID, Ingredient ID koint primary
keys, link the three tabled and make the Indredients a continuous subform
of the Product form. The Ingredients can be selected from the combo box on
the subform
TblProduct
ProductId
ProductName
etc

TblInGredient
InGredientID
InGredientName
InGredientCatlogNo

TblProductionProduct
ProductionProductID
ProductID
ProductDate
etc

TblProductionProductInGredients
ProductionProductInGredientsID
ProductionProductID
InGredientID
QuantityOfInGredient ???

Create a relationship between ProductId In TblProduct and ProductID In
TblProductionProduct. Create a relationship between InGredientID In
TblInGredient and InGredientID In TblProductionProductInGredients. Create
a relationship between ProductionProductID In TblProductionProduct and
ProductionProductID In TblProductionProductInGredients.

You then need to build a form/subform system.

The main form will be based on TblProductionProduct and will be a single
form. You can use a combobox to select the productID. Base the combobox on
TblProduct. Set the bound column to 1, column count to 2 and column width
to 0;2.

The subform will be based on TblProductionProductInGredients and will be a
continuous form. You can use a combobox to select the IngredientID. Base
the combobox on a query based on TblIngredient.
The query will have the fields:
InGredientID
InGredient:[InGredientCatlogNo] & " " & [InGredientName]

Set the bound column to 1, column count to 2 and column width to 0;2.5.

After you install the subform on the main form, open the main form in
design view, select the subform control, open properties, go to the Data
tab and check the LinkMaster and LinkChild fields. They both should be
ProductionProductID. If they are blank, click on the three dots at the
right.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"William Bradley" <br******@magma.ca> wrote in message
news:eO********************@magma.ca...

PC Datasheet wrote:

Hi,

Following are the instructions you sent to me, and they work just fine.
Basically what I want to is repeat it, using "Ingredent Name" and
"Ingredient Catalog Number". This will then be repeated ten times on a
single form.

When I tried binding the unbound box (IndexNo) in the lower example
(which was unbound by the way), I bound myself into a mess. :-( The
"Help"menu says to bind the Record Source from on of the tables or
queries listed and I must have chosen the wrong one.

So my question is to what do I bind the "Index No" box to?

Thank you,

Bill.

================================================== ==============
First, The order of the fields in the query is very important so be sure
you have that. Product is in the first field and IndexNo is in the second
field.

Open your form in design view and select the textbox. Click on the
properties icon at the top of the screen to open properties. Click on the
'Other' tab. The name property is at the top of the list. Type in IndexNo
for the name of the textbox.

Next select the combobox. Be sure you are still on the Other tab. The
name property is at the top of the list. Does it say, 'Combo131'? If not
type that in.

Go to the Data tab. Down a couple of lines is the Bound Column property.
Does it say 2? If not type in a 2.

Go to the Format tab. At the top of the list is the Column Count
property. Does it say 2? If not enter a 2. Two lines down from there is
the Column Width property. Does it say 1.5;0? If not type that in.

Go to the Events tab and click on the AfterUpdate event. At the right of
the white box are three dots, click on them. In the dialog that appears,
select Code Builder then click OK. You are now at the code module behind
the form. You should see the following at the cursor: Private Sub
Combo131_AfterUpdate()

End Sub
Put the following code between these two lines:
Me!IndexNo = Me!Combo131.Column(1)

> It sounds like you are using a continuous form with a field named
> CatalogNo that is not bound to any field in the record source of the
> form. When you look at the form in design view, does it sau 'Unbound'
> inside the field? If this is the case, you need to make CatlogNo bound.

William Bradley
Come visit us at:
http://www.catholicmissionleaflets.org
Free Rosaries available at the above.

--
William Bradley
Come visit us at:
http://www.catholicmissionleaflets.org
Free Rosaries available at the above.

[William Bradley]

Phil Stanton wrote:

Thank you for the following Paul. It is a little more complex than I thought
it would be.

Bill.

Sounds like a progrem I wrote for recipies.

You need a Production table TblProduction (ProductID and ProductName)
You need an Ingregient table TblIngredient (Ingredient ID ,IngredientName,
IngredientCatlogNo)
You need a Join table to joint a product with as many or as few
ingredients as you like JnProductionlIngredient (ProductID, Ingredient ID
and maybe a quantity) Make the ProductID, Ingredient ID koint primary
keys, link the three tabled and make the Indredients a continuous subform
of the Product form. The Ingredients can be selected from the combo box on
the subform

HTH

Phil

"William Bradley" <br******@magma.ca> wrote in message
news:WZ********************@magma.ca...

I have a production table and its accompanying form. There can be up to

ten

ingredients in what is produced.

The ingredients are obtained from the incoming goods table which supplies
the two parameters that I use, "Name" and "CatlogNo".

This production table needs to contain each ingredient by name and

catalogue

number. With help from this list I was able to set up a combo box that
allowed me to select a particular ingredient, and when I did, the catalog
number was automatically filled in. Worked fine!

Then I went down to the second ingredient and there I ran in to trouble.

As

the field "CatlogNo" was being used in the previous combo box on

Ingredient

number one, I could not use it again. Now I'm stuck. Is there a
workaround for this sort of thing?

Any help would be appreciated and please realize I am an absolute newbie
with Access.

Thank you,

Bill.
--
William Bradley
Come visit us at:
http://www.catholicmissionleaflets.org
Free Rosaries available at the above.

--
William Bradley
Come visit us at:
http://www.catholicmissionleaflets.org
Free Rosaries available at the above.

[William Bradley]

PC Datasheet wrote:

Once again, thank you for your detailed response. I had no idea it would be
this complex. In any event I will work at it and I'm sure it will work out.

Bill.

Bill,

I'm going to borrow from Phil a little. You need the following tables:

TblProduction (ProductID and ProductName)
You need an Ingregient table TblIngredient (Ingredient ID ,IngredientName,
IngredientCatlogNo)
You need a Join table to joint a product with as many or as few
ingredients as you like JnProductionlIngredient (ProductID, Ingredient ID
and maybe a quantity) Make the ProductID, Ingredient ID koint primary
keys, link the three tabled and make the Indredients a continuous subform
of the Product form. The Ingredients can be selected from the combo box on
the subform
TblProduct
ProductId
ProductName
etc

TblInGredient
InGredientID
InGredientName
InGredientCatlogNo

TblProductionProduct
ProductionProductID
ProductID
ProductDate
etc

TblProductionProductInGredients
ProductionProductInGredientsID
ProductionProductID
InGredientID
QuantityOfInGredient ???

Create a relationship between ProductId In TblProduct and ProductID In
TblProductionProduct. Create a relationship between InGredientID In
TblInGredient and InGredientID In TblProductionProductInGredients. Create
a relationship between ProductionProductID In TblProductionProduct and
ProductionProductID In TblProductionProductInGredients.

You then need to build a form/subform system.

The main form will be based on TblProductionProduct and will be a single
form. You can use a combobox to select the productID. Base the combobox on
TblProduct. Set the bound column to 1, column count to 2 and column width
to 0;2.

The subform will be based on TblProductionProductInGredients and will be a
continuous form. You can use a combobox to select the IngredientID. Base
the combobox on a query based on TblIngredient.
The query will have the fields:
InGredientID
InGredient:[InGredientCatlogNo] & " " & [InGredientName]

Set the bound column to 1, column count to 2 and column width to 0;2.5.

After you install the subform on the main form, open the main form in
design view, select the subform control, open properties, go to the Data
tab and check the LinkMaster and LinkChild fields. They both should be
ProductionProductID. If they are blank, click on the three dots at the
right.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

"William Bradley" <br******@magma.ca> wrote in message
news:eO********************@magma.ca...

PC Datasheet wrote:

Hi,

Following are the instructions you sent to me, and they work just fine.
Basically what I want to is repeat it, using "Ingredent Name" and
"Ingredient Catalog Number". This will then be repeated ten times on a
single form.

When I tried binding the unbound box (IndexNo) in the lower example
(which was unbound by the way), I bound myself into a mess. :-( The
"Help"menu says to bind the Record Source from on of the tables or
queries listed and I must have chosen the wrong one.

So my question is to what do I bind the "Index No" box to?

Thank you,

Bill.

================================================== ==============
First, The order of the fields in the query is very important so be sure
you have that. Product is in the first field and IndexNo is in the second
field.

Open your form in design view and select the textbox. Click on the
properties icon at the top of the screen to open properties. Click on the
'Other' tab. The name property is at the top of the list. Type in IndexNo
for the name of the textbox.

Next select the combobox. Be sure you are still on the Other tab. The
name property is at the top of the list. Does it say, 'Combo131'? If not
type that in.

Go to the Data tab. Down a couple of lines is the Bound Column property.
Does it say 2? If not type in a 2.

Go to the Format tab. At the top of the list is the Column Count
property. Does it say 2? If not enter a 2. Two lines down from there is
the Column Width property. Does it say 1.5;0? If not type that in.

Go to the Events tab and click on the AfterUpdate event. At the right of
the white box are three dots, click on them. In the dialog that appears,
select Code Builder then click OK. You are now at the code module behind
the form. You should see the following at the cursor: Private Sub
Combo131_AfterUpdate()

End Sub
Put the following code between these two lines:
Me!IndexNo = Me!Combo131.Column(1)

> It sounds like you are using a continuous form with a field named
> CatalogNo that is not bound to any field in the record source of the
> form. When you look at the form in design view, does it sau 'Unbound'
> inside the field? If this is the case, you need to make CatlogNo bound.

William Bradley
Come visit us at:
http://www.catholicmissionleaflets.org
Free Rosaries available at the above.

--
William Bradley
Come visit us at:
http://www.catholicmissionleaflets.org
Free Rosaries available at the above.