Inventory Combo Box

[nvisiondesign]

I'm looking to create a combo box with a list of product types say
fruits/vegtables/junk food. After I pick the value I want to be able
to select any of my products under the product types. So with the
selection of fruit I could select oranges/apples and so on. I know I
need to do something with the AfterUpdate values but as of yet I have
been unable to get any macro to work. If possible please point me to a
file where I could view this in action. (major access newbie)

Thank You

 

[Ken Snell \(MVP\)]

See this article on the ACCESS Web:
http://www.mvps.org/access/forms/frm0028.htm

 

[nvisiondesign]

Thank you for your help. I'm using the combo box from within a grid
that I'm using to create PO's. So when i select the product category
in the second item in the list the selection of the first is reset to
show no product.

Any ideas?

 

[Ken Snell \(MVP\)]

You need to give us details about your exact setup.... what are the
properties of the combo boxes (especially their names, row sources, etc.)?
What do you mean by "grid" -- is that dataasheet view?

 

[nvisiondesign]

Below is my current setup, which is based off of the Microsoft access
inventory template. And by grid I do mean data sheet view. When I
select the category for the first product it works fine, and it will
also work if I continue to only add items that are within the same
category, but when I try adding from another category all categories
are switched to the most recently selected category and all products
are removed.

Thank You

Two Combo Boxes
Combo10 - select from categories to narrow product list
ProductID- where product is selected

Row source of Combo10
SELECT [Categories].[CategoryID], [Categories].[CategoryName] FROM
Categories;

AfterUpdate
Private Sub Combo10_AfterUpdate()
Me.ProductID = Null
Me.ProductID.Requery
Me.ProductID = Me.ProductID.ItemData(0)
End Sub

Row source of ProductID
SELECT DISTINCTROW Products.ProductID, Products.ProductName FROM
Products WHERE (([Products]![CategoryID]=[Forms]![Purchase
Orders]![Purchase Orders Subform].[Form]![Combo10]));

 

[Ken Snell \(MVP\)]

Using cascading combo boxes (which is what you're doing -- the second combo
box gets its filter from the first combo box) is not recommended for
datasheet vier or continuous forms view for exactly the reason you're
seeing. In these ACCESS form views, there actually is just one instance of
the combo box being displayed multiple times; thus a change to the row
source of the combo box for "one" record will change the row source for the
combo box in the other records as well.

There are some tricks for how to make such a setup work, but they're a bit
tricky to set up -- and I cannot find my notes about this at the moment. But
it involves using a textbox to display the value for each record, and using
programming to set focus to the combo box instead of the textbox when you
try to "change" or "enter" the value for the current record.

--

Ken Snell
<MS ACCESS MVP>

Below is my current setup, which is based off of the Microsoft access
inventory template. And by grid I do mean data sheet view. When I
select the category for the first product it works fine, and it will
also work if I continue to only add items that are within the same
category, but when I try adding from another category all categories
are switched to the most recently selected category and all products
are removed.

Thank You

Two Combo Boxes
Combo10 - select from categories to narrow product list
ProductID- where product is selected

Row source of Combo10
SELECT [Categories].[CategoryID], [Categories].[CategoryName] FROM
Categories;

AfterUpdate
Private Sub Combo10_AfterUpdate()
Me.ProductID = Null
Me.ProductID.Requery
Me.ProductID = Me.ProductID.ItemData(0)
End Sub

Row source of ProductID
SELECT DISTINCTROW Products.ProductID, Products.ProductName FROM
Products WHERE (([Products]![CategoryID]=[Forms]![Purchase
Orders]![Purchase Orders Subform].[Form]![Combo10]));

You need to give us details about your exact setup.... what are the
properties of the combo boxes (especially their names, row sources,
etc.)?
What do you mean by "grid" -- is that dataasheet view?

 

[Ken Snell \(MVP\)]

Sandra Daigle (MS ACCESS MVP) has a sample database at her website that
shows ways to handle this. See the "Synch Combo Continuous" sample at
http://www.daiglenet.com/msaccess.htm

--

Ken Snell
<MS ACCESS MVP>

Using cascading combo boxes (which is what you're doing -- the second
combo box gets its filter from the first combo box) is not recommended for
datasheet vier or continuous forms view for exactly the reason you're
seeing. In these ACCESS form views, there actually is just one instance of
the combo box being displayed multiple times; thus a change to the row
source of the combo box for "one" record will change the row source for
the combo box in the other records as well.

There are some tricks for how to make such a setup work, but they're a bit
tricky to set up -- and I cannot find my notes about this at the moment.
But it involves using a textbox to display the value for each record, and
using programming to set focus to the combo box instead of the textbox
when you try to "change" or "enter" the value for the current record.

--

Ken Snell
<MS ACCESS MVP>

Below is my current setup, which is based off of the Microsoft access
inventory template. And by grid I do mean data sheet view. When I
select the category for the first product it works fine, and it will
also work if I continue to only add items that are within the same
category, but when I try adding from another category all categories
are switched to the most recently selected category and all products
are removed.

Thank You

Two Combo Boxes
Combo10 - select from categories to narrow product list
ProductID- where product is selected

Row source of Combo10
SELECT [Categories].[CategoryID], [Categories].[CategoryName] FROM
Categories;

AfterUpdate
Private Sub Combo10_AfterUpdate()
Me.ProductID = Null
Me.ProductID.Requery
Me.ProductID = Me.ProductID.ItemData(0)
End Sub

Row source of ProductID
SELECT DISTINCTROW Products.ProductID, Products.ProductName FROM
Products WHERE (([Products]![CategoryID]=[Forms]![Purchase
Orders]![Purchase Orders Subform].[Form]![Combo10]));

You need to give us details about your exact setup.... what are the
properties of the combo boxes (especially their names, row sources,
etc.)?
What do you mean by "grid" -- is that dataasheet view?

--

Ken Snell
<MS ACCESS MVP>

Thank you for your help. I'm using the combo box from within a grid
that I'm using to create PO's. So when i select the product category
in the second item in the list the selection of the first is reset to
show no product.

Any ideas?

Ken Snell (MVP) wrote:
See this article on the ACCESS Web:
http://www.mvps.org/access/forms/frm0028.htm

--

Ken Snell
<MS ACCESS MVP>



I'm looking to create a combo box with a list of product types say
fruits/vegtables/junk food. After I pick the value I want to be
able
to select any of my products under the product types. So with the
selection of fruit I could select oranges/apples and so on. I know
I
need to do something with the AfterUpdate values but as of yet I
have
been unable to get any macro to work. If possible please point me
to a
file where I could view this in action. (major access newbie)

Thank You

 

[Ken Snell \(MVP\)]

And Pat Hartman (MS ACCESS MVP) has graciously provided me with another
sample database that shows another way to handle this issue. I've posted it,
with her permission, at
http://www.cadellsoftware.org/SampleDBs.htm#ContCascadeCbo for you and
others to use.

--

Ken Snell
<MS ACCESS MVP>

Sandra Daigle (MS ACCESS MVP) has a sample database at her website that
shows ways to handle this. See the "Synch Combo Continuous" sample at
http://www.daiglenet.com/msaccess.htm

--

Ken Snell
<MS ACCESS MVP>

Using cascading combo boxes (which is what you're doing -- the second
combo box gets its filter from the first combo box) is not recommended
for datasheet vier or continuous forms view for exactly the reason you're
seeing. In these ACCESS form views, there actually is just one instance
of the combo box being displayed multiple times; thus a change to the row
source of the combo box for "one" record will change the row source for
the combo box in the other records as well.

There are some tricks for how to make such a setup work, but they're a
bit tricky to set up -- and I cannot find my notes about this at the
moment. But it involves using a textbox to display the value for each
record, and using programming to set focus to the combo box instead of
the textbox when you try to "change" or "enter" the value for the current
record.

--

Ken Snell
<MS ACCESS MVP>

Below is my current setup, which is based off of the Microsoft access
inventory template. And by grid I do mean data sheet view. When I
select the category for the first product it works fine, and it will
also work if I continue to only add items that are within the same
category, but when I try adding from another category all categories
are switched to the most recently selected category and all products
are removed.

Thank You

Two Combo Boxes
Combo10 - select from categories to narrow product list
ProductID- where product is selected

Row source of Combo10
SELECT [Categories].[CategoryID], [Categories].[CategoryName] FROM
Categories;

AfterUpdate
Private Sub Combo10_AfterUpdate()
Me.ProductID = Null
Me.ProductID.Requery
Me.ProductID = Me.ProductID.ItemData(0)
End Sub

Row source of ProductID
SELECT DISTINCTROW Products.ProductID, Products.ProductName FROM
Products WHERE (([Products]![CategoryID]=[Forms]![Purchase
Orders]![Purchase Orders Subform].[Form]![Combo10]));


Ken Snell (MVP) wrote:
You need to give us details about your exact setup.... what are the
properties of the combo boxes (especially their names, row sources,
etc.)?
What do you mean by "grid" -- is that dataasheet view?

--

Ken Snell
<MS ACCESS MVP>

Thank you for your help. I'm using the combo box from within a grid
that I'm using to create PO's. So when i select the product category
in the second item in the list the selection of the first is reset to
show no product.

Any ideas?

Ken Snell (MVP) wrote:
See this article on the ACCESS Web:
http://www.mvps.org/access/forms/frm0028.htm

--

Ken Snell
<MS ACCESS MVP>



I'm looking to create a combo box with a list of product types say
fruits/vegtables/junk food. After I pick the value I want to be
able
to select any of my products under the product types. So with the
selection of fruit I could select oranges/apples and so on. I
know I
need to do something with the AfterUpdate values but as of yet I
have
been unable to get any macro to work. If possible please point me
to a
file where I could view this in action. (major access newbie)

Thank You