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]

Re: Combo Box Problem/Question #2

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 ,
IngredientCatlo gNo)
You need a Join table to joint a product with as many or as few ingredients
as you like JnProductionlIn gredient (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" <bradleyw@magma .ca> wrote in message
news:WZqdnVdRDa mIaR7dRVn-jw@magma.ca...[color=blue]
> I have a production table and its accompanying form. There can be up to[/color]
ten[color=blue]
> 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[/color]
catalogue[color=blue]
> 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.[/color]
As[color=blue]
> the field "CatlogNo" was being used in the previous combo box on[/color]
Ingredient[color=blue]
> 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.[/color]

[PC Datasheet]

Re: Combo Box Problem/Question #2

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
resource@pcdata sheet.com

https://www.pcdatasheet.com

"William Bradley" <bradleyw@magma .ca> wrote in message
news:WZqdnVdRDa mIaR7dRVn-jw@magma.ca...[color=blue]
> 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.[/color]

[William Bradley]

Re: Combo Box Problem/Question #2

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_AfterU pdate()

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

[color=blue]
> 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.[/color]

William Bradley
Come visit us at:

http://www.catholicmissionleaflets.org

Free Rosaries available at the above.

[PC Datasheet]

Re: Combo Box Problem/Question #2

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 ,
IngredientCatlo gNo)
You need a Join table to joint a product with as many or as few ingredients
as you like JnProductionlIn gredient (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
InGredientCatlo gNo

TblProductionPr oduct
ProductionProdu ctID
ProductID
ProductDate
etc

TblProductionPr oductInGredient s
ProductionProdu ctInGredientsID
ProductionProdu ctID
InGredientID
QuantityOfInGre dient ???

Create a relationship between ProductId In TblProduct and ProductID In
TblProductionPr oduct. Create a relationship between InGredientID In
TblInGredient and InGredientID In TblProductionPr oductInGredient s. Create a
relationship between ProductionProdu ctID In TblProductionPr oduct and
ProductionProdu ctID In TblProductionPr oductInGredient s.

You then need to build a form/subform system.

The main form will be based on TblProductionPr oduct 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 TblProductionPr oductInGredient s 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:[InGredientCatlo gNo] & " " & [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 ProductionProdu ctID. If
they are blank, click on the three dots at the right.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
resource@pcdata sheet.com

https://www.pcdatasheet.com

"William Bradley" <bradleyw@magma .ca> wrote in message
news:eOmdnYQ1lZ _UgBndRVn-iQ@magma.ca...[color=blue]
> 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_AfterU pdate()
>
> End Sub
> Put the following code between these two lines:
> Me!IndexNo = Me!Combo131.Col umn(1)
>
>[color=green]
> > 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.[/color]
>
> William Bradley
> Come visit us at:
> http://www.catholicmissionleaflets.org
> Free Rosaries available at the above.[/color]

[William Bradley]

Re: Combo Box Problem/Question #2

Phil Stanton wrote:

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

Bill.
[color=blue]
> 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 ,
> IngredientCatlo gNo)
> You need a Join table to joint a product with as many or as few
> ingredients as you like JnProductionlIn gredient (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" <bradleyw@magma .ca> wrote in message
> news:WZqdnVdRDa mIaR7dRVn-jw@magma.ca...[color=green]
>> I have a production table and its accompanying form. There can be up to[/color]
> ten[color=green]
>> 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[/color]
> catalogue[color=green]
>> 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.[/color]
> As[color=green]
>> the field "CatlogNo" was being used in the previous combo box on[/color]
> Ingredient[color=green]
>> 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.[/color][/color]

--
William Bradley
Come visit us at:

http://www.catholicmissionleaflets.org

Free Rosaries available at the above.

[William Bradley]

Re: Combo Box Problem/Question #2

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.

[color=blue]
> 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 ,
> IngredientCatlo gNo)
> You need a Join table to joint a product with as many or as few
> ingredients as you like JnProductionlIn gredient (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
> InGredientCatlo gNo
>
> TblProductionPr oduct
> ProductionProdu ctID
> ProductID
> ProductDate
> etc
>
> TblProductionPr oductInGredient s
> ProductionProdu ctInGredientsID
> ProductionProdu ctID
> InGredientID
> QuantityOfInGre dient ???
>
> Create a relationship between ProductId In TblProduct and ProductID In
> TblProductionPr oduct. Create a relationship between InGredientID In
> TblInGredient and InGredientID In TblProductionPr oductInGredient s. Create
> a relationship between ProductionProdu ctID In TblProductionPr oduct and
> ProductionProdu ctID In TblProductionPr oductInGredient s.
>
> You then need to build a form/subform system.
>
> The main form will be based on TblProductionPr oduct 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 TblProductionPr oductInGredient s 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:[InGredientCatlo gNo] & " " & [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
> ProductionProdu ctID. If they are blank, click on the three dots at the
> right.
>
> --
> PC Datasheet
> Your Resource For Help With Access, Excel And Word Applications
> resource@pcdata sheet.com
> www.pcdatasheet.com
>
>
>
> "William Bradley" <bradleyw@magma .ca> wrote in message
> news:eOmdnYQ1lZ _UgBndRVn-iQ@magma.ca...[color=green]
>> 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_AfterU pdate()
>>
>> End Sub
>> Put the following code between these two lines:
>> Me!IndexNo = Me!Combo131.Col umn(1)
>>
>>[color=darkred]
>> > 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.[/color]
>>
>> William Bradley
>> Come visit us at:
>> http://www.catholicmissionleaflets.org
>> Free Rosaries available at the above.[/color][/color]

--
William Bradley
Come visit us at:

http://www.catholicmissionleaflets.org

Free Rosaries available at the above.