Combo Box to limit selection

[gleegeld]

Hi,

I have two tables, category and products. I would like to be able to have the user select the category so that the products combo box only list the products for the selected category. This will be used to create an order form in which various products will be selected.

The problem I have now is that I was using both unbound combo boxes so when I make a selection the previous selection is changed (they are all the same).

Any help would be great.

-Thanks
Gleegeld

 

[Toby1kenobe]

Hi Gleegeld

I've been doing some stuff like this and had a few problems along the way. You're question is a bit vague, but you could check the LIMIT TO LIST property of your combos. I had the problem where if one combo was requeried it was blanked.

Let me know if i can help
Toby

 

[gleegeld]

Hi Toby,

I think I must not have been clear with my question. I basically just need to know how to use 2 combo boxes, one would list the category and the second would list all the products that goes with the category.

Ex.
Category Products
Soda Coke
Sprite

I am trying to generate an order form, so I need to be able to make several selections by filtering the products based what was select for the category field.

I hope that is a bit clearer.

-Gleegeld

 

[gilave]

Hi Toby,

Assuming that your tables have the following structure:

Categories Products
CategoryID (pk) ProductID (pk)
CategoryName ProductName
CategoryID (fk)

where the tables are related through the CategoryID field then

the Categories combo box would have the following properties:

Name: cboCategoryID
RowSourceType: Table/Query
RowSource: SELECT CategoryID, CategoryName FROM Categories;
ColumnCount: 2
ColumnWidths: 0"
BoundColumn: 1
ListWidth: Auto

AfterUpdate: Event Procedure:
Me!cboProductID.Requery
-----------------------------------------------------------
and the Products combo box would have the following properties:

Name: cboProductID
RowSourceType: Table/Query
RowSource: SELECT ProductID, ProductName FROM Products WHERE CategoryID = Forms!YourFormName!cboCategoryID;
ColumnCount: 2
ColumnWidths: "0
BoundColumn: 1
ListWidth: Auto

Hope this helps.


Saux.

 

[Luther1978]

Hi Gleegeld,

I think I wanted to do a similar thing to yourself. I found this URL very Helpful.

http://www.mvps.org/access/forms/frm0028.htm

Hope this is helps.

Martin